Example usage for java.sql ResultSet getMetaData

List of usage examples for java.sql ResultSet getMetaData

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getMetaData

Introduction

In this page you can find the example usage for java.sql ResultSet getMetaData.

Prototype

ResultSetMetaData getMetaData() throws SQLException;

Source Link

Document

Retrieves the number, types and properties of this ResultSet object's columns.

Usage

From source file:org.apache.drill.test.framework.Utils.java

public static String getSqlResult(ResultSet resultSet) throws SQLException {
    StringBuffer stringBuffer = new StringBuffer();
    List columnLabels = new ArrayList<String>();

    try {/*w ww  .j  a v a  2 s.  co  m*/
        int columnCount = resultSet.getMetaData().getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            columnLabels.add(resultSet.getMetaData().getColumnLabel(i));
        }
        List<Integer> types = Lists.newArrayList();
        for (int i = 1; i <= columnCount; i++) {
            types.add(resultSet.getMetaData().getColumnType(i));
        }

        LOG.debug("Result set data types:");
        LOG.debug(Utils.getTypesInStrings(types));
        stringBuffer.append(new ColumnList(types, columnLabels).toString() + "\n");

        while (resultSet.next()) {
            List<Object> values = Lists.newArrayList();
            for (int i = 1; i <= columnCount; i++) {
                try {
                    if (resultSet.getObject(i) == null) {
                        values.add(null);
                        continue;
                    }
                    if (resultSet.getMetaData().getColumnType(i) == Types.NVARCHAR) {
                        values.add(new String(resultSet.getBytes(i), "UTF-16"));
                    } else {
                        values.add(new String(resultSet.getBytes(i), "UTF-8"));
                    }
                } catch (Exception e) {
                    if (resultSet.getMetaData().getColumnType(i) == Types.DATE) {
                        values.add(resultSet.getDate(i));
                    } else {
                        values.add(resultSet.getObject(i));
                    }
                }
            }
            stringBuffer.append(new ColumnList(types, values).toString() + "\n");
        }
    } catch (IllegalArgumentException | IllegalAccessException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }
    }
    return stringBuffer.toString();
}

From source file:com.healthmarketscience.jackcess.ImportUtil.java

/**
 * Copy an existing JDBC ResultSet into a new (or optionally existing) table
 * in this database./*from w w  w .  j  a va  2  s  . co  m*/
 * 
 * @param name Name of the new table to create
 * @param source ResultSet to copy from
 * @param filter valid import filter
 * @param useExistingTable if {@code true} use current table if it already
 *                         exists, otherwise, create new table with unique
 *                         name
 *
 * @return the name of the imported table
 * 
 * @see Builder
 */
public static String importResultSet(ResultSet source, Database db, String name, ImportFilter filter,
        boolean useExistingTable) throws SQLException, IOException {
    ResultSetMetaData md = source.getMetaData();

    name = Database.escapeIdentifier(name);
    Table table = null;
    if (!useExistingTable || ((table = db.getTable(name)) == null)) {

        List<Column> columns = new LinkedList<Column>();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            Column column = new Column();
            column.setName(Database.escapeIdentifier(md.getColumnName(i)));
            int lengthInUnits = md.getColumnDisplaySize(i);
            column.setSQLType(md.getColumnType(i), lengthInUnits);
            DataType type = column.getType();
            // we check for isTrueVariableLength here to avoid setting the length
            // for a NUMERIC column, which pretends to be var-len, even though it
            // isn't
            if (type.isTrueVariableLength() && !type.isLongValue()) {
                column.setLengthInUnits((short) lengthInUnits);
            }
            if (type.getHasScalePrecision()) {
                int scale = md.getScale(i);
                int precision = md.getPrecision(i);
                if (type.isValidScale(scale)) {
                    column.setScale((byte) scale);
                }
                if (type.isValidPrecision(precision)) {
                    column.setPrecision((byte) precision);
                }
            }
            columns.add(column);
        }

        table = createUniqueTable(db, name, columns, md, filter);
    }

    List<Object[]> rows = new ArrayList<Object[]>(COPY_TABLE_BATCH_SIZE);
    int numColumns = md.getColumnCount();

    while (source.next()) {
        Object[] row = new Object[numColumns];
        for (int i = 0; i < row.length; i++) {
            row[i] = source.getObject(i + 1);
        }
        row = filter.filterRow(row);
        if (row == null) {
            continue;
        }
        rows.add(row);
        if (rows.size() == COPY_TABLE_BATCH_SIZE) {
            table.addRows(rows);
            rows.clear();
        }
    }
    if (rows.size() > 0) {
        table.addRows(rows);
    }

    return table.getName();
}

