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:org.plasma.sdo.jdbc.service.JDBCSupport.java

protected List<PlasmaDataObject> fetch(PlasmaDataObject source, PlasmaProperty sourceProperty,
        StringBuilder sqlQuery, Connection con) {
    List<PlasmaDataObject> result = new ArrayList<PlasmaDataObject>();
    PreparedStatement statement = null;
    ResultSet rs = null;/*from   w w  w .  j av a 2s  .co  m*/
    try {
        if (log.isDebugEnabled()) {
            log.debug("fetch: " + sqlQuery.toString());
        }
        statement = con.prepareStatement(sqlQuery.toString(),
                ResultSet.TYPE_FORWARD_ONLY, /*ResultSet.TYPE_SCROLL_INSENSITIVE,*/
                ResultSet.CONCUR_READ_ONLY);

        statement.execute();
        rs = statement.getResultSet();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int numcols = rsMeta.getColumnCount();
        while (rs.next()) {
            PlasmaDataObject target = (PlasmaDataObject) source.createDataObject(sourceProperty);
            result.add(target);
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                int columnType = rsMeta.getColumnType(i);
                PlasmaProperty prop = (PlasmaProperty) target.getType().getProperty(columnName);
                Object value = converter.fromJDBCDataType(rs, i, columnType, prop);

                if (!prop.isReadOnly()) {
                    target.set(prop, value);
                } else {
                    CoreDataObject coreObject = (CoreDataObject) target;
                    coreObject.setValue(prop.getName(), value);
                }
            }
        }
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
    return result;
}

From source file:org.apache.nifi.util.hive.HiveJdbcCommon.java

/**
 * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a
 * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value.
 *
 * @param rs         The result set to convert to Avro
 * @param recordName The a priori record name to use if it cannot be determined from the result set.
 * @return A Schema object representing the result set converted to an Avro record
 * @throws SQLException if any error occurs during conversion
 *//*from www  .j av  a  2s . c  o m*/
public static Schema createSchema(final ResultSet rs, String recordName) throws SQLException {
    final ResultSetMetaData meta = rs.getMetaData();
    final int nrOfColumns = meta.getColumnCount();
    String tableName = StringUtils.isEmpty(recordName) ? "NiFi_SelectHiveQL_Record" : recordName;
    try {
        if (nrOfColumns > 0) {
            // Hive JDBC doesn't support getTableName, instead it returns table.column for column name. Grab the table name from the first column
            String firstColumnNameFromMeta = meta.getColumnName(1);
            int tableNameDelimiter = firstColumnNameFromMeta.lastIndexOf(".");
            if (tableNameDelimiter > -1) {
                String tableNameFromMeta = firstColumnNameFromMeta.substring(0, tableNameDelimiter);
                if (!StringUtils.isBlank(tableNameFromMeta)) {
                    tableName = tableNameFromMeta;
                }
            }
        }
    } catch (SQLException se) {
        // Not all drivers support getTableName, so just use the previously-set default
    }

    final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields();

    /**
     * Some missing Avro types - Decimal, Date types. May need some additional work.
     */
    for (int i = 1; i <= nrOfColumns; i++) {
        String columnNameFromMeta = meta.getColumnName(i);
        // Hive returns table.column for column name. Grab the column name as the string after the last period
        int columnNameDelimiter = columnNameFromMeta.lastIndexOf(".");
        String columnName = columnNameFromMeta.substring(columnNameDelimiter + 1);
        switch (meta.getColumnType(i)) {
        case CHAR:
        case LONGNVARCHAR:
        case LONGVARCHAR:
        case NCHAR:
        case NVARCHAR:
        case VARCHAR:
        case ARRAY:
        case STRUCT:
        case JAVA_OBJECT:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BIT:
        case BOOLEAN:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion()
                    .noDefault();
            break;

        case INTEGER:
            // Default to signed type unless otherwise noted. Some JDBC drivers don't implement isSigned()
            boolean signedType = true;
            try {
                signedType = meta.isSigned(i);
            } catch (SQLException se) {
                // Use signed types as default
            }
            if (signedType) {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                        .noDefault();
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                        .noDefault();
            }
            break;

        case SMALLINT:
        case TINYINT:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                    .noDefault();
            break;

        case BIGINT:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                    .noDefault();
            break;

        // java.sql.RowId is interface, is seems to be database
        // implementation specific, let's convert to String
        case ROWID:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case FLOAT:
        case REAL:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion()
                    .noDefault();
            break;

        case DOUBLE:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion()
                    .noDefault();
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DECIMAL:
        case NUMERIC:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DATE:
        case TIME:
        case TIMESTAMP:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BINARY:
        case VARBINARY:
        case LONGVARBINARY:
        case BLOB:
        case CLOB:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion()
                    .noDefault();
            break;

        default:
            throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i)
                    + " cannot be converted to Avro type");
        }
    }

    return builder.endRecord();
}

