Example usage for java.sql Types SMALLINT

List of usage examples for java.sql Types SMALLINT

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Types
  6. SMALLINT

Introduction

In this page you can find the example usage for java.sql Types SMALLINT.

Prototype

int SMALLINT

To view the source code for java.sql Types SMALLINT.

Click Source Link

Document

The constant in the Java programming language, sometimes referred to as a type code, that identifies the generic SQL type SMALLINT.

Usage

From source file:net.sourceforge.msscodefactory.cffreeswitch.v2_1.CFFswMySql.CFFswMySqlISOLanguageTable.java

public void updateISOLanguage(CFFswAuthorization Authorization, CFFswISOLanguageBuff Buff) {
    final String S_ProcName = "updateISOLanguage";
    ResultSet resultSet = null;/*from   ww w  . j a v  a  2  s .c  om*/
    try {
        short Id = Buff.getRequiredId();
        String ISOCode = Buff.getRequiredISOCode();
        String BaseLanguageCode = Buff.getRequiredBaseLanguageCode();
        Short ISOCountryId = Buff.getOptionalISOCountryId();
        int Revision = Buff.getRequiredRevision();
        Connection cnx = schema.getCnx();
        String sql = "call " + schema.getLowerDbSchemaName() + ".sp_update_iso_lang( ?, ?, ?, ?, ?, ?" + ", "
                + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " )";
        if (stmtUpdateByPKey == null) {
            stmtUpdateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtUpdateByPKey.setString(argIdx++, "ISLN");
        stmtUpdateByPKey.setShort(argIdx++, Id);
        stmtUpdateByPKey.setString(argIdx++, ISOCode);
        stmtUpdateByPKey.setString(argIdx++, BaseLanguageCode);
        if (ISOCountryId != null) {
            stmtUpdateByPKey.setShort(argIdx++, ISOCountryId.shortValue());
        } else {
            stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        stmtUpdateByPKey.setInt(argIdx++, Revision);
        try {
            resultSet = stmtUpdateByPKey.executeQuery();
        } catch (SQLException e) {
            if (e.getErrorCode() != 1329) {
                throw e;
            }
            resultSet = null;
        }
        if ((resultSet != null) && resultSet.next()) {
            CFFswISOLanguageBuff updatedBuff = unpackISOLanguageResultSetToBuff(resultSet);
            if ((resultSet != null) && resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
            }
            Buff.setRequiredISOCode(updatedBuff.getRequiredISOCode());
            Buff.setRequiredBaseLanguageCode(updatedBuff.getRequiredBaseLanguageCode());
            Buff.setOptionalISOCountryId(updatedBuff.getOptionalISOCountryId());
            Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_0.CFAstMySql.CFAstMySqlISOLanguageTable.java

public void updateISOLanguage(CFAstAuthorization Authorization, CFAstISOLanguageBuff Buff) {
    final String S_ProcName = "updateISOLanguage";
    ResultSet resultSet = null;//w w  w . j  av  a  2s .c  o  m
    try {
        short Id = Buff.getRequiredId();
        String ISOCode = Buff.getRequiredISOCode();
        String BaseLanguageCode = Buff.getRequiredBaseLanguageCode();
        Short ISOCountryId = Buff.getOptionalISOCountryId();
        int Revision = Buff.getRequiredRevision();
        Connection cnx = schema.getCnx();
        String sql = "call " + schema.getLowerSchemaDbName() + ".sp_update_iso_lang( ?, ?, ?, ?, ?, ?" + ", "
                + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " )";
        if (stmtUpdateByPKey == null) {
            stmtUpdateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtUpdateByPKey.setString(argIdx++, "ISLN");
        stmtUpdateByPKey.setShort(argIdx++, Id);
        stmtUpdateByPKey.setString(argIdx++, ISOCode);
        stmtUpdateByPKey.setString(argIdx++, BaseLanguageCode);
        if (ISOCountryId != null) {
            stmtUpdateByPKey.setShort(argIdx++, ISOCountryId.shortValue());
        } else {
            stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        stmtUpdateByPKey.setInt(argIdx++, Revision);
        try {
            resultSet = stmtUpdateByPKey.executeQuery();
        } catch (SQLException e) {
            if (e.getErrorCode() != 1329) {
                throw e;
            }
            resultSet = null;
        }
        if ((resultSet != null) && resultSet.next()) {
            CFAstISOLanguageBuff updatedBuff = unpackISOLanguageResultSetToBuff(resultSet);
            if ((resultSet != null) && resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
            }
            Buff.setRequiredISOCode(updatedBuff.getRequiredISOCode());
            Buff.setRequiredBaseLanguageCode(updatedBuff.getRequiredBaseLanguageCode());
            Buff.setOptionalISOCountryId(updatedBuff.getOptionalISOCountryId());
            Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstMySql.CFAstMySqlISOLanguageTable.java

public void updateISOLanguage(CFAstAuthorization Authorization, CFAstISOLanguageBuff Buff) {
    final String S_ProcName = "updateISOLanguage";
    ResultSet resultSet = null;//from  w w w  .  j  ava  2 s.  c  o  m
    try {
        short Id = Buff.getRequiredId();
        String ISOCode = Buff.getRequiredISOCode();
        String BaseLanguageCode = Buff.getRequiredBaseLanguageCode();
        Short ISOCountryId = Buff.getOptionalISOCountryId();
        int Revision = Buff.getRequiredRevision();
        Connection cnx = schema.getCnx();
        String sql = "call " + schema.getLowerDbSchemaName() + ".sp_update_iso_lang( ?, ?, ?, ?, ?, ?" + ", "
                + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " )";
        if (stmtUpdateByPKey == null) {
            stmtUpdateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtUpdateByPKey.setString(argIdx++, "ISLN");
        stmtUpdateByPKey.setShort(argIdx++, Id);
        stmtUpdateByPKey.setString(argIdx++, ISOCode);
        stmtUpdateByPKey.setString(argIdx++, BaseLanguageCode);
        if (ISOCountryId != null) {
            stmtUpdateByPKey.setShort(argIdx++, ISOCountryId.shortValue());
        } else {
            stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        stmtUpdateByPKey.setInt(argIdx++, Revision);
        try {
            resultSet = stmtUpdateByPKey.executeQuery();
        } catch (SQLException e) {
            if (e.getErrorCode() != 1329) {
                throw e;
            }
            resultSet = null;
        }
        if ((resultSet != null) && resultSet.next()) {
            CFAstISOLanguageBuff updatedBuff = unpackISOLanguageResultSetToBuff(resultSet);
            if ((resultSet != null) && resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
            }
            Buff.setRequiredISOCode(updatedBuff.getRequiredISOCode());
            Buff.setRequiredBaseLanguageCode(updatedBuff.getRequiredBaseLanguageCode());
            Buff.setOptionalISOCountryId(updatedBuff.getOptionalISOCountryId());
            Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccMySql.CFAccMySqlISOLanguageTable.java

public void updateISOLanguage(CFAccAuthorization Authorization, CFAccISOLanguageBuff Buff) {
    final String S_ProcName = "updateISOLanguage";
    ResultSet resultSet = null;/*from  w w w  .  jav a 2s .  com*/
    try {
        short Id = Buff.getRequiredId();
        String ISOCode = Buff.getRequiredISOCode();
        String BaseLanguageCode = Buff.getRequiredBaseLanguageCode();
        Short ISOCountryId = Buff.getOptionalISOCountryId();
        int Revision = Buff.getRequiredRevision();
        Connection cnx = schema.getCnx();
        String sql = "call " + schema.getLowerSchemaDbName() + ".sp_update_iso_lang( ?, ?, ?, ?, ?, ?" + ", "
                + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " )";
        if (stmtUpdateByPKey == null) {
            stmtUpdateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtUpdateByPKey.setString(argIdx++, "ISLN");
        stmtUpdateByPKey.setShort(argIdx++, Id);
        stmtUpdateByPKey.setString(argIdx++, ISOCode);
        stmtUpdateByPKey.setString(argIdx++, BaseLanguageCode);
        if (ISOCountryId != null) {
            stmtUpdateByPKey.setShort(argIdx++, ISOCountryId.shortValue());
        } else {
            stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        stmtUpdateByPKey.setInt(argIdx++, Revision);
        try {
            resultSet = stmtUpdateByPKey.executeQuery();
        } catch (SQLException e) {
            if (e.getErrorCode() != 1329) {
                throw e;
            }
            resultSet = null;
        }
        if ((resultSet != null) && resultSet.next()) {
            CFAccISOLanguageBuff updatedBuff = unpackISOLanguageResultSetToBuff(resultSet);
            if ((resultSet != null) && resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
            }
            Buff.setRequiredISOCode(updatedBuff.getRequiredISOCode());
            Buff.setRequiredBaseLanguageCode(updatedBuff.getRequiredBaseLanguageCode());
            Buff.setOptionalISOCountryId(updatedBuff.getOptionalISOCountryId());
            Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfensyntax.v2_2.CFEnSyntaxMySql.CFEnSyntaxMySqlISOLanguageTable.java

public void updateISOLanguage(CFEnSyntaxAuthorization Authorization, CFEnSyntaxISOLanguageBuff Buff) {
    final String S_ProcName = "updateISOLanguage";
    ResultSet resultSet = null;//from w  ww . ja v a2s.  co m
    try {
        short Id = Buff.getRequiredId();
        String ISOCode = Buff.getRequiredISOCode();
        String BaseLanguageCode = Buff.getRequiredBaseLanguageCode();
        Short ISOCountryId = Buff.getOptionalISOCountryId();
        int Revision = Buff.getRequiredRevision();
        Connection cnx = schema.getCnx();
        String sql = "call " + schema.getLowerDbSchemaName() + ".sp_update_iso_lang( ?, ?, ?, ?, ?, ?" + ", "
                + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " )";
        if (stmtUpdateByPKey == null) {
            stmtUpdateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtUpdateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtUpdateByPKey.setString(argIdx++, "ISLN");
        stmtUpdateByPKey.setShort(argIdx++, Id);
        stmtUpdateByPKey.setString(argIdx++, ISOCode);
        stmtUpdateByPKey.setString(argIdx++, BaseLanguageCode);
        if (ISOCountryId != null) {
            stmtUpdateByPKey.setShort(argIdx++, ISOCountryId.shortValue());
        } else {
            stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        stmtUpdateByPKey.setInt(argIdx++, Revision);
        try {
            resultSet = stmtUpdateByPKey.executeQuery();
        } catch (SQLException e) {
            if (e.getErrorCode() != 1329) {
                throw e;
            }
            resultSet = null;
        }
        if ((resultSet != null) && resultSet.next()) {
            CFEnSyntaxISOLanguageBuff updatedBuff = unpackISOLanguageResultSetToBuff(resultSet);
            if ((resultSet != null) && resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
            }
            Buff.setRequiredISOCode(updatedBuff.getRequiredISOCode());
            Buff.setRequiredBaseLanguageCode(updatedBuff.getRequiredBaseLanguageCode());
            Buff.setOptionalISOCountryId(updatedBuff.getOptionalISOCountryId());
            Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:com.mmnaseri.dragonfly.metadata.impl.AnnotationTableMetadataResolver.java

private static int getColumnType(Class<?> javaType, Method method, ColumnMetadata foreignReference) {
    final int dimensions = ReflectionUtils.getArrayDimensions(javaType);
    javaType = ReflectionUtils.mapType(ReflectionUtils.getComponentType(javaType));
    if (dimensions > 1) {
        throw new UnsupportedColumnTypeError("Arrays of dimension > 1 are not supported");
    }/* w ww .j a v  a2s . c o  m*/
    if (Byte.class.equals(javaType) && dimensions == 0) {
        return Types.TINYINT;
    } else if (Short.class.equals(javaType)) {
        return Types.SMALLINT;
    } else if (Integer.class.equals(javaType)) {
        return Types.INTEGER;
    } else if (Long.class.equals(javaType)) {
        return Types.BIGINT;
    } else if (Float.class.equals(javaType)) {
        return Types.FLOAT;
    } else if (Double.class.equals(javaType)) {
        return Types.DOUBLE;
    } else if (BigDecimal.class.equals(javaType)) {
        return Types.DECIMAL;
    } else if (BigInteger.class.equals(javaType)) {
        return Types.NUMERIC;
    } else if (Character.class.equals(javaType)) {
        return Types.CHAR;
    } else if (String.class.equals(javaType) || Class.class.equals(javaType)) {
        if (method != null && method.isAnnotationPresent(Column.class)
                && method.getAnnotation(Column.class).length() > 0) {
            return Types.VARCHAR;
        } else {
            return Types.LONGVARCHAR;
        }
    } else if (Date.class.isAssignableFrom(javaType)) {
        if (javaType.equals(java.sql.Date.class)) {
            return Types.DATE;
        } else if (javaType.equals(Time.class)) {
            return Types.TIME;
        } else if (javaType.equals(java.sql.Timestamp.class)) {
            return Types.TIMESTAMP;
        }
        final TemporalType temporalType = method != null && method.isAnnotationPresent(Temporal.class)
                ? method.getAnnotation(Temporal.class).value()
                : null;
        return (temporalType == null || temporalType.equals(TemporalType.TIMESTAMP)) ? Types.TIMESTAMP
                : (temporalType.equals(TemporalType.DATE) ? Types.DATE : Types.TIME);
    } else if (Byte.class.equals(javaType) && dimensions > 0) {
        return Types.VARBINARY;
    } else if (Enum.class.isAssignableFrom(javaType)) {
        return Types.VARCHAR;
    } else if (Boolean.class.equals(javaType)) {
        return Types.BOOLEAN;
    } else if (Collection.class.isAssignableFrom(javaType)
            && method.isAnnotationPresent(BasicCollection.class)) {
        return Types.LONGVARCHAR;
    }
    if (foreignReference != null) {
        return Integer.MIN_VALUE;
    }
    return Types.LONGVARCHAR;
}

From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectPostgreSQL.java

@Override
public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException {
    if (elements == null || elements.length == 0) {
        return null;
    }/*from  ww w  .j  a  va2  s  .com*/
    String typeName;
    switch (type) {
    case Types.VARCHAR:
        typeName = "varchar";
        break;
    case Types.CLOB:
        typeName = "text";
        break;
    case Types.BIT:
        typeName = "bool";
        break;
    case Types.BIGINT:
        typeName = "int8";
        break;
    case Types.DOUBLE:
        typeName = "float8";
        break;
    case Types.TIMESTAMP:
        typeName = "timestamp";
        break;
    case Types.SMALLINT:
        typeName = "int2";
        break;
    case Types.INTEGER:
        typeName = "int4";
        break;
    case Types.OTHER: // id
        switch (idType) {
        case VARCHAR:
            typeName = "varchar";
            break;
        case UUID:
            typeName = "uuid";
            break;
        case SEQUENCE:
            typeName = "int8";
            break;
        default:
            throw new AssertionError("Unknown id type: " + idType);
        }
        break;
    default:
        throw new AssertionError("Unknown type: " + type);
    }
    return connection.createArrayOf(typeName, elements);
}

From source file:net.sourceforge.msscodefactory.cfcrm.v2_0.CFCrmDb2LUW.CFCrmDb2LUWAddressTable.java

public CFCrmAddressBuff[] readBuffByCountryIdx(CFCrmAuthorization Authorization, Short CountryId) {
    final String S_ProcName = "readBuffByCountryIdx";
    ResultSet resultSet = null;/*from   www  .  j  ava 2 s .c o  m*/
    try {
        Connection cnx = schema.getCnx();
        final String sql = "CALL sp_read_address_by_countryidx( ?, ?, ?, ?, ?" + ", " + "?" + " )";
        if (stmtReadBuffByCountryIdx == null) {
            stmtReadBuffByCountryIdx = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtReadBuffByCountryIdx.setLong(argIdx++,
                (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByCountryIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadBuffByCountryIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadBuffByCountryIdx.setLong(argIdx++,
                (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByCountryIdx.setLong(argIdx++,
                (Authorization == null) ? 0 : Authorization.getSecTenantId());
        if (CountryId != null) {
            stmtReadBuffByCountryIdx.setShort(argIdx++, CountryId.shortValue());
        } else {
            stmtReadBuffByCountryIdx.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        resultSet = stmtReadBuffByCountryIdx.executeQuery();
        List<CFCrmAddressBuff> buffList = new LinkedList<CFCrmAddressBuff>();
        while (resultSet.next()) {
            CFCrmAddressBuff buff = unpackAddressResultSetToBuff(resultSet);
            buffList.add(buff);
        }
        int idx = 0;
        CFCrmAddressBuff[] retBuff = new CFCrmAddressBuff[buffList.size()];
        Iterator<CFCrmAddressBuff> iter = buffList.iterator();
        while (iter.hasNext()) {
            retBuff[idx++] = iter.next();
        }
        return (retBuff);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:com.flexive.core.storage.GenericDivisionImporter.java

/**
 * Import data from a zip archive to a database table
 *
 * @param stmt               statement to use
 * @param zip                zip archive containing the zip entry
 * @param ze                 zip entry within the archive
 * @param xpath              xpath containing the entries to import
 * @param table              name of the table
 * @param executeInsertPhase execute the insert phase?
 * @param executeUpdatePhase execute the update phase?
 * @param updateColumns      columns that should be set to <code>null</code> in a first pass (insert)
 *                           and updated to the provided values in a second pass (update),
 *                           columns that should be used in the where clause have to be prefixed
 *                           with "KEY:", to assign a default value use the expression "columnname:default value",
 *                           if the default value is "@", it will be a negative counter starting at 0, decreasing.
 *                           If the default value starts with "%", it will be set to the column following the "%"
 *                           character in the first pass
 * @throws Exception on errors/*from   w ww.j a  v  a2  s.  c  o  m*/
 */
protected void importTable(Statement stmt, final ZipFile zip, final ZipEntry ze, final String xpath,
        final String table, final boolean executeInsertPhase, final boolean executeUpdatePhase,
        final String... updateColumns) throws Exception {
    //analyze the table
    final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " WHERE 1=2");
    StringBuilder sbInsert = new StringBuilder(500);
    StringBuilder sbUpdate = updateColumns.length > 0 ? new StringBuilder(500) : null;
    if (rs == null)
        throw new IllegalArgumentException("Can not analyze table [" + table + "]!");
    sbInsert.append("INSERT INTO ").append(table).append(" (");
    final ResultSetMetaData md = rs.getMetaData();
    final Map<String, ColumnInfo> updateClauseColumns = updateColumns.length > 0
            ? new HashMap<String, ColumnInfo>(md.getColumnCount())
            : null;
    final Map<String, ColumnInfo> updateSetColumns = updateColumns.length > 0
            ? new LinkedHashMap<String, ColumnInfo>(md.getColumnCount())
            : null;
    final Map<String, String> presetColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null;
    //preset to a referenced column (%column syntax)
    final Map<String, String> presetRefColumns = updateColumns.length > 0 ? new HashMap<String, String>(10)
            : null;
    final Map<String, Integer> counters = updateColumns.length > 0 ? new HashMap<String, Integer>(10) : null;
    final Map<String, ColumnInfo> insertColumns = new HashMap<String, ColumnInfo>(
            md.getColumnCount() + (counters != null ? counters.size() : 0));
    int insertIndex = 1;
    int updateSetIndex = 1;
    int updateClauseIndex = 1;
    boolean first = true;
    for (int i = 0; i < md.getColumnCount(); i++) {
        final String currCol = md.getColumnName(i + 1).toLowerCase();
        if (updateColumns.length > 0) {
            boolean abort = false;
            for (String col : updateColumns) {
                if (col.indexOf(':') > 0 && !col.startsWith("KEY:")) {
                    String value = col.substring(col.indexOf(':') + 1);
                    col = col.substring(0, col.indexOf(':'));
                    if ("@".equals(value)) {
                        if (currCol.equalsIgnoreCase(col)) {
                            counters.put(col, 0);
                            insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++));
                            sbInsert.append(',').append(currCol);
                        }
                    } else if (value.startsWith("%")) {
                        if (currCol.equalsIgnoreCase(col)) {
                            presetRefColumns.put(col, value.substring(1));
                            insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++));
                            sbInsert.append(',').append(currCol);
                            //                                System.out.println("==> adding presetRefColumn "+col+" with value of "+value.substring(1));
                        }
                    } else if (!presetColumns.containsKey(col))
                        presetColumns.put(col, value);
                }
                if (currCol.equalsIgnoreCase(col)) {
                    abort = true;
                    updateSetColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), updateSetIndex++));
                    break;
                }
            }
            if (abort)
                continue;
        }
        if (first) {
            first = false;
        } else
            sbInsert.append(',');
        sbInsert.append(currCol);
        insertColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), insertIndex++));
    }
    if (updateColumns.length > 0 && executeUpdatePhase) {
        sbUpdate.append("UPDATE ").append(table).append(" SET ");
        int counter = 0;
        for (String updateColumn : updateSetColumns.keySet()) {
            if (counter++ > 0)
                sbUpdate.append(',');
            sbUpdate.append(updateColumn).append("=?");
        }
        sbUpdate.append(" WHERE ");
        boolean hasKeyColumn = false;
        for (String col : updateColumns) {
            if (!col.startsWith("KEY:"))
                continue;
            hasKeyColumn = true;
            String keyCol = col.substring(4);
            for (int i = 0; i < md.getColumnCount(); i++) {
                if (!md.getColumnName(i + 1).equalsIgnoreCase(keyCol))
                    continue;
                updateClauseColumns.put(keyCol, new ColumnInfo(md.getColumnType(i + 1), updateClauseIndex++));
                sbUpdate.append(keyCol).append("=? AND ");
                break;
            }

        }
        if (!hasKeyColumn)
            throw new IllegalArgumentException("Update columns require a KEY!");
        sbUpdate.delete(sbUpdate.length() - 5, sbUpdate.length()); //remove trailing " AND "
        //"shift" clause indices
        for (String col : updateClauseColumns.keySet()) {
            GenericDivisionImporter.ColumnInfo ci = updateClauseColumns.get(col);
            ci.index += (updateSetIndex - 1);
        }
    }
    if (presetColumns != null) {
        for (String key : presetColumns.keySet())
            sbInsert.append(',').append(key);
    }
    sbInsert.append(")VALUES(");
    for (int i = 0; i < insertColumns.size(); i++) {
        if (i > 0)
            sbInsert.append(',');
        sbInsert.append('?');
    }
    if (presetColumns != null) {
        for (String key : presetColumns.keySet())
            sbInsert.append(',').append(presetColumns.get(key));
    }
    sbInsert.append(')');
    if (DBG) {
        LOG.info("Insert statement:\n" + sbInsert.toString());
        if (updateColumns.length > 0)
            LOG.info("Update statement:\n" + sbUpdate.toString());
    }
    //build a map containing all nodes that require attributes
    //this allows for matching simple xpath queries like "flatstorages/storage[@name='FX_FLAT_STORAGE']/data"
    final Map<String, List<String>> queryAttributes = new HashMap<String, List<String>>(5);
    for (String pElem : xpath.split("/")) {
        if (!(pElem.indexOf('@') > 0 && pElem.indexOf('[') > 0))
            continue;
        List<String> att = new ArrayList<String>(5);
        for (String pAtt : pElem.split("@")) {
            if (!(pAtt.indexOf('=') > 0))
                continue;
            att.add(pAtt.substring(0, pAtt.indexOf('=')));
        }
        queryAttributes.put(pElem.substring(0, pElem.indexOf('[')), att);
    }
    final PreparedStatement psInsert = stmt.getConnection().prepareStatement(sbInsert.toString());
    final PreparedStatement psUpdate = updateColumns.length > 0 && executeUpdatePhase
            ? stmt.getConnection().prepareStatement(sbUpdate.toString())
            : null;
    try {
        final SAXParser parser = SAXParserFactory.newInstance().newSAXParser();
        final DefaultHandler handler = new DefaultHandler() {
            private String currentElement = null;
            private Map<String, String> data = new HashMap<String, String>(10);
            private StringBuilder sbData = new StringBuilder(10000);
            boolean inTag = false;
            boolean inElement = false;
            int counter;
            List<String> path = new ArrayList<String>(10);
            StringBuilder currPath = new StringBuilder(100);
            boolean insertMode = true;

            /**
             * {@inheritDoc}
             */
            @Override
            public void startDocument() throws SAXException {
                counter = 0;
                inTag = false;
                inElement = false;
                path.clear();
                currPath.setLength(0);
                sbData.setLength(0);
                data.clear();
                currentElement = null;
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void processingInstruction(String target, String data) throws SAXException {
                if (target != null && target.startsWith("fx_")) {
                    if (target.equals("fx_mode"))
                        insertMode = "insert".equals(data);
                } else
                    super.processingInstruction(target, data);
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void endDocument() throws SAXException {
                if (insertMode)
                    LOG.info("Imported [" + counter + "] entries into [" + table + "] for xpath [" + xpath
                            + "]");
                else
                    LOG.info("Updated [" + counter + "] entries in [" + table + "] for xpath [" + xpath + "]");
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void startElement(String uri, String localName, String qName, Attributes attributes)
                    throws SAXException {
                pushPath(qName, attributes);
                if (currPath.toString().equals(xpath)) {
                    inTag = true;
                    data.clear();
                    for (int i = 0; i < attributes.getLength(); i++) {
                        String name = attributes.getLocalName(i);
                        if (StringUtils.isEmpty(name))
                            name = attributes.getQName(i);
                        data.put(name, attributes.getValue(i));
                    }
                } else {
                    currentElement = qName;
                }
                inElement = true;
                sbData.setLength(0);
            }

            /**
             * Push a path element from the stack
             *
             * @param qName element name to push
             * @param att attributes
             */
            private void pushPath(String qName, Attributes att) {
                if (att.getLength() > 0 && queryAttributes.containsKey(qName)) {
                    String curr = qName + "[";
                    boolean first = true;
                    final List<String> attList = queryAttributes.get(qName);
                    for (int i = 0; i < att.getLength(); i++) {
                        if (!attList.contains(att.getQName(i)))
                            continue;
                        if (first)
                            first = false;
                        else
                            curr += ',';
                        curr += "@" + att.getQName(i) + "='" + att.getValue(i) + "'";
                    }
                    curr += ']';
                    path.add(curr);
                } else
                    path.add(qName);
                buildPath();
            }

            /**
             * Pop the top path element from the stack
             */
            private void popPath() {
                path.remove(path.size() - 1);
                buildPath();
            }

            /**
             * Rebuild the current path
             */
            private synchronized void buildPath() {
                currPath.setLength(0);
                for (String s : path)
                    currPath.append(s).append('/');
                if (currPath.length() > 1)
                    currPath.delete(currPath.length() - 1, currPath.length());
                //                    System.out.println("currPath: " + currPath);
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void endElement(String uri, String localName, String qName) throws SAXException {
                if (currPath.toString().equals(xpath)) {
                    if (DBG)
                        LOG.info("Insert [" + xpath + "]: [" + data + "]");
                    inTag = false;
                    try {
                        if (insertMode) {
                            if (executeInsertPhase) {
                                processColumnSet(insertColumns, psInsert);
                                counter += psInsert.executeUpdate();
                            }
                        } else {
                            if (executeUpdatePhase) {
                                if (processColumnSet(updateSetColumns, psUpdate)) {
                                    processColumnSet(updateClauseColumns, psUpdate);
                                    counter += psUpdate.executeUpdate();
                                }
                            }
                        }
                    } catch (SQLException e) {
                        throw new SAXException(e);
                    } catch (ParseException e) {
                        throw new SAXException(e);
                    }
                } else {
                    if (inTag) {
                        data.put(currentElement, sbData.toString());
                    }
                    currentElement = null;
                }
                popPath();
                inElement = false;
                sbData.setLength(0);
            }

            /**
             * Process a column set
             *
             * @param columns the columns to process
             * @param ps prepared statement to use
             * @return if data other than <code>null</code> has been set
             * @throws SQLException on errors
             * @throws ParseException on date/time conversion errors
             */
            private boolean processColumnSet(Map<String, ColumnInfo> columns, PreparedStatement ps)
                    throws SQLException, ParseException {
                boolean dataSet = false;
                for (String col : columns.keySet()) {
                    ColumnInfo ci = columns.get(col);
                    String value = data.get(col);
                    if (insertMode && counters != null && counters.get(col) != null) {
                        final int newVal = counters.get(col) - 1;
                        value = String.valueOf(newVal);
                        counters.put(col, newVal);
                        //                            System.out.println("new value for " + col + ": " + newVal);
                    }
                    if (insertMode && presetRefColumns != null && presetRefColumns.get(col) != null) {
                        value = data.get(presetRefColumns.get(col));
                        //                            System.out.println("Set presetRefColumn for "+col+" to ["+value+"] from column ["+presetRefColumns.get(col)+"]");
                    }

                    if (value == null)
                        ps.setNull(ci.index, ci.columnType);
                    else {
                        dataSet = true;
                        switch (ci.columnType) {
                        case Types.BIGINT:
                        case Types.NUMERIC:
                            if (DBG)
                                LOG.info("BigInt " + ci.index + "->" + new BigDecimal(value));
                            ps.setBigDecimal(ci.index, new BigDecimal(value));
                            break;
                        case java.sql.Types.DOUBLE:
                            if (DBG)
                                LOG.info("Double " + ci.index + "->" + Double.parseDouble(value));
                            ps.setDouble(ci.index, Double.parseDouble(value));
                            break;
                        case java.sql.Types.FLOAT:
                        case java.sql.Types.REAL:
                            if (DBG)
                                LOG.info("Float " + ci.index + "->" + Float.parseFloat(value));
                            ps.setFloat(ci.index, Float.parseFloat(value));
                            break;
                        case java.sql.Types.TIMESTAMP:
                        case java.sql.Types.DATE:
                            if (DBG)
                                LOG.info("Timestamp/Date " + ci.index + "->"
                                        + FxFormatUtils.getDateTimeFormat().parse(value));
                            ps.setTimestamp(ci.index,
                                    new Timestamp(FxFormatUtils.getDateTimeFormat().parse(value).getTime()));
                            break;
                        case Types.TINYINT:
                        case Types.SMALLINT:
                            if (DBG)
                                LOG.info("Integer " + ci.index + "->" + Integer.valueOf(value));
                            ps.setInt(ci.index, Integer.valueOf(value));
                            break;
                        case Types.INTEGER:
                        case Types.DECIMAL:
                            try {
                                if (DBG)
                                    LOG.info("Long " + ci.index + "->" + Long.valueOf(value));
                                ps.setLong(ci.index, Long.valueOf(value));
                            } catch (NumberFormatException e) {
                                //Fallback (temporary) for H2 if the reported long is a big decimal (tree...)
                                ps.setBigDecimal(ci.index, new BigDecimal(value));
                            }
                            break;
                        case Types.BIT:
                        case Types.CHAR:
                        case Types.BOOLEAN:
                            if (DBG)
                                LOG.info("Boolean " + ci.index + "->" + value);
                            if ("1".equals(value) || "true".equals(value))
                                ps.setBoolean(ci.index, true);
                            else
                                ps.setBoolean(ci.index, false);
                            break;
                        case Types.LONGVARBINARY:
                        case Types.VARBINARY:
                        case Types.BLOB:
                        case Types.BINARY:
                            ZipEntry bin = zip.getEntry(value);
                            if (bin == null) {
                                LOG.error("Failed to lookup binary [" + value + "]!");
                                ps.setNull(ci.index, ci.columnType);
                                break;
                            }
                            try {
                                ps.setBinaryStream(ci.index, zip.getInputStream(bin), (int) bin.getSize());
                            } catch (IOException e) {
                                LOG.error("IOException importing binary [" + value + "]: " + e.getMessage(), e);
                            }
                            break;
                        case Types.CLOB:
                        case Types.LONGVARCHAR:
                        case Types.VARCHAR:
                        case SQL_LONGNVARCHAR:
                        case SQL_NCHAR:
                        case SQL_NCLOB:
                        case SQL_NVARCHAR:
                            if (DBG)
                                LOG.info("String " + ci.index + "->" + value);
                            ps.setString(ci.index, value);
                            break;
                        default:
                            LOG.warn("Unhandled type [" + ci.columnType + "] for column [" + col + "]");
                        }
                    }
                }
                return dataSet;
            }

            /**
             * {@inheritDoc}
             */
            @Override
            public void characters(char[] ch, int start, int length) throws SAXException {
                if (inElement)
                    sbData.append(ch, start, length);
            }

        };
        handler.processingInstruction("fx_mode", "insert");
        parser.parse(zip.getInputStream(ze), handler);
        if (updateColumns.length > 0 && executeUpdatePhase) {
            handler.processingInstruction("fx_mode", "update");
            parser.parse(zip.getInputStream(ze), handler);
        }
    } finally {
        Database.closeObjects(GenericDivisionImporter.class, psInsert, psUpdate);
    }
}

From source file:net.sourceforge.msscodefactory.cfcrm.v2_1.CFCrmDb2LUW.CFCrmDb2LUWContactTable.java

public CFCrmContactBuff[] readBuffByTimezoneIdx(CFCrmAuthorization Authorization, Short ISOTimezoneId) {
    final String S_ProcName = "readBuffByTimezoneIdx";
    ResultSet resultSet = null;/*from   w  w w.  j  a va2  s .  c  o m*/
    try {
        Connection cnx = schema.getCnx();
        final String sql = "CALL sp_read_contact_by_timezoneidx( ?, ?, ?, ?, ?" + ", " + "?" + " )";
        if (stmtReadBuffByTimezoneIdx == null) {
            stmtReadBuffByTimezoneIdx = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtReadBuffByTimezoneIdx.setLong(argIdx++,
                (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByTimezoneIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadBuffByTimezoneIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadBuffByTimezoneIdx.setLong(argIdx++,
                (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByTimezoneIdx.setLong(argIdx++,
                (Authorization == null) ? 0 : Authorization.getSecTenantId());
        if (ISOTimezoneId != null) {
            stmtReadBuffByTimezoneIdx.setShort(argIdx++, ISOTimezoneId.shortValue());
        } else {
            stmtReadBuffByTimezoneIdx.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        resultSet = stmtReadBuffByTimezoneIdx.executeQuery();
        List<CFCrmContactBuff> buffList = new LinkedList<CFCrmContactBuff>();
        while (resultSet.next()) {
            CFCrmContactBuff buff = unpackContactResultSetToBuff(resultSet);
            buffList.add(buff);
        }
        int idx = 0;
        CFCrmContactBuff[] retBuff = new CFCrmContactBuff[buffList.size()];
        Iterator<CFCrmContactBuff> iter = buffList.iterator();
        while (iter.hasNext()) {
            retBuff[idx++] = iter.next();
        }
        return (retBuff);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}