Example usage for java.sql ResultSetMetaData getColumnTypeName

List of usage examples for java.sql ResultSetMetaData getColumnTypeName

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

Introduction

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

Prototype

String getColumnTypeName(int column) throws SQLException;

Source Link

Document

Retrieves the designated column's database-specific type name.

Usage

From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java

/**
 * Convert from JDBC metadata to Avatica columns.
 *///  ww w . jav a 2s  .  co m
protected static List<ColumnMetaData> columns(ResultSetMetaData metaData) throws SQLException {
    if (metaData == null) {
        return Collections.emptyList();
    }
    final List<ColumnMetaData> columns = new ArrayList<>();
    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        final SqlType sqlType = SqlType.valueOf(metaData.getColumnType(i));
        final ColumnMetaData.Rep rep = ColumnMetaData.Rep.of(sqlType.internal);
        ColumnMetaData.AvaticaType t = ColumnMetaData.scalar(metaData.getColumnType(i),
                metaData.getColumnTypeName(i), rep);
        ColumnMetaData md = new ColumnMetaData(i - 1, metaData.isAutoIncrement(i), metaData.isCaseSensitive(i),
                metaData.isSearchable(i), metaData.isCurrency(i), metaData.isNullable(i), metaData.isSigned(i),
                metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i),
                metaData.getSchemaName(i), metaData.getPrecision(i), metaData.getScale(i),
                metaData.getTableName(i), metaData.getCatalogName(i), t, metaData.isReadOnly(i),
                metaData.isWritable(i), metaData.isDefinitelyWritable(i), metaData.getColumnClassName(i));
        columns.add(md);
    }
    return columns;
}

From source file:com.linuxrouter.netcool.session.QueryUtils.java

public ArrayList<HashMap<String, Object>> executeQuery(String dbName, String sql) {
    Long start = System.currentTimeMillis();
    ArrayList<HashMap<String, Object>> result = new ArrayList<>();
    HashMap<Integer, String> colTypes = new HashMap<Integer, String>();
    HashMap<Integer, String> colNames = new HashMap<Integer, String>();
    try {/*from   ww w  . j  ava 2  s . co m*/
        //connection caching...
        Connection con = null;
        if (connectionMap.get(dbName) == null) {
            BasicDataSource ds = DbUtils.getSimpleDataSourceByName(dbName);
            con = ds.getConnection();
            connectionMap.put(dbName, con);
        } else {
            con = connectionMap.get(dbName);

        }

        Statement st = con.createStatement();

        ResultSet rs = st.executeQuery(sql);
        ResultSetMetaData metaData = rs.getMetaData();
        int colCount = metaData.getColumnCount();
        for (int i = 1; i <= colCount; i++) {
            colTypes.put(i, metaData.getColumnTypeName(i));
            colNames.put(i, metaData.getColumnLabel(i));
        }
        while (rs.next()) {
            HashMap<String, Object> dado = new HashMap<>();
            for (int i = 1; i <= colCount; i++) {
                dado.put(colNames.get(i), rs.getObject(i));

            }
            result.add(dado);
        }
        rs.close();
        st.close();
        //con.close();
        Long end = System.currentTimeMillis();
        //logger.debug("Query on external DB took: " + (end - start) + "ms");
    } catch (SQLException ex) {
        logger.error("Erro ao executar query:", ex);
    }
    return result;
}

From source file:com.sinosoft.one.data.jade.rowmapper.MapEntryRowMapper.java

public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    if (rowNum == 0) {
        if (StringUtils.isNotEmpty(keyColumn)) {
            keyColumnIndex = rs.findColumn(keyColumn);
            if (keyColumnIndex <= 0) {
                throw new IllegalArgumentException(
                        String.format("wrong key name %s for method: %s ", keyColumn, modifier.getMethod()));
            }/*www.j  ava 2s. c  o m*/
            keyColumn = null;
        }

        if (logger.isDebugEnabled()) {
            logger.debug(String.format("keyIndex=%s; for method: %s ", keyColumnIndex, modifier.getMethod()));
        }
    }

    //   JDBC ResultSet ? Key
    Object key = JdbcUtils.getResultSetValue(rs, keyColumnIndex, keyType);
    if (key != null && !keyType.isInstance(key)) {
        ResultSetMetaData rsmd = rs.getMetaData();
        throw new TypeMismatchDataAccessException( // NL
                "Type mismatch affecting row number " + rowNum + " and column type '"
                        + rsmd.getColumnTypeName(keyColumnIndex) + "' expected type is '" + keyType + "'");
    }

    return new MapEntryImpl<Object, Object>(key, mapper.mapRow(rs, rowNum));
}

