Example usage for java.sql ResultSetMetaData getColumnCount

List of usage examples for java.sql ResultSetMetaData getColumnCount

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

Introduction

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

Prototype

int getColumnCount() throws SQLException;

Source Link

Document

Returns the number of columns in this ResultSet object.

Usage

From source file:at.bestsolution.persistence.java.Util.java

public static Map<String, Object> mapResultSet(ResultSet set) throws SQLException {
    Map<String, Object> map = new HashMap<String, Object>();
    ResultSetMetaData m = set.getMetaData();
    int columnCount = m.getColumnCount();

    for (int i = 0; i < columnCount; i++) {
        map.put(m.getColumnName(i), set.getObject(i));
    }//from   w w w .j  a v  a  2 s  .c  o m
    return map;
}

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  w w  . 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.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 w  w w . ja v a  2  s.  c  om
    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();
        }
    }

}

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(/*from  w ww. ja  va 2  s  . c  o  m*/
                "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:com.viettel.ws.client.JDBCUtil.java

/**
 * Create xml string - fastest, but may have encoding issues
 *
 * @param rs - a Result set//from w w w  .j  ava2s.com
 * @return - XML string of a result set
 * @throws SQLException - If error when read data from database
 */
public static String toXML(ResultSet rs) throws SQLException {
    ResultSetMetaData rsmd = rs.getMetaData();
    int colCount = rsmd.getColumnCount();
    StringBuilder xml = new StringBuilder();
    xml.append("<Results>");

    while (rs.next()) {
        xml.append("<Row>");

        for (int i = 1; i <= colCount; i++) {
            String columnName = rsmd.getColumnName(i);
            Object value = rs.getObject(i);
            xml.append("<").append(columnName).append(">");

            if (value != null) {
                xml.append(value.toString().trim());
            }
            xml.append("</").append(columnName).append(">");
        }
        xml.append("</Row>");
    }

    xml.append("</Results>");

    return xml.toString();
}

From source file:ExecuteSQL.java

/**
 * This method attempts to output the contents of a ResultSet in a textual
 * table. It relies on the ResultSetMetaData class, but a fair bit of the
 * code is simple string manipulation.//from  www.  j av a 2s .co m
 */
static void printResultsTable(ResultSet rs, OutputStream output) throws SQLException {
    // Set up the output stream
    PrintWriter out = new PrintWriter(output);

    // Get some "meta data" (column names, etc.) about the results
    ResultSetMetaData metadata = rs.getMetaData();

    // Variables to hold important data about the table to be displayed
    int numcols = metadata.getColumnCount(); // how many columns
    String[] labels = new String[numcols]; // the column labels
    int[] colwidths = new int[numcols]; // the width of each
    int[] colpos = new int[numcols]; // start position of each
    int linewidth; // total width of table

    // Figure out how wide the columns are, where each one begins,
    // how wide each row of the table will be, etc.
    linewidth = 1; // for the initial '|'.
    for (int i = 0; i < numcols; i++) { // for each column
        colpos[i] = linewidth; // save its position
        labels[i] = metadata.getColumnLabel(i + 1); // get its label
        // Get the column width. If the db doesn't report one, guess
        // 30 characters. Then check the length of the label, and use
        // it if it is larger than the column width
        int size = metadata.getColumnDisplaySize(i + 1);
        if (size == -1)
            size = 30; // Some drivers return -1...
        if (size > 500)
            size = 30; // Don't allow unreasonable sizes
        int labelsize = labels[i].length();
        if (labelsize > size)
            size = labelsize;
        colwidths[i] = size + 1; // save the column the size
        linewidth += colwidths[i] + 2; // increment total size
    }

    // Create a horizontal divider line we use in the table.
    // Also create a blank line that is the initial value of each
    // line of the table
    StringBuffer divider = new StringBuffer(linewidth);
    StringBuffer blankline = new StringBuffer(linewidth);
    for (int i = 0; i < linewidth; i++) {
        divider.insert(i, '-');
        blankline.insert(i, " ");
    }
    // Put special marks in the divider line at the column positions
    for (int i = 0; i < numcols; i++)
        divider.setCharAt(colpos[i] - 1, '+');
    divider.setCharAt(linewidth - 1, '+');

    // Begin the table output with a divider line
    out.println(divider);

    // The next line of the table contains the column labels.
    // Begin with a blank line, and put the column names and column
    // divider characters "|" into it. overwrite() is defined below.
    StringBuffer line = new StringBuffer(blankline.toString());
    line.setCharAt(0, '|');
    for (int i = 0; i < numcols; i++) {
        int pos = colpos[i] + 1 + (colwidths[i] - labels[i].length()) / 2;
        overwrite(line, pos, labels[i]);
        overwrite(line, colpos[i] + colwidths[i], " |");
    }

    // Then output the line of column labels and another divider
    out.println(line);
    out.println(divider);

    // Now, output the table data. Loop through the ResultSet, using
    // the next() method to get the rows one at a time. Obtain the
    // value of each column with getObject(), and output it, much as
    // we did for the column labels above.
    while (rs.next()) {
        line = new StringBuffer(blankline.toString());
        line.setCharAt(0, '|');
        for (int i = 0; i < numcols; i++) {
            Object value = rs.getObject(i + 1);
            if (value != null)
                overwrite(line, colpos[i] + 1, value.toString().trim());
            overwrite(line, colpos[i] + colwidths[i], " |");
        }
        out.println(line);
    }

    // Finally, end the table with one last divider line.
    out.println(divider);
    out.flush();
}

From source file:com.viettel.ws.client.JDBCUtil.java

/**
 * Create document using DOM api/*ww  w . ja  v  a2s. c  o m*/
 *
 * @param rs a result set
 * @return A document of a result set
 * @throws ParserConfigurationException - If error when parse string
 * @throws SQLException - If error when read data from database
 */
public static Document toDocument(ResultSet rs) throws ParserConfigurationException, SQLException {
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    factory.setFeature(FEATURE_GENERAL_ENTITIES, false);
    factory.setFeature(FEATURE_PARAMETER_ENTITIES, false);
    factory.setXIncludeAware(false);
    factory.setExpandEntityReferences(false);
    DocumentBuilder builder = factory.newDocumentBuilder();
    Document doc = builder.newDocument();

    Element results = doc.createElement("Results");
    doc.appendChild(results);

    ResultSetMetaData rsmd = rs.getMetaData();
    int colCount = rsmd.getColumnCount();

    while (rs.next()) {
        Element row = doc.createElement("Row");
        results.appendChild(row);

        for (int i = 1; i <= colCount; i++) {
            String columnName = rsmd.getColumnName(i);
            Object value = rs.getObject(i);

            Element node = doc.createElement(columnName);
            node.appendChild(doc.createTextNode(value.toString()));
            row.appendChild(node);
        }
    }
    return doc;
}

From source file:com.example.querybuilder.server.Jdbc.java

public static int getColumnCount(ResultSetMetaData metaData) {
    try {/*from w  ww .  j  a v a  2 s.  co  m*/
        return metaData.getColumnCount();
    } catch (SQLException e) {
        throw new SqlRuntimeException(e);
    }
}

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

/**
 * Examines the result set of a JDBC query and creates an Avro schema with appropriately mapped types to accept rows from the JDBC result.
 *
 * @param rs A result set used to obtain data type information
 * @return an instance of Avro Schema/*from   w  w w  .  ja  v  a  2s  .com*/
 * @throws SQLException if errors occur while reading data from the database
 */
public static Schema createSchema(final ResultSet rs) throws SQLException {
    final ResultSetMetaData meta = rs.getMetaData();
    final int nrOfColumns = meta.getColumnCount();
    String tableName = "";
    try {
        tableName = meta.getTableName(1);
    } catch (SQLException e) {

    }
    if (StringUtils.isBlank(tableName)) {
        tableName = "NiFi_ExecuteSQL_Record";
    }

    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++) {
        switch (meta.getColumnType(i)) {
        case CHAR:
        case LONGNVARCHAR:
        case LONGVARCHAR:
        case NCHAR:
        case NVARCHAR:
        case VARCHAR:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().stringType()
                    .endUnion().noDefault();
            break;
        case BIT:
        case BOOLEAN:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().booleanType()
                    .endUnion().noDefault();
            break;

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

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

        case BIGINT:
            builder.name(meta.getColumnName(i)).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(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().stringType()
                    .endUnion().noDefault();
            break;

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

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

        // Did not find direct suitable type, need to be clarified!!!!
        case DECIMAL:
        case NUMERIC:
            builder.name(meta.getColumnName(i)).type().unionOf().nullBuilder().endNull().and().stringType()
                    .endUnion().noDefault();
            break;

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

        case BINARY:
        case VARBINARY:
        case LONGVARBINARY:
        case ARRAY:
        case BLOB:
        case CLOB:
            builder.name(meta.getColumnName(i)).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: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;//ww  w  .  j  a v a  2  s  .  c o m
    try {
        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);
    }
}