Java Code Examples for java.sql.ResultSet#getObject()

@Override
public void op() {
    try {
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Object col1 = rs.getObject(1);
            Object col2 = rs.getObject(2);
            Object col3 = rs.getObject(3);
            this.hash = this.hash ^ col1.hashCode();
            this.hash = this.hash ^ col2.hashCode();
            this.hash = this.hash ^ col3.hashCode();
        }
        rs.close();
    }
    catch (SQLException e) {
        e.printStackTrace();
    }
}
 

@Override
protected Period get(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws SQLException {
    final PGInterval interval = (PGInterval) rs.getObject(names[0]);

    if (interval == null) {
        return null;
    }

    final int years = interval.getYears();
    final int months = interval.getMonths();
    final int days = interval.getDays();

    return Period.ofYears(years)
            .plusMonths(months)
            .plusDays(days);
}
 

protected void outputResultSet(ResultSet resultSet, AkibanAppender appender) throws IOException, SQLException {
    ResultSetMetaData metaData = resultSet.getMetaData();
    int ncols = metaData.getColumnCount();
    PostgresType[] pgTypes = new PostgresType[ncols];
    for (int i = 0; i < ncols; i++) {
        DataTypeDescriptor sqlType = resultColumnSQLType(metaData, i+1);
        pgTypes[i] = PostgresType.fromDerby(sqlType, null);
    }
    encoder.appendString("[");
    boolean first = true;
    while (resultSet.next()) {
        encoder.appendString(first ? "{" : ",{");
        for (int i = 0; i < ncols; i++) {
            String name = metaData.getColumnName(i+1);
            Object value = resultSet.getObject(i+1);
            outputValue(name, value, pgTypes[i], appender, (i == 0));
        }
        encoder.appendString("}");
        first = false;
    }
    resultSet.close();
    encoder.appendString("]");
}
 

private void checkIndexColumn(ResultSet rs, int columnNumber,
    Object expectedValue, AtomicInteger numRows) throws Exception {
  Object[] failedRow = null;
  while (rs.next()) {
    if (!expectedValue.equals(rs.getObject(columnNumber))) {
      failedRow = new Object[rs.getMetaData().getColumnCount()];
      for (int index = 0; index < failedRow.length; index++) {
        failedRow[index] = rs.getObject(index + 1);
      }
    }
    else if (numRows != null) {
      numRows.incrementAndGet();
    }
  }
  rs.close();
  if (failedRow != null) {
    fail("unexpected index column [" + failedRow[columnNumber - 1]
        + "] expected [" + expectedValue + "] for row: "
        + java.util.Arrays.toString(failedRow));
  }
}
 

/**
 * <p>
 * Casting to UDTs.
 * </p>
 */
public void test_06_casts() throws Exception
{
    Connection conn = getConnection();

    // cast a NULL as a UDT
    goodStatement
        ( conn,
          "create type price_06_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" );
    assertResults
        (
         conn,
         "values ( cast ( null as price_06_b ) )\n",
         new String[][]
         {
             { null },
         },
         false
         );

    // casting an untyped parameter to a UDT
    PreparedStatement ps = chattyPrepare
        ( conn, "values ( cast ( ? as price_06_b ) )" );
    ps.setObject( 1, Price.makePrice() );
    ResultSet rs = ps.executeQuery();
    rs.next();
    Price result = (Price) rs.getObject( 1 );
    rs.close();
    ps.close();
    assertTrue( Price.makePrice().equals( result ) );
}
 

@Override
@SuppressWarnings("unchecked")
public T extractData (ResultSet rs) throws SQLException, DataAccessException {
	if (rs.next ()) {
		return (T) rs.getObject (1);
	}
	return null;
}
 

public static String currentSchema(Statement stmts)
    throws SQLException {
  ResultSet rs = stmts
      .executeQuery("SELECT x from (VALUES(CURRENT SCHEMA)) as mySch(x) ");
  rs.next();
  return (String)rs.getObject(1);
}
 

public static void main(String[] args) throws Exception {

        TDataSource ds = new TDataSource();

        // init a datasource with dynamic config on diamond
        ds.setAppName("TBRAC2_APP");
        ds.setDynamicRule(true);
        ds.init();

        System.out.println("init done");

        Connection conn = ds.getConnection();

        // select all records
        PreparedStatement ps = conn.prepareStatement("select _rcp_daily_case_main.id from rcp_daily_case_main as _rcp_daily_case_main where gmt_create =?");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse("2013-10-20");
        ps.setObject(1, date);

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            StringBuilder sb = new StringBuilder();
            int count = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= count; i++) {

                String key = rs.getMetaData().getColumnLabel(i);
                Object val = rs.getObject(i);
                sb.append("[" + rs.getMetaData().getTableName(i) + "." + key + "->" + val + "]");
            }
            System.out.println(sb.toString());
        }

        rs.close();
        ps.close();
        conn.close();

        System.out.println("query done");

    }
 

