Java Code Examples for java.sql.PreparedStatement#setObject()

/**
 * Helper method for *SetObject* tests.
 * Insert data into the given PreparedStatement, or any of its subclasses, with the following structure:
 * 1 - `id` INT
 * 2 - `ot1` VARCHAR
 * 3 - `ot2` BLOB
 * 4 - `odt1` VARCHAR
 * 5 - `odt2` BLOB
 * 
 * @param pstmt
 * @return the row count of inserted records.
 * @throws Exception
 */
private int insertTestDataOffsetDTTypes(PreparedStatement pstmt) throws Exception {
    pstmt.setInt(1, 1);
    pstmt.setObject(2, testOffsetTime, JDBCType.VARCHAR);
    pstmt.setObject(3, testOffsetTime);
    pstmt.setObject(4, testOffsetDateTime, JDBCType.VARCHAR);
    pstmt.setObject(5, testOffsetDateTime);
    assertEquals(1, pstmt.executeUpdate());

    if (pstmt instanceof CallableStatement) {
        CallableStatement cstmt = (CallableStatement) pstmt;

        cstmt.setInt("id", 2);
        cstmt.setObject("ot1", testOffsetTime, JDBCType.VARCHAR);
        cstmt.setObject("ot2", testOffsetTime);
        cstmt.setObject("odt1", testOffsetDateTime, JDBCType.VARCHAR);
        cstmt.setObject("odt2", testOffsetDateTime);
        assertEquals(1, cstmt.executeUpdate());

        return 2;
    }

    return 1;
}
 

public static void paramTest(String type, Object param1, Object[] param2,
    Object[] param3, int[] out, ResultSet[] rs1) throws SQLException {
  Connection conn = DriverManager.getConnection("jdbc:default:connection");
  try {
    PreparedStatement pstmt = conn.prepareStatement(
      "values(cast(? as " + type + "))");
    if (param1 != null) {
      pstmt.setObject(1, param1);
    }
    else {
      pstmt.setNull(1, pstmt.getMetaData().getColumnType(1));
    }
    rs1[0] = pstmt.executeQuery();
  } finally {
    conn.close();
  }
  //java.lang.reflect.Array.set(param2, 0, param1);
  param2[0] = param1;
  out[0] = 5;
}
 

/**
 * 
 * @param sql
 * @param params
 * @return
 */
public int ExecuteNonQuery(String sql,Object[] params){
	int reNum=-1;
	Connection conn=null;
	PreparedStatement ps=null;
	try{
		ps=conn.prepareStatement(sql);
		for(int i=0;i<params.length;i++){
			ps.setObject(i+1, params[i]);
		}
		reNum=ps.executeUpdate();
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		this.close(ps, null, conn);
	}
	
	return reNum;
}
 

@Test
public void test() {
    try {
        jdbcTemplate.update("delete from complextbl_0000");
        jdbcTemplate.update("delete from complextbl_0001");
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        PreparedStatement ps1 = connection.prepareStatement("insert into complextbl_0000 (id,name) values(?,?)");
        ps1.setObject(1, 10);
        ps1.setObject(2, "TEST");
        ps1.execute();

        System.out.println("stop run");
        // this time exchange and wait untill exchange complete
        connection.rollback();

        PreparedStatement ps2 = connection.prepareStatement("insert into complextbl_0001 (id,name) values(?,?)");
        ps2.setObject(1, 10);
        ps2.setObject(2, "TEST");
        ps2.execute();
        connection.commit();
    } catch (SQLException e) {
        Assert.fail(ExceptionUtils.getFullStackTrace(e));
    } finally {
        jdbcTemplate.update("delete from complextbl_0000");
        jdbcTemplate.update("delete from complextbl_0001");
    }
}
 

/**
 * Test BigDecimal with scale as parameter.
 */
