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:net.solarnetwork.node.dao.jdbc.AbstractJdbcDao.java

/**
 * Persist a domain object, without using auto-generated keys.
 * /*from  ww  w  . ja va2 s. co m*/
 * @param obj
 *        the domain object to persist
 * @param sqlInsert
 *        the SQL insert statement to use
 * @return the primary key created for the domain object
 */
protected Long storeDomainObjectWithoutAutogeneratedKeys(final T obj, final String sqlInsert) {
    Object result = getJdbcTemplate().execute(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement(sqlInsert);
            setStoreStatementValues(obj, ps);
            return ps;
        }
    }, new PreparedStatementCallback<Object>() {

        @Override
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.execute();
            int count = ps.getUpdateCount();
            if (count == 1 && ps.getMoreResults()) {
                ResultSet rs = ps.getResultSet();
                if (rs.next()) {
                    return rs.getObject(1);
                }
            }
            return null;
        }
    });
    if (result instanceof Long) {
        return (Long) result;
    } else if (result instanceof Number) {
        return Long.valueOf(((Number) result).longValue());
    }
    if (log.isWarnEnabled()) {
        log.warn("Unexpected (non-number) primary key returned: " + result);
    }
    return null;
}

From source file:fabrice.CSVPrinter.java

/**
 * Prints all the objects in the given JDBC result set.
 *
 * @param resultSet/*from   www .  j a v a 2  s. co  m*/
 *            result set the values to print.
 * @throws IOException
 *             If an I/O error occurs
 * @throws SQLException
 *             if a database access error occurs
 */
public void printRecords(final ResultSet resultSet) throws SQLException, IOException {
    final int columnCount = resultSet.getMetaData().getColumnCount();
    while (resultSet.next()) {
        for (int i = 1; i <= columnCount; i++) {
            print(resultSet.getObject(i));
        }
        println();
    }
}

From source file:net.solarnetwork.node.dao.jdbc.test.PreparedStatementCsvReaderTests.java

@Test
public void importTable() throws Exception {
    final String tableName = "SOLARNODE.TEST_CSV_IO";
    executeSqlScript("net/solarnetwork/node/dao/jdbc/test/csv-data-01.sql", false);
    importData(tableName);/*  w w  w.  j a v a2 s  .c o m*/
    final MutableInt row = new MutableInt(0);
    final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
    sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
    final Calendar utcCalendar = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
    jdbcTemplate.query(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            // TODO Auto-generated method stub
            return con.prepareStatement("select PK,STR,INUM,DNUM,TS from solarnode.test_csv_io order by pk");
        }
    }, new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            row.increment();
            final int i = row.intValue();
            assertEquals("PK " + i, i, rs.getLong(1));
            if (i == 2) {
                assertNull("STR " + i, rs.getString(2));
            } else {
                assertEquals("STR " + i, "s0" + i, rs.getString(2));
            }
            if (i == 3) {
                assertNull("INUM " + i, rs.getObject(3));
            } else {
                assertEquals("INUM " + i, i, rs.getInt(3));
            }
            if (i == 4) {
                assertNull("DNUM " + i, rs.getObject(4));
            } else {
                assertEquals("DNUM " + i, i, rs.getDouble(4), 0.01);
            }
            if (i == 5) {
                assertNull("TS " + i, rs.getObject(5));
            } else {
                Timestamp ts = rs.getTimestamp(5, utcCalendar);
                try {
                    assertEquals("TS " + i, sdf.parse("2016-10-0" + i + "T12:01:02.345Z"), ts);
                } catch (ParseException e) {
                    // should not get here
                }
            }
        }
    });
    assertEquals("Imported count", 5, row.intValue());
}

From source file:com.micromux.cassandra.jdbc.CollectionsTest.java

