Example usage for java.sql ResultSet getObject

List of usage examples for java.sql ResultSet getObject

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

Introduction

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

Prototype

Object getObject(String columnLabel) throws SQLException;

Source Link

Document

Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.

Usage

From source file:kr.co.bitnine.octopus.testutils.MemoryDatabaseTest.java

private void verifyTableEquals(JSONObject expectedTable, ResultSet actualRows) throws Exception {
    ResultSetMetaData actualRowsMetaData = actualRows.getMetaData();

    JSONArray expectedSchema = (JSONArray) expectedTable.get("table-schema");
    assertEquals(expectedSchema.size(), actualRowsMetaData.getColumnCount());
    for (int i = 0; i < expectedSchema.size(); i++)
        assertEquals(expectedSchema.get(i), actualRowsMetaData.getColumnName(i + 1));

    for (Object rowObj : (JSONArray) expectedTable.get("table-rows")) {
        JSONArray row = (JSONArray) rowObj;
        actualRows.next();/*  w  ww  . j a v  a  2  s .  c o m*/

        for (int i = 0; i < row.size(); i++) {
            Object expected = row.get(i);
            Object actual;

            int sqlType = actualRowsMetaData.getColumnType(i + 1);
            switch (sqlType) {
            case Types.INTEGER:
                if (expected instanceof Boolean)
                    expected = (long) ((Boolean) expected ? 1 : 0);
                actual = actualRows.getLong(i + 1);
                break;
            case Types.NULL:
            case Types.FLOAT:
            case Types.VARCHAR:
                actual = actualRows.getObject(i + 1);
                break;
            default:
                throw new RuntimeException("java.sql.Types " + sqlType + " is not supported");
            }

            assertEquals(expected, actual);
        }
    }
    assertFalse(actualRows.next());
}

From source file:jp.co.golorp.emarf.model.Models.java

/**
 * Model?????IO//from ww  w  .j  a v  a  2 s  .  co  m
 *
 * @param sql
 *            sql
 * @param params
 *            params
 * @return List
 */
private static List<Map<String, Object>> getDatas(final String sql, final Object... params) {

    // ?SQL?
    String rawSql = getRawSql(sql, params);

    // SQL??????
    List<Map<String, Object>> datas = ModelsCache.get(rawSql);
    if (datas != null) {
        return datas;
    }

    // 
    statementLog(rawSql);

    // ?
    PreparedStatement ps = null;
    try {
        ps = Connections.get().prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            ps.setString(i + 1, String.valueOf(params[i]));
        }

        // ??
        datas = new ArrayList<Map<String, Object>>();

        ResultSet rs = null;
        try {

            // ???
            rs = ps.executeQuery();
            while (rs.next()) {

                // ?
                Map<String, Object> data = new LinkedHashMap<String, Object>();

                // ResultSet?META?
                ResultSetMetaData meta = rs.getMetaData();

                // META????
                int columnCount = meta.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {

                    // ???
                    String columnName = meta.getColumnName(i);

                    // ???
                    String propertyName = StringUtil.toCamelCase(columnName);

                    String key = propertyName;
                    if (data.containsKey(propertyName)) {
                        String modelName = StringUtil.toUpperCamelCase(meta.getTableName(i));
                        key = modelName + "." + propertyName;
                    }

                    // 
                    data.put(key, rs.getObject(columnName));
                }

                // 
                datas.add(data);
            }

        } catch (SQLException e) {
            throw new SystemError(e);
        } finally {
            IOUtil.closeQuietly(rs);
        }

    } catch (SQLException e) {
        throw new SystemError(e);
    } finally {
        IOUtil.closeQuietly(ps);
    }

    // SQL?
    // ModelsCache.set(rawSql, datas);

    return datas;
}

From source file:mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java