From source file:com.gzj.tulip.jade.rowmapper.MapEntryRowMapper.java

@Override
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    if (rowNum == 0) {
        if (StringUtils.isNotEmpty(keyColumn)) {
            keyColumnIndex = rs.findColumn(keyColumn);
            if (keyColumnIndex <= 0) {
                throw new IllegalArgumentException(
                        String.format("wrong key name %s for method: %s ", keyColumn, modifier.getMethod()));
            }/*from  www  .j  av  a2 s  . co m*/
            keyColumn = null;
        }

        if (logger.isDebugEnabled()) {
            logger.debug(String.format("keyIndex=%s; for method: %s ", keyColumnIndex, modifier.getMethod()));
        }
    }

    //   JDBC ResultSet ? Key
    Object key = JdbcUtils.getResultSetValue(rs, keyColumnIndex, keyType);
    if (key != null && !keyType.isInstance(key)) {
        ResultSetMetaData rsmd = rs.getMetaData();
        throw new TypeMismatchDataAccessException( // NL
                "Type mismatch affecting row number " + rowNum + " and column type '"
                        + rsmd.getColumnTypeName(keyColumnIndex) + "' expected type is '" + keyType + "'");
    }

    return new MapEntryImpl<Object, Object>(key, mapper.mapRow(rs, rowNum));
}

From source file:com.abixen.platform.module.chart.service.impl.AbstractDatabaseService.java

private DataSourceValueWeb getDataFromColumn(ResultSet row, String columnName) {
    try {/* w w  w  .  j  a v a2s  . co m*/
        ResultSetMetaData resultSetMetaData = row.getMetaData();
        String columnTypeName = resultSetMetaData.getColumnTypeName(row.findColumn(columnName));
        if ("BIGINT".equals(columnTypeName)) {
            columnTypeName = "INTEGER";
        }
        if ("VARCHAR".equals(columnTypeName)) {
            columnTypeName = "STRING";
        }
        return getValueAsDataSourceValue(row, columnName, DataValueType.valueOf(columnTypeName));
    } catch (SQLException e) {
        throw new DataSourceValueException("Error when getting value from column. " + e.getMessage());
    }
}

From source file:com.simplymeasured.prognosticator.ThreadedQueryRunnable.java

@Override
public void run() {
    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);

    try {//w w  w.  java  2 s  .c  om
        template.query(query, parameters, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                try {
                    Map<String, Object> result = Maps.newHashMap();

                    final ResultSetMetaData metadata = resultSet.getMetaData();

                    for (int i = 1; i <= metadata.getColumnCount(); i++) {
                        String columnTypeName = metadata.getColumnTypeName(i);

                        final Object value;

                        if ("array".equalsIgnoreCase(columnTypeName)) {
                            String stringValue = resultSet.getString(i);

                            if (stringValue != null) {
                                value = objectMapper.readValue(stringValue, List.class);
                            } else {
                                value = null;
                            }
                        } else if ("map".equalsIgnoreCase(columnTypeName)
                                || "struct".equalsIgnoreCase(columnTypeName)) {
                            String stringValue = resultSet.getString(i);

                            if (stringValue != null) {
                                value = objectMapper.readValue(stringValue, Map.class);
                            } else {
                                value = null;
                            }
                        } else {
                            value = resultSet.getObject(i);
                        }

                        result.put(metadata.getColumnName(i), value);
                    }

                    resultQueue.put(result);
                } catch (SQLException se) {
                    LOG.warn("Database error!", se);
                    throw new RuntimeException("Database error!", se);
                } catch (InterruptedException ie) {
                    LOG.warn("Query killed!", ie);
                    throw new RuntimeException("Query killed!", ie);
                } catch (Exception ex) {
                    LOG.warn("Unable to parse row!", ex);
                    throw new RuntimeException("Unable to parse row!", ex);
                }
            }
        });

        resultQueue.put(Collections.<String, Object>emptyMap());
    } catch (DataAccessException dae) {
        try {
            resultQueue.put(Collections.<String, Object>emptyMap());
        } catch (InterruptedException ie) {
            LOG.warn("Queue is dead!", ie);
        }

        LOG.warn("Unable to execute query - attempting to clean up", dae);
    } catch (InterruptedException ie) {
        LOG.warn("Queue is dead!", ie);
    }
}