@SuppressWarnings("unchecked")
@Override
public <S> Map<S, Integer> countBy(final Field<S> byField) throws SQLException {
	final SqlContext context = new SqlContext(this);
	final List<Object> bindings = new ArrayList<Object>();
	final String fromClause = getFromClause(context, bindings);
	initTableNameMap(true);
	final Tuple2<String, List<Object>> wcab = getWhereClauseAndBindings(context);
	bindings.addAll(wcab.b);
	String sql = fromClause + wcab.a;
	sql = "select "+ Util.derefField(byField, context)
			+", count("+ Util.derefField(byField, context) +")"+ sql
			+" group by "+ Util.derefField(byField, context);
	Util.log(sql, null);
	final Tuple2<Connection,Boolean> connInfo = getConnR(getDataSource());
	final Connection conn = connInfo.a;
	final PreparedStatement ps = createPS(sql, conn);
	setBindings(ps, bindings);
	_preExecute(context, conn);
	ps.execute();
	final ResultSet rs = ps.getResultSet();
	final Map<Object, Integer> result = new LinkedHashMap<Object, Integer>();
	while (rs.next()) {
		Object key = null;
		if (byField.TYPE == Long.class) key = rs.getLong(1); else
		if (byField.TYPE == Double.class) key = rs.getDouble(1); else
		key = rs.getObject(1);
		final int value = rs.getInt(2);
		result.put(key, value);
	}
	rs.close();
	ps.close();
	_postExecute(context, conn);
	if (connInfo.b) {
		conn.close();
	}
	return (Map<S, Integer>) result;
}
 

public Map<String, Object> map(ResultSet rs, int rowNum) throws SQLException {
	initColumns(rs);
	Map<String, Object> mapOfColValues = new LinkedHashMap<String, Object>(columns.length);
	for (int i = 0; i < columns.length; i++) {
		Object obj = rs.getObject(i + 1);
		mapOfColValues.put(columns[i], obj);
	}
	return mapOfColValues;
}
 

public static Object getRSData(ResultSet rs, String columnName, int jdbcType) throws SQLException {
    if (jdbcType == Types.BIT || jdbcType == Types.BOOLEAN) {
        return rs.getByte(columnName);
    } else {
        return rs.getObject(columnName);
    }
}
 

/**
 * <p>
 * Casting to UDTs.
 * </p>
 */
public void test_06_casts() throws Exception
{
    Connection conn = getConnection();

    // cast a NULL as a UDT
    goodStatement
        ( conn,
          "create type price_06_b external name 'com.splicemachine.dbTesting.functionTests.tests.lang.Price' language java\n" );
    assertResults
        (
         conn,
         "values ( cast ( null as price_06_b ) )\n",
         new String[][]
         {
             { null },
         },
         false
         );

    // casting an untyped parameter to a UDT
    PreparedStatement ps = chattyPrepare
        ( conn, "values ( cast ( ? as price_06_b ) )" );
    ps.setObject( 1, Price.makePrice() );
    ResultSet rs = ps.executeQuery();
    rs.next();
    Price result = (Price) rs.getObject( 1 );
    rs.close();
    ps.close();
    assertTrue( Price.makePrice().equals( result ) );
}
 

protected void execute(Connection connection, String sql, Object[] params) throws SQLException {
    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }

        boolean query = stmt.execute();
        if (query) {
            ResultSet rs = stmt.getResultSet();
            while (rs.next()) {
                rs.getObject(1);
            }
        }
    }
}
 

@Override
protected DbRecordValue parseDbRecordAsObject( DbColumn dbColumn, ResultSet res,
                                               int columnIndex ) throws IOException, SQLException {

    DbRecordValue recordValue = null;

    Object valueAsObject = res.getObject(columnIndex);
    if (valueAsObject == null) {
        // null object
        recordValue = new DbRecordValue(dbColumn, null);
    } else if (valueAsObject instanceof Blob) {
        // a blob, it is binary type, but we need to read in some special way as hex string
        StringBuilder stringValue = addBinDataAsHexAndCloseStream(new StringBuilder(),
                                                                  ((Blob) valueAsObject).getBinaryStream());
        recordValue = new DbRecordValue(dbColumn, stringValue.toString());
    } else {
        // it is not binary or it is a non-blob binary
        OracleColumnDescription columnDescription = new OracleColumnDescription(dbColumn.getColumnName(),
                                                                                dbColumn.getColumnType());
        if (columnDescription.isTypeBinary()) {
            recordValue = new DbRecordValue(dbColumn, res.getString(columnIndex));
        } else {
            recordValue = new DbRecordValue(dbColumn, valueAsObject);
        }
    }

    return recordValue;
}
 

