Example usage for java.sql ResultSetMetaData getColumnType

List of usage examples for java.sql ResultSetMetaData getColumnType

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSetMetaData
  6. getColumnType

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getColumnType.

Prototype

int getColumnType(int column) throws SQLException;

Source Link

Document

Retrieves the designated column's SQL type.

Usage

From source file:db.migration.V023__UpdateOrganisationToimipisteKoodi.java

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    LOG.info("migrate()...");

    // Get all organisations
    List<Map> resultSet = jdbcTemplate.query("SELECT * FROM organisaatio o", new RowMapper<Map>() {
        @Override/*from w w  w  .j  a  v  a  2  s .  c  o m*/
        public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
            Map r = new HashMap<String, Object>();

            ResultSetMetaData metadata = rs.getMetaData();
            for (int i = 1; i <= metadata.getColumnCount(); i++) {
                String cname = metadata.getColumnName(i);
                int ctype = metadata.getColumnType(i);

                switch (ctype) {
                case Types.VARCHAR:
                    r.put(cname, rs.getString(cname));
                    break;

                default:
                    break;
                }
            }

            LOG.debug("  read from db : org = {}", r);

            _organisations.put((String) r.get("oid"), r);
            return r;
        }
    });

    // Generate and update initial values for toimipistekoodis
    for (Map org : resultSet) {
        if (isToimipiste(org, jdbcTemplate)) {
            String tpKoodi = calculateToimipisteKoodi(org, jdbcTemplate);
            updateToimipisteKoodi(org, tpKoodi, jdbcTemplate);
        }
    }

    LOG.info("  Processed {} organisations, updated {} Opetuspistes", _organisations.size(), _numUpdated);

    LOG.info("migrate()... done.");
}

From source file:com.chiorichan.database.DatabaseEngine.java