From source file:org.jfree.data.jdbc.JDBCCategoryDataset.java

/**
 * Populates the dataset by executing the supplied query against the
 * existing database connection.  If no connection exists then no action
 * is taken./*from ww w.j  ava2  s . com*/
 * <p>
 * The results from the query are extracted and cached locally, thus
 * applying an upper limit on how many rows can be retrieved successfully.
 *
 * @param con  the connection.
 * @param query  the query.
 *
 * @throws SQLException if there is a problem executing the query.
 */
public void executeQuery(Connection con, String query) throws SQLException {

    Statement statement = null;
    ResultSet resultSet = null;
    try {
        statement = con.createStatement();
        resultSet = statement.executeQuery(query);
        ResultSetMetaData metaData = resultSet.getMetaData();

        int columnCount = metaData.getColumnCount();

        if (columnCount < 2) {
            throw new SQLException("JDBCCategoryDataset.executeQuery() : insufficient columns "
                    + "returned from the database.");
        }

        // Remove any previous old data
        int i = getRowCount();
        while (--i >= 0) {
            removeRow(i);
        }

        while (resultSet.next()) {
            // first column contains the row key...
            Comparable rowKey = resultSet.getString(1);
            for (int column = 2; column <= columnCount; column++) {

                Comparable columnKey = metaData.getColumnName(column);
                int columnType = metaData.getColumnType(column);

                switch (columnType) {
                case Types.TINYINT:
                case Types.SMALLINT:
                case Types.INTEGER:
                case Types.BIGINT:
                case Types.FLOAT:
                case Types.DOUBLE:
                case Types.DECIMAL:
                case Types.NUMERIC:
                case Types.REAL: {
                    Number value = (Number) resultSet.getObject(column);
                    if (this.transpose) {
                        setValue(value, columnKey, rowKey);
                    } else {
                        setValue(value, rowKey, columnKey);
                    }
                    break;
                }
                case Types.DATE:
                case Types.TIME:
                case Types.TIMESTAMP: {
                    Date date = (Date) resultSet.getObject(column);
                    Number value = new Long(date.getTime());
                    if (this.transpose) {
                        setValue(value, columnKey, rowKey);
                    } else {
                        setValue(value, rowKey, columnKey);
                    }
                    break;
                }
                case Types.CHAR:
                case Types.VARCHAR:
                case Types.LONGVARCHAR: {
                    String string = (String) resultSet.getObject(column);
                    try {
                        Number value = Double.valueOf(string);
                        if (this.transpose) {
                            setValue(value, columnKey, rowKey);
                        } else {
                            setValue(value, rowKey, columnKey);
                        }
                    } catch (NumberFormatException e) {
                        // suppress (value defaults to null)
                    }
                    break;
                }
                default:
                    // not a value, can't use it (defaults to null)
                    break;
                }
            }
        }

        fireDatasetChanged(new DatasetChangeInfo());
        //TODO: fill in real change info
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                // report this?
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                // report this?
            }
        }
    }
}

From source file:at.ac.univie.isc.asio.engine.sql.WebRowSetWriter.java

private void columnDefinition(final int idx, final ResultSetMetaData context)
        throws XMLStreamException, SQLException {
    // @formatter:off
    xml.writeStartElement(WRS, "column-definition");
    tag("column-index", idx);
    tag("auto-increment", context.isAutoIncrement(idx));
    tag("case-sensitive", context.isCaseSensitive(idx));
    tag("currency", context.isCurrency(idx));
    tag("nullable", context.isNullable(idx));
    tag("signed", context.isSigned(idx));
    tag("searchable", context.isSearchable(idx));
    tag("column-display-size", context.getColumnDisplaySize(idx));
    tag("column-label", context.getColumnLabel(idx));
    tag("column-name", context.getColumnName(idx));
    tag("schema-name", context.getSchemaName(idx));
    tag("column-precision", context.getPrecision(idx));
    tag("column-scale", context.getScale(idx));
    tag("table-name", context.getTableName(idx));
    tag("catalog-name", context.getCatalogName(idx));
    tag("column-type", context.getColumnType(idx));
    tag("column-type-name", context.getColumnTypeName(idx));
    xml.writeEndElement();/*from  w  ww  .  j  a  va2s .c  o m*/
    // @formatter:on
}