public void testBigDecimalSetObjectWithScale() throws Exception
{
    getConnection().setAutoCommit(false);
    String sql = "CREATE TABLE numtab (num NUMERIC(10,6))";
    Statement stmt = createStatement();
    assertUpdateCount(stmt, 0, sql);
    stmt.close();
    commit();

    // make a big decimal from string
    BigDecimal bdFromString = new BigDecimal("2.33333333");

    sql = "INSERT INTO  numtab  VALUES(?)";
    PreparedStatement ps =  prepareStatement(sql);
    // setObject using the big decimal value
    int scale = 2;
    ps.setObject(1, bdFromString, java.sql.Types.DECIMAL, scale);
    assertUpdateCount(ps, 1);
    ps.close();
    // check the value
    sql = "SELECT num FROM numtab";
    stmt = createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    rs.next();
    // Check that the correct scale was set
    BigDecimal expected
        = bdFromString.setScale(scale, BigDecimal.ROUND_DOWN);
    BigDecimal actual = (BigDecimal)rs.getObject(1);
    assertEquals("Actual value: " + actual
                 + "does not match expected value: " + expected,
                 expected.compareTo(actual), 0);
    rs.close();
    stmt.close();

    commit();
}
 

/**
 * Apply a PreparedStatement repeatedly with the set of parameter
 * vectors. (Any null params are assumed to be of type CHAR).
 * @param action the ps to execute
 * @param table an array of parameter vectors to use for each
 * execution of the PreparedStatement
 */
public static void apply(PreparedStatement action, Object[][] table)
    throws SQLException {
    for (int row = 0; row < table.length; ++row) {
        for (int col = 0; col < table[row].length; ++col) {
            Object obj = table[row][col];
            if (obj == null) {
                action.setNull(col+1,java.sql.Types.CHAR);
                continue;
            }
            action.setObject(col+1, obj);
        }
        action.execute();
    }
}
 

@Test
public void assertClearParameters() throws SQLException {
    for (PreparedStatement each : preparedStatements) {
        Object obj = new Object();
        each.setObject(1, obj);
        each.setObject(2, obj, 0);
        each.setObject(3, null);
        each.setObject(4, null);
        each.setObject(5, obj, 0, 0);
        assertThat(((ShardingSpherePreparedStatement) each).getParameters().size(), is(5));
        each.clearParameters();
        assertTrue(((ShardingSpherePreparedStatement) each).getParameters().isEmpty());
    }
}
 

@Override
protected boolean insertBatch(PreparedStatement pstmt)
    throws SQLException {
  int i = 0;
  pstmt.clearParameters();
  for (JdbcEntryData pendingEntry : PostgresBufferedInserter.this.pendingInserts) {
    for (JdbcEntryDatum datum : pendingEntry) {
      pstmt.setObject(++i, datum.getVal());
    }
  }
  log.debug("Executing SQL " + pstmt);
  return pstmt.execute();
}
 

@Override
protected void set(PreparedStatement st, Range range, int index, SharedSessionContractImplementor session) throws SQLException {

    if (range == null) {
        st.setNull(index, Types.OTHER);
    } else {
        PGobject object = new PGobject();
        object.setType(determineRangeType(range));
        object.setValue(range.asString());

        st.setObject(index, object);
    }
}
 

@Override
public int setSubqueryParameters(PreparedStatement statement, ArrayBasedQueryParameters parameters, int startIndex)
        throws SQLException {
    if (parameters.getSubqueryParameterValues().length > 0) {
        for (Object value : parameters.getSubqueryParameterValues()) {
            statement.setObject(startIndex, value);
            startIndex++;
        }
    }
    return parameters.getSubqueryParameterValues().length;
}
 

@Override
public void setNonNullParameter(PreparedStatement ps, int i,
        String parameter, JdbcType jdbcType) throws SQLException {
    PGobject jsonObject = new PGobject();
    jsonObject.setType("json");
    jsonObject.setValue(parameter);
    ps.setObject(i, jsonObject);
}
 

/**
 * Fills in the given fields in the queue into the given prepared statement
 * @param ps
 * @param searchValues
 * @param firstField
 * @return
 * @throws SQLException
 */
private static PreparedStatement fillInWhereClause(PreparedStatement ps, Queue<Map.Entry<String, FieldType>> searchValues, int firstField) throws SQLException{
	//insert all values in the prepared statement in the order
    //in which the values had been put in the queue
    for (Map.Entry<String, FieldType> entry : searchValues){
        switch(entry.getValue()) {
            case STRING:
                ps.setString(firstField, entry.getKey());
                break;
            case DATE:
                ps.setTimestamp(firstField, new Timestamp(Long.parseLong(entry.getKey())));
                break;
            case LONG:
                ps.setLong(firstField, Long.parseLong(entry.getKey()));
                break;
            case DOUBLE:
                ps.setDouble(firstField, Double.parseDouble(entry.getKey()));
                break;
            case UUID:
                ps.setObject(firstField, UUID.fromString(entry.getKey()));
                break;
            case BOOLEAN:
            	ps.setBoolean(firstField, Boolean.valueOf(entry.getKey()));
            	break;
        }
        firstField++;
    }
    return ps;
}
 

