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:cc.osint.graphd.db.SQLDB.java

private JSONObject jsonizeResultSet(ResultSet rs) throws Exception {
    List<JSONObject> results = new ArrayList<JSONObject>();
    ResultSetMetaData md = rs.getMetaData();
    int colmax = md.getColumnCount();
    int i;/*  w ww  . j  av  a 2s .  c  om*/
    for (; rs.next();) {
        JSONObject result = new JSONObject();
        for (i = 1; i <= colmax; i++) {
            String colName = md.getColumnName(i).toLowerCase();
            String colClassName = md.getColumnClassName(i);
            String colType = md.getColumnTypeName(i);
            Object obj = rs.getObject(i);
            result.put(colName, obj);
            log.info(colName + ": " + colClassName + ": " + colType + ": " + obj.toString());
        }
        results.add(result);
    }
    JSONObject result = new JSONObject();
    result.put("results", results);
    return result;
}

From source file:JDBCExecutor.java

public void executeStatement(String sql) {

    LOG("Executing query: " + sql);
    try (Connection connection = getConnection()) {
        Statement stmt = connection.createStatement();
        LOG("\t Time taken to create statement : ");

        ResultSet rs = stmt.executeQuery(sql);
        LOG("\t Time taken to execute query : ");
        if (rs == null) {
            return;
        }//from   w ww. j a v a 2 s . co m

        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        LOG("\t\t Time taken to get resultset metadata: ");

        boolean processedFirstRecord = false;
        while (rs.next()) {
            for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                Object colObj = rs.getObject(i);
            }
            if (!processedFirstRecord) {
                processedFirstRecord = true;
                LOG("\t\t Processed first record : ");
            }
        }
        LOG("\t\t Processed all records : ");

        rs.close();
        LOG("\t\t Closed resultSet : ");
    } catch (SQLException e) {
        LOG("Error executing query", e);
    }
    LOG("\t Closed connection : ");
}

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

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

    Statement statement = con.createStatement();

    String insert = "INSERT INTO testcollection (k,L) VALUES( 1,[1, 3, 12345]);";
    statement.executeUpdate(insert);//from ww  w. j  a va2  s. c  o m

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

    assertEquals(1, result.getInt("k"));

    Object myObj = result.getObject("l");
    if (LOG.isDebugEnabled())
        LOG.debug("l           = '{}'\n", myObj);
    List<Long> myList = (List<Long>) myObj;
    assertEquals(3, myList.size());
    assertTrue(12345L == myList.get(2));
    assertTrue(myObj instanceof ArrayList);

    // TODO: make this work again?
    //myList = (List<Long>) extras(result).getList("l");
    statement.close();
    assertTrue(3L == myList.get(1));
}

From source file:net.solarnetwork.node.dao.jdbc.power.JdbcPowerDatumDao.java

@Override
@Transactional(readOnly = true, propagation = Propagation.REQUIRED)
public List<PowerDatum> getDatumNotUploaded(String destination) {
    return findDatumNotUploaded(new RowMapper<PowerDatum>() {

        @Override/* www  .java2s  .c  o m*/
        public PowerDatum mapRow(ResultSet rs, int rowNum) throws SQLException {
            if (log.isTraceEnabled()) {
                log.trace("Handling result row " + rowNum);
            }
            PowerDatum datum = new PowerDatum();
            int col = 1;
            datum.setCreated(rs.getTimestamp(col++));
            datum.setSourceId(rs.getString(col++));

            Number val = (Number) rs.getObject(col++);
            datum.setLocationId(val == null ? null : val.longValue());

            val = (Number) rs.getObject(col++);
            datum.setWatts(val == null ? null : val.intValue());

            val = (Number) rs.getObject(col++);
            datum.setBatteryVolts(val == null ? null : val.floatValue());

            val = (Number) rs.getObject(col++);
            datum.setBatteryAmpHours(val == null ? null : val.doubleValue());

            val = (Number) rs.getObject(col++);
            datum.setDcOutputVolts(val == null ? null : val.floatValue());

            val = (Number) rs.getObject(col++);
            datum.setDcOutputAmps(val == null ? null : val.floatValue());

            val = (Number) rs.getObject(col++);
            datum.setAcOutputVolts(val == null ? null : val.floatValue());

            val = (Number) rs.getObject(col++);
            datum.setAcOutputAmps(val == null ? null : val.floatValue());

            val = (Number) rs.getObject(col++);
            datum.setWattHourReading(val == null ? null : val.longValue());

            val = (Number) rs.getObject(col++);
            datum.setAmpHourReading(val == null ? null : val.doubleValue());

            return datum;
        }
    });
}

From source file:it.unibas.spicy.persistence.idgenerator.utils.ReadDB.java