@Test
public void testUpdateList() throws Exception {
    if (LOG.isDebugEnabled())
        LOG.debug("Test: 'testUpdateList'\n");

    Statement statement = con.createStatement();

    String update1 = "UPDATE testcollection SET L = L + [2,4,6] WHERE k = 1;";
    statement.executeUpdate(update1);//from ww w.  ja v  a2 s.co  m

    ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;");
    result.next();

    assertEquals(1, result.getInt("k"));
    Object myObj = result.getObject("l");
    List<Long> myList = (List<Long>) myObj;
    assertEquals(6, myList.size());
    assertTrue(12345L == myList.get(2));

    if (LOG.isDebugEnabled())
        LOG.debug("l           = '{}'", myObj);

    String update2 = "UPDATE testcollection SET L = [98,99,100] + L WHERE k = 1;";
    statement.executeUpdate(update2);
    result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;");
    result.next();
    myObj = result.getObject("l");
    myList = (List<Long>) myObj;

    // 98, 99, 100, 1, 3, 12345, 2, 4, 6
    // remove all of these values from the list - it should be empty
    assertEquals("Checking the size of the List", 9, myList.size());

    myList.remove(Long.valueOf(98));
    myList.remove(Long.valueOf(99));
    myList.remove(Long.valueOf(100));
    myList.remove(Long.valueOf(1));
    myList.remove(Long.valueOf(3));
    myList.remove(Long.valueOf(12345));
    myList.remove(Long.valueOf(2));
    myList.remove(Long.valueOf(4));
    myList.remove(Long.valueOf(6));

    assertEquals("List should now be empty", 0, myList.size());

    if (LOG.isDebugEnabled())
        LOG.debug("l           = '{}'", myObj);

    String update3 = "UPDATE testcollection SET L[0] = 2000 WHERE k = 1;";
    statement.executeUpdate(update3);
    result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;");
    result.next();
    myObj = result.getObject("l");
    myList = (List<Long>) myObj;

    if (LOG.isDebugEnabled())
        LOG.debug("l           = '{}'", myObj);

    //        String update4 = "UPDATE testcollection SET L = L +  ? WHERE k = 1;";
    String update4 = "UPDATE testcollection SET L =  ? WHERE k = 1;";

    PreparedStatement prepared = con.prepareStatement(update4);
    List<Long> myNewList = new ArrayList<Long>();
    myNewList.add(8888L);
    myNewList.add(9999L);
    prepared.setObject(1, myNewList, Types.OTHER);
    prepared.execute();

    result = prepared.executeQuery("SELECT * FROM testcollection WHERE k = 1;");
    result.next();
    myObj = result.getObject("l");
    myList = (List<Long>) myObj;

    if (LOG.isDebugEnabled())
        LOG.debug("l (prepared)= '{}'\n", myObj);
}

From source file:com.mvdb.etl.dao.impl.JdbcGenericDAO.java

@Override
public DataHeader fetchAll2(File snapshotDirectory, Timestamp modifiedAfter, String objectName,
        final String keyName, final String updateTimeColumnName) {
    File objectFile = new File(snapshotDirectory, "data-" + objectName + ".dat");
    final GenericConsumer genericConsumer = new SequenceFileConsumer(objectFile);
    final DataHeader dataHeader = new DataHeader();

    String sql = "SELECT * FROM " + objectName + " o where o.update_time >= ?";

    getJdbcTemplate().query(sql, new Object[] { modifiedAfter }, new RowCallbackHandler() {

        @Override//from  w  ww .j  av a2s  .  co  m
        public void processRow(ResultSet row) throws SQLException {
            final Map<String, Object> dataMap = new HashMap<String, Object>();
            ResultSetMetaData rsm = row.getMetaData();
            int columnCount = rsm.getColumnCount();
            for (int column = 1; column < (columnCount + 1); column++) {
                dataMap.put(rsm.getColumnName(column), row.getObject(rsm.getColumnLabel(column)));
            }

            DataRecord dataRecord = new GenericDataRecord(dataMap, keyName, globalMvdbKeyMaker,
                    updateTimeColumnName, new GlobalMvdbUpdateTimeMaker());
            genericConsumer.consume(dataRecord);
            dataHeader.incrementCount();

        }
    });

    genericConsumer.flushAndClose();

    writeDataHeader(dataHeader, objectName, snapshotDirectory);
    return dataHeader;
}

From source file:com.base2.kagura.core.report.connectors.FreemarkerSQLDataReportConnector.java