private void readFields(ResultSet rs, List fields) throws SQLException {
  Iterator iterator = fields.iterator();
  while (iterator.hasNext()) {
    rs.getObject((String) iterator.next());
  }
}
 

public Object get(final ResultSet target) throws SQLException {
	return target.getObject(column);
}
 

/**
  * Checks if all combinations of the 'uniqueFields' are unique in the
  * table 'uniqueTable'.
  * Writes feedback to 'out', and returns true iff all combinations of
  * 'uniqueTable.uniqueFields[0]', ..., 'uniqueTable.uniqueFields[n]' are
  * unique (i.e. no combination of the fields occurring twice).
  */
private boolean uniquenessCheck(String uniqueFields[], String uniqueTable)
        throws IOException, SQLException {
  boolean allUnique = true;

  String fieldsString = "";
  String notNullFieldsString = "not (";
  if (uniqueFields.length > 0) {
    fieldsString += uniqueFields[0];
    notNullFieldsString += uniqueFields[0] + " is null";

    for (int i = 1; i < uniqueFields.length; i++) {
      fieldsString += ", " + uniqueFields[i];
      notNullFieldsString += " or " + uniqueFields[i]
              + " is null";
    }
    notNullFieldsString += ")";
  } else
    throw new SQLException();

  out.write("\n\nChecking for duplicate values for the combination "
          + fieldsString + " ...\n");

  // Find all subject indicators.
  String query = "select " + fieldsString + ", count("
          + uniqueFields[0] + ") as count from " + uniqueTable
          + " where " + notNullFieldsString + " group by " + fieldsString;

  out.write(query);
  out.flush();

  ResultSet resultSet = conn.createStatement().executeQuery(query);

  if (resultSet.next()) {
    do {
      int count = resultSet.getInt("count");

      if (count > 1) {
        Object currentValue = resultSet.getObject(uniqueFields[0]);

        String rowFeedback = "The value combination '"
                + currentValue.toString();

        for (int i = 1; i < uniqueFields.length; i++) {
          currentValue = resultSet.getObject(uniqueFields[i]);
          rowFeedback += "', '" + (currentValue == null
                  ? "null"
                  : currentValue.toString());
        }

        rowFeedback += "' occurred " + count + " times.\n";
        out.write(rowFeedback);

        allUnique = false;
      }
    } while (resultSet.next());
  }

  if (allUnique)
    out.write("... There are no duplicate values for " + uniqueTable + "."
            + uniqueFields[0] + ".\n");
  else
    out.write("\n...There are duplicate values for " + uniqueTable + "."
            + uniqueFields[0] + ". Details are given above.\n");

  return allUnique;
}
 

/**
 * Compares the first column of a result set against a String-valued
 * reference set, disregarding order entirely.
 *
 * @param resultSet Result set
 * @param refSet    Expected results
 * @throws Exception .
 */
public static void compareResultSet(
    ResultSet resultSet,
    Set<String> refSet) throws Exception {
  Set<String> actualSet = new HashSet<>();
  final int columnType = resultSet.getMetaData().getColumnType(1);
  final ColumnMetaData.Rep rep = rep(columnType);
  while (resultSet.next()) {
    final String s = resultSet.getString(1);
    final String s0 = s == null ? "0" : s;
    final boolean wasNull0 = resultSet.wasNull();
    actualSet.add(s);
    switch (rep) {
    case BOOLEAN:
      assertThat(resultSet.getBoolean(1), equalTo(Boolean.valueOf(s)));
      break;
    case BYTE:
    case SHORT:
    case INTEGER:
    case LONG:
      long l;
      try {
        l = Long.parseLong(s0);
      } catch (NumberFormatException e) {
        // Large integers come out in scientific format, say "5E+06"
        l = (long) Double.parseDouble(s0);
      }
      assertThat(resultSet.getByte(1), equalTo((byte) l));
      assertThat(resultSet.getShort(1), equalTo((short) l));
      assertThat(resultSet.getInt(1), equalTo((int) l));
      assertThat(resultSet.getLong(1), equalTo(l));
      break;
    case FLOAT:
    case DOUBLE:
      final double d = Double.parseDouble(s0);
      assertThat(resultSet.getFloat(1), equalTo((float) d));
      assertThat(resultSet.getDouble(1), equalTo(d));
      break;
    }
    final boolean wasNull1 = resultSet.wasNull();
    final Object object = resultSet.getObject(1);
    final boolean wasNull2 = resultSet.wasNull();
    assertThat(object == null, equalTo(wasNull0));
    assertThat(wasNull1, equalTo(wasNull0));
    assertThat(wasNull2, equalTo(wasNull0));
  }
  resultSet.close();
  assertEquals(refSet, actualSet);
}
 

