Example usage for java.sql ResultSetMetaData getColumnLabel

List of usage examples for java.sql ResultSetMetaData getColumnLabel

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

Introduction

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

Prototype

String getColumnLabel(int column) throws SQLException;

Source Link

Document

Gets the designated column's suggested title for use in printouts and displays.

Usage

From source file:org.apache.bigtop.itest.hive.TestJdbc.java

@Test
public void preparedStmtAndResultSet() throws SQLException {
    final String tableName = "bigtop_jdbc_psars_test_table";
    try (Statement stmt = conn.createStatement()) {
        stmt.execute("drop table if exists " + tableName);
        stmt.execute("create table " + tableName + " (bo boolean, ti tinyint, db double, fl float, "
                + "i int, lo bigint, sh smallint, st varchar(32))");
    }//from   w ww .j av  a2 s. com

    // NOTE Hive 1.2 theoretically support binary, Date & Timestamp in JDBC, but I get errors when I
    // try to put them in the query.
    try (PreparedStatement ps = conn
            .prepareStatement("insert into " + tableName + " values (?, ?, ?, ?, ?, ?, ?, ?)")) {
        ps.setBoolean(1, true);
        ps.setByte(2, (byte) 1);
        ps.setDouble(3, 3.141592654);
        ps.setFloat(4, 3.14f);
        ps.setInt(5, 3);
        ps.setLong(6, 10L);
        ps.setShort(7, (short) 20);
        ps.setString(8, "abc");
        ps.executeUpdate();
    }

    try (PreparedStatement ps = conn.prepareStatement("insert into " + tableName + " (i, st) " + "values(?, ?)",
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
        ps.setNull(1, Types.INTEGER);
        ps.setObject(2, "mary had a little lamb");
        ps.executeUpdate();
        ps.setNull(1, Types.INTEGER, null);
        ps.setString(2, "its fleece was white as snow");
        ps.clearParameters();
        ps.setNull(1, Types.INTEGER, null);
        ps.setString(2, "its fleece was white as snow");
        ps.execute();

    }

    try (Statement stmt = conn.createStatement()) {

        ResultSet rs = stmt.executeQuery("select * from " + tableName);

        ResultSetMetaData md = rs.getMetaData();

        int colCnt = md.getColumnCount();
        LOG.debug("Column count is " + colCnt);

        for (int i = 1; i <= colCnt; i++) {
            LOG.debug("Looking at column " + i);
            String strrc = md.getColumnClassName(i);
            LOG.debug("Column class name is " + strrc);

            int intrc = md.getColumnDisplaySize(i);
            LOG.debug("Column display size is " + intrc);

            strrc = md.getColumnLabel(i);
            LOG.debug("Column label is " + strrc);

            strrc = md.getColumnName(i);
            LOG.debug("Column name is " + strrc);

            intrc = md.getColumnType(i);
            LOG.debug("Column type is " + intrc);

            strrc = md.getColumnTypeName(i);
            LOG.debug("Column type name is " + strrc);

            intrc = md.getPrecision(i);
            LOG.debug("Precision is " + intrc);

            intrc = md.getScale(i);
            LOG.debug("Scale is " + intrc);

            boolean boolrc = md.isAutoIncrement(i);
            LOG.debug("Is auto increment? " + boolrc);

            boolrc = md.isCaseSensitive(i);
            LOG.debug("Is case sensitive? " + boolrc);

            boolrc = md.isCurrency(i);
            LOG.debug("Is currency? " + boolrc);

            intrc = md.getScale(i);
            LOG.debug("Scale is " + intrc);

            intrc = md.isNullable(i);
            LOG.debug("Is nullable? " + intrc);

            boolrc = md.isReadOnly(i);
            LOG.debug("Is read only? " + boolrc);

        }

        while (rs.next()) {
            LOG.debug("bo = " + rs.getBoolean(1));
            LOG.debug("bo = " + rs.getBoolean("bo"));
            LOG.debug("ti = " + rs.getByte(2));
            LOG.debug("ti = " + rs.getByte("ti"));
            LOG.debug("db = " + rs.getDouble(3));
            LOG.debug("db = " + rs.getDouble("db"));
            LOG.debug("fl = " + rs.getFloat(4));
            LOG.debug("fl = " + rs.getFloat("fl"));
            LOG.debug("i = " + rs.getInt(5));
            LOG.debug("i = " + rs.getInt("i"));
            LOG.debug("lo = " + rs.getLong(6));
            LOG.debug("lo = " + rs.getLong("lo"));
            LOG.debug("sh = " + rs.getShort(7));
            LOG.debug("sh = " + rs.getShort("sh"));
            LOG.debug("st = " + rs.getString(8));
            LOG.debug("st = " + rs.getString("st"));
            LOG.debug("tm = " + rs.getObject(8));
            LOG.debug("tm = " + rs.getObject("st"));
            LOG.debug("tm was null " + rs.wasNull());
        }
        LOG.debug("bo is column " + rs.findColumn("bo"));

        int intrc = rs.getConcurrency();
        LOG.debug("concurrency " + intrc);

        intrc = rs.getFetchDirection();
        LOG.debug("fetch direction " + intrc);

        intrc = rs.getType();
        LOG.debug("type " + intrc);

        Statement copy = rs.getStatement();

        SQLWarning warning = rs.getWarnings();
        while (warning != null) {
            LOG.debug("Found a warning: " + warning.getMessage());
            warning = warning.getNextWarning();
        }
        rs.clearWarnings();
    }
}

From source file:gsn.storage.StorageManager.java

public DataField[] tableToStructure(CharSequence tableName, Connection connection) throws SQLException {
    StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" where 1=0 ");
    ResultSet rs = null;/* w  w w.j  a v a2s  .c  om*/
    DataField[] toReturn = null;
    try {
        rs = executeQueryWithResultSet(sb, connection);
        ResultSetMetaData structure = rs.getMetaData();
        ArrayList<DataField> toReturnArr = new ArrayList<DataField>();
        for (int i = 1; i <= structure.getColumnCount(); i++) {
            String colName = structure.getColumnLabel(i);
            if (colName.equalsIgnoreCase("pk"))
                continue;
            int colType = structure.getColumnType(i);
            byte colTypeInGSN = convertLocalTypeToGSN(colType);
            if (colTypeInGSN == -100) {
                logger.error(
                        "The type can't be converted to GSN form - error description: virtual sensor name is: "
                                + tableName + ", field name is: " + colName + ", query is: " + sb);
            }
            toReturnArr.add(new DataField(colName, colTypeInGSN));
        }
        toReturn = toReturnArr.toArray(new DataField[] {});
    } finally {
        if (rs != null)
            close(rs);
    }
    return toReturn;
}

