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

The following examples show how to use java.sql.PreparedStatement#setObject() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may want to check out the right sidebar which shows the related API usage.
Example 1
/**
 * 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;
}
 
Example 2
Source Project: gemfirexd-oss   File: TestProcedures.java    License: Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 3
Source Project: Crawer   File: Db.java    License: MIT License 6 votes vote down vote up
/**
 * 
 * @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;
}
 
Example 4
@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();
}
 
Example 5
Source Project: phoenix   File: PhoenixIndexDBWritable.java    License: Apache License 2.0 5 votes vote down vote up
@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());
       }
   }
   
}
 
Example 6
/**
 * 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;
}
 
Example 7
Source Project: mmpt   File: JSONTypeHandler.java    License: MIT License 5 votes vote down vote up
@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);
}
 
Example 8
@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;
}
 
Example 9
@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);
    }
}
 
Example 10
/**
 * 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();
}
 
Example 11
Source Project: tddl5   File: ExchangeInTranslation.java    License: Apache License 2.0 5 votes vote down vote up
@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");
    }
}
 
Example 12
@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());
    }
}
 
Example 13
/**
 * 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();
    }
}
 
Example 14
/**
 * 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");
    }
  }
}
 
Example 15
Source Project: incubator-pinot   File: SqlQueryBuilder.java    License: Apache License 2.0 4 votes vote down vote up
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;
}
 
Example 16
/**
 * 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();
}
 
Example 17
Source Project: gemfirexd-oss   File: PlatformImplBase.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * {@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);
        }
    }
}
 
Example 18
/**
 * 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]);
  }
}
 
Example 19
@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();
    
    
    
   
}
 
Example 20
Source Project: lams   File: DB2v7Delegate.java    License: GNU General Public License v2.0 2 votes vote down vote up
/**
 * 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);
}