@Override
public void write(PreparedStatement statement) throws SQLException {
   Preconditions.checkNotNull(values);
   Preconditions.checkNotNull(columnMetadata);
   for(int i = 0 ; i < values.size() ; i++) {
       Object value = values.get(i);
       ColumnInfo columnInfo = columnMetadata.get(i);
       if(value == null) {
           statement.setNull(i + 1, columnInfo.getSqlType());               
       } else {
           statement.setObject(i + 1, value , columnInfo.getSqlType());
       }
   }
   
}
 

public PreparedStatement createUpdateStatementForIndexTable(Connection connection,
    AbstractIndexEntity entity) throws Exception {
  String tableName =
      entityMappingHolder.tableToEntityNameMap.inverse().get(entity.getClass().getSimpleName());
  LinkedHashMap<String, ColumnInfo> columnInfoMap =
      entityMappingHolder.columnInfoPerTable.get(tableName);

  StringBuilder sqlBuilder = new StringBuilder("UPDATE " + tableName + " SET ");
  String delim = "";
  LinkedHashMap<String, Object> parameterMap = new LinkedHashMap<>();
  for (ColumnInfo columnInfo : columnInfoMap.values()) {
    String columnNameInDB = columnInfo.columnNameInDB;
    if (!columnNameInDB.equalsIgnoreCase(BASE_ID)
        && !AUTO_UPDATE_COLUMN_SET.contains(columnNameInDB)) {
      Object val = columnInfo.field.get(entity);
      if (val != null) {
        if (Enum.class.isAssignableFrom(val.getClass())) {
          val = val.toString();
        }
        sqlBuilder.append(delim);
        sqlBuilder.append(columnNameInDB);
        sqlBuilder.append("=");
        sqlBuilder.append("?");
        delim = ",";
        parameterMap.put(columnNameInDB, val);
      }
    }
  }
  //ADD WHERE CLAUSE TO CHECK FOR ENTITY ID
  sqlBuilder.append(" WHERE base_id=?");
  parameterMap.put(BASE_ID, entity.getBaseId());
  LOG.debug("Update statement:{}" , sqlBuilder);
  int parameterIndex = 1;
  PreparedStatement prepareStatement = connection.prepareStatement(sqlBuilder.toString());
  for (Entry<String, Object> paramEntry : parameterMap.entrySet()) {
    String dbFieldName = paramEntry.getKey();
    ColumnInfo info = columnInfoMap.get(dbFieldName);
    LOG.debug("Setting value: {} for {}", paramEntry.getValue(), dbFieldName);
    prepareStatement.setObject(parameterIndex++, paramEntry.getValue(), info.sqlType);
  }
  return prepareStatement;
}
 

/**
 * SNOW-88426: skip bind parameter index check if prepare fails and defer the error checks to execute
 */
@Test
public void testSelectWithBinding() throws Throwable
{
  try (Connection connection = init())
  {
    connection.createStatement().execute("create or replace table TESTNULL(created_time timestamp_ntz, mid int)");
    PreparedStatement ps;
    ResultSet rs;
    try
    {
      // skip bind parameter index check if prepare fails and defer the error checks to execute
      ps = connection.prepareStatement(
          "SELECT 1 FROM TESTNULL WHERE CREATED_TIME = TO_TIMESTAMP(?, 3) and MID = ?"
      );
      ps.setObject(1, 0);
      ps.setObject(2, null);
      ps.setObject(1000, null); // this won't raise an exception.
      rs = ps.executeQuery();
      assertFalse(rs.next());
      rs.close();
      ps.close();

      // describe is success and do the index range check
      ps = connection.prepareStatement(
          "SELECT 1 FROM TESTNULL WHERE CREATED_TIME = TO_TIMESTAMP(?::NUMBER, 3) and MID = ?"
      );
      ps.setObject(1, 0);
      ps.setObject(2, null);
      ps.setObject(1000, null); // this won't raise an exception.

      rs = ps.executeQuery();
      assertFalse(rs.next());
      rs.close();
      ps.close();

    }
    finally
    {
      connection.createStatement().execute("drop table if exists TESTNULL");
    }
  }
}
 

