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:esg.gateway.service.ESGAccessLogServiceImpl.java

/**
   Initializes the service by setting up the database connection and result handling.
*///  w  w w .ja  va2s. co  m
public void init() {
    Properties props = new Properties();
    props.setProperty("db.protocol", "jdbc:postgresql:");
    props.setProperty("db.host", "localhost");
    props.setProperty("db.port", "5432");
    props.setProperty("db.database", "esgcet");
    props.setProperty("db.user", "dbsuper");
    props.setProperty("db.password", "changeme");
    try {
        props.putAll(new ESGFProperties());
    } catch (IOException ex) {
        log.error(ex);
    }

    queryRunner = new QueryRunner(DatabaseResource.init(props.getProperty("db.driver", "org.postgresql.Driver"))
            .setupDataSource(props).getDataSource());

    resultSetHandler = new ResultSetHandler<List<String[]>>() {
        public List<String[]> handle(ResultSet rs) throws SQLException {
            ArrayList<String[]> results = new ArrayList<String[]>();
            String[] record = null;
            assert (null != results);

            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            log.trace("Number of fields: " + cols);

            log.trace("adding column data...");
            record = new String[cols];
            for (int i = 0; i < cols; i++) {
                try {
                    record[i] = meta.getColumnLabel(i + 1) + "|" + meta.getColumnType(i + 1);
                } catch (SQLException e) {
                    log.error(e);
                }
            }
            results.add(record);

            for (int i = 0; rs.next(); i++) {
                log.trace("Looking at record " + (i + 1));
                record = new String[cols];
                for (int j = 0; j < cols; j++) {
                    record[j] = rs.getString(j + 1);
                    log.trace("gathering result record column " + (j + 1) + " -> " + record[j]);
                }
                log.trace("adding record ");
                results.add(record);
                record = null; //gc courtesy
            }
            return results;
        }
    };
    log.trace("initialization complete");
}

From source file:eu.stratosphere.api.java.record.io.jdbc.JDBCInputFormat.java

/**
 * Stores the next resultSet row in a Record
 * /*w  ww .j  a v  a 2 s  .c o m*/
 * @param record
 *        target Record
 * @return boolean value indicating that the operation was successful
 */
@Override
public Record nextRecord(Record record) {
    try {
        resultSet.next();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int column_count = rsmd.getColumnCount();
        record.setNumFields(column_count);

        for (int pos = 0; pos < column_count; pos++) {
            int type = rsmd.getColumnType(pos + 1);
            retrieveTypeAndFillRecord(pos, type, record);
        }
        return record;
    } catch (SQLException e) {
        throw new IllegalArgumentException("Couldn't read data:\t" + e.getMessage());
    } catch (NotTransformableSQLFieldException e) {
        throw new IllegalArgumentException(
                "Couldn't read data because of unknown column sql-type:\t" + e.getMessage());
    } catch (NullPointerException e) {
        throw new IllegalArgumentException("Couldn't access resultSet:\t" + e.getMessage());
    }
}

From source file:org.jumpmind.vaadin.ui.common.CommonUiUtils.java