private GeneratedKey insertWithGeneratedKeyReturn(Object record, boolean useDeclaredOnly) {
    PreparedStatement st = null;// w w  w.  j a  va  2s . c om
    boolean inTrans = inTransaction();
    String command = null;
    int batchCount = 0;
    GeneratedKey gk = null;
    if (!inTrans)
        startTransaction();
    try {
        Class objClass = record.getClass();
        String schema = getEntitySchema(objClass);
        Boolean isCamelCased = useCamelCase(objClass);
        HashMap<Method, String> fieldMapping = getFieldMapping(objClass, GET, isCamelCased, useDeclaredOnly);
        String idFieldName = getIdFieldName(fieldMapping);
        HashMap<Integer, Method> indexMapping = new HashMap();
        String tableName = getTableName(objClass);
        if (tableName == null)
            tableName = getDbName(isCamelCased, objClass.getSimpleName(), null);
        command = getInsertCommand(tableName, schema, fieldMapping, indexMapping);
        if (idFieldName != null) {
            st = conn.prepareStatement(command, new String[] { idFieldName });
        } else {
            st = conn.prepareStatement(command);
        }
        for (int index : indexMapping.keySet()) {
            Object value = indexMapping.get(index).invoke(record, null);
            if (value instanceof java.util.Date) {
                value = new java.sql.Date(((java.util.Date) value).getTime());
            }
            st.setObject((Integer) index, value);
        }
        st.execute();
        ResultSet rs = st.getGeneratedKeys();
        if (rs.next()) {
            gk = new GeneratedKey(idFieldName, rs.getObject(1));
        }
        if (!inTrans)
            commitTransaction();
        return gk;
    } catch (Exception ex) {
        ex.printStackTrace();
        if (!inTrans)
            rollbackTransaction();
        throw new DataQueryException(command, "insertWithGeneratedKeyReturn", ex);
    } finally {
        if (st != null) {
            try {
                st.close();
            } catch (Exception ex) {
            }
        }
    }
}

From source file:com.mapd.bench.Benchmark.java

String executeQuery(String sql, int expected, int iterations, int queryNum) {
    Connection conn = null;/*from w w  w  .j a va  2s. c  o m*/
    Statement stmt = null;

    Long firstExecute = 0l;
    Long firstJdbc = 0l;
    Long firstIterate = 0l;

    DescriptiveStatistics statsExecute = new DescriptiveStatistics();
    DescriptiveStatistics statsJdbc = new DescriptiveStatistics();
    DescriptiveStatistics statsIterate = new DescriptiveStatistics();
    DescriptiveStatistics statsTotal = new DescriptiveStatistics();

    long totalTime = 0;

    try {
        //Open a connection
        logger.debug("Connecting to database url :" + url);
        conn = DriverManager.getConnection(url, iUser, iPasswd);

        long startTime = System.currentTimeMillis();
        for (int loop = 0; loop < iterations; loop++) {

            //Execute a query
            stmt = conn.createStatement();

            long timer = System.currentTimeMillis();
            ResultSet rs = stmt.executeQuery(sql);

            long executeTime = 0;
            long jdbcTime = 0;

            // gather internal execute time for MapD as we are interested in that
            if (driver.equals(JDBC_DRIVER)) {
                executeTime = stmt.getQueryTimeout();
                jdbcTime = (System.currentTimeMillis() - timer) - executeTime;
            } else {
                jdbcTime = (System.currentTimeMillis() - timer);
                executeTime = 0;
            }
            // this is fake to get our intenal execute time.
            logger.debug("Query Timeout/AKA internal Execution Time was " + stmt.getQueryTimeout()
                    + " ms Elapsed time in JVM space was " + (System.currentTimeMillis() - timer) + "ms");

            timer = System.currentTimeMillis();
            //Extract data from result set
            int resultCount = 0;
            while (rs.next()) {
                Object obj = rs.getObject(1);
                if (obj != null && obj.equals(statsExecute)) {
                    logger.info("Impossible");
                }
                resultCount++;
            }
            long iterateTime = (System.currentTimeMillis() - timer);

            if (resultCount != expected) {
                logger.error("Expect " + expected + " actual " + resultCount + " for query " + sql);
                // don't run anymore
                break;
            }

            if (loop == 0) {
                firstJdbc = jdbcTime;
                firstExecute = executeTime;
                firstIterate = iterateTime;

            } else {
                statsJdbc.addValue(jdbcTime);
                statsExecute.addValue(executeTime);
                statsIterate.addValue(iterateTime);
                statsTotal.addValue(jdbcTime + executeTime + iterateTime);
            }

            //Clean-up environment
            rs.close();
            stmt.close();
        }
        totalTime = System.currentTimeMillis() - startTime;
        conn.close();
    } catch (SQLException se) {
        //Handle errors for JDBC
        se.printStackTrace();
    } catch (Exception e) {
        //Handle errors for Class.forName
        e.printStackTrace();
    } finally {
        //finally block used to close resources
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException se2) {
        } // nothing we can do
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        } //end finally try
    } //end try

    return String.format(lineDescriptor, queryNum, statsTotal.getMean(), statsTotal.getMin(),
            statsTotal.getMax(), statsTotal.getPercentile(85), statsExecute.getMean(), statsExecute.getMin(),
            statsExecute.getMax(), statsExecute.getPercentile(85), statsExecute.getPercentile(25),
            statsExecute.getStandardDeviation(), statsJdbc.getMean(), statsJdbc.getMin(), statsJdbc.getMax(),
            statsJdbc.getPercentile(85), statsIterate.getMean(), statsIterate.getMin(), statsIterate.getMax(),
            statsIterate.getPercentile(85), firstExecute, firstJdbc, firstIterate, iterations, totalTime,
            (long) statsTotal.getSum() + firstExecute + firstJdbc + firstIterate);

}