From source file:gsn.storage.StorageManager.java

public DataField[] tableToStructureByString(String tableName, Connection connection) throws SQLException {
    StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" where 1=0 ");
    ResultSet rs = null;/*from  w  w  w .j a  v  a 2s.com*/
    DataField[] toReturn = null;
    try {
        rs = executeQueryWithResultSet(sb, connection);
        ResultSetMetaData structure = rs.getMetaData();
        ArrayList<DataField> toReturnArr = new ArrayList<DataField>();
        for (int i = 1; i <= structure.getColumnCount(); i++) {
            String colName = structure.getColumnLabel(i);
            if (colName.equalsIgnoreCase("pk"))
                continue;
            if (colName.equalsIgnoreCase("timed"))
                continue;
            int colType = structure.getColumnType(i);
            String colTypeName = structure.getColumnTypeName(i);
            int precision = structure.getPrecision(i);
            byte colTypeInGSN = convertLocalTypeToGSN(colType);
            if (colTypeInGSN == -100) {
                logger.error(
                        "The type can't be converted to GSN form - error description: virtual sensor name is: "
                                + tableName + ", field name is: " + colName + ", query is: " + sb);
            }
            if ((colTypeInGSN == DataTypes.VARCHAR) || (colTypeInGSN == DataTypes.CHAR))
                toReturnArr.add(new DataField(colName, colTypeName, precision, colName));
            else
                toReturnArr.add(new DataField(colName, colTypeInGSN));
        }
        toReturn = toReturnArr.toArray(new DataField[] {});
    } finally {
        if (rs != null)
            close(rs);
    }
    return toReturn;
}

From source file:org.apache.sqoop.manager.SqlManager.java