From source file:com.webbfontaine.valuewebb.model.util.Utils.java

public static List<Object[]> transformToList(ResultSet rs) throws SQLException {
    ResultSetMetaData rsMetaData = rs.getMetaData();

    int numberOfColumns = rsMetaData.getColumnCount();

    List<Object[]> result = new ArrayList<>(numberOfColumns);

    while (rs.next()) {
        Object[] row = new Object[numberOfColumns];
        for (int i = 0; i < numberOfColumns; i++) {
            if (rsMetaData.getColumnType(i + 1) == Types.TIMESTAMP) {
                row[i] = rs.getDate(i + 1);
            } else {
                row[i] = rs.getObject(i + 1);
            }//from   w  w  w  .  j a v  a  2  s . c o m
        }
        result.add(row);
    }

    return result;
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.DBExcelUtility.java

public static void displayResultSetRow(ResultSet rs) throws Exception {
    int maxcol = 20;
    StringBuffer sb = new StringBuffer();
    ResultSetMetaData mdata = rs.getMetaData();

    sb.append("\n");
    maxcol = mdata.getColumnCount();//from www  .j  a  va 2s  . c  o m

    for (int i = 1; i <= maxcol; i++) {
        sb.append(mdata.getColumnName(i) + "\t");
    }

    int rowcount = 0;

    sb.append("\n");
    for (int i = 1; i <= maxcol; i++) {
        try {
            sb.append(rs.getObject(i) + "\t");
        } catch (Exception e) {
            System.out.println("Exception " + e);
        }
    }
    System.out.println(sb.toString());
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.DBExcelUtility.java

public static void debugResultSet(ResultSet rs) throws Exception {
    int maxcol = 20;
    StringBuffer sb = new StringBuffer();
    ResultSetMetaData mdata = rs.getMetaData();

    sb.append("\n");
    maxcol = mdata.getColumnCount();/*from   w w  w .ja  va  2 s  .c  o m*/
    System.out.println("Total Columns : " + maxcol);

    for (int i = 1; i <= maxcol; i++) {
        sb.append(mdata.getColumnName(i) + "\t");
    }

    int rowcount = 0;

    while (rs.next()) {
        sb.append("\n");
        ++rowcount;
        for (int i = 1; i <= maxcol; i++) {
            try {
                sb.append(rs.getObject(i) + "\t");
            } catch (Exception e) {
                System.out.println("Exception " + e);
            }
        }
    }
    System.out.println(sb.toString());
    System.out.println("Total Rows : " + rowcount);
}

From source file:cn.clickvalue.cv2.model.rowmapper.BeanPropertyRowMapper.java

/**
 * Retrieve a JDBC column value from a ResultSet, using the most appropriate
 * value type. The returned value should be a detached value object, not having
 * any ties to the active ResultSet: in particular, it should not be a Blob or
 * Clob object but rather a byte array respectively String representation.
 * <p>Uses the <code>getObject(index)</code> method, but includes additional "hacks"
 * to get around Oracle 10g returning a non-standard object for its TIMESTAMP
 * datatype and a <code>java.sql.Date</code> for DATE columns leaving out the
 * time portion: These columns will explicitly be extracted as standard
 * <code>java.sql.Timestamp</code> object.
 * @param rs is the ResultSet holding the data
 * @param index is the column index//from   www  .ja va  2s . com
 * @return the value object
 * @throws SQLException if thrown by the JDBC API
 * @see java.sql.Blob
 * @see java.sql.Clob
 * @see java.sql.Timestamp
 */
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
    Object obj = rs.getObject(index);
    if (obj instanceof Blob) {
        obj = rs.getBytes(index);
    } else if (obj instanceof Clob) {
        obj = rs.getString(index);
    } else if (obj != null && obj.getClass().getName().startsWith("oracle.sql.TIMESTAMP")) {
        obj = rs.getTimestamp(index);
    } else if (obj != null && obj.getClass().getName().startsWith("oracle.sql.DATE")) {
        String metaDataClassName = rs.getMetaData().getColumnClassName(index);
        if ("java.sql.Timestamp".equals(metaDataClassName)
                || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
            obj = rs.getTimestamp(index);
        } else {
            obj = rs.getDate(index);
        }
    } else if (obj != null && obj instanceof java.sql.Date) {
        if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
            obj = rs.getTimestamp(index);
        }
    }
    return obj;
}