public void setDatabaseField(Object persistent, PersistentField field,
                             ResultSet rs) throws SQLException {

    if (rs.getObject(field.getDatabaseName()) == null) {
        setField(persistent, field.getName(), null);
        return;
    }

    switch (field.getType()) {
        case PersistentField.ATTRIBUTE:
        case PersistentField.ID:
        case PersistentField.QUALIFIER:
        case PersistentField.ELEMENT:
        case PersistentField.LONG:
            setField(persistent, field.getName(),
                    rs.getLong(field.getDatabaseName()));
            break;
        case PersistentField.DATE:
            Timestamp timestamp = rs.getTimestamp(field.getDatabaseName());
            setField(persistent, field.getName(), timestamp);
            break;
        case PersistentField.DOUBLE:
            setField(persistent, field.getName(),
                    rs.getDouble(field.getDatabaseName()));
            break;
        case PersistentField.TEXT:
            setField(persistent, field.getName(),
                    rs.getString(field.getDatabaseName()));
            break;
        case PersistentField.BINARY:
            setField(persistent, field.getName(),
                    rs.getBytes(field.getDatabaseName()));
            break;
        case PersistentField.INTEGER:
            setField(persistent, field.getName(),
                    rs.getInt(field.getDatabaseName()));
            break;
        case PersistentField.VALUE_BRANCH_ID:
            setField(persistent, field.getName(),
                    rs.getLong(field.getDatabaseName()));
            break;

        default:
            throw new RuntimeException("Unknow field type to set.");
    }

}
 

public void testExecuteQueryWithDataAwareProcedureCallWithGlobalEscape()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS_WITH_GLOBAL(?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  cs.setInt(1, 2);
  
  cs.execute();
  
 // clientSQLExecute(1,"INSERT INTO EMP.PARTITIONTESTTABLE VALUES (2, 2, '3') "); 
 // clientSQLExecute(1,"INSERT INTO EMP.PARTITIONTESTTABLE VALUES (3, 3, '3') ");
 // clientSQLExecute(1,"INSERT INTO EMP.PARTITIONTESTTABLE VALUES (4, 4, '3') ");
 // clientSQLExecute(1,"INSERT INTO EMP.PARTITIONTESTTABLE VALUES (5, 5, '3') ");
 // clientSQLExecute(1,"INSERT INTO EMP.PARTITIONTESTTABLE VALUES (2, 2, '2') ");
 // clientSQLExecute(1,"INSERT INTO EMP.PARTITIONTESTTABLE VALUES (2, 2, '4') ");
  Set<String> expectedResults=new HashSet<String>();
  expectedResults.add("223");   
  expectedResults.add("333");
  expectedResults.add("443");
  expectedResults.add("553");
  expectedResults.add("222");
  expectedResults.add("224");
 
       
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    Set<String> results=new HashSet<String>();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
        
      }
      if(expectedResults.contains(row)) {
         if(results.contains(row)) {
           fail("the duplicated row"+row);
         }
         else {
           results.add(row);
         }
      }
      else {
        fail("the expected results do not contain this row "+row);
      }
      System.out.println("XXX testExecuteQueryWithDataAwareProcedureCall the row="+row+ "resultset index="+rsIndex+ " rowIndex="+rowIndex);
   }
   if(rsIndex<2) {
     if(results.size()!=expectedResults.size()) {
       fail("The number of rows in the results is supposed to be "+expectedResults.size()+ " not "+results.size());
     }
       
   }
   else {
     if(results.size()!=0) {
       fail("The number of rows in the results is supposed to be "+0+ " not "+results.size());
     }
   }
   
  } while (cs.getMoreResults());
  
  if(rsIndex!=3) {
    fail("the number of result sets to be excpected is 4 not" + (rsIndex+1));
  }
  
}