/** * Add an exception statement either as a batch or execute immediately if * batch updates are not supported. */ void updateExceptionStatement(PreparedStatement exceptionStatement, String txt, short i, long eventId) throws SQLException { exceptionStatement.setLong(1, eventId); exceptionStatement.setShort(2, i); exceptionStatement.setString(3, txt); if (cnxSupportsBatchUpdates) { exceptionStatement.addBatch(); } else { exceptionStatement.execute(); } }
public void executeBatch(List<Entity> entities) throws SQLException { try ( Connection connection = dataSource.getConnection(); PreparedStatement statement = connection.prepareStatement(SQL); ) { for (Entity entity : entities) { statement.setObject(1, entity.getSomeProperty()); // ... statement.addBatch(); } statement.executeBatch(); } }
public void save(List<Entity> entities) throws SQLException { try ( Connection connection = database.getConnection(); PreparedStatement statement = connection.prepareStatement(SQL_INSERT); ) { int i = 0; for (Entity entity : entities) { statement.setString(1, entity.getSomeProperty()); // ... statement.addBatch(); i++; if (i % 1000 == 0 || i == entities.size()) { statement.executeBatch(); // Execute every 1000 items. } } } }
@Override public void remove(List<Long> pks) { PreparedStatement statement = null; try { this.connection.setAutoCommit(false); statement = this.connection.prepareStatement("DELETE FROM t_transaction WHERE offset = ?"); for (long pk : pks) { statement.setLong(1, pk); statement.addBatch(); } int[] executeBatch = statement.executeBatch(); this.connection.commit(); } catch (Exception e) { log.warn("createDB Exception", e); } finally { if (null != statement) { try { statement.close(); } catch (SQLException e) { } } } }
private void writeVectorClock(Connection connection, long databaseVersionId, VectorClock vectorClock) throws SQLException { try (PreparedStatement preparedStatement = getStatement(connection, "databaseversion.insert.all.writeVectorClock.sql")) { for (Map.Entry<String, Long> vectorClockEntry : vectorClock.entrySet()) { preparedStatement.setLong(1, databaseVersionId); preparedStatement.setString(2, vectorClockEntry.getKey()); preparedStatement.setLong(3, vectorClockEntry.getValue()); preparedStatement.addBatch(); } preparedStatement.executeBatch(); } }
@Override public void doAddMessageReference(TransactionContext c, long sequence, MessageId messageID, ActiveMQDestination destination, long expirationTime, String messageRef) throws SQLException, IOException { PreparedStatement s = c.getAddMessageStatement(); try { if (s == null) { s = c.getConnection().prepareStatement(this.statements.getAddMessageStatement()); if (this.batchStatements) { c.setAddMessageStatement(s); } } s.setLong(1, messageID.getBrokerSequenceId()); s.setString(2, messageID.getProducerId().toString()); s.setLong(3, messageID.getProducerSequenceId()); s.setString(4, destination.getQualifiedName()); s.setLong(5, expirationTime); s.setString(6, messageRef); if (this.batchStatements) { s.addBatch(); } else if (s.executeUpdate() != 1) { throw new SQLException("Failed add a message"); } } finally { if (!this.batchStatements) { s.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(); }
/** * 创建批量操作的{@link PreparedStatement} * * @param conn 数据库连接 * @param sql SQL语句,使用"?"做为占位符 * @param paramsBatch "?"对应参数批次列表 * @return {@link PreparedStatement} * @throws SQLException SQL异常 * @since 4.1.13 */ public static PreparedStatement prepareStatementForBatch(Connection conn, String sql, Object[]... paramsBatch) throws SQLException { Assert.notBlank(sql, "Sql String must be not blank!"); sql = sql.trim(); // SqlLog.INSTASNCE.log(sql, paramsBatch); PreparedStatement ps = conn.prepareStatement(sql); for (Object[] params : paramsBatch) { StatementUtil.fillParams(ps, params); ps.addBatch(); } return ps; }
@Override public boolean addBatch() throws SQLException { pstmt.addBatch(); pstmt.clearParameters(); batchCount++; if (batchCount % maxBatchSize == 0L) { pstmt.executeBatch(); pstmt.getConnection().commit(); return true; } return false; }
PreparedStatement pstmt = connection.prepareStatement( "UPDATE table SET field=? WHERE id=?"); for (int i = 0; i < 100; i++) { pstmt.setInt(1, i * i); pstmt.setInt(2, i); pstmt.addBatch(); } pstmt.executeBatch(); pstmt.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(); }
connection.setAutoCommit(false); PreparedStatement ps = connection.prepareStatement(query); for (Record record : records) { // etc. ps.addBatch(); } ps.executeBatch(); connection.commit();
protected void insertProperties(Map<String, String> mergedMap, Connection connection, long eventId) throws SQLException { Set<String> propertiesKeys = mergedMap.keySet(); if (propertiesKeys.size() > 0) { PreparedStatement insertPropertiesStatement = null; try { insertPropertiesStatement = connection.prepareStatement(insertPropertiesSQL); for (String key : propertiesKeys) { String value = mergedMap.get(key); insertPropertiesStatement.setLong(1, eventId); insertPropertiesStatement.setString(2, key); insertPropertiesStatement.setString(3, value); if (cnxSupportsBatchUpdates) { insertPropertiesStatement.addBatch(); } else { insertPropertiesStatement.execute(); } } if (cnxSupportsBatchUpdates) { insertPropertiesStatement.executeBatch(); } } finally { closeStatement(insertPropertiesStatement); } } }
public void insertFlashedTokens(FlashedToken[] flashedTokenList) throws Exception { try (Connection connection = ds.getConnection(); PreparedStatement ps = connection.prepareStatement(insertToken)) { for (FlashedToken flashedToken : flashedTokenList) { insert(ps, flashedToken); ps.addBatch(); } ps.executeBatch(); connection.commit(); } }
public void removeSpecificFileVersions(Map<FileHistoryId, List<FileVersion>> purgeFileVersions) throws SQLException { if (purgeFileVersions.size() > 0) { try (PreparedStatement preparedStatement = getStatement(connection, "fileversion.delete.all.removeSpecificFileVersionsByIds.sql")) { for (FileHistoryId purgeFileHistoryId : purgeFileVersions.keySet()) { for (FileVersion purgeFileVersion : purgeFileVersions.get(purgeFileHistoryId)) { preparedStatement.setString(1, purgeFileHistoryId.toString()); preparedStatement.setLong(2, purgeFileVersion.getVersion()); preparedStatement.addBatch(); } } preparedStatement.executeBatch(); } } }
() -> executeBatch(getConnection(), connection -> { PreparedStatement preparedStatement = connection.prepareStatement(sql); SerializedObject<?> payload = event.serializePayload(serializer, dataType); SerializedObject<?> metaData = event.serializeMetaData(serializer, dataType); preparedStatement.setString(1, event.getIdentifier()); preparedStatement.setString(2, event.getAggregateIdentifier()); preparedStatement.setLong(3, event.getSequenceNumber()); preparedStatement.setString(4, event.getType()); writeTimestamp(preparedStatement, 5, event.getTimestamp()); preparedStatement.setString(6, payload.getType().getName()); preparedStatement.setObject(8, payload.getData()); preparedStatement.setObject(9, metaData.getData()); preparedStatement.addBatch();
public void executeBatch(List<Entity> entities) throws SQLException { try ( Connection connection = dataSource.getConnection(); PreparedStatement statement = connection.prepareStatement(SQL); ) { int i = 0; for (Entity entity : entities) { statement.setObject(1, entity.getSomeProperty()); // ... statement.addBatch(); i++; if (i % 1000 == 0 || i == entities.size()) { statement.executeBatch(); // Execute every 1000 items. } } } }
String[][] a = new String[10][2]; PreparedStatement pst = con.prepareStatement("INSERT INTO sap_details VALUES (?,?)"); for (int i = 0; i < 10; i++) { pst.setString(1, a[i][0]); pst.setString(2, a[i][1]); pst.addBatch(); } int[] results = pst.executeBatch();
@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(); }