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: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 ww  w  . j  ava 2  s.  c om
 * @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.streamsets.pipeline.stage.BaseHiveIT.java

/**
 * Validate structure of the result set (column names and types).
 *///w  w w.  ja v a 2  s  .com
public static void assertResultSetStructure(ResultSet rs, Pair<String, Integer>... columns) throws Exception {
    ResultSetMetaData metaData = rs.getMetaData();
    Assert.assertEquals(Utils.format("Unexpected number of columns"), columns.length,
            metaData.getColumnCount());
    int i = 1;
    for (Pair<String, Integer> column : columns) {
        Assert.assertEquals(Utils.format("Unexpected name for column {}", i), column.getLeft(),
                metaData.getColumnName(i));
        Assert.assertEquals(Utils.format("Unexpected type for column {}", i), (int) column.getRight(),
                metaData.getColumnType(i));
        i++;
    }
}

From source file:Main.java

public static Document documentify(ResultSet rs) throws ParserConfigurationException, SQLException {
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    Document doc = builder.newDocument();
    Element results = doc.createElement("Results");
    doc.appendChild(results);/*from  ww w .ja  va2  s  .  co m*/
    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.paladin.sys.db.QueryHelper.java

/**
 * ?//from w w  w . j ava  2  s. com
 *
 * @param rs 
 * @return Map<String, String>  Map
 * @throws SQLException
 */
private static Map<String, Object> getMapFromRs(final ResultSet rs) {
    Map<String, Object> t_map = new HashMap<String, Object>();
    int columnCount = 0;
    try {
        columnCount = rs.getMetaData().getColumnCount();// ?
        // ????
        for (int i = 0; i < columnCount; i++) {
            t_map.put(rs.getMetaData().getColumnName(i + 1).toUpperCase(),
                    Tools.null2String(rs.getString(i + 1)));// ??map
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return t_map;
}

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));
    }/*w  ww  .j  a  va2 s.  c o  m*/
    return map;
}

From source file:org.apache.drill.jdbc.test.JdbcAssert.java

static String toString(ResultSet resultSet, int expectedRecordCount) throws SQLException {
    StringBuilder buf = new StringBuilder();
    int total = 0, n;
    while (resultSet.next()) {
        n = resultSet.getMetaData().getColumnCount();
        total++;/*w w w  .  j av  a2 s  .co m*/
        String sep = "";
        for (int i = 1; i <= n; i++) {
            buf.append(sep).append(resultSet.getMetaData().getColumnLabel(i)).append("=")
                    .append(resultSet.getObject(i));
            sep = "; ";
        }
        buf.append("\n");
    }
    return buf.toString();
}

From source file:io.cloudslang.content.database.utils.Format.java

/**
 * Returns tabular form of resultSet similar to what you would get from
 * running a query from the command line
 *
 * @param resultSet populated result set
 * @return//from  ww w.j ava 2s  .  c  om
 * @throws SQLException
 */
public static String resultSetToDelimitedColsAndRows(ResultSet resultSet, boolean checkNullTermination,
        String colDelimiter, String rowDelimiter) throws SQLException {
    //        assert (resultSet != null);

    final StringBuilder delimitedResult = new StringBuilder();
    if (resultSet != null) {
        final int nCols = resultSet.getMetaData().getColumnCount();
        // populate rows and cols
        while (resultSet.next()) {
            for (int colN = 0; colN < nCols; colN++) {
                if (colN != 0) {
                    delimitedResult.append(colDelimiter);
                }
                delimitedResult.append(getColumn(resultSet, colN + 1, checkNullTermination));
            }
            delimitedResult.append(rowDelimiter);
        }
    }

    //If a multi-char delimiter is used, removing the last character is not enough
    int length = rowDelimiter.length();
    if ((delimitedResult.length() - length) >= 0) {
        return delimitedResult.substring(0, delimitedResult.length() - length);
    }
    return EMPTY;
}

From source file:com.xqdev.sql.MLSQL.java

private static void addResultSet(Element root, ResultSet rs) throws SQLException {
    Namespace sql = root.getNamespace();

    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    while (rs.next()) {
        Element tuple = new Element("tuple", sql);
        for (int i = 1; i <= columnCount; i++) {
            String colName = rsmd.getColumnName(i); // names aren't guaranteed OK in xml
            String colTypeName = rsmd.getColumnTypeName(i);

            // Decode a BLOB if one is found and place it into the result as a encoded Base 64 string
            String colValue = "";
            if ("BLOB".equalsIgnoreCase(colTypeName)) {
                Blob b = rs.getBlob(i);
                if (b != null && b.length() > 0) {
                    Base64 b64 = new Base64();
                    String b64Blob = b64.encodeBase64String(b.getBytes(1, (int) b.length()));
                    colValue = b64Blob;
                } else
                    colValue = "";
            } else {
                colValue = rs.getString(i);
            }/*from   w w w .ja  va  2 s  . c o  m*/

            boolean wasNull = rs.wasNull();
            Element elt = new Element(colName);
            if (wasNull) {
                elt.setAttribute("null", "true");
            }
            if ("UNKNOWN".equalsIgnoreCase(colTypeName)) {
                tuple.addContent(elt.setText("UNKNOWN TYPE")); // XXX ugly
            } else {
                tuple.addContent(elt.setText(colValue));
            }
        }
        root.addContent(tuple);
    }

}

