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:br.bookmark.db.util.ResultSetUtils.java

/**
 * Returns next record of result set as a Map.
 * The keys of the map are the column names,
 * as returned by the metadata.//from   w  w w.ja v a  2s .com
 * The values are the columns as Objects.
 *
 * @param resultSet The ResultSet to process.
 * @exception SQLException if an error occurs.
 */
public static Map getMap(ResultSet resultSet) throws SQLException {

    // Acquire resultSet MetaData
    ResultSetMetaData metaData = resultSet.getMetaData();
    int cols = metaData.getColumnCount();

    // Create hashmap, sized to number of columns
    HashMap row = new HashMap(cols, 1);

    // Transfer record into hashmap
    if (resultSet.next()) {
        for (int i = 1; i <= cols; i++) {
            row.put(metaData.getColumnName(i), resultSet.getObject(i));
        }
    } // end while

    return ((Map) row);

}

From source file:br.bookmark.db.util.ResultSetUtils.java

/**
 * Return a Collection of Maps, each representing
 * a row from the ResultSet./*  ww w .  jav a 2s  .  c o m*/
 * The keys of the map are the column names,
 * as returned by the metadata.
 * The values are the columns as Objects.
 *
 * @param resultSet The ResultSet to process.
 * @exception SQLException if an error occurs.
 */
public static Collection getMaps(ResultSet resultSet) throws SQLException {

    // Acquire resultSet MetaData
    ResultSetMetaData metaData = resultSet.getMetaData();
    int cols = metaData.getColumnCount();

    // Use ArrayList to maintain ResultSet sequence
    ArrayList list = new ArrayList();

    // Scroll to each record, make map of row, add to list
    while (resultSet.next()) {
        HashMap row = new HashMap(cols, 1);
        for (int i = 1; i <= cols; i++) {
            row.put(metaData.getColumnName(i), resultSet.getString(i));
        }
        list.add(row);
    } // end while

    return ((Collection) list);

}

From source file:com.splicemachine.homeless.TestUtils.java

private static Map<String, Object> resultSetToOrderedMap(ResultSet rs) throws SQLException {
    Map<String, Object> result = new LinkedHashMap<>();
    ResultSetMetaData rsmd = rs.getMetaData();
    int cols = rsmd.getColumnCount();

    for (int i = 1; i <= cols; i++) {
        result.put(rsmd.getColumnName(i), rs.getObject(i));
    }//from   w  w w  .j  av a2  s  . co m

    return result;
}

From source file:it.greenvulcano.gvesb.utils.ResultSetUtils.java

/**
 * Returns all values from the ResultSet as an XML.
 * For instance, if the ResultSet has 3 values, the returned XML will have following fields:
 *                                <RowSet>
 *                                  <data>
 *                                    <row>
 *                                      <col>value1</col>
 *                                      <col>value2</col>
 *                                      <col>value3</col>
 *                                    </row>
 *                                    <row>
 *                                      <col>value4</col>
 *                                      <col>value5</col>
 *                                      <col>value6</col>
 *                                    </row>
 *                                  ../*from   w  ww  . j  a  v a2s .  c  o  m*/
 *                                    <row>
 *                                      <col>valuex</col>
 *                                      <col>valuey</col>
 *                                      <col>valuez</col>
 *                                    </row>
 *                                  </data>
 *                                </RowSet>
 * @param rs
 * @return
 * @throws Exception
 */