protected Map<String, String> getColumnTypeNamesForRawQuery(String stmt) {
    ResultSet results;/*from   ww  w.jav  a 2s.  c  om*/
    try {
        results = execute(stmt);
    } catch (SQLException sqlE) {
        LOG.error("Error executing statement: " + sqlE.toString(), sqlE);
        release();
        return null;
    }

    try {
        Map<String, String> colTypeNames = new HashMap<String, String>();

        int cols = results.getMetaData().getColumnCount();
        ResultSetMetaData metadata = results.getMetaData();
        for (int i = 1; i < cols + 1; i++) {
            String colTypeName = metadata.getColumnTypeName(i);

            String colName = metadata.getColumnName(i);
            if (colName == null || colName.equals("")) {
                colName = metadata.getColumnLabel(i);
            }

            colTypeNames.put(colName, colTypeName);
        }

        return colTypeNames;
    } catch (SQLException sqlException) {
        LOG.error("Error reading from database: " + sqlException.toString());
        return null;
    } finally {
        try {
            results.close();
            getConnection().commit();
        } catch (SQLException sqlE) {
            LOG.warn("SQLException closing ResultSet: " + sqlE.toString());
        }

        release();
    }
}

From source file:rapture.repo.jdbc.JDBCStructuredStore.java

private ResultSetExtractor<List<Map<String, Object>>> createResultSetExtractor() {
    return new ResultSetExtractor<List<Map<String, Object>>>() {
        @Override/*from  w  w w  . j av a  2 s .  c o  m*/
        public List<Map<String, Object>> extractData(ResultSet rs) throws SQLException, DataAccessException {
            List<Map<String, Object>> ret = new ArrayList<>();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numColumns = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> m = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);
                for (int i = 1; i <= numColumns; i++) {
                    m.put(rsmd.getColumnLabel(i), rs.getObject(i));
                }
                ret.add(m);
            }
            return ret;
        }
    };
}

From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java

/**
 * @param resultSet/*from   w w w  .  ja v a  2  s.co  m*/
 * @return
 */
private Map extractRowData(ResultSet resultSet) throws SQLException {

    Map row = new HashMap();
    ResultSetMetaData rsMeta = resultSet.getMetaData();
    for (int idx = rsMeta.getColumnCount(); idx > 0; idx--) {
        Object value = resultSet.getObject(idx);
        row.put(rsMeta.getColumnLabel(idx).toLowerCase(), value);
        row.put(rsMeta.getColumnName(idx).toLowerCase(), value);
    }
    return row;

}

From source file:net.antidot.semantic.rdf.rdb2rdf.r2rml.core.R2RMLEngine.java

private ResultSetMetaData extractMetaDatas(ResultSet rows2) throws SQLException {
    ResultSetMetaData meta = rows.getMetaData();
    // Tests the presence of duplicate column names in the SELECT list of
    // the SQL query
    Set<String> columnsNames = new HashSet<String>();
    for (int i = 1; i <= meta.getColumnCount(); i++) {
        String columnName = meta.getColumnLabel(i);
        if (!columnsNames.contains(columnName))
            columnsNames.add(columnName);
        else/*  w w w .ja  v  a  2  s .c  om*/
            throw new SQLException("[R2RMLEngine:extractMetaDatas] duplicate column names in the "
                    + "SELECT list of the SQL query");

    }
    return meta;
}

From source file:org.openiot.gsn.storage.StorageManager.java

/**
 * Returns false if the table doesnt exist. If the table exists but the
 * structure is not compatible with the specified fields the method throws
 * GSNRuntimeException. Note that this method doesn't close the connection
 *
 * @param tableName/*from   w  w  w.  java2  s  .c o m*/
 * @param connection (this method will not close it and the caller is responsible
 *                   for closing the connection)
 * @return
 * @throws SQLException
 * @Throws GSNRuntimeException
 */

public boolean tableExists(CharSequence tableName, DataField[] fields, Connection connection)
        throws SQLException, GSNRuntimeException {
    if (!ValidityTools.isValidJavaVariable(tableName))
        throw new GSNRuntimeException("Table name is not valid");
    StringBuilder sb = new StringBuilder("select * from ").append(tableNameGeneratorInString(tableName))
            .append(" where 1=0 ");
    ResultSet rs = null;
    try {
        rs = executeQueryWithResultSet(sb, connection);
        ResultSetMetaData structure = rs.getMetaData();
        if (fields != null && fields.length > 0)
            nextField: for (DataField field : fields) {
                for (int i = 1; i <= structure.getColumnCount(); i++) {
                    String colName = structure.getColumnLabel(i);
                    int colType = structure.getColumnType(i);
                    int colTypeScale = structure.getScale(i);
                    if (field.getName().equalsIgnoreCase(colName))
                        if (field.getDataTypeID() == convertLocalTypeToGSN(colType, colTypeScale))
                            continue nextField;
                        else
                            throw new GSNRuntimeException("The column : " + colName + " in the >" + tableName
                                    + "< table is not compatible with type : " + field.getType()
                                    + ". The actual type for this table (currently in the database): "
                                    + colType);
                }
                throw new GSNRuntimeException("The table " + tableName + " in the database, doesn't have the >"
                        + field.getName() + "< column.");
            }
    } catch (SQLException e) {
        if (e.getErrorCode() == getTableNotExistsErrNo() || e.getMessage().contains("does not exist"))
            return false;
        else {
            logger.error(e.getErrorCode());
            throw e;
        }
    } finally {
        close(rs);
    }
    return true;
}