/**
 * Test DistinctGroupedAggregateResultSet
 */
public void testDistinctGroupedAggregateResultSet() throws Exception {
  // [sjigyasu] The test is invalid for GFXD because it tries to
  // do a drop while a transaction is progress.
  if (isTransactional) {
    return;
  }

    createTestTable("emp", ES+","+DNO+")", "emp_data");
    PreparedStatement del =
        prepareStatement("delete from emp where mgrname = ?");

    // DistinctGroupedAggregateResultSet,
    // ProjectRestrictResultSet,ScrollInsensitiveResultSet,
    // TableScanResultSet
    PreparedStatement tst = prepareStatement
        ("select count(distinct mgrname) nummgrs, dno "+
         "from emp group by dno having dno <> ?");

    testDistinctGroupedAggregateResultSet(tst, del);

    Statement s = createStatement();
    s.executeUpdate("drop table emp");
    createTestTable("emp", ES+","+DNO+")", "emp_data");

    // re-execute on the new table
    testDistinctGroupedAggregateResultSet(tst, del);

    // restore data
    s.executeUpdate("delete from emp");
    s.executeUpdate(insertFrom("emp", "emp_data"));
    commit();

    // make sure another transaction has exclusive locks
    Statement s2 = c2.createStatement();
    if (!isAvoidGFXDBugs()) {  // unsupported isolation level
      s2.executeUpdate("update emp set c0 = c0");

      // re-execute with different isolation level (will get lock timeout
      // with other isolation levels)
      getConnection().
          setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
      tst.setObject(1, dept[0][1]);
      JDBC.assertFullResultSet(tst.executeQuery(),
                               new Object[][] {{i1, k51}, {i2, k52}},
                               false);
    }
    tst.close();
    del.close();
    s.close();
    s2.close();
}
 

/**
 * {@inheritDoc}
 */
public Iterator query(Database model, String sql, Collection parameters, Table[] queryHints) throws DatabaseOperationException
{
    Connection        connection = borrowConnection();
    PreparedStatement statement  = null;
    ResultSet         resultSet  = null;
    Iterator          answer     = null;

    try
    {
        statement = connection.prepareStatement(sql);

        int paramIdx = 1;

        for (Iterator iter = parameters.iterator(); iter.hasNext(); paramIdx++)
        {
            Object arg = iter.next();

            if (arg instanceof BigDecimal)
            {
                // to avoid scale problems because setObject assumes a scale of 0
                statement.setBigDecimal(paramIdx, (BigDecimal)arg);
            }
            else
            {
                statement.setObject(paramIdx, arg);
            }
        }
        resultSet = statement.executeQuery();
        answer    = createResultSetIterator(model, resultSet, queryHints);
        return answer;
    }
    catch (SQLException ex)
    {
        throw new DatabaseOperationException("Error while performing a query", ex);
    }
    finally
    {
        // if any exceptions are thrown, close things down
        // otherwise we're leaving it open for the iterator
        if (answer == null)
        {
            closeStatement(statement);
            returnConnection(connection);
        }
    }
}
 

/**
 * QueryExecutorStoredProc can be executed with <I>ON ALL/ON TABLE</I>
 * to execute a SQL SELECT QUERY.
 *
 * We intentionally catch throwable for distributed query execution.
 *   
 * @param inQuery SQL SELECT Query to be executed
 * @param paramValueList List of values for bind parameters (i.e. ? marks)
 * @param errorStateValue Output parameter of error status
 * @param resultSet Output resultset
 * @param pCtx ProcedureExecutionContext internally passed by GemFireXD driver 
 */
 @SuppressWarnings("PMD.AvoidCatchingThrowable")
public static void executeSelect(
  String inQuery,
  @SuppressWarnings("rawtypes") List paramValueList,
  int[] errorStateValue,
  ResultSet[] resultSet,
  ProcedureExecutionContext pCtx)