From source file:org.cloudgraph.rdb.service.JDBCSupport.java

protected List<PropertyPair> fetchRow(PlasmaType type, StringBuilder sql, Connection con) {
    List<PropertyPair> result = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    ResultSet rs = null;/* www . ja v  a  2s  .c o  m*/
    try {
        if (log.isDebugEnabled()) {
            log.debug("fetch: " + sql.toString());
        }
        statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /*
                                                                                       * ResultSet
                                                                                       * .
                                                                                       * TYPE_SCROLL_INSENSITIVE
                                                                                       * ,
                                                                                       */
                ResultSet.CONCUR_READ_ONLY);

        statement.execute();
        rs = statement.getResultSet();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int numcols = rsMeta.getColumnCount();
        int count = 0;
        while (rs.next()) {
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                int columnType = rsMeta.getColumnType(i);
                PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
                PlasmaProperty valueProp = prop;
                while (!valueProp.getType().isDataType()) {
                    valueProp = getOppositePriKeyProperty(valueProp);
                }
                Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp);
                if (value != null) {
                    PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                    if (!valueProp.equals(prop))
                        pair.setValueProp(valueProp);
                    result.add(pair);
                }
            }
            count++;
        }
        if (log.isDebugEnabled())
            log.debug("returned " + count + " results");
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
    return result;
}

From source file:org.cloudgraph.rdb.service.JDBCSupport.java

protected Map<String, PropertyPair> fetchRowMap(PlasmaType type, StringBuilder sql, Connection con) {
    Map<String, PropertyPair> result = new HashMap<String, PropertyPair>();
    PreparedStatement statement = null;
    ResultSet rs = null;/*  w  w  w  . j a v  a  2 s.c om*/
    try {
        if (log.isDebugEnabled()) {
            log.debug("fetch: " + sql.toString());
        }

        statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /*
                                                                                       * ResultSet
                                                                                       * .
                                                                                       * TYPE_SCROLL_INSENSITIVE
                                                                                       * ,
                                                                                       */
                ResultSet.CONCUR_READ_ONLY);

        statement.execute();
        rs = statement.getResultSet();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int numcols = rsMeta.getColumnCount();
        int count = 0;
        while (rs.next()) {
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                int columnType = rsMeta.getColumnType(i);
                PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
                PlasmaProperty valueProp = prop;
                while (!valueProp.getType().isDataType()) {
                    valueProp = getOppositePriKeyProperty(valueProp);
                }
                Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp);
                if (value != null) {
                    PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                    if (!valueProp.equals(prop))
                        pair.setValueProp(valueProp);
                    result.put(prop.getName(), pair);
                }
            }
            count++;
        }
        if (log.isDebugEnabled())
            log.debug("returned " + count + " results");
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
    return result;
}

From source file:org.sakaiproject.webservices.SakaiReport.java

protected String toCsvString(ResultSet rs, boolean includeHeaderRow) throws IOException, SQLException {
    StringWriter stringWriter = new StringWriter();
    CsvWriter writer = new CsvWriter(stringWriter, ',');
    writer.setRecordDelimiter('\n');
    writer.setForceQualifier(true);/*from w ww  .  ja v a 2s .c om*/
    ResultSetMetaData rsmd = rs.getMetaData();
    int numColumns = rsmd.getColumnCount();

    if (includeHeaderRow) {
        String[] row = new String[numColumns];
        for (int i = 1; i < numColumns + 1; i++) {
            row[i - 1] = rsmd.getColumnLabel(i);
        }
        writer.writeRecord(row);
    }

    while (rs.next()) {
        String[] row = new String[numColumns];
        for (int i = 1; i < numColumns + 1; i++) {

            String column_name = rsmd.getColumnName(i);

            LOG.debug("Column Name=" + column_name + ",type=" + rsmd.getColumnType(i));

            switch (rsmd.getColumnType(i)) {
            case Types.BIGINT:
                row[i - 1] = String.valueOf(rs.getInt(i));
                break;
            case Types.BOOLEAN:
                row[i - 1] = String.valueOf(rs.getBoolean(i));
                break;
            case Types.BLOB:
                row[i - 1] = rs.getBlob(i).toString();
                break;
            case Types.DOUBLE:
                row[i - 1] = String.valueOf(rs.getDouble(i));
                break;
            case Types.FLOAT:
                row[i - 1] = String.valueOf(rs.getFloat(i));
                break;
            case Types.INTEGER:
                row[i - 1] = String.valueOf(rs.getInt(i));
                break;
            case Types.LONGVARCHAR:
                row[i - 1] = rs.getString(i);
                break;
            case Types.NVARCHAR:
                row[i - 1] = rs.getNString(i);
                break;
            case Types.VARCHAR:
                row[i - 1] = rs.getString(i);
                break;
            case Types.TINYINT:
                row[i - 1] = String.valueOf(rs.getInt(i));
                break;
            case Types.SMALLINT:
                row[i - 1] = String.valueOf(rs.getInt(i));
                break;
            case Types.DATE:
                row[i - 1] = rs.getDate(i).toString();
                break;
            case Types.TIMESTAMP:
                row[i - 1] = rs.getTimestamp(i).toString();
                break;
            default:
                row[i - 1] = rs.getString(i);
                break;

            }
            LOG.debug("value: " + row[i - 1]);
        }
        writer.writeRecord(row);
        //writer.endRecord();

    }

    LOG.debug("csv output:" + stringWriter.toString());

    return stringWriter.toString();
}