/**
 * Returns a preparedStatement result into a List of Maps. Not the most efficient way of storing the values, however
 * the results should be limited at this stage.
 * @param rows The result set.// w  w w.ja  v  a2  s.c o  m
 * @return Mapped results.
 * @throws RuntimeException upon any error, adds values to "errors"
 */
public List<Map<String, Object>> resultSetToMap(ResultSet rows) {
    try {
        List<Map<String, Object>> beans = new ArrayList<Map<String, Object>>();
        int columnCount = rows.getMetaData().getColumnCount();
        while (rows.next()) {
            LinkedHashMap<String, Object> bean = new LinkedHashMap<String, Object>();
            beans.add(bean);
            for (int i = 0; i < columnCount; i++) {
                Object object = rows.getObject(i + 1);
                String columnLabel = rows.getMetaData().getColumnLabel(i + 1);
                String columnName = rows.getMetaData().getColumnName(i + 1);
                bean.put(StringUtils.defaultIfEmpty(columnLabel, columnName),
                        object != null ? object.toString() : "");
            }
        }
        return beans;
    } catch (Exception ex) {
        errors.add(ex.getMessage());
        throw new RuntimeException(ex);
    }
}

From source file:com.modelmetrics.cloudconverter.forceutil.DataUpsertExecutor.java

public void executeWithResultSet(MigrationContext migrationContext) throws Exception {

    log.debug("starting data transfer (upsert)...");

    dao.setSalesforceSession(migrationContext.getSalesforceSession());

    Collection<Sproxy> toUpsert = new ArrayList<Sproxy>();

    ResultSet rs = migrationContext.getResultSet();
    ResultSetMetaData rsmd = migrationContext.getResultSetMetaData();

    if (rs == null) {
        log.info("result set is null");
    }/*from w ww .  ja va2 s.  c  o m*/

    while (rs.next()) {

        Sproxy current = sproxyBuilder.buildEmpty(migrationContext.getCustomObject().getFullName());

        for (int i = 0; i < rsmd.getColumnCount(); i++) {
            current.setValue(migrationContext.getFieldMap().get(rsmd.getColumnName(i + 1)),
                    rs.getObject(i + 1));
        }

        toUpsert.add(current);

        if (toUpsert.size() == MAX_SPROXY_BATCH_SIZE) {
            dao.upsert(migrationContext.getExternalIdForUpsert(), toUpsert);
            toUpsert = new ArrayList<Sproxy>();
        }

    }

    log.debug("starting the upsert..." + migrationContext.getExternalIdForUpsert());

    dao.upsert(migrationContext.getExternalIdForUpsert(), toUpsert);

    log.debug("insert complete...");

}

From source file:com.atolcd.alfresco.repo.patch.SchemaUpgradeScriptPatch.java

/**
 * Execute the given SQL statement, absorbing exceptions that we expect
 * during schema creation or upgrade.//ww w . j  av a  2  s  .  c  om
 * 
 * @param fetchColumnName
 *            the name of the column value to return
 */
private Object executeStatement(Connection connection, String sql, String fetchColumnName, boolean optional,
        int line, File file) throws Exception {
    StringBuilder executedStatements = executedStatementsThreadLocal.get();
    if (executedStatements == null) {
        throw new IllegalArgumentException("The executedStatementsThreadLocal must be populated");
    }

    Statement stmt = connection.createStatement();
    Object ret = null;
    try {
        if (logger.isDebugEnabled()) {
            LogUtil.debug(logger, MSG_EXECUTING_STATEMENT, sql);
        }
        boolean haveResults = stmt.execute(sql);
        // Record the statement
        executedStatements.append(sql).append(";\n\n");
        if (haveResults && fetchColumnName != null) {
            ResultSet rs = stmt.getResultSet();
            if (rs.next()) {
                // Get the result value
                ret = rs.getObject(fetchColumnName);
            }
        }
    } catch (SQLException e) {
        if (optional) {
            // it was marked as optional, so we just ignore it
            LogUtil.debug(logger, MSG_OPTIONAL_STATEMENT_FAILED, sql, e.getMessage(), file.getAbsolutePath(),
                    line);
        } else {
            LogUtil.error(logger, ERR_STATEMENT_FAILED, sql, e.getMessage(), file.getAbsolutePath(), line);
            throw e;
        }
    } finally {
        try {
            stmt.close();
        } catch (Throwable e) {
        }
    }
    return ret;
}