throws SQLException {

  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine(
      "QueryExecutorStoredProc-executeSelect entering" +
      " inQuery=" + inQuery +
      " paramValueList=" + paramValueList +
      " errorStateValue=" + LogUtils.getErrorStateValueArrayStr(errorStateValue) +
      " resultSet=" + LogUtils.getResultSetArrayStr(resultSet, 20));
  }
  Connection conn = pCtx.getConnection();
  PreparedStatement pStmt;
  try {
    pStmt = conn.prepareStatement(inQuery);
    int columnPosition = 1;
    for (Object paramValue : paramValueList) {
      pStmt.setObject(columnPosition, paramValue);
      columnPosition++;
    }
    resultSet[0] = pStmt.executeQuery();

  } catch (Throwable e) {
    errorStateValue[0] = 1;
    if (Log.getLogWriter().fineEnabled()) {
      Log.getLogWriter().fine(
        "QueryExecutorStoredProc-executeSelect" +
        " ERROR=" + e);
    }
  }
  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine(
      "QueryExecutorStoredProc-executeSelect exiting" +
      " errorStateValue[0]=" + errorStateValue[0]);
  }
}
 

@Test
public void testIssue102() throws Exception
{
    // null int or long should be... null !
	System.out.println();
    System.out.println("Test Issue #102");
    System.out.println("--------------");
    
	Statement stmt = con.createStatement();
    //java.util.Date now = new java.util.Date();
    
    
    // Create the target Column family with each basic data type available on Cassandra
            
    String createCF = "CREATE COLUMNFAMILY t102 (bigint_col bigint PRIMARY KEY, null_int_col int , null_bigint_col bigint, not_null_int_col int);";
    
    stmt.execute(createCF);
    stmt.close();
    con.close();

    // open it up again to see the new CF
    con = DriverManager.getConnection(String.format("jdbc:cassandra://%s:%d/%s?loadbalancing=TokenAwarePolicy(RoundRobinPolicy())",HOST,PORT,KEYSPACE));
    System.out.println("con.getMetaData().getDatabaseProductName() = " + con.getMetaData().getDatabaseProductName());
    System.out.println("con.getMetaData().getDriverName() = " + con.getMetaData().getDriverName());
    Statement statement = con.createStatement();
    /*
     * INSERT INTO test.t80(bigint_col , ascii_col , blob_col , boolean_col , decimal_col , double_col , 
    										float_col , inet_col , int_col , text_col , timestamp_col , uuid_col , 
    										timeuuid_col , varchar_col , varint_col )
    			values(1, 'test', TextAsBlob('test'), true, 5.1, 5.123142 , 
    										4.2134432 , '192.168.1.1', 1 , 'text' , '2015-01-01 10:10:10' , now() , 
    										now(), 'test' , 3435 );
     * 
     */
    
    
    String insert = "INSERT INTO t102(bigint_col,not_null_int_col) values(?,?);";
    
    
    
    
		
    PreparedStatement pstatement = con.prepareStatement(insert);
    
    
    pstatement.setObject(1, 1L); // bigint
    pstatement.setObject(2, 1); // int
            
    pstatement.execute();
            
    ResultSet result = statement.executeQuery("SELECT * FROM t102 where bigint_col=1;");
    
    AssertJUnit.assertTrue(result.next());
    AssertJUnit.assertEquals(1L, result.getLong("bigint_col"));
    System.out.println("null_bigint_col = " +  result.getLong("null_bigint_col"));
    AssertJUnit.assertEquals(0L,result.getLong("null_bigint_col"));
    AssertJUnit.assertTrue(result.wasNull());
    AssertJUnit.assertEquals(0,result.getInt("null_int_col"));
    AssertJUnit.assertTrue(result.wasNull());
    AssertJUnit.assertEquals(1,result.getInt("not_null_int_col"));
    AssertJUnit.assertFalse(result.wasNull());
    
    statement.close();
    pstatement.close();
    
    
    
   
}
 

/**
 * Sets the designated parameter to the byte array of the given
 * <code>ByteArrayOutputStream</code>.  Will set parameter value to null if the 
 * <code>ByteArrayOutputStream</code> is null.
 * Wraps <code>{@link PreparedStatement#setObject(int, java.lang.Object, int)}</code> rather than
 * <code>{@link PreparedStatement#setBytes(int, byte[])}</code> as required by the 
 * DB2 v7 database.
 */
@Override           
protected void setBytes(PreparedStatement ps, int index, ByteArrayOutputStream baos) throws SQLException {
    ps.setObject(index, ((baos == null) ? null : baos.toByteArray()), java.sql.Types.BLOB);
}