From source file:com.abixen.platform.service.businessintelligence.multivisualization.service.impl.AbstractDatabaseService.java

private DataValueWeb getDataFromColumn(ResultSet row, String columnName) {
    try {//from   w w  w.  ja va 2s  .  c  o m
        ResultSetMetaData resultSetMetaData = row.getMetaData();
        String columnTypeName = resultSetMetaData.getColumnTypeName(row.findColumn(columnName)).toUpperCase();
        if ("BIGINT".equals(columnTypeName)) {
            columnTypeName = "INTEGER";
        }
        if ("VARCHAR".equals(columnTypeName)) {
            columnTypeName = "STRING";
        }
        if ("FLOAT8".equals(columnTypeName)) {
            columnTypeName = "DOUBLE";
        }
        return getValueAsDataSourceValue(row, columnName, DataValueType.valueOf(columnTypeName));
    } catch (SQLException e) {
        throw new DataSourceValueException("Error when getting value from column. " + e.getMessage());
    }
}

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.application.service.database.AbstractDatabaseService.java

private String getValidColumnTypeName(Integer columnIndex, ResultSetMetaData resultSetMetaData)
        throws SQLException {
    String columnTypeName = resultSetMetaData.getColumnTypeName(columnIndex).toUpperCase();
    Map<String, String> databaseTypeOnApplicationType = buildDatabaseTypeOnApplicationType();
    String mappedColumnType = databaseTypeOnApplicationType.get(columnTypeName);
    if (mappedColumnType != null) {
        return mappedColumnType;
    }//from  w ww.  j av a  2 s .  co m
    return columnTypeName;
}

From source file:com.abixen.platform.service.businessintelligence.multivisualization.service.impl.JsonFilterServiceImpl.java

