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.pentaho.reporting.engine.classic.core.modules.misc.tablemodel.TypeMapper.java

public static Class<?> mapForColumn(ResultSetMetaData rsmd, int i) {
    try {//ww  w. ja  v a  2  s.c o m
        final ClassLoader cl = ObjectUtilities.getClassLoader(TypeMapper.class);
        try {
            final String tn = rsmd.getColumnClassName(i + 1);
            if (tn == null) {
                final int colType = rsmd.getColumnType(i + 1);
                return mapSQLType(colType);
            } else {
                return Class.forName(tn, false, cl);
            }
        } catch (final Exception oops) {
            // ignore exception
            final int colType = rsmd.getColumnType(i + 1);
            return mapSQLType(colType);
        }
    } catch (Exception e) {
        // still ignore the exception
        return Object.class;
    }
}

From source file:com.adaptris.jdbc.JdbcResultSetImpl.java

private static JdbcResultRow mapRow(ResultSet resultSet) throws SQLException {
    ResultSetMetaData rsmd = resultSet.getMetaData();
    int columnCount = rsmd.getColumnCount();

    JdbcResultRow row = new JdbcResultRow();
    for (int counter = 1; counter <= columnCount; counter++) {
        row.setFieldValue(StringUtils.defaultIfEmpty(rsmd.getColumnLabel(counter), rsmd.getColumnName(counter)),
                resultSet.getObject(counter), rsmd.getColumnType(counter));
    }//from  w w  w  .  j av a2  s.  c o m
    return row;
}

From source file:it.alidays.mapengine.codegenerator.MapperEngineCodeGenerator.java

private static void manageRetrieve(Retrieve retrieve, Connection connection, String packageName,
        File destinationDir)/*from  w  w  w .  j  a  v a  2  s  . c  om*/
        throws SQLException, JClassAlreadyExistsException, IOException, MapperEngineCodeGeneratorException {
    logger.info("Generating map for {}", retrieve.getId());

    int vuidCount = RetrieveHandler.getVuidCount(retrieve.getContent());
    String content = retrieve.getContent().replaceAll(RetrieveHandler.VUID_KEY, "?");

    Map<String, Integer> columns = new LinkedHashMap<>();

    logger.info("\tRetrieving columns' name");
    try (PreparedStatement preparedStatement = connection.prepareStatement(content)) {
        for (int index = 1; index <= vuidCount; index++) {
            preparedStatement.setObject(index, "_");
        }

        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        for (int i = 1, n = resultSetMetaData.getColumnCount(); i <= n; i++) {
            String columnName = Utils.arrangeColumnName(resultSetMetaData.getColumnLabel(i));
            Integer columnType = resultSetMetaData.getColumnType(i);
            columns.put(columnName, columnType);
        }
    }
    logger.info("\tRetrieved {} columns' name", columns.size());

    createMapClass(retrieve, columns, packageName, destinationDir);
    createRetrieveClass(retrieve, packageName, destinationDir);

    logger.info("Map successfully generated for {}", retrieve.getId());
}

From source file:org.georepublic.db.utils.ResultSetConverter.java