public static Document getResultSetAsDOM(ResultSet rs) throws Exception {
    XMLUtils xml = XMLUtils.getParserInstance();
    try {
        Document doc = xml.newDocument("RowSet");
        Element docRoot = doc.getDocumentElement();

        if (rs != null) {
            try {
                ResultSetMetaData metadata = rs.getMetaData();
                Element data = null;
                Element row = null;
                Element col = null;
                Text text = null;
                String textVal = null;
                while (rs.next()) {
                    boolean restartResultset = false;
                    for (int j = 1; j <= metadata.getColumnCount() && !restartResultset; j++) {
                        col = xml.createElement(doc, "col");
                        restartResultset = false;
                        switch (metadata.getColumnType(j)) {
                        case Types.CLOB: {
                            Clob clob = rs.getClob(j);
                            if (clob != null) {
                                Reader is = clob.getCharacterStream();
                                StringWriter strW = new StringWriter();

                                IOUtils.copy(is, strW);
                                is.close();
                                textVal = strW.toString();
                            } else {
                                textVal = "";
                            }
                        }
                            break;
                        case Types.BLOB: {
                            Blob blob = rs.getBlob(j);
                            if (blob != null) {
                                InputStream is = blob.getBinaryStream();
                                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                                IOUtils.copy(is, baos);
                                is.close();
                                try {
                                    byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length());
                                    textVal = new String(Base64.getEncoder().encode(buffer));
                                } catch (SQLFeatureNotSupportedException exc) {
                                    textVal = new String(Base64.getEncoder().encode(baos.toByteArray()));
                                }
                            } else {
                                textVal = "";
                            }
                        }
                            break;
                        case -10: { // OracleTypes.CURSOR
                            Object obj = rs.getObject(j);
                            if (obj instanceof ResultSet) {
                                rs = (ResultSet) obj;
                                metadata = rs.getMetaData();
                            }
                            restartResultset = true;
                        }
                            break;
                        default: {
                            textVal = rs.getString(j);
                            if (textVal == null) {
                                textVal = "";
                            }
                        }
                        }
                        if (restartResultset) {
                            continue;
                        }
                        if (row == null || j == 1) {
                            row = xml.createElement(doc, "row");
                        }
                        if (textVal != null) {
                            text = doc.createTextNode(textVal);
                            col.appendChild(text);
                        }
                        row.appendChild(col);
                    }
                    if (row != null) {
                        if (data == null) {
                            data = xml.createElement(doc, "data");
                        }
                        data.appendChild(row);
                    }
                }
                if (data != null) {
                    docRoot.appendChild(data);
                }
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (Exception exc) {
                        // do nothing
                    }
                    rs = null;
                }
            }
        }

        return doc;
    } finally {
        XMLUtils.releaseParserInstance(xml);
    }
}

From source file:com.adaptris.core.util.JdbcUtil.java

public static Connection testConnection(Connection sqlConnection, String testStatement, boolean debugMode)
        throws SQLException {
    Statement stmt = sqlConnection.createStatement();
    ResultSet rs = null;
    try {//from   w  ww  .  j a  v  a 2s. c  o m
        if (isEmpty(testStatement)) {
            return sqlConnection;
        }
        if (debugMode) {
            rs = stmt.executeQuery(testStatement);
            if (rs.next()) {
                StringBuffer sb = new StringBuffer("TestStatement Results - ");
                ResultSetMetaData rsm = rs.getMetaData();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    sb.append("[");
                    sb.append(rsm.getColumnName(i));
                    sb.append("=");
                    sb.append(rs.getObject(i));
                    sb.append("] ");
                }
                log.trace(sb.toString());
            }
        } else {
            stmt.execute(testStatement);
        }
    } finally {
        JdbcUtil.closeQuietly(rs);
        JdbcUtil.closeQuietly(stmt);
    }
    return sqlConnection;
}

From source file:be.dataminded.nifi.plugins.util.JdbcCommon.java

/**
 * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a
 * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value.
 *
 * @param rs         The result set to convert to Avro
 * @param recordName The a priori record name to use if it cannot be determined from the result set.
 * @return A Schema object representing the result set converted to an Avro record
 * @throws SQLException if any error occurs during conversion
 *//*from w ww  .jav a  2s . com*/