From source file:mil.army.usace.data.nativequery.rdbms.NativeRdbmsQuery.java

public <T> T getSingleSqlValue(String sql, Class returnType, Object[] params) {
    PreparedStatement st = null;//  w ww  . ja  v a 2  s. com
    ResultSet rs = null;
    try {
        st = conn.prepareStatement(sql);
        setParams(st, params);
        rs = st.executeQuery();
        rs.next();
        if (returnType != null)
            return (T) convertType(rs.getObject(1), returnType);
        else
            return (T) rs.getObject(1);
    } catch (Exception ex) {
        throw new NativeQueryException(sql, null, ex);
    } finally {
        closeOnFinally(rs, st);
    }
}

From source file:com.mmnaseri.dragonfly.fluent.impl.AbstractSelectQueryFinalizer.java

private <H> List<Map<Mapping, Object>> execute(SelectQueryExecution<E, H> selection) {
    final Connection connection = session.getConnection();
    final PreparedStatement preparedStatement;
    try {//  ww w.  j  a v a2 s. com
        final String sql = selection.getSql() + ";";
        LogFactory.getLog(Statement.class).info("Preparing statement: " + sql);
        preparedStatement = connection.prepareStatement(sql);
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to get a prepared statement from the database", e);
    }
    for (ParameterDescriptor descriptor : selection.getParameters()) {
        try {
            if (descriptor.getValue() == null) {
                preparedStatement.setNull(descriptor.getIndex(), descriptor.getSqlType());
            } else {
                preparedStatement.setObject(descriptor.getIndex(), descriptor.getValue());
            }
        } catch (SQLException e) {
            throw new StatementPreparationException(
                    "Failed to prepare statement for parameter " + descriptor.getIndex(), e);
        }
    }
    final ResultSet resultSet;
    final ResultSetMetaData metaData;
    try {
        resultSet = preparedStatement.executeQuery();
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to retrieve the results from the data source", e);
    }
    try {
        metaData = resultSet.getMetaData();
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to get result set metadata for query", e);
    }
    final ArrayList<Map<Mapping, Object>> result = new ArrayList<Map<Mapping, Object>>();
    while (true) {
        try {
            if (!resultSet.next()) {
                break;
            }
            final HashMap<Mapping, Object> map = new HashMap<Mapping, Object>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                map.put(new ImmutableMapping(metaData.getTableName(i), metaData.getColumnName(i),
                        metaData.getColumnLabel(i)), resultSet.getObject(i));
            }
            result.add(map);
        } catch (SQLException e) {
            throw new DatabaseNegotiationException("Failed to get the next row", e);
        }

    }
    return result;
}

From source file:com.mirth.connect.donkey.test.util.TestUtils.java

public static Map<String, Object> getCustomMetaData(String channelId, long messageId, int metaDataId)
        throws SQLException {
    Map<String, Object> map = new HashMap<String, Object>();
    List<MetaDataColumn> columns = getExistingMetaDataColumns(channelId);

    if (columns.size() > 0) {
        long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId);
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet result = null;

        try {/* w w  w.  j a  v a2  s . co m*/
            connection = getConnection();
            statement = connection.prepareStatement(
                    "SELECT * FROM d_mcm" + localChannelId + " WHERE message_id = ? AND metadata_id = ?");
            statement.setLong(1, messageId);
            statement.setInt(2, metaDataId);
            result = statement.executeQuery();

            if (result.next()) {
                for (MetaDataColumn column : columns) {
                    result.getObject(column.getName());

                    if (!result.wasNull()) {
                        // @formatter:off
                        switch (column.getType()) {
                        case BOOLEAN:
                            map.put(column.getName(), result.getBoolean(column.getName()));
                            break;
                        case NUMBER:
                            map.put(column.getName(), result.getBigDecimal(column.getName()));
                            break;
                        case STRING:
                            map.put(column.getName(), result.getString(column.getName()));
                            break;
                        case TIMESTAMP:
                            Calendar calendar = Calendar.getInstance();
                            calendar.setTimeInMillis(result.getTimestamp(column.getName()).getTime());
                            map.put(column.getName(), calendar);
                            break;
                        }
                        // @formatter:on
                    }
                }
            }
        } finally {
            close(result);
            close(statement);
            close(connection);
        }
    }

    return map;
}