public static Table putResultsInTable(final ResultSet rs, int maxResultSize, final boolean showRowNumbers,
        String... excludeValues) throws SQLException {

    final Table table = createTable();
    table.setImmediate(true);/*from w ww  .  j  a v a2 s .com*/
    table.setSortEnabled(true);
    table.setSelectable(true);
    table.setMultiSelect(true);
    table.setColumnReorderingAllowed(true);
    table.setColumnReorderingAllowed(true);
    table.setColumnCollapsingAllowed(true);

    final ResultSetMetaData meta = rs.getMetaData();
    int columnCount = meta.getColumnCount();
    table.addContainerProperty("#", Integer.class, null);
    Set<String> columnNames = new HashSet<String>();
    Set<Integer> skipColumnIndexes = new HashSet<Integer>();
    int[] types = new int[columnCount];
    for (int i = 1; i <= columnCount; i++) {
        String realColumnName = meta.getColumnName(i);
        String columnName = realColumnName;
        if (!Arrays.asList(excludeValues).contains(columnName)) {

            int index = 1;
            while (columnNames.contains(columnName)) {
                columnName = realColumnName + "_" + index++;
            }
            columnNames.add(columnName);

            Class<?> typeClass = Object.class;
            int type = meta.getColumnType(i);
            types[i - 1] = type;
            switch (type) {
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.NUMERIC:
            case Types.REAL:
            case Types.DECIMAL:
                typeClass = BigDecimal.class;
                break;
            case Types.TINYINT:
            case Types.SMALLINT:
            case Types.BIGINT:
            case Types.INTEGER:
                typeClass = Long.class;
                break;
            case Types.VARCHAR:
            case Types.CHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.CLOB:
                typeClass = String.class;
            default:
                break;
            }
            table.addContainerProperty(i, typeClass, null);
            table.setColumnHeader(i, columnName);
        } else {
            skipColumnIndexes.add(i - 1);
        }

    }
    int rowNumber = 1;
    while (rs.next() && rowNumber <= maxResultSize) {
        Object[] row = new Object[columnNames.size() + 1];
        row[0] = new Integer(rowNumber);
        int rowIndex = 1;
        for (int i = 0; i < columnCount; i++) {
            if (!skipColumnIndexes.contains(i)) {
                Object o = getObject(rs, i + 1);
                int type = types[i];
                switch (type) {
                case Types.FLOAT:
                case Types.DOUBLE:
                case Types.REAL:
                case Types.NUMERIC:
                case Types.DECIMAL:
                    if (o == null) {
                        o = new BigDecimal(-1);
                    }
                    if (!(o instanceof BigDecimal)) {
                        o = new BigDecimal(castToNumber(o.toString()));
                    }
                    break;
                case Types.TINYINT:
                case Types.SMALLINT:
                case Types.BIGINT:
                case Types.INTEGER:
                    if (o == null) {
                        o = new Long(-1);
                    }

                    if (!(o instanceof Long)) {
                        o = new Long(castToNumber(o.toString()));
                    }
                    break;
                default:
                    break;
                }
                row[rowIndex] = o == null ? NULL_TEXT : o;
                rowIndex++;
            }
        }
        table.addItem(row, rowNumber);
        rowNumber++;
    }

    if (rowNumber < 100) {
        table.setColumnWidth("#", 18);
    } else if (rowNumber < 1000) {
        table.setColumnWidth("#", 25);
    } else {
        table.setColumnWidth("#", 30);
    }

    if (!showRowNumbers) {
        table.setColumnCollapsed("#", true);
    }

    return table;
}

From source file:org.pentaho.metadata.SQLModelGeneratorTest.java

/**
 * The following method returns an array of int(java.sql.Types) containing the column types for
 * a given ResultSetMetaData object./*from  w  w  w  .  java  2  s  .  co  m*/
 */
public int[] getColumnTypes(ResultSetMetaData resultSetMetaData) throws SQLException {
    int columnCount = resultSetMetaData.getColumnCount();
    int[] returnValue = new int[columnCount];
    for (int colIndex = 1; colIndex <= columnCount; colIndex++) {
        returnValue[colIndex - 1] = resultSetMetaData.getColumnType(colIndex);
    }

    return returnValue;
}

From source file:kr.co.bitnine.octopus.engine.CursorHive.java

