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:com.mvdb.etl.dao.impl.JdbcOrderDAO.java

@Override
public Map<String, ColumnMetadata> findMetadata() {
    String sql = "SELECT * FROM ORDERS limit 1";
    final Map<String, ColumnMetadata> metaDataMap = new HashMap<String, ColumnMetadata>();

    getJdbcTemplate().query(sql, new RowCallbackHandler() {

        @Override/* w ww .  j a  v a  2s  .  com*/
        public void processRow(ResultSet row) throws SQLException {
            ResultSetMetaData rsm = row.getMetaData();
            int columnCount = rsm.getColumnCount();
            for (int column = 1; column < (columnCount + 1); column++) {
                ColumnMetadata metadata = new ColumnMetadata();
                metadata.setColumnLabel(rsm.getColumnLabel(column));
                metadata.setColumnName(rsm.getColumnName(column));
                metadata.setColumnType(rsm.getColumnType(column));
                metadata.setColumnTypeName(rsm.getColumnTypeName(column));

                metaDataMap.put(rsm.getColumnName(column), metadata);
            }

        }
    });

    return metaDataMap;
}

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

private synchronized 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  av a  2 s  .  c o m*/

        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 

    } 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;
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testSignedIntShouldBeInt() throws SQLException, IllegalArgumentException, IllegalAccessException {
    final ResultSetMetaData metadata = mock(ResultSetMetaData.class);
    when(metadata.getColumnCount()).thenReturn(1);
    when(metadata.getColumnType(1)).thenReturn(Types.INTEGER);
    when(metadata.isSigned(1)).thenReturn(true);
    when(metadata.getColumnName(1)).thenReturn("Col1");
    when(metadata.getTableName(1)).thenReturn("Table1");

    final ResultSet rs = mock(ResultSet.class);
    when(rs.getMetaData()).thenReturn(metadata);

    Schema schema = JdbcCommon.createSchema(rs);
    Assert.assertNotNull(schema);/*from   w  w w. j a  va  2  s  .c  om*/

    Schema.Field field = schema.getField("Col1");
    Schema fieldSchema = field.schema();
    Assert.assertEquals(2, fieldSchema.getTypes().size());

    boolean foundIntSchema = false;
    boolean foundNullSchema = false;

    for (Schema type : fieldSchema.getTypes()) {
        if (type.getType().equals(Schema.Type.INT)) {
            foundIntSchema = true;
        } else if (type.getType().equals(Schema.Type.NULL)) {
            foundNullSchema = true;
        }
    }

    assertTrue(foundIntSchema);
    assertTrue(foundNullSchema);
}

From source file:org.apache.syncope.core.util.ContentExporter.java