From source file:gobblin.source.extractor.extract.jdbc.JdbcExtractor.java

private String parseColumnAsString(final ResultSet resultset, final ResultSetMetaData resultsetMetadata, int i)
        throws SQLException {

    if (isBlob(resultsetMetadata.getColumnType(i))) {
        return readBlobAsString(resultset.getBlob(i));
    }/*ww  w  .  ja  v a  2 s  . c om*/
    if ((resultsetMetadata.getColumnType(i) == Types.BIT || resultsetMetadata.getColumnType(i) == Types.BOOLEAN)
            && convertBitToBoolean()) {
        return Boolean.toString(resultset.getBoolean(i));
    }
    return resultset.getString(i);
}

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

/**
 * Validate the Metadata for the result set of a metadata getColumns call.
 *///from w w  w . ja  v a  2s.co m
public void testMetaDataGetColumnsMetaData() throws SQLException {
    ResultSet rs = con.getMetaData().getColumns(null, null, "testhivejdbcdriver\\_table", null);

    ResultSetMetaData rsmd = rs.getMetaData();

    assertEquals("TABLE_CAT", rsmd.getColumnName(1));
    assertEquals(Types.VARCHAR, rsmd.getColumnType(1));
    assertEquals(Integer.MAX_VALUE, rsmd.getColumnDisplaySize(1));

    assertEquals("ORDINAL_POSITION", rsmd.getColumnName(17));
    assertEquals(Types.INTEGER, rsmd.getColumnType(17));
    assertEquals(11, rsmd.getColumnDisplaySize(17));
}

From source file:org.openiot.gsn.storage.StorageManager.java

/**
 * Returns false if the table doesnt exist. If the table exists but the
 * structure is not compatible with the specified fields the method throws
 * GSNRuntimeException. Note that this method doesn't close the connection
 *
 * @param tableName//from   w ww .  j  av  a2 s .  co m
 * @param connection (this method will not close it and the caller is responsible
 *                   for closing the connection)
 * @return
 * @throws SQLException
 * @Throws GSNRuntimeException
 */

public boolean tableExists(CharSequence tableName, DataField[] fields, Connection connection)
        throws SQLException, GSNRuntimeException {
    if (!ValidityTools.isValidJavaVariable(tableName))
        throw new GSNRuntimeException("Table name is not valid");
    StringBuilder sb = new StringBuilder("select * from ").append(tableNameGeneratorInString(tableName))
            .append(" where 1=0 ");
    ResultSet rs = null;
    try {
        rs = executeQueryWithResultSet(sb, connection);
        ResultSetMetaData structure = rs.getMetaData();
        if (fields != null && fields.length > 0)
            nextField: for (DataField field : fields) {
                for (int i = 1; i <= structure.getColumnCount(); i++) {
                    String colName = structure.getColumnLabel(i);
                    int colType = structure.getColumnType(i);
                    int colTypeScale = structure.getScale(i);
                    if (field.getName().equalsIgnoreCase(colName))
                        if (field.getDataTypeID() == convertLocalTypeToGSN(colType, colTypeScale))
                            continue nextField;
                        else
                            throw new GSNRuntimeException("The column : " + colName + " in the >" + tableName
                                    + "< table is not compatible with type : " + field.getType()
                                    + ". The actual type for this table (currently in the database): "
                                    + colType);
                }
                throw new GSNRuntimeException("The table " + tableName + " in the database, doesn't have the >"
                        + field.getName() + "< column.");
            }
    } catch (SQLException e) {
        if (e.getErrorCode() == getTableNotExistsErrNo() || e.getMessage().contains("does not exist"))
            return false;
        else {
            logger.error(e.getErrorCode());
            throw e;
        }
    } finally {
        close(rs);
    }
    return true;
}