@Override
public TupleDesc describe() throws PostgresException {
    if (tupDesc != null)
        return tupDesc;

    prepareConnection();/*w w w  . j  a va2 s  .c om*/
    prepareStatement(0);
    try {
        checkCancel();
        ResultSet rs = stmt.executeQuery();
        checkCancel();
        ResultSetMetaData rsmd = rs.getMetaData();
        int colCnt = rsmd.getColumnCount();
        PostgresAttribute[] attrs = new PostgresAttribute[colCnt];
        for (int i = 0; i < colCnt; i++) {
            String colName = getColumnName(rsmd.getColumnName(i + 1));
            int colType = rsmd.getColumnType(i + 1);
            LOG.debug("JDBC type of column '" + colName + "' is " + colType);
            PostgresType type = TypeInfo.postresTypeOfJdbcType(colType);
            int typeInfo = -1;
            if (type == PostgresType.VARCHAR)
                typeInfo = rsmd.getColumnDisplaySize(i + 1);
            attrs[i] = new PostgresAttribute(colName, type, typeInfo);
        }
        rs.close();
        stmt.close();
        stmt = null;

        tupDesc = new TupleDesc(attrs, getResultFormats());
        return tupDesc;
    } catch (SQLException e) {
        PostgresErrorData edata = new PostgresErrorData(PostgresSeverity.ERROR,
                "failed to execute by-pass query: " + e.getMessage());
        throw new PostgresException(edata, e);
    }
}

From source file:kr.co.bitnine.octopus.engine.CursorByPass.java

private void execute(int numRows) throws PostgresException {
    if (getState() == State.DONE || getState() == State.FAILED)
        setState(State.READY);//from  w  w w  .j  a  va 2 s  .  com

    if (getState() != State.READY)
        return;

    LOG.debug("execute CursorByPass (rows=" + numRows + ")");

    try {
        // NOTE: some JDBC drivers do not ignore setFetchSize(0)
        if (numRows > 0)
            stmt.setFetchSize(numRows);

        checkCancel();
        ResultSet rs = stmt.executeQuery();
        checkCancel();

        ResultSetMetaData rsmd = rs.getMetaData();
        int colCnt = rsmd.getColumnCount();
        PostgresAttribute[] attrs = new PostgresAttribute[colCnt];
        for (int i = 0; i < colCnt; i++) {
            String colName = rsmd.getColumnName(i + 1);
            int colType = rsmd.getColumnType(i + 1);
            LOG.info("JDBC type of column '" + colName + "' is " + colType);
            PostgresType type = TypeInfo.postresTypeOfJdbcType(colType);
            int typeInfo = -1;
            if (type == PostgresType.VARCHAR)
                typeInfo = rsmd.getColumnDisplaySize(i + 1);
            attrs[i] = new PostgresAttribute(colName, type, typeInfo);
        }

        tupDesc = new TupleDesc(attrs, getResultFormats());
        tupSetByPass = new TupleSetByPass(this, rs, tupDesc);

        setState(State.ACTIVE);
    } catch (SQLException e) {
        setState(State.FAILED);

        close();

        PostgresErrorData edata = new PostgresErrorData(PostgresSeverity.ERROR,
                "failed to execute by-pass query: " + e.getMessage());
        throw new PostgresException(edata, e);
    }
}

From source file:eu.stratosphere.api.io.jdbc.JDBCInputFormat.java

/**
 * Stores the next resultSet row in a Record
 * //from w  w w . j  a v a 2s  . c  o  m
 * @param record
 *        target Record
 * @return boolean value indicating that the operation was successful
 */
@Override
public boolean nextRecord(Record record) {
    try {
        resultSet.next();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int column_count = rsmd.getColumnCount();
        record.setNumFields(column_count);

        for (int pos = 0; pos < column_count; pos++) {
            int type = rsmd.getColumnType(pos + 1);
            retrieveTypeAndFillRecord(pos, type, record);
        }
        return true;
    } catch (SQLException e) {
        throw new IllegalArgumentException("Couldn't read data:\t" + e.getMessage());
    } catch (NotTransformableSQLFieldException e) {
        throw new IllegalArgumentException(
                "Couldn't read data because of unknown column sql-type:\t" + e.getMessage());
    } catch (NullPointerException e) {
        throw new IllegalArgumentException("Couldn't access resultSet:\t" + e.getMessage());
    }
}