From source file:com.micromux.cassandra.jdbc.CollectionsTest.java

@Test
public void testWriteReadTimestampMap() throws Exception {
    if (LOG.isDebugEnabled())
        LOG.debug("Test: 'testWriteReadTimestampMap'\n");

    Statement statement = con.createStatement();

    // add some items to the set        
    String sql = "insert into testcollection(k,M2) values(?,?)";
    Map<String, Date> is = new HashMap<String, Date>();
    is.put("K" + System.currentTimeMillis(), new Date());
    PreparedStatement ps = con.prepareStatement(sql);

    {// w w  w. j  a  v a 2s  . c o  m
        ps.setInt(1, 1);
        ps.setObject(2, is, java.sql.Types.OTHER);
        ps.executeUpdate();
    }
    ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;");
    result.next();

    assertEquals(1, result.getInt("k"));
    //Object myObj = result.getObject("m");
    Map<String, Date> map = (Map<String, Date>) result.getObject("m2");
    //Map<Double,Boolean> myMap = (Map<Double,Boolean>) myObj;
    assertEquals(1, map.size());
    if (LOG.isDebugEnabled())
        LOG.debug("map key : " + map);

}

From source file:com.opencsv.ResultSetHelperService.java

private String getColumnValue(ResultSet rs, int colType, int colIndex, boolean trim, String dateFormatString,
        String timestampFormatString) throws SQLException, IOException {

    String value = "";

    switch (colType) {
    case Types.BIT:
    case Types.JAVA_OBJECT:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getObject(colIndex), "");
        value = ObjectUtils.toString(rs.getObject(colIndex), "");
        break;//  www  .j  a  va 2 s  .  co m
    case Types.BOOLEAN:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getBoolean(colIndex));
        value = ObjectUtils.toString(rs.getBoolean(colIndex));
        break;
    case Types.NCLOB: // todo : use rs.getNClob
    case Types.CLOB:
        Clob c = rs.getClob(colIndex);
        if (c != null) {
            StrBuilder sb = new StrBuilder();
            sb.readFrom(c.getCharacterStream());
            value = sb.toString();
        }
        break;
    case Types.BIGINT:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getLong(colIndex));
        value = ObjectUtils.toString(rs.getLong(colIndex));
        break;
    case Types.DECIMAL:
    case Types.REAL:
    case Types.NUMERIC:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getBigDecimal(colIndex), "");
        value = ObjectUtils.toString(rs.getBigDecimal(colIndex), "");
        break;
    case Types.DOUBLE:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getDouble(colIndex));
        value = ObjectUtils.toString(rs.getDouble(colIndex));
        break;
    case Types.FLOAT:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getFloat(colIndex));
        value = ObjectUtils.toString(rs.getFloat(colIndex));
        break;
    case Types.INTEGER:
    case Types.TINYINT:
    case Types.SMALLINT:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getInt(colIndex));
        value = ObjectUtils.toString(rs.getInt(colIndex));
        break;
    case Types.DATE:
        java.sql.Date date = rs.getDate(colIndex);
        if (date != null) {
            SimpleDateFormat df = new SimpleDateFormat(dateFormatString);
            value = df.format(date);
        }
        break;
    case Types.TIME:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getTime(colIndex), "");
        value = ObjectUtils.toString(rs.getTime(colIndex), "");
        break;
    case Types.TIMESTAMP:
        value = handleTimestamp(rs.getTimestamp(colIndex), timestampFormatString);
        break;
    case Types.NVARCHAR: // todo : use rs.getNString
    case Types.NCHAR: // todo : use rs.getNString
    case Types.LONGNVARCHAR: // todo : use rs.getNString
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
    case Types.CHAR:
        String columnValue = rs.getString(colIndex);
        if (trim && columnValue != null) {
            value = columnValue.trim();
        } else {
            value = columnValue;
        }
        break;
    default:
        value = "";
    }

    if (rs.wasNull() || value == null) {
        value = "";
    }

    return value;
}