From source file:ExecSQL.java

/**
     * Prints a result set.//w  w  w .jav  a2  s  . co m
     * @param stat the statement whose result set should be printed
     */
    public static void showResultSet(Statement stat) throws SQLException {
        ResultSet result = stat.getResultSet();
        ResultSetMetaData metaData = result.getMetaData();
        int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            if (i > 1)
                System.out.print(", ");
            System.out.print(metaData.getColumnLabel(i));
        }
        System.out.println();

        while (result.next()) {
            for (int i = 1; i <= columnCount; i++) {
                if (i > 1)
                    System.out.print(", ");
                System.out.print(result.getString(i));
            }
            System.out.println();
        }
        result.close();
    }

From source file:TerminalMonitor.java

static public void processResults(ResultSet results) throws SQLException {
    try {/*  w  w w  . j  a v  a2 s .com*/
        ResultSetMetaData meta = results.getMetaData();
        StringBuffer bar = new StringBuffer();
        StringBuffer buffer = new StringBuffer();
        int cols = meta.getColumnCount();
        int row_count = 0;
        int i, width = 0;

        // Prepare headers for each of the columns
        // The display should look like:
        //  --------------------------------------
        //  | Column One | Column Two |
        //  --------------------------------------
        //  | Row 1 Value | Row 1 Value |
        //  --------------------------------------

        // create the bar that is as long as the total of all columns
        for (i = 1; i <= cols; i++) {
            width += meta.getColumnDisplaySize(i);
        }
        width += 1 + cols;
        for (i = 0; i < width; i++) {
            bar.append('-');
        }
        bar.append('\n');
        buffer.append(bar.toString() + "|");
        // After the first bar goes the column labels
        for (i = 1; i <= cols; i++) {
            StringBuffer filler = new StringBuffer();
            String label = meta.getColumnLabel(i);
            int size = meta.getColumnDisplaySize(i);
            int x;

            // If the label is longer than the column is wide,
            // then we truncate the column label
            if (label.length() > size) {
                label = label.substring(0, size);
            }
            // If the label is shorter than the column, pad it with spaces
            if (label.length() < size) {
                int j;

                x = (size - label.length()) / 2;
                for (j = 0; j < x; j++) {
                    filler.append(' ');
                }
                label = filler + label + filler;
                if (label.length() > size) {
                    label = label.substring(0, size);
                } else {
                    while (label.length() < size) {
                        label += " ";
                    }
                }
            }
            // Add the column header to the buffer
            buffer.append(label + "|");
        }
        // Add the lower bar
        buffer.append("\n" + bar.toString());
        // Format each row in the result set and add it on
        while (results.next()) {
            row_count++;

            buffer.append('|');
            // Format each column of the row
            for (i = 1; i <= cols; i++) {
                StringBuffer filler = new StringBuffer();
                Object value = results.getObject(i);
                int size = meta.getColumnDisplaySize(i);
                String str;

                if (results.wasNull()) {
                    str = "NULL";
                } else {
                    str = value.toString();
                }
                if (str.length() > size) {
                    str = str.substring(0, size);
                }
                if (str.length() < size) {
                    int j, x;

                    x = (size - str.length()) / 2;
                    for (j = 0; j < x; j++) {
                        filler.append(' ');
                    }
                    str = filler + str + filler;
                    if (str.length() > size) {
                        str = str.substring(0, size);
                    } else {
                        while (str.length() < size) {
                            str += " ";
                        }
                    }
                }
                buffer.append(str + "|");
            }
            buffer.append("\n");
        }
        // Stick a row count up at the top
        if (row_count == 0) {
            buffer = new StringBuffer("No rows selected.\n");
        } else if (row_count == 1) {
            buffer = new StringBuffer("1 row selected.\n" + buffer.toString() + bar.toString());
        } else {
            buffer = new StringBuffer(row_count + " rows selected.\n" + buffer.toString() + bar.toString());
        }
        System.out.print(buffer.toString());
        System.out.flush();
    } catch (SQLException e) {
        throw e;
    } finally {
        try {
            results.close();
        } catch (SQLException e) {
        }
    }
}