From source file:org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.java

private void configurePartitionProperties(MutableContext context, LinkConfiguration linkConfig,
        FromJobConfiguration fromJobConfig) {
    // ----- configure column name -----

    String partitionColumnName = fromJobConfig.fromJobConfig.partitionColumn;

    if (partitionColumnName == null) {
        // if column is not specified by the user,
        // find the primary key of the fromTable (when there is a fromTable).
        String tableName = fromJobConfig.fromJobConfig.tableName;
        if (tableName != null) {
            partitionColumnName = executor.getPrimaryKey(tableName);
        }//from   w w  w  .  j  a v  a  2 s  . c  o  m
    }

    if (partitionColumnName != null) {
        context.setString(GenericJdbcConnectorConstants.CONNECTOR_JDBC_PARTITION_COLUMNNAME,
                partitionColumnName);

    } else {
        throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0005);
    }

    // ----- configure column type, min value, and max value -----

    String minMaxQuery = fromJobConfig.fromJobConfig.boundaryQuery;

    if (minMaxQuery == null) {
        StringBuilder builder = new StringBuilder();

        String schemaName = fromJobConfig.fromJobConfig.schemaName;
        String tableName = fromJobConfig.fromJobConfig.tableName;
        String tableSql = fromJobConfig.fromJobConfig.sql;

        if (tableName != null && tableSql != null) {
            // when both fromTable name and fromTable sql are specified:
            throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0007);

        } else if (tableName != null) {
            // when fromTable name is specified:

            // For databases that support schemas (IE: postgresql).
            String fullTableName = (schemaName == null) ? executor.delimitIdentifier(tableName)
                    : executor.delimitIdentifier(schemaName) + "." + executor.delimitIdentifier(tableName);

            String column = partitionColumnName;
            builder.append("SELECT MIN(");
            builder.append(column);
            builder.append("), MAX(");
            builder.append(column);
            builder.append(") FROM ");
            builder.append(fullTableName);

        } else if (tableSql != null) {
            String column = executor.qualify(partitionColumnName, GenericJdbcConnectorConstants.SUBQUERY_ALIAS);
            builder.append("SELECT MIN(");
            builder.append(column);
            builder.append("), MAX(");
            builder.append(column);
            builder.append(") FROM ");
            builder.append("(");
            builder.append(tableSql.replace(GenericJdbcConnectorConstants.SQL_CONDITIONS_TOKEN, "1 = 1"));
            builder.append(") ");
            builder.append(GenericJdbcConnectorConstants.SUBQUERY_ALIAS);

        } else {
            // when neither are specified:
            throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0008);
        }

        minMaxQuery = builder.toString();
    }

    LOG.debug("Using minMaxQuery: " + minMaxQuery);
    ResultSet rs = executor.executeQuery(minMaxQuery);
    try {
        ResultSetMetaData rsmd = rs.getMetaData();
        if (rsmd.getColumnCount() != 2) {
            throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0006);
        }

        rs.next();

        int columnType = rsmd.getColumnType(1);
        String min = rs.getString(1);
        String max = rs.getString(2);

        LOG.info("Boundaries: min=" + min + ", max=" + max + ", columnType=" + columnType);

        context.setInteger(GenericJdbcConnectorConstants.CONNECTOR_JDBC_PARTITION_COLUMNTYPE, columnType);
        context.setString(GenericJdbcConnectorConstants.CONNECTOR_JDBC_PARTITION_MINVALUE, min);
        context.setString(GenericJdbcConnectorConstants.CONNECTOR_JDBC_PARTITION_MAXVALUE, max);

    } catch (SQLException e) {
        throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0006, e);
    }
}

From source file:uk.ac.kcl.rowmappers.DocumentRowMapper.java