public static Schema createSchema(final ResultSet rs, String recordName, boolean convertNames)
        throws SQLException {
    final ResultSetMetaData meta = rs.getMetaData();
    final int nrOfColumns = meta.getColumnCount();
    String tableName = StringUtils.isEmpty(recordName) ? "NiFi_ExecuteSQL_Record" : recordName;
    if (nrOfColumns > 0) {
        String tableNameFromMeta = meta.getTableName(1);
        if (!StringUtils.isBlank(tableNameFromMeta)) {
            tableName = tableNameFromMeta;
        }
    }

    if (convertNames) {
        tableName = normalizeNameForAvro(tableName);
    }

    final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields();

    /**
     * Some missing Avro types - Decimal, Date types. May need some additional work.
     */
    for (int i = 1; i <= nrOfColumns; i++) {
        /**
        *   as per jdbc 4 specs, getColumnLabel will have the alias for the column, if not it will have the column name.
        *  so it may be a better option to check for columnlabel first and if in case it is null is someimplementation,
        *  check for alias. Postgres is the one that has the null column names for calculated fields.
        */
        String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnLabel(i)) ? meta.getColumnLabel(i)
                : meta.getColumnName(i);
        String columnName = convertNames ? normalizeNameForAvro(nameOrLabel) : nameOrLabel;
        switch (meta.getColumnType(i)) {
        case CHAR:
        case LONGNVARCHAR:
        case LONGVARCHAR:
        case NCHAR:
        case NVARCHAR:
        case VARCHAR:
        case CLOB:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BIT:
        case BOOLEAN:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion()
                    .noDefault();
            break;

        case INTEGER:
            if (meta.isSigned(i)) {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                        .noDefault();
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                        .noDefault();
            }
            break;

        case SMALLINT:
        case TINYINT:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                    .noDefault();
            break;

        case BIGINT:
            // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that.
            // If the precision > 19 (or is negative), use a string for the type, otherwise use a long. The object(s) will be converted
            // to strings as necessary
            int precision = meta.getPrecision(i);
            if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                        .noDefault();
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                        .noDefault();
            }
            break;

        // java.sql.RowId is interface, is seems to be database
        // implementation specific, let's convert to String
        case ROWID:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case FLOAT:
        case REAL:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion()
                    .noDefault();
            break;

        case DOUBLE:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion()
                    .noDefault();
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DECIMAL:
        case NUMERIC:
            int scale = meta.getScale(i);
            if (scale == 0) {
                if (meta.getPrecision(i) < 10) {
                    builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                            .noDefault();
                } else {
                    builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType()
                            .endUnion().noDefault();
                }
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion()
                        .noDefault();
            }
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DATE:
        case TIME:
        case TIMESTAMP:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BINARY:
        case VARBINARY:
        case LONGVARBINARY:
        case ARRAY:
        case BLOB:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion()
                    .noDefault();
            break;

        default:
            throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i)
                    + " cannot be converted to Avro type");
        }
    }

    return builder.endRecord();
}

From source file:jdbc.JdbcUtils.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.// w w w.j ava 2  s. c  o m
 * <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
 * @return the value object
 * @see java.sql.Blob
 * @see java.sql.Clob
 * @see java.sql.Timestamp
 * @see oracle.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 Date) {
        if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
            obj = rs.getTimestamp(index);
        }
    }
    return obj;
}

From source file:com.fer.hr.service.util.export.CsvExporter.java