From source file:com.glaf.core.util.DBUtils.java

public static List<String> getTables(Connection connection) {
    List<String> tables = new java.util.ArrayList<String>();
    String[] types = { "TABLE" };
    try {//w ww  .  j  av  a2s .c  o  m
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getTables(null, null, null, types);
        while (rs.next()) {
            tables.add(rs.getObject("TABLE_NAME").toString());
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    }
    return tables;
}

From source file:com.glaf.core.util.DBUtils.java

public static List<String> getTables() {
    List<String> tables = new java.util.ArrayList<String>();
    String[] types = { "TABLE" };
    Connection connection = null;
    try {//from   w ww  .ja  v a 2  s.  c  o m
        connection = DBConnectionFactory.getConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getTables(null, null, null, types);
        while (rs.next()) {
            tables.add(rs.getObject("TABLE_NAME").toString());
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(connection);
    }
    return tables;
}

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

private static void insertHiveResultsIntoOracleTable(OraHiveOptions opts, String insertSql,
        List<OracleTableColumn> oracleColumns, Connection oracleConnection, ResultSet resultSet,
        OraHiveCounters counters) {/*w ww  .  j  av  a2  s  .  com*/

    long timerHiveFetching = 0;
    long timerOracleInserting = 0;
    long rowsProcessed = 0;

    try {

        oracle.jdbc.OraclePreparedStatement statement = (oracle.jdbc.OraclePreparedStatement) oracleConnection
                .prepareStatement(insertSql);

        int rowIdx = 0;
        int batchIdx = 0;
        int numberOfBatchesCommitted = 0;

        try {
            resultSet.setFetchSize(opts.insertBatchSize);
        } catch (SQLException e) {
            try {
                // Apply fetchN hack for much better performance with pre 0.8 JDBC driver
                LOG.info(
                        "Hive ResultSet does not implement setFetchSize. Wrapping with FetchNResultSet for better performance.");
                resultSet = new FetchNResultSet(resultSet);
                resultSet.setFetchSize(opts.insertBatchSize);
            } catch (IllegalArgumentException iae) {
                LOG.warn(
                        "Wrapping Hive ResultSet with FetchNResultSet failed. Performance may be poor for large result sets.");
                LOG.debug("FetchNResultSet exception was:", iae);
            }
        }
        long start = System.nanoTime();
        while (resultSet.next()) {
            for (int idx = 0; idx < oracleColumns.size(); idx++) { // <- JDBC is 1-based
                statement.setObject(idx + 1, resultSet.getObject(idx + 1));
            }
            timerHiveFetching += System.nanoTime() - start;

            rowsProcessed++;
            statement.addBatch();

            rowIdx++;
            if (rowIdx == opts.insertBatchSize) {
                rowIdx = 0;

                start = System.nanoTime();

                // executeBatchWithRetry(statement, oracleConnection);
                statement.executeBatch();
                statement.clearBatch();

                timerOracleInserting += System.nanoTime() - start;

                batchIdx++;
            }

            if (batchIdx == opts.commitBatchCount) {
                batchIdx = 0;
                oracleConnection.commit();
                numberOfBatchesCommitted++;
                LOG.info(String.format("Number of rows inserted so far: %d",
                        numberOfBatchesCommitted * (opts.insertBatchSize * opts.commitBatchCount)));
            }
            start = System.nanoTime();
        }

        if (rowIdx > 0) {
            start = System.nanoTime();

            //executeBatchWithRetry(statement, oracleConnection);
            statement.executeBatch();

            timerOracleInserting += System.nanoTime() - start;
        }

        oracleConnection.commit();

        statement.close();
    } catch (SQLException ex) {

        if (Utilities.oracleSessionHasBeenKilled(ex)) {
            LOG.info("\n*********************************************************"
                    + "\nThe Oracle session in use has been killed by a 3rd party."
                    + "\n*********************************************************");
        } else
            LOG.error("An error occurred within the process of fetching Hive results "
                    + "and inserting them into an Oracle table. (1)", ex);

        try {
            oracleConnection.rollback();
        } catch (SQLException e) {
        }

        System.exit(1);
    } catch (Exception ex) {
        LOG.error("An error occurred within the process of fetching Hive results "
                + "and inserting them into an Oracle table. (2)", ex);
    } finally {
        LOG.info(String.format("Number of rows obtained from Hive: %d", rowsProcessed));
    }

    counters.rowsProcessed = rowsProcessed;
    counters.hiveFetchTimeNanoSec = timerHiveFetching;
    counters.oracleInsertTimeNanoSec = timerOracleInserting;
}