public static JSONArray convert(ResultSet rs) throws SQLException, JSONException {

    JSONArray json = new JSONArray();
    ResultSetMetaData rsmd = rs.getMetaData();

    while (rs.next()) {
        int numColumns = rsmd.getColumnCount();
        JSONObject obj = new JSONObject();

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

            if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
                obj.put(column_name, rs.getArray(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
                obj.put(column_name, rs.getInt(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
                obj.put(column_name, rs.getBoolean(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
                obj.put(column_name, rs.getBlob(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
                obj.put(column_name, rs.getDouble(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
                obj.put(column_name, rs.getFloat(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
                obj.put(column_name, rs.getInt(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
                obj.put(column_name, rs.getNString(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
                obj.put(column_name, rs.getString(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
                obj.put(column_name, rs.getInt(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
                obj.put(column_name, rs.getInt(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
                obj.put(column_name, rs.getDate(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
                obj.put(column_name, rs.getTimestamp(column_name));
            } else {
                obj.put(column_name, rs.getObject(column_name));
            }/*from  w w  w . j  a v a2 s  .  co m*/
        }

        json.put(obj);
    }

    return json;

}

From source file:com.thinkbiganalytics.util.JdbcCommon.java

/**
 * converts a JDBC result set to an Avro stream
 *
 * @param rs        The result set of the JDBC query
 * @param outStream The output stream to for the Avro formatted records
 * @return the number of rows converted to Avro format
 * @throws SQLException if errors occur while reading data from the database
 * @throws IOException  if unable to convert to Avro format
 *///from w ww. j a  v a  2  s .  c o m
public static long convertToAvroStream(final ResultSet rs, final OutputStream outStream)
        throws SQLException, IOException {
    final Schema schema = createSchema(rs);
    final GenericRecord rec = new GenericData.Record(schema);

    final DatumWriter<GenericRecord> datumWriter = new GenericDatumWriter<>(schema);
    try (final DataFileWriter<GenericRecord> dataFileWriter = new DataFileWriter<>(datumWriter)) {
        dataFileWriter.create(schema, outStream);

        final ResultSetMetaData meta = rs.getMetaData();
        final int nrOfColumns = meta.getColumnCount();
        long nrOfRows = 0;
        while (rs.next()) {
            for (int i = 1; i <= nrOfColumns; i++) {
                final int javaSqlType = meta.getColumnType(i);
                final Object value = rs.getObject(i);

                if (value == null) {
                    rec.put(i - 1, null);

                } else if (javaSqlType == BINARY || javaSqlType == VARBINARY || javaSqlType == LONGVARBINARY
                        || javaSqlType == ARRAY || javaSqlType == BLOB || javaSqlType == CLOB) {
                    // bytes requires little bit different handling
                    byte[] bytes = rs.getBytes(i);
                    ByteBuffer bb = ByteBuffer.wrap(bytes);
                    rec.put(i - 1, bb);

                } else if (value instanceof Byte) {
                    // tinyint(1) type is returned by JDBC driver as java.sql.Types.TINYINT
                    // But value is returned by JDBC as java.lang.Byte
                    // (at least H2 JDBC works this way)
                    // direct put to avro record results:
                    // org.apache.avro.AvroRuntimeException: Unknown datum type java.lang.Byte
                    rec.put(i - 1, ((Byte) value).intValue());

                } else if (value instanceof BigDecimal || value instanceof BigInteger) {
                    // Avro can't handle BigDecimal and BigInteger as numbers - it will throw an AvroRuntimeException such as: "Unknown datum type: java.math.BigDecimal: 38"
                    rec.put(i - 1, value.toString());

                } else if (value instanceof Number || value instanceof Boolean) {
                    rec.put(i - 1, value);

                } else {
                    // The different types that we support are numbers (int, long, double, float),
                    // as well as boolean values and Strings. Since Avro doesn't provide
                    // timestamp types, we want to convert those to Strings. So we will cast anything other
                    // than numbers or booleans to strings by using the toString() method.
                    rec.put(i - 1, value.toString());
                }
            }
            dataFileWriter.append(rec);
            nrOfRows += 1;
        }

        return nrOfRows;
    }
}

From source file:com.nextep.designer.sqlclient.ui.helpers.SQLHelper.java

public static INextepMetadata createOfflineMetadata(ResultSetMetaData md, String sqlQuery) throws SQLException {
    final NextepResultSetMetaData nmd = new NextepResultSetMetaData();
    DMLParseResult parseResult = null;/*from  w  w  w. j a  v a  2s  .  com*/
    nmd.setColumnCount(md.getColumnCount());
    for (int i = 1; i <= md.getColumnCount(); i++) {
        nmd.setColumnName(i, md.getColumnName(i));
        nmd.setColumnType(i, md.getColumnType(i));
        // Fetching tablename from driver
        String tableName = md.getTableName(i);
        // If not available we try to parse ourselves
        if (tableName == null || "".equals(tableName)) {
            // Parsing
            if (parseResult == null) {
                try {
                    parseResult = parseSQL(sqlQuery, 1);
                } catch (RuntimeException e) {
                    LOGGER.error("Error while parsing SQL : " + e.getMessage(), e);
                }
            }
            // Only providing name on single table select
            if (parseResult != null && parseResult.getFromTables().size() == 1) {
                tableName = parseResult.getFromTables().iterator().next().getTableName();
            }
        }
        nmd.setTableName(i, tableName);
    }
    return nmd;
}

From source file:org.jumpmind.symmetric.db.derby.DerbyFunctions.java

protected static void appendCsvString(String tableName, String[] columnNames, String[] pkColumnNames,
        ResultSet rs, StringBuilder builder) throws SQLException {
    ResultSetMetaData metaData = rs.getMetaData();
    for (String columnName : columnNames) {
        if (StringUtils.isNotBlank(columnName)) {
            int index = findColumnIndex(metaData, columnName);
            if (index >= 0) {
                int type = metaData.getColumnType(index);
                switch (type) {
                case Types.BLOB:
                    builder.append(//w w w. jav a  2s .c o  m
                            blobToString(columnName, tableName, getPrimaryKeyWhereString(pkColumnNames, rs)));
                    builder.append(",");
                    break;
                case Types.CLOB:
                    builder.append(
                            clobToString(columnName, tableName, getPrimaryKeyWhereString(pkColumnNames, rs)));
                    builder.append(",");
                    break;
                default:
                    builder.append(escape(rs.getString(index)));
                    builder.append(",");
                    break;
                }
            } else {
                builder.append(",");
            }
        } else {
            builder.append(",");
        }
    }
}

From source file:ProxyAuthTest.java

private static void runQuery(String sqlStmt) throws Exception {
    Statement stmt = con.createStatement();
    ResultSet res = stmt.executeQuery(sqlStmt);

    ResultSetMetaData meta = res.getMetaData();
    System.out.println("Resultset has " + meta.getColumnCount() + " columns");
    for (int i = 1; i <= meta.getColumnCount(); i++) {
        System.out.println(/*w w w .  java 2s  .c om*/
                "Column #" + i + " Name: " + meta.getColumnName(i) + " Type: " + meta.getColumnType(i));
    }

    while (res.next()) {
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            System.out.println("Column #" + i + ": " + res.getString(i));
        }
    }
    res.close();
    stmt.close();
}

From source file:org.jumpmind.symmetric.db.derby.DerbyFunctions.java

public static String getPrimaryKeyWhereString(String[] pkColumnNames, ResultSet rs) throws SQLException {
    final String AND = " and ";
    ResultSetMetaData metaData = rs.getMetaData();
    StringBuilder b = new StringBuilder();
    for (int i = 0; i < pkColumnNames.length; i++) {
        String columnName = pkColumnNames[i];
        int index = findColumnIndex(metaData, columnName);
        int type = metaData.getColumnType(index);
        if (type != Types.BINARY && type != Types.BLOB && type != Types.LONGVARBINARY
                && type != Types.VARBINARY) {
            b.append("\"").append(columnName).append("\"=");
            switch (type) {
            case Types.BIT:
            case Types.TINYINT:
            case Types.SMALLINT:
            case Types.INTEGER:
            case Types.BIGINT:
            case Types.FLOAT:
            case Types.REAL:
            case Types.DOUBLE:
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.BOOLEAN:
                b.append(rs.getObject(index));
                break;
            case Types.CHAR:
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                b.append("\"").append(rs.getString(index)).append("\"");
                break;
            case Types.DATE:
            case Types.TIMESTAMP:
                b.append("{ts '");
                b.append(rs.getString(index));
                b.append("'}");
                break;
            }// w  ww.  j  av a 2 s.c  o  m
            b.append(AND);
        }
    }
    b.replace(b.length() - AND.length(), b.length(), "");
    return b.toString();
}

From source file:org.trafodion.rest.util.JdbcT4Util.java

public static JSONArray convertResultSetToJSON(java.sql.ResultSet rs) throws Exception {
    if (LOG.isDebugEnabled())
        LOG.debug("Begin convertResultSetToJSON");

    JSONArray json = new JSONArray();

    try {//from w  w  w . j  a v a  2s  .c om

        java.sql.ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next()) {
            int numColumns = rsmd.getColumnCount();
            JSONObject obj = new JSONObject();

            for (int i = 1; i < numColumns + 1; i++) {

                String column_name = rsmd.getColumnName(i);

                if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
                    obj.put(column_name, rs.getArray(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
                    obj.put(column_name, rs.getLong(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
                    obj.put(column_name, rs.getBoolean(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
                    obj.put(column_name, rs.getBlob(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
                    obj.put(column_name, rs.getDouble(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
                    obj.put(column_name, rs.getFloat(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
                    obj.put(column_name, rs.getNString(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.CHAR
                        || rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
                    //prevent obj.put from removing null key value from JSONObject
                    String s = rs.getString(column_name);
                    if (s == null)
                        obj.put(column_name, new String(""));
                    else
                        obj.put(column_name, rs.getString(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
                    obj.put(column_name, rs.getDate(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
                    obj.put(column_name, rs.getTimestamp(column_name));
                } else {
                    obj.put(column_name, rs.getObject(column_name));
                }

            } //end foreach 
            json.put(obj);

        } //end while 

        if (json.length() == 0) {

            int numColumns = rsmd.getColumnCount();
            JSONObject obj = new JSONObject();

            for (int i = 1; i < numColumns + 1; i++) {

                String column_name = rsmd.getColumnName(i);
                obj.put(column_name, "");
            }
            json.put(obj);
        }

    } catch (SQLException e) {
        e.printStackTrace();
        if (LOG.isDebugEnabled())
            LOG.error(e.getMessage());
        throw e;
    } catch (Exception e) {
        e.printStackTrace();
        if (LOG.isDebugEnabled())
            LOG.error(e.getMessage());
        throw e;
    }

    if (LOG.isDebugEnabled())
        LOG.debug("End convertResultSetToJSON");

    return json;
}