From source file:org.exist.xquery.modules.oracle.ExecuteFunction.java

@Override
public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException {

    if (args.length == 5 || args.length == 6) {
        // was a connection and PL/SQL statement specified?
        if (args[0].isEmpty() || args[1].isEmpty()) {
            return (Sequence.EMPTY_SEQUENCE);
        }//from   w  w  w  . j  ava 2 s .c  o m

        // get the Connection
        long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong();
        Connection connection = SQLModule.retrieveConnection(context, connectionUID);

        if (connection == null) {
            return (Sequence.EMPTY_SEQUENCE);
        }

        // get the PL/SQL statement
        String plSql = args[1].getStringValue();

        // get the input parameters (if any)
        Element parameters = null;
        if (!args[2].isEmpty()) {
            parameters = (Element) args[2].itemAt(0);
        }

        // was a result set position specified?
        int resultSetPos = 0;
        if (!args[3].isEmpty()) {
            resultSetPos = ((IntegerValue) args[3].itemAt(0)).getInt();
        }

        boolean haveReturnCode = false;
        int plSqlSuccess = 1; // default value of 1 for success
        if (args.length == 6) {
            // a return code is expected so what is the value indicating success?
            plSqlSuccess = ((IntegerValue) args[5].itemAt(0)).getInt();
            haveReturnCode = true;
        }

        CallableStatement statement = null;
        ResultSet resultSet = null;

        try {
            MemTreeBuilder builder = context.getDocumentBuilder();
            int iRow = 0;

            statement = connection.prepareCall(plSql);
            if (haveReturnCode) {
                statement.registerOutParameter(1, Types.NUMERIC);
            }
            if (resultSetPos != 0) {
                statement.registerOutParameter(resultSetPos, OracleTypes.CURSOR);
            }
            if (!args[2].isEmpty()) {
                setParametersOnPreparedStatement(statement, parameters);
            }

            statement.execute();

            if (haveReturnCode) {
                int returnCode = statement.getInt(1);
                if (returnCode != plSqlSuccess) {
                    LOG.error(plSql + " failed [" + returnCode + "]");
                    return (Sequence.EMPTY_SEQUENCE);
                }
            }

            if (resultSetPos != 0) {
                // iterate through the result set building an XML document
                builder.startDocument();

                builder.startElement(new QName("result", OracleModule.NAMESPACE_URI, OracleModule.PREFIX),
                        null);
                builder.addAttribute(new QName("count", null, null), String.valueOf(-1));

                resultSet = (ResultSet) statement.getObject(resultSetPos);

                ResultSetMetaData rsmd = resultSet.getMetaData();
                int iColumns = rsmd.getColumnCount();

                while (resultSet.next()) {
                    builder.startElement(new QName("row", OracleModule.NAMESPACE_URI, OracleModule.PREFIX),
                            null);
                    builder.addAttribute(new QName("index", null, null), String.valueOf(resultSet.getRow()));

                    // get each tuple in the row
                    for (int i = 0; i < iColumns; i++) {
                        String columnName = rsmd.getColumnLabel(i + 1);
                        if (columnName != null) {
                            String colValue = resultSet.getString(i + 1);

                            String colElement = "field";

                            if (((BooleanValue) args[4].itemAt(0)).effectiveBooleanValue()
                                    && columnName.length() > 0) {
                                // use column names as the XML node

                                /**
                                 * Spaces in column names are replaced with
                                 * underscore's
                                 */

                                colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_'));
                            }

                            builder.startElement(
                                    new QName(colElement, OracleModule.NAMESPACE_URI, OracleModule.PREFIX),
                                    null);

                            if (!((BooleanValue) args[4].itemAt(0)).effectiveBooleanValue()
                                    || columnName.length() <= 0) {
                                String name;

                                if (columnName.length() > 0) {
                                    name = SQLUtils.escapeXmlAttr(columnName);
                                } else {
                                    name = "Column: " + String.valueOf(i + 1);
                                }

                                builder.addAttribute(new QName("name", null, null), name);
                            }

                            builder.addAttribute(
                                    new QName("type", OracleModule.NAMESPACE_URI, OracleModule.PREFIX),
                                    rsmd.getColumnTypeName(i + 1));
                            builder.addAttribute(new QName("type", Namespaces.SCHEMA_NS, "xs"),
                                    Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1))));

                            if (resultSet.wasNull()) {
                                // Add a null indicator attribute if the value was SQL Null
                                builder.addAttribute(
                                        new QName("null", OracleModule.NAMESPACE_URI, OracleModule.PREFIX),
                                        "true");
                            }

                            if (colValue != null) {
                                builder.characters(SQLUtils.escapeXmlText(colValue));
                            }

                            builder.endElement();
                        }
                    }

                    builder.endElement();

                    iRow++;
                }
                builder.endElement();

                // Change the root element count attribute to have the correct value

                NodeValue node = (NodeValue) builder.getDocument().getDocumentElement();

                Node count = node.getNode().getAttributes().getNamedItem("count");

                if (count != null) {
                    count.setNodeValue(String.valueOf(iRow));
                }
                builder.endDocument();

                // return the XML result set
                return (node);
            } else {
                // there was no result set so just return an empty sequence
                return (Sequence.EMPTY_SEQUENCE);
            }
        } catch (SQLException sqle) {

            LOG.error("oracle:execute() Caught SQLException \"" + sqle.getMessage() + "\" for PL/SQL: \""
                    + plSql + "\"", sqle);

            //return details about the SQLException
            MemTreeBuilder builder = context.getDocumentBuilder();

            builder.startDocument();
            builder.startElement(new QName("exception", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);

            boolean recoverable = false;
            if (sqle instanceof SQLRecoverableException) {
                recoverable = true;
            }
            builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable));

            builder.startElement(new QName("state", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
            String sqlState = sqle.getSQLState();
            if (sqlState != null) {
                builder.characters(sqle.getSQLState());
            } else {
                builder.characters("null");
            }

            builder.endElement();

            builder.startElement(new QName("message", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
            builder.characters(sqle.getMessage());
            builder.endElement();

            builder.startElement(new QName("stack-trace", OracleModule.NAMESPACE_URI, OracleModule.PREFIX),
                    null);
            ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream();
            sqle.printStackTrace(new PrintStream(bufStackTrace));
            builder.characters(new String(bufStackTrace.toByteArray()));
            builder.endElement();

            builder.startElement(new QName("oracle", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
            builder.characters(SQLUtils.escapeXmlText(plSql));
            builder.endElement();

            int line = getLine();
            int column = getColumn();

            builder.startElement(new QName("xquery", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
            builder.addAttribute(new QName("line", null, null), String.valueOf(line));
            builder.addAttribute(new QName("column", null, null), String.valueOf(column));
            builder.endElement();

            builder.endElement();
            builder.endDocument();

            return (NodeValue) builder.getDocument().getDocumentElement();
        } finally {
            release(connection, statement, resultSet);
        }
    } else {
        throw new XPathException("Invalid number of arguments [" + args.length + "]");
    }
}

From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java

public void setColumnSpecificHeaders(Record record, Set<String> knownTableNames, ResultSetMetaData metaData,
        String jdbcNameSpacePrefix) throws SQLException {
    Record.Header header = record.getHeader();
    Set<String> tableNames = new HashSet<>();
    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".jdbcType",
                String.valueOf(metaData.getColumnType(i)));

        // Additional headers per various types
        switch (metaData.getColumnType(i)) {
        case Types.DECIMAL:
        case Types.NUMERIC:
            header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".scale",
                    String.valueOf(metaData.getScale(i)));
            header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".precision",
                    String.valueOf(metaData.getPrecision(i)));
            break;
        }/*from   w w  w  .  j  av  a2 s .c  o  m*/

        String tableName = metaData.getTableName(i);

        // Store the column's table name (if not empty)
        if (StringUtils.isNotEmpty(tableName)) {
            tableNames.add(tableName);
        }
    }

    if (tableNames.isEmpty()) {
        tableNames.addAll(knownTableNames);
    }

    header.setAttribute(jdbcNameSpacePrefix + "tables", Joiner.on(",").join(tableNames));
}