private void mapDBFields(Document doc, ResultSet rs) throws SQLException, IOException {
    //add additional query fields for ES export
    ResultSetMetaData meta = rs.getMetaData();

    int colCount = meta.getColumnCount();

    for (int col = 1; col <= colCount; col++) {
        Object value = rs.getObject(col);
        if (value != null) {
            String colLabel = meta.getColumnLabel(col).toLowerCase();
            if (!fieldsToIgnore.contains(colLabel)) {
                DateTime dateTime;/*  w w w. j a  v a2s.c o  m*/
                //map correct SQL time types
                switch (meta.getColumnType(col)) {
                case 91:
                    Date dt = (Date) value;
                    dateTime = new DateTime(dt.getTime());
                    doc.getAssociativeArray().put(meta.getColumnLabel(col).toLowerCase(),
                            eSCompatibleDateTimeFormatter.print(dateTime));
                    break;
                case 93:
                    Timestamp ts = (Timestamp) value;
                    dateTime = new DateTime(ts.getTime());
                    doc.getAssociativeArray().put(meta.getColumnLabel(col).toLowerCase(),
                            eSCompatibleDateTimeFormatter.print(dateTime));
                    break;
                default:
                    doc.getAssociativeArray().put(meta.getColumnLabel(col).toLowerCase(), rs.getString(col));
                    break;
                }
            }
        }

        //map binary content from FS or database if required (as per docman reader)
        if (value != null && meta.getColumnLabel(col).equalsIgnoreCase(binaryContentFieldName)) {
            switch (binaryContentSource) {
            case "database":
                doc.setBinaryContent(rs.getBytes(col));
                break;
            case "fileSystemWithDBPath":
                Resource resource = context.getResource(pathPrefix + rs.getString(col));
                doc.setBinaryContent(IOUtils.toByteArray(resource.getInputStream()));
                break;
            default:
                break;
            }
        }
    }
}

From source file:org.apache.sqoop.connector.jdbc.oracle.util.OracleQueries.java

private static OracleTableColumns getTableColumns(Connection connection, OracleTable table,
        boolean omitLobColumns, String dataTypesClause, HashSet<String> columnNamesToOmit) throws SQLException {

    String sql = "SELECT column_name, data_type " + " FROM dba_tab_columns" + " WHERE owner = ?"
            + " and table_name = ?" + " %s" + " ORDER BY column_id";

    sql = String.format(sql, dataTypesClause == null ? "" : " and " + dataTypesClause);

    LOG.debug(String.format("%s : sql = \n%s", OracleUtilities.getCurrentMethodName(), sql));

    OracleTableColumns result = new OracleTableColumns();
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString(1, getTableSchema(connection, table));
    statement.setString(2, table.getName());

    ResultSet resultSet = statement.executeQuery();

    while (resultSet.next()) {

        String columnName = resultSet.getString("column_name");

        if (columnNamesToOmit != null) {
            if (columnNamesToOmit.contains(columnName)) {
                continue;
            }// w ww.  j a v  a 2  s.  c  o m
        }

        result.add(new OracleTableColumn(columnName, resultSet.getString("data_type")));
    }

    resultSet.close();
    statement.close();

    // Now get the actual JDBC data-types for these columns...
    StringBuilder columnList = new StringBuilder();
    for (int idx = 0; idx < result.size(); idx++) {
        if (idx > 0) {
            columnList.append(",");
        }
        columnList.append(result.get(idx).getName());
    }
    sql = String.format("SELECT %s FROM %s WHERE 0=1", columnList.toString(), table.toString());
    Statement statementDesc = connection.createStatement();
    ResultSet resultSetDesc = statementDesc.executeQuery(sql);
    ResultSetMetaData metaData = resultSetDesc.getMetaData();
    for (int idx = 0; idx < metaData.getColumnCount(); idx++) {
        result.get(idx).setOracleType(metaData.getColumnType(idx + 1)); // <- JDBC
                                                                        // is
                                                                        // 1-based
    }
    resultSetDesc.close();
    statementDesc.close();

    return result;
}