public ArrayList<InputDataModel> readTargetDatabase(String[] targetColumns)
        throws SQLException, IOException, ClassNotFoundException {
    ArrayList<InputDataModel> inputData = new ArrayList<>();
    ArrayList<String> configurationProperties = getExportDatabaseConfig();
    Connection connection = null;
    try {/* www . jav  a  2 s  .  c om*/
        connection = getConnectionToDatabase(configurationProperties.get(0),
                configurationProperties.get(1) + configurationProperties.get(4), configurationProperties.get(2),
                configurationProperties.get(3));
        Statement statement = connection.createStatement();

        String columnsToQuery = "";
        for (String column : targetColumns) {
            columnsToQuery += column + ",";
        }

        ResultSet tableRows = statement.executeQuery(
                "SELECT " + columnsToQuery.substring(0, columnsToQuery.length() - 1) + " FROM " + table + ";");

        ResultSetMetaData rsmd = tableRows.getMetaData();
        int columnsNumber = rsmd.getColumnCount();
        while (tableRows.next()) {
            InputDataModel idm = new InputDataModel();
            for (int i = 1; i <= columnsNumber; i++) {
                idm.addValue(String.valueOf(tableRows.getObject(i)));
            }
            inputData.add(idm);
        }
    } catch (ClassNotFoundException | SQLException e) {
        System.err.println(e.getMessage());
        System.exit(-1);
    } finally {
        if (connection != null)
            connection.close();
    }
    return inputData;
}

From source file:com.adito.jdbc.DBDumper.java

/**
 * Dump a single result set row as an INSERT statement.
 * /*from   w  w  w . jav  a 2  s  . co m*/
 * @param writer
 * @param resultSet
 * @throws SQLException
 */
public void dumpRow(PrintWriter writer, ResultSet resultSet) throws SQLException {
    String tableName = resultSet.getMetaData().getTableName(1);
    int columnCount = resultSet.getMetaData().getColumnCount();
    writer.print("INSERT INTO " + tableName + " VALUES (");
    for (int j = 0; j < columnCount; j++) {
        if (j > 0) {
            writer.print(", ");
        }
        Object value = resultSet.getObject(j + 1);
        if (value == null) {
            writer.print("NULL");
        } else {
            String outputValue = value.toString();
            if (value instanceof Number) {
                writer.print(outputValue);
            } else {
                /*
                 * TODO
                 * 
                 * This escaping will current only work
                 * for HSQLDB. This needs to be moved up
                 * into the engine.
                 */
                outputValue = outputValue.replaceAll("'", "''");
                writer.print("'" + outputValue + "'");
            }
        }
    }
    writer.println(");");
}

From source file:ca.on.gov.jus.icon.common.util.LookupNestedMapHandler.java

/**
 * Iterates through the query result set and creates a correctly
 * indexed map of maps.  This is only intended to work with a result
 * set that contains three columns and the first column must
 * cast successfully to a <code>Integer</code> datatype.
 * //w w  w .  ja  va 2 s . com
 * @param   rs      The result set that is used to obtain the name/value data.
 * @return   Object   The <code>java.util.Map</code> collection containing the results. 
 * @see org.apache.commons.dbutils.ResultSetHandler#handle(java.sql.ResultSet)
 */
public Object handle(ResultSet rs) throws SQLException {
    Map results = new HashMap();
    Map nestedMap = new HashMap();
    Integer currentId = null;

    // Get the number of columns in the result set.
    ResultSetMetaData rsmd = rs.getMetaData();
    int cols = rsmd.getColumnCount();

    // Populate the Map with the name value pairs
    // if the result set contains two columns of data.
    if (cols == MAP_RESULT_SET_COLUMNS) {
        while (rs.next()) {
            Object primaryId = rs.getObject(1);
            Object secondaryId = rs.getObject(2);
            Object secondaryValue = rs.getObject(3);

            // Initialize the current id on the first iteration.
            if (currentId == null) {
                currentId = (Integer) primaryId;
            }

            // Check if the primary id is not the same as the current id.         
            if (currentId.compareTo(primaryId) != 0) {
                // Put the current nested map into the result map
                // and create a new nested map.
                results.put(currentId, nestedMap);
                nestedMap = new HashMap();
                currentId = (Integer) primaryId;
            }

            // Put the key & value into the current nested map.
            // This occurs after checking if a new nested map is required.
            nestedMap.put(secondaryId, secondaryValue);
        }

        // Put the final nested map into the results map
        // once the iterations are complete.
        results.put(currentId, nestedMap);
    }

    return results;
}

From source file:com.linuxrouter.netcool.session.QueryUtils.java

