Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

Introduction

In this page you can find the example usage for java.sql PreparedStatement setObject.

Prototype

default void setObject(int parameterIndex, Object x, SQLType targetSqlType) throws SQLException 

Source Link

Document

Sets the value of the designated parameter with the given object.

Usage

From source file:org.springframework.jdbc.object.BatchSqlUpdateTests.java

private void doTestBatchUpdate(boolean flushThroughBatchSize) throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
    final int[] ids = new int[] { 100, 200 };
    final int[] rowsAffected = new int[] { 1, 2 };

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.getConnection();
    ctrlPreparedStatement.setReturnValue(mockConnection);
    mockPreparedStatement.setObject(1, new Integer(ids[0]), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();//  w  w w  .  j  av a  2  s.  c om
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setObject(1, new Integer(ids[1]), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeBatch();
    ctrlPreparedStatement.setReturnValue(rowsAffected);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

    mockConnection.prepareStatement(sql);
    ctrlConnection.setReturnValue(mockPreparedStatement);
    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 1);

    ctrlPreparedStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    BatchSqlUpdate update = new BatchSqlUpdate(mockDataSource, sql);
    update.declareParameter(new SqlParameter(Types.INTEGER));
    if (flushThroughBatchSize) {
        update.setBatchSize(2);
    }

    update.update(ids[0]);
    update.update(ids[1]);

    if (flushThroughBatchSize) {
        assertEquals(0, update.getQueueCount());
        assertEquals(2, update.getRowsAffected().length);
    } else {
        assertEquals(2, update.getQueueCount());
        assertEquals(0, update.getRowsAffected().length);
    }

    int[] actualRowsAffected = update.flush();
    assertEquals(0, update.getQueueCount());

    if (flushThroughBatchSize) {
        assertTrue("flush did not execute updates", actualRowsAffected.length == 0);
    } else {
        assertTrue("executed 2 updates", actualRowsAffected.length == 2);
        assertEquals(rowsAffected[0], actualRowsAffected[0]);
        assertEquals(rowsAffected[1], actualRowsAffected[1]);
    }

    actualRowsAffected = update.getRowsAffected();
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);
    assertEquals(rowsAffected[0], actualRowsAffected[0]);
    assertEquals(rowsAffected[1], actualRowsAffected[1]);

    update.reset();
    assertEquals(0, update.getRowsAffected().length);

    ctrlPreparedStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:com.mtgi.analytics.JdbcBehaviorEventPersisterImpl.java

protected void nullSafeSet(PreparedStatement stmt, int index, Object value, int sqlType) throws SQLException {
    if (value == null)
        stmt.setNull(index, sqlType);//from  w w  w.  ja v  a 2s. co m
    else
        stmt.setObject(index, value, sqlType);
}

From source file:com.squid.core.jdbc.formatter.DefaultJDBCDataFormatter.java

public void setData(PreparedStatement pstmt, int position, Object data, int colType, int size, int precision)
        throws SQLException {
    if (colType == Types.NVARCHAR) {
        pstmt.setString(position, (String) data);
    } else {/*w w  w  .  j av  a2s .c  om*/
        pstmt.setObject(position, data, colType);
    }
}

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 ww  .  j a  v  a2s.c om*/
        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.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);/*from   w w w  . j  ava2  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("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 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);// w  ww .  j av a2  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");
    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.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);// w  ww .  j a v a 2 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);
}

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

@Test
public void testObjectTimestamp() throws Exception {
    Statement stmt = con.createStatement();
    java.util.Date now = new java.util.Date();

    // Create the target Column family
    //String createCF = "CREATE COLUMNFAMILY t74 (id BIGINT PRIMARY KEY, col1 TIMESTAMP)";        
    String createCF = "CREATE COLUMNFAMILY t74 (id BIGINT PRIMARY KEY, col1 TIMESTAMP)";

    stmt.execute(createCF);//www. j a va  2s . c o  m
    stmt.close();
    con.close();

    // open it up again to see the new CF
    con = DriverManager
            .getConnection(String.format("jdbc:cassandra://%s:%d/%s?%s", HOST, PORT, KEYSPACE, OPTIONS));

    Statement statement = con.createStatement();

    String insert = "INSERT INTO t74 (id, col1) VALUES (?, ?);";

    PreparedStatement pstatement = con.prepareStatement(insert);
    pstatement.setLong(1, 1L);
    pstatement.setObject(2, new Timestamp(now.getTime()), Types.TIMESTAMP);
    pstatement.execute();

    ResultSet result = statement.executeQuery("SELECT * FROM t74;");

    assertTrue(result.next());
    assertEquals(1L, result.getLong(1));

    // try reading Timestamp directly
    Timestamp stamp = result.getTimestamp(2);
    assertEquals(now, stamp);

    // try reading Timestamp as an object
    stamp = result.getObject(2, Timestamp.class);
    assertEquals(now, stamp);

    System.out.println(resultToDisplay(result, 74, "current date"));

}

From source file:com.ineunet.knife.persist.Jdbc.java

/**
 * @param sql// w  w  w  . j a v a2  s .  c o  m
 * @param params
 *            e.g. batch size is 10. params.size=10. params.Object[].length
 *            = types.length.
 * @param types
 *            sql types. Must match params.Object[]. e.g. [1, "q"] match
 *            with [int, String]
 * @return an array of the number of rows affected by each statement
 */
public int[] batchUpdate(String sql, final List<Object[]> params, final int[] types) {
    return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            for (int j = 0; j < types.length; j++) {
                ps.setObject(j + 1, params.get(i)[j], types[j]);
            }
        }

        @Override
        public int getBatchSize() {
            return params.size();
        }
    });
}

From source file:jp.co.tis.gsp.tools.dba.dialect.SqlserverDialect.java

@Override
public void setObjectInStmt(PreparedStatement stmt, int parameterIndex, String value, int sqlType)
        throws SQLException {
    if (sqlType == UN_USABLE_TYPE) {
        stmt.setNull(parameterIndex, Types.NULL);
    } else if (StringUtil.isBlank(value) || "".equals(value)) {
        stmt.setNull(parameterIndex, sqlType);
    } else if (sqlType == Types.TIME) {
        stmt.setTimestamp(parameterIndex, Timestamp.valueOf("1970-01-01 " + value));
    } else {/*www.jav  a 2  s. c  o m*/
        stmt.setObject(parameterIndex, value, sqlType);
    }
}