@Override public int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException { return batchUpdate(sql, batchArgs, new int[0]); }
return batchUpdate( sql, new BatchPreparedStatementSetter() {
/** * Delegate method to execute the batch insert. */ private int[] executeBatchInternal(final List<List<Object>> batchValues) { if (logger.isDebugEnabled()) { logger.debug("Executing statement " + getInsertString() + " with batch of size: " + batchValues.size()); } return getJdbcTemplate().batchUpdate(getInsertString(), new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { setParameterValues(ps, batchValues.get(i), getInsertTypes()); } @Override public int getBatchSize() { return batchValues.size(); } }); }
public void addUsers(final Integer startIndex, final Integer size) throws Exception { jdbcTemplate.batchUpdate(INSERT_BARE_BONE_USER, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { String userId = "user-"+(i+startIndex); int pos = 1; ps.setString(pos++, userId); ps.setString(pos++, userId); ps.setString(pos++, userId); ps.setString(pos++, userId + "@test.com"); ps.setString(pos++, IdentityZoneHolder.get().getId()); } @Override public int getBatchSize() { return size; } }); }
/** * Trigger any queued update operations to be added as a final batch. * @return an array of the number of rows affected by each statement */ public int[] flush() { if (this.parameterQueue.isEmpty()) { return new int[0]; } int[] rowsAffected = getJdbcTemplate().batchUpdate( resolveSql(), new BatchPreparedStatementSetter() { @Override public int getBatchSize() { return parameterQueue.size(); } @Override public void setValues(PreparedStatement ps, int index) throws SQLException { Object[] params = parameterQueue.removeFirst(); newPreparedStatementSetter(params).setValues(ps); } }); for (int rowCount : rowsAffected) { checkRowsAffected(rowCount); if (this.trackRowsAffected) { this.rowsAffected.add(rowCount); } } return rowsAffected; }
public void addApprovals(final Integer minUserId, final Integer maxUserId, final Integer countPerUser) throws Exception { jdbcTemplate.batchUpdate("insert into authz_approvals (user_id, client_id, scope, expiresat, status, lastmodifiedat) values (?,?,?,?,?,?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { int index = (i+minUserId) / countPerUser; String userId = "user-"+(minUserId+index); int pos = 1; ps.setString(pos++, userId); ps.setString(pos++, "c"+random.nextInt(200)); ps.setString(pos++, "uaa.user."+i); ps.setTimestamp(pos++, new Timestamp(System.currentTimeMillis()+300000)); ps.setString(pos++, "APPROVED"); ps.setTimestamp(pos++, new Timestamp(System.currentTimeMillis())); } @Override public int getBatchSize() { return (maxUserId - minUserId) * countPerUser; } }); }
@Test public void testBatchUpdateWithEmptyList() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"; JdbcTemplate template = new JdbcTemplate(this.dataSource, false); int[] actualRowsAffected = template.batchUpdate(sql, Collections.emptyList()); assertTrue("executed 0 updates", actualRowsAffected.length == 0); }
@Test public void testBatchUpdate() throws Exception { final String[] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1", "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2"}; given(this.statement.executeBatch()).willReturn(new int[] {1, 1}); mockDatabaseMetaData(true); given(this.connection.createStatement()).willReturn(this.statement); JdbcTemplate template = new JdbcTemplate(this.dataSource, false); int[] actualRowsAffected = template.batchUpdate(sql); assertTrue("executed 2 updates", actualRowsAffected.length == 2); verify(this.statement).addBatch(sql[0]); verify(this.statement).addBatch(sql[1]); verify(this.statement).close(); verify(this.connection, atLeastOnce()).close(); }
@Test public void testBatchUpdateWithCollectionOfObjects() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"; final List<Integer> ids = Arrays.asList(100, 200, 300); final int[] rowsAffected1 = new int[] {1, 2}; final int[] rowsAffected2 = new int[] {3}; given(this.preparedStatement.executeBatch()).willReturn(rowsAffected1, rowsAffected2); mockDatabaseMetaData(true); ParameterizedPreparedStatementSetter<Integer> setter = (ps, argument) -> ps.setInt(1, argument.intValue()); JdbcTemplate template = new JdbcTemplate(this.dataSource, false); int[][] actualRowsAffected = template.batchUpdate(sql, ids, 2, setter); assertEquals("executed 2 updates", 2, actualRowsAffected[0].length); assertEquals(rowsAffected1[0], actualRowsAffected[0][0]); assertEquals(rowsAffected1[1], actualRowsAffected[0][1]); assertEquals(rowsAffected2[0], actualRowsAffected[1][0]); verify(this.preparedStatement, times(3)).addBatch(); verify(this.preparedStatement).setInt(1, ids.get(0)); verify(this.preparedStatement).setInt(1, ids.get(1)); verify(this.preparedStatement).setInt(1, ids.get(2)); verify(this.preparedStatement).close(); verify(this.connection, atLeastOnce()).close(); }
@Test public void testBatchUpdateWithListOfObjectArrays() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"; final List<Object[]> ids = new ArrayList<>(2); ids.add(new Object[] {100}); ids.add(new Object[] {200}); final int[] rowsAffected = new int[] {1, 2}; given(this.preparedStatement.executeBatch()).willReturn(rowsAffected); mockDatabaseMetaData(true); JdbcTemplate template = new JdbcTemplate(this.dataSource, false); int[] actualRowsAffected = template.batchUpdate(sql, ids); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]); assertEquals(rowsAffected[1], actualRowsAffected[1]); verify(this.preparedStatement, times(2)).addBatch(); verify(this.preparedStatement).setObject(1, 100); verify(this.preparedStatement).setObject(1, 200); verify(this.preparedStatement).close(); verify(this.connection, atLeastOnce()).close(); }
@Test public void testBatchUpdateWithBatchFailure() throws Exception { final String[] sql = {"A", "B", "C", "D"}; given(this.statement.executeBatch()).willThrow( new BatchUpdateException(new int[] {1, Statement.EXECUTE_FAILED, 1, Statement.EXECUTE_FAILED})); mockDatabaseMetaData(true); given(this.connection.createStatement()).willReturn(this.statement); JdbcTemplate template = new JdbcTemplate(this.dataSource, false); try { template.batchUpdate(sql); } catch (UncategorizedSQLException ex) { assertThat(ex.getSql(), equalTo("B; D")); } }
@Test public void testBatchUpdateWithListOfObjectArraysPlusTypeInfo() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"; final List<Object[]> ids = new ArrayList<>(2); ids.add(new Object[] {100}); ids.add(new Object[] {200}); final int[] sqlTypes = new int[] {Types.NUMERIC}; final int[] rowsAffected = new int[] {1, 2}; given(this.preparedStatement.executeBatch()).willReturn(rowsAffected); mockDatabaseMetaData(true); this.template = new JdbcTemplate(this.dataSource, false); int[] actualRowsAffected = this.template.batchUpdate(sql, ids, sqlTypes); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]); assertEquals(rowsAffected[1], actualRowsAffected[1]); verify(this.preparedStatement, times(2)).addBatch(); verify(this.preparedStatement).setObject(1, 100, sqlTypes[0]); verify(this.preparedStatement).setObject(1, 200, sqlTypes[0]); verify(this.preparedStatement).close(); verify(this.connection, atLeastOnce()).close(); }
@Test public void testBatchUpdateWithNoBatchSupport() throws Exception { final String[] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1", "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2"}; given(this.statement.execute(sql[0])).willReturn(false); given(this.statement.getUpdateCount()).willReturn(1, 1); given(this.statement.execute(sql[1])).willReturn(false); mockDatabaseMetaData(false); given(this.connection.createStatement()).willReturn(this.statement); JdbcTemplate template = new JdbcTemplate(this.dataSource, false); int[] actualRowsAffected = template.batchUpdate(sql); assertTrue("executed 2 updates", actualRowsAffected.length == 2); verify(this.statement, never()).addBatch(anyString()); verify(this.statement).close(); verify(this.connection, atLeastOnce()).close(); }
@Test public void testBatchUpdateWithPreparedStatementAndNoBatchSupport() 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}; given(this.preparedStatement.executeUpdate()).willReturn(rowsAffected[0], rowsAffected[1]); BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setInt(1, ids[i]); } @Override public int getBatchSize() { return ids.length; } }; int[] actualRowsAffected = this.template.batchUpdate(sql, setter); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]); assertEquals(rowsAffected[1], actualRowsAffected[1]); verify(this.preparedStatement, never()).addBatch(); verify(this.preparedStatement).setInt(1, ids[0]); verify(this.preparedStatement).setInt(1, ids[1]); verify(this.preparedStatement).close(); verify(this.connection).close(); }
@Test public void testBatchUpdateWithNoBatchSupportAndSelect() throws Exception { final String[] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1", "SELECT * FROM NOSUCHTABLE"}; given(this.statement.execute(sql[0])).willReturn(false); given(this.statement.getUpdateCount()).willReturn(1); given(this.statement.execute(sql[1])).willReturn(true); mockDatabaseMetaData(false); given(this.connection.createStatement()).willReturn(this.statement); JdbcTemplate template = new JdbcTemplate(this.dataSource, false); this.thrown.expect(InvalidDataAccessApiUsageException.class); try { template.batchUpdate(sql); } finally { verify(this.statement, never()).addBatch(anyString()); verify(this.statement).close(); verify(this.connection, atLeastOnce()).close(); } }
@Test public void testBatchUpdateWithPreparedStatement() 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}; given(this.preparedStatement.executeBatch()).willReturn(rowsAffected); mockDatabaseMetaData(true); BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setInt(1, ids[i]); } @Override public int getBatchSize() { return ids.length; } }; JdbcTemplate template = new JdbcTemplate(this.dataSource, false); int[] actualRowsAffected = template.batchUpdate(sql, setter); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]); assertEquals(rowsAffected[1], actualRowsAffected[1]); verify(this.preparedStatement, times(2)).addBatch(); verify(this.preparedStatement).setInt(1, ids[0]); verify(this.preparedStatement).setInt(1, ids[1]); verify(this.preparedStatement).close(); verify(this.connection, atLeastOnce()).close(); }
int[] actualRowsAffected = template.batchUpdate(sql, setter); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]);
int[] actualRowsAffected = template.batchUpdate(sql, setter); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]);
@Test public void testBatchUpdateFails() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"; final int[] ids = new int[] {100, 200}; SQLException sqlException = new SQLException(); given(this.preparedStatement.executeBatch()).willThrow(sqlException); mockDatabaseMetaData(true); BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setInt(1, ids[i]); } @Override public int getBatchSize() { return ids.length; } }; this.thrown.expect(DataAccessException.class); this.thrown.expect(exceptionCause(sameInstance(sqlException))); try { this.template.batchUpdate(sql, setter); } finally { verify(this.preparedStatement, times(2)).addBatch(); verify(this.preparedStatement).setInt(1, ids[0]); verify(this.preparedStatement).setInt(1, ids[1]); verify(this.preparedStatement).close(); verify(this.connection, atLeastOnce()).close(); } }
int[] actualRowsAffected = template.batchUpdate(sql, setter); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]);