List of usage examples for java.sql PreparedStatement setObject
default void setObject(int parameterIndex, Object x, SQLType targetSqlType) throws SQLException
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); } }