private static byte[] getCsv(ResultSet rs, String delimiter, String enclosing, boolean printHeader,
        List<KeyValue<String, String>> additionalColumns) {
    Integer width = 0;/*from  w  ww .  j  av  a 2s.  c  o  m*/

    Integer height = 0;
    StringBuilder sb = new StringBuilder();
    String addCols = null;
    ResultSetHelper rsch = new ResultSetHelper();
    try {
        while (rs.next()) {
            if (height == 0) {
                width = rs.getMetaData().getColumnCount();
                String header = null;
                if (additionalColumns != null) {
                    for (KeyValue<String, String> kv : additionalColumns) {
                        if (header == null) {
                            header = "";
                            addCols = "";
                        } else {
                            header += delimiter;
                        }
                        header += enclosing + kv.getKey() + enclosing;
                        addCols += enclosing + kv.getValue() + enclosing + delimiter;
                    }
                }
                for (int s = 0; s < width; s++) {
                    if (header != null) {
                        header += delimiter;
                    } else {
                        header = "";
                    }
                    header += enclosing + rs.getMetaData().getColumnName(s + 1) + enclosing;
                }
                if (header != null && printHeader) {
                    header += "\r\n";
                    sb.append(header);
                }
            }
            if (addCols != null) {
                sb.append(addCols);
            }
            for (int i = 0; i < width; i++) {
                int colType = rs.getMetaData().getColumnType(i + 1);
                String content = rsch.getValue(rs, colType, i + 1);
                if (content == null) {
                    content = "";
                }
                if (i > 0) {
                    sb.append(delimiter);
                }
                content = content.replace("\"", "\"\"");
                sb.append(enclosing).append(content).append(enclosing);
            }
            sb.append("\r\n");
            height++;
        }
        return sb.toString().getBytes(SaikuProperties.webExportCsvTextEncoding); //$NON-NLS-1$
    } catch (Exception e) {
        log.error("Exception", e);
    }
    return new byte[0];
}

From source file:com.fer.hr.olap.util.ObjectUtil.java

public static List<SimpleCubeElement> convert2simple(ResultSet rs) {
    try {/*from w  ww .  jav  a 2 s  .  c  om*/
        int width = 0;
        boolean first = true;
        List<SimpleCubeElement> elements = new ArrayList<>();
        if (rs != null) {
            while (rs.next()) {
                if (first) {
                    first = false;
                    width = rs.getMetaData().getColumnCount();
                }
                String[] row = new String[3];
                for (int i = 0; i < width; i++) {
                    row[i] = rs.getString(i + 1);
                }
                SimpleCubeElement s = new SimpleCubeElement(row[0], row[1], row[2]);
                elements.add(s);
            }
        }
        return elements;

    } catch (Exception e) {
        throw new SaikuServiceException("Error converting ResultSet into SimpleCubeElement", e);
    } finally {
        if (rs != null) {
            Statement statement = null;
            Connection con = null;
            try {
                statement = rs.getStatement();

            } catch (Exception e) {
                throw new SaikuServiceException(e);
            } finally {
                try {
                    rs.close();
                    if (statement != null) {
                        statement.close();
                    }
                } catch (Exception ee) {
                    LOG.error("Could not close statement", ee);
                }

                rs = null;
            }
        }
    }
}

From source file:com.baidu.qa.service.test.util.JdbcUtil.java

protected static List<Map<String, Object>> excuteQuerySql(String sqlStr, String dbname) throws Exception {

    List<Map<String, Object>> rltList = new ArrayList<Map<String, Object>>();

    //   ???//from  ww w.  j a  v  a  2  s  . co m
    Connection con = null;
    Statement sm = null;
    ResultSet rs = null;
    try {
        //?
        con = MysqlDatabaseManager.getCon(dbname);
        Assert.assertNotNull("connect to db error:" + dbname, con);

        //??
        sm = con.createStatement();
        rs = sm.executeQuery(sqlStr);
        log.info("[sql:]" + sqlStr);
        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int count = 0;
        String key;
        Object value;

        //          ???? 
        while (rs.next()) {
            Map<String, Object> expectData = new HashMap<String, Object>();
            count = 0;
            while (count++ < numberOfColumns) {

                key = rsmd.getColumnLabel(count);
                value = rs.getObject(key);
                expectData.put(key, value);
            }
            rltList.add(expectData);
        }

        return rltList;

    } catch (Exception e) {
        throw e;
    } finally {
        if (con != null) {
            con.close();

        }
        if (sm != null) {
            sm.close();
        }
        if (rs != null) {
            rs.close();
        }
    }

}