public static Map<String, Object> convertRow(ResultSet rs) throws SQLException {
    Map<String, Object> result = Maps.newLinkedHashMap();
    ResultSetMetaData rsmd = rs.getMetaData();

    int numColumns = rsmd.getColumnCount();

    for (int i = 1; i < numColumns + 1; i++) {
        String columnName = rsmd.getColumnName(i);

        // Loader.getLogger().info( "Column: " + columnName + " <-> " + rsmd.getColumnTypeName( i ) );

        if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
            result.put(columnName, rs.getArray(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BIT) // Sometimes tinyints are read as bits
        {//w w w  .  java 2s. com
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
            result.put(columnName, rs.getBoolean(columnName));
        } else if (rsmd.getColumnTypeName(i).contains("BLOB")
                || rsmd.getColumnType(i) == java.sql.Types.BINARY) {
            // BLOG = Max Length 65,535. Recommended that you use a LONGBLOG.
            byte[] bytes = rs.getBytes(columnName);
            result.put(columnName, bytes);
            /*
             * try
             * {
             * result.put( columnName, new String( bytes, "ISO-8859-1" ) );
             * }
             * catch ( UnsupportedEncodingException e )
             * {
             * e.printStackTrace();
             * }
             */
        } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
            result.put(columnName, rs.getDouble(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
            result.put(columnName, rs.getFloat(columnName));
        } else if (rsmd.getColumnTypeName(i).equals("INT")) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
            result.put(columnName, rs.getNString(columnName));
        } else if (rsmd.getColumnTypeName(i).equals("VARCHAR")) {
            result.put(columnName, rs.getString(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
            result.put(columnName, rs.getDate(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
            result.put(columnName, rs.getTimestamp(columnName));
        } else {
            result.put(columnName, rs.getObject(columnName));
        }
    }

    return result;
}

From source file:net.sf.jdbcwrappers.trim.TrimmingResultSetInvocationHandler.java

private void fetchCharColumns() throws SQLException {
    if (charColumns == null) {
        ResultSetMetaData metadata = target.getMetaData();
        int columnCount = metadata.getColumnCount();
        charColumns = new HashSet<String>();
        isCharColumn = new boolean[columnCount];
        for (int i = 1; i <= columnCount; i++) {
            if (metadata.getColumnType(i) == Types.CHAR) {
                charColumns.add(metadata.getColumnLabel(i).toUpperCase());
                isCharColumn[i - 1] = true;
            }/*from   w ww  . ja v a  2 s  .  c  o  m*/
        }
        if (LOG.isDebugEnabled()) {
            LOG.debug("CHAR columns: " + charColumns);
        }
    }
}

From source file:net.sf.jdbcwrappers.trim.TrimmingResultSetWrapper.java

private void fetchCharColumns() throws SQLException {
    if (charColumns == null) {
        ResultSetMetaData metadata = getMetaData();
        int columnCount = metadata.getColumnCount();
        charColumns = new HashSet<String>();
        isCharColumn = new boolean[columnCount];
        for (int i = 1; i <= columnCount; i++) {
            if (metadata.getColumnType(i) == Types.CHAR) {
                charColumns.add(metadata.getColumnName(i).toUpperCase());
                isCharColumn[i - 1] = true;
            }//from www  .java2  s  .co  m
        }
        if (log.isDebugEnabled()) {
            log.debug("CHAR columns: " + charColumns);
        }
    }
}

From source file:com.opencsv.ResultSetHelperService.java

@Override
public String[] getColumnValues(ResultSet rs, boolean trim, String dateFormatString, String timeFormatString)
        throws SQLException, IOException {
    ResultSetMetaData metadata = rs.getMetaData();
    String[] valueArray = new String[metadata.getColumnCount()];
    for (int i = 1; i <= metadata.getColumnCount(); i++) {
        valueArray[i - 1] = getColumnValue(rs, metadata.getColumnType(i), i, trim, dateFormatString,
                timeFormatString);//from w ww.  ja  v  a 2  s . c  o  m
    }
    return valueArray;
}

From source file:org.glom.web.server.database.RelatedListNavigation.java

public NavigationRecord getNavigationRecord(final TypedDataItem primaryKeyValue) {

    if (portal == null) {
        Log.error(documentID, tableName,
                "The related list navigation cannot be determined because the LayoutItemPortal has not been found.");
        return null;
    }/*from ww  w  . j a va 2 s.c o  m*/

    if (primaryKeyValue == null) {
        Log.error(documentID, tableName,
                "The related list navigation cannot be determined because the primaryKeyValue is null.");
        return null;
    }

    final TableToViewDetails navigationTable = document.getPortalSuitableTableToViewDetails(portal);
    if (navigationTable == null) {
        Log.error(documentID, tableName,
                "The related list navigation cannot cannot be determined because the navigation table details are empty.");
        return null;
    }

    if (StringUtils.isEmpty(navigationTable.tableName)) {
        Log.error(documentID, tableName,
                "The related list navigation cannot cannot be determined because the navigation table name is empty.");
        return null;
    }

    // Get the primary key of that table:
    final Field navigationTablePrimaryKey = document.getTablePrimaryKeyField(navigationTable.tableName);

    // Build a layout item to get the field's value:
    final LayoutItemField navigationRelationshipItem = new LayoutItemField();
    navigationRelationshipItem.setName(navigationTablePrimaryKey.getName());
    navigationRelationshipItem.setFullFieldDetails(navigationTablePrimaryKey);
    if (navigationTable.usesRelationship != null) {
        navigationRelationshipItem.setRelationship(navigationTable.usesRelationship.getRelationship());
        navigationRelationshipItem
                .setRelatedRelationship(navigationTable.usesRelationship.getRelatedRelationship());
    }

    // Get the value of the navigation related primary key:
    final List<LayoutItemField> fieldsToGet = new ArrayList<LayoutItemField>();
    fieldsToGet.add(navigationRelationshipItem);

    // For instance "invoice_line_id" if this is a portal to an "invoice_lines" table:
    final String relatedTableName = portal.getTableUsed("" /* not relevant */);
    final Field primaryKeyField = document.getTablePrimaryKeyField(relatedTableName);
    if (primaryKeyField == null) {
        Log.error(documentID, tableName,
                "The related table's primary key field could not be found, for related table "
                        + relatedTableName);
        return null;
    }

    final NavigationRecord navigationRecord = new NavigationRecord();
    String query = null;
    ResultSet rs = null;
    try {
        if (primaryKeyValue != null) {

            // Make sure that the value knows its actual type,
            // in case it was received via a URL parameter as a string representation:
            Utils.transformUnknownToActualType(primaryKeyValue, primaryKeyField.getGlomType());

            query = SqlUtils.buildSqlSelectWithKey(relatedTableName, fieldsToGet, primaryKeyField,
                    primaryKeyValue, document.getSqlDialect());

            rs = SqlUtils.executeQuery(cpds, query);

            // Set the output parameters:
            navigationRecord.setTableName(navigationTable.tableName);

            rs.next();
            final TypedDataItem navigationTablePrimaryKeyValue = new TypedDataItem();
            final ResultSetMetaData rsMetaData = rs.getMetaData();
            final int queryReturnValueType = rsMetaData.getColumnType(1);
            switch (navigationTablePrimaryKey.getGlomType()) {
            case TYPE_NUMERIC:
                if (queryReturnValueType == java.sql.Types.NUMERIC) {
                    navigationTablePrimaryKeyValue.setNumber(rs.getDouble(1));
                } else {
                    logNavigationTablePrimaryKeyTypeMismatchError(Field.GlomFieldType.TYPE_NUMERIC,
                            rsMetaData.getColumnTypeName(1));
                }
                break;
            case TYPE_TEXT:
                if (queryReturnValueType == java.sql.Types.VARCHAR) {
                    navigationTablePrimaryKeyValue.setText(rs.getString(1));
                } else {
                    logNavigationTablePrimaryKeyTypeMismatchError(Field.GlomFieldType.TYPE_TEXT,
                            rsMetaData.getColumnTypeName(1));
                }
                break;
            default:
                Log.error(documentID, tableName,
                        "Unsupported java.sql.Type: " + rsMetaData.getColumnTypeName(1));
                Log.error(documentID, tableName,
                        "The navigation table primary key value will not be created. This is a bug.");
                break;
            }

            // The value is empty when there there is no record to match the key in the related table:
            // For instance, if an invoice lines record mentions a product id, but the product does not exist in the
            // products table.
            if (navigationTablePrimaryKeyValue.isEmpty()) {
                Log.info(documentID, tableName, "SQL query returned empty primary key for navigation to the "
                        + navigationTable.tableName + "table. Navigation may not work correctly");
                navigationRecord.setPrimaryKeyValue(null);
            } else {
                navigationRecord.setPrimaryKeyValue(navigationTablePrimaryKeyValue);
            }
        }
    } catch (final SQLException e) {
        Log.error(documentID, tableName, "Error executing database query: " + query, e);
        // TODO: somehow notify user of problem
        return null;
    } finally {
        // cleanup everything that has been used
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (final Exception e) {
            Log.error(documentID, tableName,
                    "Error closing database resources. Subsequent database queries may not work.", e);
        }
    }

    return navigationRecord;
}

From source file:com.xtesoft.xtecuannet.framework.templater.filler.utils.SQLScanner.java

public List<SQLField> getSQLFields(String tableName) {
    List<SQLField> fields = new ArrayList<SQLField>(0);
    PreparedStatement psta = null;
    try {//w w w.j a  va 2 s . c o  m
        logger.info("Processing table: " + tableName);

        psta = getConnection().prepareStatement("select top 1 * from " + tableName);
        ResultSet rset = psta.executeQuery();
        ResultSetMetaData metadata = rset.getMetaData();
        int columnCount = metadata.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            SQLField field = new SQLField(metadata.getColumnName(i), metadata.getColumnType(i));
            fields.add(field);
        }

        rset.close();
        psta.close();

    } catch (Exception e) {
        logger.error("Error getting fields for table: " + tableName, e);
    }

    return fields;
}

From source file:ch.cern.db.flume.sink.kite.util.InferSchemaFromTable.java

public Schema getSchema() throws SQLException {

    FieldAssembler<Schema> builder = SchemaBuilder.record("log").fields();

    Connection connection = DriverManager.getConnection(connection_url, connection_user, connection_password);

    Statement statement = connection.createStatement();
    String query = "SELECT * " + "FROM " + tableName + " WHERE 0=1";

    LOG.info("Running query for obtaining metadata: " + query);

    ResultSet result = statement.executeQuery(query);
    ResultSetMetaData metadata = result.getMetaData();
    int columnCount = metadata.getColumnCount();

    for (int i = 1; i <= columnCount; i++) {
        String columnName = metadata.getColumnName(i);
        int columnType = metadata.getColumnType(i);

        boolean nullable = metadata.isNullable(i) != ResultSetMetaData.columnNoNulls;

        FieldTypeBuilder<Schema> field = builder.name(columnName).doc("SQL type: " + columnType).type();

        switch (columnType) {
        case java.sql.Types.SMALLINT:
        case java.sql.Types.TINYINT:
        case java.sql.Types.INTEGER:
        case java.sql.Types.BIGINT:
            if (nullable)
                field.nullable().intType().noDefault();
            else/* ww w .j  a va 2s .  c  o m*/
                field.intType().noDefault();
            break;
        case java.sql.Types.BOOLEAN:
            if (nullable)
                field.nullable().booleanType().noDefault();
            else
                field.booleanType().noDefault();
            break;
        case java.sql.Types.NUMERIC:
        case java.sql.Types.DOUBLE:
        case java.sql.Types.FLOAT:
            if (nullable)
                field.nullable().doubleType().noDefault();
            else
                field.doubleType().noDefault();
            break;
        case java.sql.Types.TIMESTAMP:
        case -101: //TIMESTAMP(3) WITH TIME ZONE
        case -102: //TIMESTAMP(6) WITH LOCAL TIME ZONE
        default:
            if (nullable)
                field.nullable().stringType().noDefault();
            else
                field.stringType().noDefault();
            break;
        }
    }

    return builder.endRecord();
}

From source file:org.protempa.backend.dsb.relationaldb.ConstantResultProcessor.java

@Override
public void process(ResultSet resultSet) throws SQLException {
    ResultCache<Constant> results = getResults();
    EntitySpec entitySpec = getEntitySpec();
    String entitySpecName = entitySpec.getName();
    //boolean hasRefs = entitySpec.getInboundRefSpecs().length > 0;
    String[] propIds = entitySpec.getPropositionIds();
    ColumnSpec codeSpec = entitySpec.getCodeSpec();
    if (codeSpec != null) {
        List<ColumnSpec> codeSpecL = codeSpec.asList();
        codeSpec = codeSpecL.get(codeSpecL.size() - 1);
    }//from  w  w  w  .  ja  v a 2s . c  o m
    Logger logger = SQLGenUtil.logger();
    PropertySpec[] propertySpecs = entitySpec.getPropertySpecs();
    Value[] propertyValues = new Value[propertySpecs.length];
    int count = 0;
    String[] uniqueIds = new String[entitySpec.getUniqueIdSpecs().length];
    SourceSystem dsType = DataSourceBackendSourceSystem.getInstance(getDataSourceBackendId());
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    int[] columnTypes = new int[resultSetMetaData.getColumnCount()];
    for (int i = 0; i < columnTypes.length; i++) {
        columnTypes[i] = resultSetMetaData.getColumnType(i + 1);
    }

    while (resultSet.next()) {
        int i = 1;
        String keyId = resultSet.getString(i++);
        if (keyId == null) {
            logger.warning("A keyId is null. Skipping record.");
            continue;
        }

        i = readUniqueIds(uniqueIds, resultSet, i);
        if (Arrays.contains(uniqueIds, null)) {
            if (logger.isLoggable(Level.WARNING)) {
                logger.log(Level.WARNING, "Unique ids contain null ({0}). Skipping record.",
                        StringUtils.join(uniqueIds, ", "));
                continue;
            }
        }
        UniqueId uniqueId = generateUniqueId(entitySpecName, uniqueIds);

        String propId = null;
        if (!isCasePresent()) {
            if (codeSpec == null) {
                assert propIds.length == 1 : "Don't know which proposition id to assign to";
                propId = propIds[0];
            } else {
                String code = resultSet.getString(i++);
                propId = sqlCodeToPropositionId(codeSpec, code);
                if (propId == null) {
                    continue;
                }
            }
        } else {
            i++;
        }

        i = extractPropertyValues(resultSet, i, propertyValues, columnTypes);

        if (isCasePresent()) {
            propId = resultSet.getString(i++);
        }

        Constant cp = new Constant(propId, uniqueId);
        for (int j = 0; j < propertySpecs.length; j++) {
            PropertySpec propertySpec = propertySpecs[j];
            cp.setProperty(propertySpec.getName(), propertyValues[j]);
        }
        cp.setSourceSystem(dsType);
        logger.log(Level.FINEST, "Created constant {0}", cp);
        results.add(keyId, cp);
        if (++count % FLUSH_SIZE == 0) {
            try {
                results.flush(this);
            } catch (IOException ex) {
                throw new QueryResultsCacheException("Flushing primitive parameters to cache failed", ex);
            }
            if (logger.isLoggable(Level.FINE)) {
                Logging.logCount(logger, Level.FINE, count, "Retrieved {0} record", "Retrieved {0} records");
            }
        }
    }
    try {
        results.flush(this);
    } catch (IOException ex) {
        throw new QueryResultsCacheException("Flushing primitive parameters to cache failed", ex);
    }
    if (logger.isLoggable(Level.FINE)) {
        Logging.logCount(logger, Level.FINE, count, "Retrieved {0} record total",
                "Retrieved {0} records total");
    }
}

From source file:RowSetModel.java

public Class getColumnClass(int column) {
    String cname;//from  w  w  w. j a  va 2 s.  com
    int type;

    try {
        ResultSetMetaData meta = rowSet.getMetaData();

        if (meta == null) {
            return null;
        }
        type = meta.getColumnType(column + 1);
    } catch (SQLException e) {
        e.printStackTrace();
        return super.getColumnClass(column);
    }
    switch (type) {
    case Types.BIT: {
        cname = "java.lang.Boolean";
        break;
    }
    case Types.TINYINT: {
        cname = "java.lang.Byte";
        break;
    }
    case Types.SMALLINT: {
        cname = "java.lang.Short";
        break;
    }
    case Types.INTEGER: {
        cname = "java.lang.Integer";
        break;
    }
    case Types.BIGINT: {
        cname = "java.lang.Long";
        break;
    }
    case Types.FLOAT:
    case Types.REAL: {
        cname = "java.lang.Float";
        break;
    }
    case Types.DOUBLE: {
        cname = "java.lang.Double";
        break;
    }
    case Types.NUMERIC: {
        cname = "java.lang.Number";
        break;
    }
    case Types.DECIMAL: {
        cname = "java.math.BigDecimal";
        break;
    }
    case Types.CHAR:
    case Types.VARCHAR:
    case Types.LONGVARCHAR: {
        cname = "java.lang.String";
        break;
    }
    case Types.DATE: {
        cname = "java.sql.Date";
        break;
    }
    case Types.TIME: {
        cname = "java.sql.Time";
        break;
    }
    case Types.TIMESTAMP: {
        cname = "java.sql.Timestamp";
        break;
    }
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.LONGVARBINARY: {
        cname = "byte[]";
        break;
    }
    case Types.OTHER:
    case Types.JAVA_OBJECT: {
        cname = "java.lang.Object";
        break;
    }
    case Types.CLOB: {
        cname = "java.sql.Clob";
        break;
    }
    case Types.BLOB: {
        cname = "java.ssql.Blob";
        break;
    }
    case Types.REF: {
        cname = "java.sql.Ref";
        break;
    }
    case Types.STRUCT: {
        cname = "java.sql.Struct";
        break;
    }
    default: {
        return super.getColumnClass(column);
    }
    }
    try {
        return Class.forName(cname);
    } catch (Exception e) {
        e.printStackTrace();
        return super.getColumnClass(column);
    }
}