private void doExportTable(final TransformerHandler handler, final Connection conn, final String tableName,
        final String whereClause) throws SQLException, SAXException {

    LOG.debug("Export table {}", tableName);

    AttributesImpl attrs = new AttributesImpl();

    PreparedStatement stmt = null;
    ResultSet rs = null;//from   w  ww  .  j ava2 s .c  om
    ResultSet pkeyRS = null;
    try {
        // ------------------------------------
        // retrieve primary keys to perform an ordered select

        final DatabaseMetaData meta = conn.getMetaData();
        pkeyRS = meta.getPrimaryKeys(null, null, tableName);

        final StringBuilder orderBy = new StringBuilder();

        while (pkeyRS.next()) {
            final String columnName = pkeyRS.getString("COLUMN_NAME");
            if (columnName != null) {
                if (orderBy.length() > 0) {
                    orderBy.append(",");
                }

                orderBy.append(columnName);
            }
        }

        // ------------------------------------
        StringBuilder query = new StringBuilder();
        query.append("SELECT * FROM ").append(tableName).append(" a");
        if (StringUtils.isNotBlank(whereClause)) {
            query.append(" WHERE ").append(whereClause);
        }
        if (orderBy.length() > 0) {
            query.append(" ORDER BY ").append(orderBy);
        }
        stmt = conn.prepareStatement(query.toString());

        rs = stmt.executeQuery();
        while (rs.next()) {
            attrs.clear();

            final ResultSetMetaData rsMeta = rs.getMetaData();
            for (int i = 0; i < rsMeta.getColumnCount(); i++) {
                final String columnName = rsMeta.getColumnName(i + 1);
                final Integer columnType = rsMeta.getColumnType(i + 1);

                // Retrieve value taking care of binary values.
                String value = getValues(rs, columnName, columnType);
                if (value != null && (!COLUMNS_TO_BE_NULLIFIED.containsKey(tableName)
                        || !COLUMNS_TO_BE_NULLIFIED.get(tableName).contains(columnName))) {

                    attrs.addAttribute("", "", columnName, "CDATA", value);
                }
            }

            handler.startElement("", "", tableName, attrs);
            handler.endElement("", "", tableName);

            LOG.debug("Add record {}", attrs);
        }
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                LOG.error("While closing result set", e);
            }
        }
        if (pkeyRS != null) {
            try {
                pkeyRS.close();
            } catch (SQLException e) {
                LOG.error("While closing result set", e);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                LOG.error("While closing result set", e);
            }
        }
    }
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testUnsignedIntShouldBeLong()
        throws SQLException, IllegalArgumentException, IllegalAccessException {
    final ResultSetMetaData metadata = mock(ResultSetMetaData.class);
    when(metadata.getColumnCount()).thenReturn(1);
    when(metadata.getColumnType(1)).thenReturn(Types.INTEGER);
    when(metadata.getPrecision(1)).thenReturn(10);
    when(metadata.isSigned(1)).thenReturn(false);
    when(metadata.getColumnName(1)).thenReturn("Col1");
    when(metadata.getTableName(1)).thenReturn("Table1");

    final ResultSet rs = mock(ResultSet.class);
    when(rs.getMetaData()).thenReturn(metadata);

    Schema schema = JdbcCommon.createSchema(rs);
    Assert.assertNotNull(schema);/*from  w  w  w.  j ava  2  s.co m*/

    Schema.Field field = schema.getField("Col1");
    Schema fieldSchema = field.schema();
    Assert.assertEquals(2, fieldSchema.getTypes().size());

    boolean foundLongSchema = false;
    boolean foundNullSchema = false;

    for (Schema type : fieldSchema.getTypes()) {
        if (type.getType().equals(Schema.Type.LONG)) {
            foundLongSchema = true;
        } else if (type.getType().equals(Schema.Type.NULL)) {
            foundNullSchema = true;
        }
    }

    assertTrue(foundLongSchema);
    assertTrue(foundNullSchema);
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testMediumUnsignedIntShouldBeInt()
        throws SQLException, IllegalArgumentException, IllegalAccessException {
    final ResultSetMetaData metadata = mock(ResultSetMetaData.class);
    when(metadata.getColumnCount()).thenReturn(1);
    when(metadata.getColumnType(1)).thenReturn(Types.INTEGER);
    when(metadata.getPrecision(1)).thenReturn(8);
    when(metadata.isSigned(1)).thenReturn(false);
    when(metadata.getColumnName(1)).thenReturn("Col1");
    when(metadata.getTableName(1)).thenReturn("Table1");

    final ResultSet rs = mock(ResultSet.class);
    when(rs.getMetaData()).thenReturn(metadata);

    Schema schema = JdbcCommon.createSchema(rs);
    Assert.assertNotNull(schema);/*  ww w  . j  a v a2  s.  c  o  m*/

    Schema.Field field = schema.getField("Col1");
    Schema fieldSchema = field.schema();
    Assert.assertEquals(2, fieldSchema.getTypes().size());

    boolean foundIntSchema = false;
    boolean foundNullSchema = false;

    for (Schema type : fieldSchema.getTypes()) {
        if (type.getType().equals(Schema.Type.INT)) {
            foundIntSchema = true;
        } else if (type.getType().equals(Schema.Type.NULL)) {
            foundNullSchema = true;
        }
    }

    assertTrue(foundIntSchema);
    assertTrue(foundNullSchema);
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testConvertToAvroStreamForShort() throws SQLException, IOException {
    final ResultSetMetaData metadata = mock(ResultSetMetaData.class);
    when(metadata.getColumnCount()).thenReturn(1);
    when(metadata.getColumnType(1)).thenReturn(Types.TINYINT);
    when(metadata.getColumnName(1)).thenReturn("t_int");
    when(metadata.getTableName(1)).thenReturn("table");

    final ResultSet rs = mock(ResultSet.class);
    when(rs.getMetaData()).thenReturn(metadata);

    final AtomicInteger counter = new AtomicInteger(1);
    Mockito.doAnswer(new Answer<Boolean>() {
        @Override/*from w  w  w  . j  a va 2 s  .  c o m*/
        public Boolean answer(InvocationOnMock invocation) throws Throwable {
            return counter.getAndDecrement() > 0;
        }
    }).when(rs).next();

    final short s = 25;
    when(rs.getObject(Mockito.anyInt())).thenReturn(s);

    final ByteArrayOutputStream baos = new ByteArrayOutputStream();

    JdbcCommon.convertToAvroStream(rs, baos, false);

    final byte[] serializedBytes = baos.toByteArray();

    final InputStream instream = new ByteArrayInputStream(serializedBytes);

    final DatumReader<GenericRecord> datumReader = new GenericDatumReader<>();
    try (final DataFileStream<GenericRecord> dataFileReader = new DataFileStream<>(instream, datumReader)) {
        GenericRecord record = null;
        while (dataFileReader.hasNext()) {
            record = dataFileReader.next(record);
            assertEquals(Short.toString(s), record.get("t_int").toString());
        }
    }
}

From source file:org.apache.ambari.server.orm.DBAccessorImpl.java

public int getColumnType(String tableName, String columnName) throws SQLException {
    String query = "SELECT " + columnName + " FROM " + tableName;
    Statement statement = null;//from   w  w  w .j a v a2  s . c om

    try {
        statement = getConnection().createStatement();
        ResultSet rs = statement.executeQuery(query);

        ResultSetMetaData rsmd = rs.getMetaData();
        return rsmd.getColumnType(1);
    } finally {
        if (null != statement)
            statement.close();
    }
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testCreateSchemaTypes() throws SQLException, IllegalArgumentException, IllegalAccessException {
    final Set<Integer> fieldsToIgnore = new HashSet<>();
    fieldsToIgnore.add(Types.NULL);
    fieldsToIgnore.add(Types.OTHER);

    final Field[] fieldTypes = Types.class.getFields();
    for (final Field field : fieldTypes) {
        final Object fieldObject = field.get(null);
        final int type = (int) fieldObject;

        if (fieldsToIgnore.contains(Types.NULL)) {
            continue;
        }//w  w  w  .  java  2  s .c  om

        final ResultSetMetaData metadata = mock(ResultSetMetaData.class);
        when(metadata.getColumnCount()).thenReturn(1);
        when(metadata.getColumnType(1)).thenReturn(type);
        when(metadata.getColumnName(1)).thenReturn(field.getName());
        when(metadata.getTableName(1)).thenReturn("table");

        final ResultSet rs = mock(ResultSet.class);
        when(rs.getMetaData()).thenReturn(metadata);

        try {
            JdbcCommon.createSchema(rs);
        } catch (final IllegalArgumentException | SQLException sqle) {
            sqle.printStackTrace();
            Assert.fail("Failed when using type " + field.getName());
        }
    }
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testConvertToAvroStreamForBigDecimal() throws SQLException, IOException {
    final ResultSetMetaData metadata = mock(ResultSetMetaData.class);
    when(metadata.getColumnCount()).thenReturn(1);
    when(metadata.getColumnType(1)).thenReturn(Types.NUMERIC);
    when(metadata.getColumnName(1)).thenReturn("The.Chairman");
    when(metadata.getTableName(1)).thenReturn("1the::table");

    final ResultSet rs = mock(ResultSet.class);
    when(rs.getMetaData()).thenReturn(metadata);

    final AtomicInteger counter = new AtomicInteger(1);
    Mockito.doAnswer(new Answer<Boolean>() {
        @Override/*from  w  ww.ja  v  a  2s . c o m*/
        public Boolean answer(InvocationOnMock invocation) throws Throwable {
            return counter.getAndDecrement() > 0;
        }
    }).when(rs).next();

    final BigDecimal bigDecimal = new BigDecimal(38D);
    when(rs.getObject(Mockito.anyInt())).thenReturn(bigDecimal);

    final ByteArrayOutputStream baos = new ByteArrayOutputStream();

    JdbcCommon.convertToAvroStream(rs, baos, true);

    final byte[] serializedBytes = baos.toByteArray();

    final InputStream instream = new ByteArrayInputStream(serializedBytes);

    final DatumReader<GenericRecord> datumReader = new GenericDatumReader<>();
    try (final DataFileStream<GenericRecord> dataFileReader = new DataFileStream<>(instream, datumReader)) {
        GenericRecord record = null;
        while (dataFileReader.hasNext()) {
            record = dataFileReader.next(record);
            assertEquals("_1the__table", record.getSchema().getName());
            assertEquals(bigDecimal.toString(), record.get("The_Chairman").toString());
        }
    }
}