From source file:com.zimbra.cs.mailbox.util.MetadataDump.java

private static Row getItemRow(DbConnection conn, int groupId, int mboxId, int itemId, boolean fromDumpster)
        throws ServiceException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {/*from ww  w  .  j  a  v a2  s.  co  m*/
        String sql = "SELECT * FROM " + DbMailItem.getMailItemTableName(groupId, fromDumpster)
                + " WHERE mailbox_id = " + mboxId + " AND id = " + itemId;
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
        if (!rs.next())
            throw ServiceException.INVALID_REQUEST("No such item: mbox=" + mboxId + ", item=" + itemId, null);
        Row row = new Row();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int cols = rsMeta.getColumnCount();
        for (int i = 1; i <= cols; i++) {
            String colName = rsMeta.getColumnName(i);
            String colValue = rs.getString(i);
            if (rs.wasNull())
                colValue = null;
            row.addColumn(colName, colValue);
        }
        return row;
    } catch (SQLException e) {
        throw ServiceException.INVALID_REQUEST("No such item: mbox=" + mboxId + ", item=" + itemId, e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

From source file:com.quest.orahive.HiveJdbcClient.java

private static List<OracleTableColumn> getOracleTableColumnsForHiveResults(ResultSet resultSet) {

    List<OracleTableColumn> result = null;

    try {/*from  w w w .j  a v a2  s.  c o m*/
        ResultSetMetaData metaData = resultSet.getMetaData();

        result = new ArrayList<OracleTableColumn>(metaData.getColumnCount());

        for (int idx = 0; idx < metaData.getColumnCount(); idx++) {
            OracleTableColumn column = new OracleTableColumn();
            result.add(column);

            // column Name...
            column.setName(metaData.getColumnLabel(idx + 1)); //<- 1-based in JDBC;

            // Sql Type...
            column.sqlType = metaData.getColumnType(idx + 1); //<- 1-based in JDBC

            // column Oracle data-type...

            Constants.OracleType oracleType = javaSqlTypeToOracleType(column.sqlType);

            switch (oracleType) {

            case VARCHAR2: {

                column.oracleDataType = String.format("%s(%d)", oracleType.toString(), 4000 // Max length for a varchar
                );
                break;
            }

            default: {
                column.oracleDataType = oracleType.toString();
                break;
            }

            }
        }

    } catch (SQLException ex) {
        LOG.error("An error occurred when processing the metadata for the Hive result-set.", ex);
        System.exit(1);
    }

    return result;
}

From source file:com.bigdata.etl.util.DwUtil.java

public static void bulkInsert(String tableName, List<Map<String, String>> lst) {

    ResultSet rs = null;
    java.sql.Statement stmt = null;

    try (java.sql.Connection conn = DataSource.getConnection()) {
        stmt = conn.createStatement();//ww w  .  j  ava2  s.c o m
        rs = stmt.executeQuery("select top 0 * from " + tableName);
        try (SQLServerBulkCopy bulk = new SQLServerBulkCopy(url + "user=" + user + ";password=" + password)) {
            SQLServerBulkCopyOptions sqlsbc = new SQLServerBulkCopyOptions();
            sqlsbc.setBulkCopyTimeout(60 * 60 * 1000);
            bulk.setBulkCopyOptions(sqlsbc);
            bulk.setDestinationTableName(tableName);
            ResultSetMetaData rsmd = rs.getMetaData();
            if (lst == null) {
                return;
            }
            // System.out.println(LocalTime.now() + " "+Thread.currentThread().getId()+" "+lst.size());
            try (CachedRowSetImpl x = new CachedRowSetImpl()) {
                x.populate(rs);
                for (int k = 0; k < lst.size(); k++) {
                    Map<String, String> map = lst.get(k);
                    x.last();
                    x.moveToInsertRow();
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        String name = rsmd.getColumnName(i).toUpperCase();
                        int type = rsmd.getColumnType(i);//package java.sql.Type?

                        try {
                            switch (type) {
                            case Types.VARCHAR:
                            case Types.NVARCHAR:
                                int len = rsmd.getColumnDisplaySize(i);
                                String v = map.get(name);
                                if (map.containsKey(name)) {
                                    x.updateString(i, v.length() > len ? v.substring(0, len) : v);
                                } else {
                                    x.updateNull(i);
                                }
                                break;
                            case Types.BIGINT:
                                if (map.containsKey(name) && map.get(name).matches("\\d{1,}")) {
                                    x.updateLong(i, Long.valueOf(map.get(name)));
                                } else {
                                    //   x.updateLong(i, 0);
                                    x.updateNull(i);
                                }
                                break;
                            case Types.FLOAT:
                                if (map.containsKey(name) && map.get(name).matches("([+-]?)\\d*\\.\\d+$")) {
                                    x.updateFloat(i, Float.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);

                                }
                                break;
                            case Types.DOUBLE:
                                if (map.containsKey(name) && map.get(name).trim().length() > 0
                                        && StringUtils.isNumeric(map.get(name))) {
                                    x.updateDouble(i, Double.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);
                                }
                                break;

                            case Types.INTEGER:
                                if (map.containsKey(name) && map.get(name).matches("\\d{1,}")) {
                                    x.updateInt(i, Integer.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);
                                }
                                break;

                            default:
                                throw new RuntimeException("? " + type);
                            }
                            /*
                            if(map.containsKey("SYS_TELECOM"))
                            System.err.println(map.get("SYS_TELECOM"));
                             */
                        } catch (RuntimeException | SQLException e) {
                            Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE,
                                    "? name=" + name + " v=" + map.get(name), e);
                        }

                    }
                    x.insertRow();
                    x.moveToCurrentRow();
                    //x.acceptChanges();
                }

                long start = System.currentTimeMillis();
                bulk.writeToServer(x);
                long end = System.currentTimeMillis();
                System.out.println(LocalTime.now() + " " + Thread.currentThread().getId() + " "
                        + (end - start) + "ms" + " " + x.size());
            }
        }

    } catch (SQLException e) {
        Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE, null, e);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}

From source file:com.google.visualization.datasource.util.SqlDataSourceHelper.java

/**
 * Returns the table description which includes the ids, labels and types of
 * the table columns./*from  w  ww .j  av  a 2  s .co  m*/
 *
 * @param rs The result set holding the data from the sql table.
 * @param columnIdsList The list of the column ids in the data table.
 *
 * @return The table description.
 *
 * @throws SQLException Thrown when the connection to the database failed.
 */
static DataTable buildColumns(ResultSet rs, List<String> columnIdsList) throws SQLException {
    DataTable result = new DataTable();
    ResultSetMetaData metaData = rs.getMetaData();
    int numOfCols = metaData.getColumnCount();
    // For each column in the table, create the column description. SQL indexes
    // are 1-based.
    for (int i = 1; i <= numOfCols; i++) {
        String id = (columnIdsList == null) ? metaData.getColumnLabel(i) : columnIdsList.get(i - 1);
        ColumnDescription columnDescription = new ColumnDescription(id,
                sqlTypeToValueType(metaData.getColumnType(i)), metaData.getColumnLabel(i));
        result.addColumn(columnDescription);
    }
    return result;
}