private Map<String, String> getColumnTypeMapping(ResultSetMetaData rsmd) throws SQLException {
    int columnCount = rsmd.getColumnCount();
    Map<String, String> columnTypeMapping = new HashMap<>();

    IntStream.range(1, columnCount + 1).forEach(i -> {
        try {//from  ww  w  .  ja v  a2 s.  c o  m
            String columnTypeName = rsmd.getColumnTypeName(i);
            if ("BIGINT".equals(columnTypeName)) {
                columnTypeName = "INTEGER";
            }
            if ("VARCHAR".equals(columnTypeName)) {
                columnTypeName = "STRING";
            }
            if ("FLOAT8".equals(columnTypeName)) {
                columnTypeName = "DOUBLE";
            }
            columnTypeMapping.put(rsmd.getColumnName(i).toUpperCase(), columnTypeName.toUpperCase());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    });
    return columnTypeMapping;
}

From source file:org.apache.fineract.infrastructure.dataexport.helper.DataExportUtils.java

/**
 * Gets the meta data of the columns of the specified table
 * //w  w  w .  j a  v a 2  s  . c o  m
 * @param tableName
 * @param jdbcTemplate
 * @return List of {@link EntityColumnMetaData} objects
 */
public static List<EntityColumnMetaData> getTableColumnsMetaData(final String tableName,
        final JdbcTemplate jdbcTemplate) {
    final List<EntityColumnMetaData> entityColumnsMetaData = new ArrayList<>();
    final List<String> columnNames = new ArrayList<>();
    final DataExportCoreTable coreTable = DataExportCoreTable.newInstance(tableName);
    Set<String> columnsToBeRemovedFromListsOfEntityColumns = new HashSet<>(
            Arrays.asList(DataExportEntityColumnName.TRANSFER_TO_OFFICE_ID, DataExportEntityColumnName.VERSION,
                    DataExportEntityColumnName.IMAGE_ID, DataExportEntityColumnName.ACCOUNT_TYPE_ENUM,
                    DataExportEntityColumnName.DEPOSIT_TYPE_ENUM, DataExportEntityColumnName.SUB_STATUS,
                    DataExportEntityColumnName.FULL_NAME));

    try {
        // see - http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
        // LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. 
        // It can also be employed to obtain the types of the result columns if you are using a MySQL API 
        // that makes result set metadata available.
        final ResultSetMetaData resultSetMetaData = jdbcTemplate
                .query("select * from " + tableName + " limit 0", new ResultSetExtractor<ResultSetMetaData>() {

                    @Override
                    public ResultSetMetaData extractData(ResultSet rs)
                            throws SQLException, DataAccessException {
                        return rs.getMetaData();
                    }
                });

        if (resultSetMetaData != null) {
            final int numberOfColumns = resultSetMetaData.getColumnCount();

            for (int i = 1; i <= numberOfColumns; i++) {
                String columnName = resultSetMetaData.getColumnName(i);
                String columnLabel = resultSetMetaData.getColumnName(i);
                String columnType = resultSetMetaData.getColumnTypeName(i);
                Integer columnIsNullable = resultSetMetaData.isNullable(i);
                boolean isNullable = (columnIsNullable != 0);

                if (coreTable != null) {
                    switch (coreTable) {
                    case M_LOAN_TRANSACTION:
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.UNRECOGNIZED_INCOME_PORTION);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.SUSPENDED_INTEREST_PORTION_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.SUSPENDED_FEE_CHARGES_PORTION_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.SUSPENDED_PENALTY_CHARGES_PORTION_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.OUTSTANDING_LOAN_BALANCE_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.RECOVERED_PORTION_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.PAYMENT_DETAIL_ID);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.OFFICE_ID);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.IS_ACCCOUNT_TRANSFER);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.APPUSER_ID);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.EXTERNAL_ID);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.CREATED_DATE);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.TRANSACTION_TYPE_ENUM);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.LOAN_ID);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.AMOUNT);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.PRINCIPAL_PORTION_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.INTEREST_PORTION_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.FEE_CHARGES_PORTION_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.PENALTY_CHARGES_PORTION_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.OVERPAYMENT_PORTION_DERIVED);
                        break;

                    case M_SAVINGS_ACCOUNT_TRANSACTION:
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.OVERDRAFT_AMOUNT_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.RUNNING_BALANCE_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.CUMULATIVE_BALANCE_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.BALANCE_NUMBER_OF_DAYS_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.BALANCE_END_DATE_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.CREATED_DATE);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.TRANSACTION_TYPE_ENUM);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.APPUSER_ID);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.SAVINGS_ACCOUNT_ID);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.AMOUNT);
                        break;

                    case M_LOAN_REPAYMENT_SCHEDULE:
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.ID);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.LOAN_ID);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.FROMDATE);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.INSTALLMENT);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.PRINCIPAL_COMPLETED_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.PRINCIPAL_WRITTENOFF_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.INTEREST_COMPLETED_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.INTEREST_WAIVED_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.INTEREST_WRITTENOFF_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.ACCRUAL_INTEREST_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.SUSPENDED_INTEREST_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.FEE_CHARGES_WRITTENOFF_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.FEE_CHARGES_COMPLETED_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.FEE_CHARGES_WAIVED_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.ACCRUAL_FEE_CHARGES_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.SUSPENDED_FEE_CHARGES_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.PENALTY_CHARGES_COMPLETED_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.PENALTY_CHARGES_WAIVED_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.PENALTY_CHARGES_WRITTEN_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.ACCRUAL_PENALTY_CHARGES_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.SUSPENDED_PENALTY_CHARGES_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.TOTAL_PAID_IN_ADVANCE_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.TOTAL_PAID_LATE_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.COMPLETED_DERIVED);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.CREATED_BY_ID);
                        columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.CREATED_DATE);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.LAST_MODIFIED_BY_ID);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.LAST_MODIFIED_DATE);
                        columnsToBeRemovedFromListsOfEntityColumns
                                .add(DataExportEntityColumnName.RECALCULATED_INTEREST_COMPONENT);
                        break;

                    default:
                        break;
                    }
                }

                if (!columnsToBeRemovedFromListsOfEntityColumns.contains(columnName)) {
                    if (columnName.equals(DataExportEntityColumnName.ID)) {
                        columnLabel = DataExportEntityColumnName.TRANSACTION_ID;
                    }
                    EntityColumnMetaData entityColumnMetaData = EntityColumnMetaData.newInstance(columnName,
                            columnLabel, columnType, isNullable);

                    entityColumnsMetaData.add(entityColumnMetaData);
                    columnNames.add(columnName);
                }
            }
        }
    }

    catch (Exception exception) {
        exception.printStackTrace();
    }

    return entityColumnsMetaData;
}