public ArrayList<HashMap<String, Object>> executeQuery(String dbName, String sql) {
    Long start = System.currentTimeMillis();
    ArrayList<HashMap<String, Object>> result = new ArrayList<>();
    HashMap<Integer, String> colTypes = new HashMap<Integer, String>();
    HashMap<Integer, String> colNames = new HashMap<Integer, String>();
    try {// w  w  w .j a  v  a  2  s.  c o m
        //connection caching...
        Connection con = null;
        if (connectionMap.get(dbName) == null) {
            BasicDataSource ds = DbUtils.getSimpleDataSourceByName(dbName);
            con = ds.getConnection();
            connectionMap.put(dbName, con);
        } else {
            con = connectionMap.get(dbName);

        }

        Statement st = con.createStatement();

        ResultSet rs = st.executeQuery(sql);
        ResultSetMetaData metaData = rs.getMetaData();
        int colCount = metaData.getColumnCount();
        for (int i = 1; i <= colCount; i++) {
            colTypes.put(i, metaData.getColumnTypeName(i));
            colNames.put(i, metaData.getColumnLabel(i));
        }
        while (rs.next()) {
            HashMap<String, Object> dado = new HashMap<>();
            for (int i = 1; i <= colCount; i++) {
                dado.put(colNames.get(i), rs.getObject(i));

            }
            result.add(dado);
        }
        rs.close();
        st.close();
        //con.close();
        Long end = System.currentTimeMillis();
        //logger.debug("Query on external DB took: " + (end - start) + "ms");
    } catch (SQLException ex) {
        logger.error("Erro ao executar query:", ex);
    }
    return result;
}

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

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

    Statement statement = con.createStatement();

    // add some items to the set
    String update1 = "UPDATE testcollection SET S = S + {'green', 'white', 'orange'} WHERE k = 1;";
    statement.executeUpdate(update1);// ww w.  jav  a  2s .  co  m

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

    assertEquals(1, result.getInt("k"));
    Object myObj = result.getObject("s");
    Set<String> mySet = (Set<String>) myObj;
    assertEquals(5, mySet.size());
    assertTrue(mySet.contains("white"));

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

    // remove an item from the set
    String update2 = "UPDATE testcollection SET S = S - {'red'} WHERE k = 1;";
    statement.executeUpdate(update2);

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

    assertEquals(1, result.getInt("k"));

    myObj = result.getObject("s");
    mySet = (Set<String>) myObj;
    assertEquals(4, mySet.size());
    assertTrue(mySet.contains("white"));
    assertFalse(mySet.contains("red"));

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

    String update4 = "UPDATE testcollection SET S =  ? WHERE k = 1;";

    PreparedStatement prepared = con.prepareStatement(update4);
    Set<String> myNewSet = new HashSet<String>();
    myNewSet.add("black");
    myNewSet.add("blue");
    prepared.setObject(1, myNewSet, Types.OTHER);
    prepared.execute();

    result = prepared.executeQuery("SELECT * FROM testcollection WHERE k = 1;");
    result.next();
    myObj = result.getObject("s");
    mySet = (Set<String>) myObj;

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

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

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

    Statement statement = con.createStatement();

    // add some items to the set
    String update1 = "UPDATE testcollection SET M = M + {1.0: true, 3.0: false, 5.0: false} WHERE k = 1;";
    statement.executeUpdate(update1);//from   w ww .j ava2 s .  c om

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

    assertEquals(1, result.getInt("k"));
    Object myObj = result.getObject("m");
    Map<Double, Boolean> myMap = (Map<Double, Boolean>) myObj;
    assertEquals(6, myMap.size());
    assertTrue(myMap.keySet().contains(5.0));

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

    // remove an item from the map
    String update2 = "DELETE M[6.0] FROM testcollection WHERE k = 1;";
    statement.executeUpdate(update2);

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

    assertEquals(1, result.getInt("k"));

    myObj = result.getObject("m");
    myMap = (Map<Double, Boolean>) myObj;
    assertEquals(5, myMap.size());
    assertTrue(myMap.keySet().contains(5.0));
    assertFalse(myMap.keySet().contains(6.0));

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

    String update4 = "UPDATE testcollection SET M =  ? WHERE k = 1;";

    PreparedStatement prepared = con.prepareStatement(update4);
    Map<Double, Boolean> myNewMap = new LinkedHashMap<Double, Boolean>();
    myNewMap.put(10.0, false);
    myNewMap.put(12.0, true);
    prepared.setObject(1, myNewMap, Types.OTHER);
    prepared.execute();

    result = prepared.executeQuery("SELECT * FROM testcollection WHERE k = 1;");
    result.next();
    myObj = result.getObject("m");
    myMap = (Map<Double, Boolean>) myObj;

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