public record User(Long id, String email, String displayName, boolean active) {
public static User of(String email, String displayName) {
return new User(null, email, displayName, true);
}
public User withId(long assignedId) {
return new User(assignedId, email, displayName, active);
}
}import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BatchUserRepository {
private static final int BATCH_SIZE = 1_000;
private static final String INSERT_SQL =
"INSERT INTO users (email, display_name, active) VALUES (?, ?, ?)";
private final DataSource dataSource;
public BatchUserRepository(DataSource dataSource) {
this.dataSource = dataSource;
}
public List<User> saveAll(List<User> users) throws SQLException {
List<User> persisted = new ArrayList<>(users.size());
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(
INSERT_SQL, Statement.RETURN_GENERATED_KEYS)) {
List<User> pending = new ArrayList<>(BATCH_SIZE);
for (User user : users) {
ps.setString(1, user.email());
ps.setString(2, user.displayName());
ps.setBoolean(3, user.active());
ps.addBatch();
pending.add(user);
if (pending.size() == BATCH_SIZE) {
flush(ps, pending, persisted);
}
}
if (!pending.isEmpty()) {
flush(ps, pending, persisted);
}
conn.commit();
} catch (SQLException ex) {
conn.rollback();
throw ex;
}
}
return persisted;
}
private void flush(PreparedStatement ps, List<User> pending, List<User> persisted)
throws SQLException {
ps.executeBatch();
try (ResultSet keys = ps.getGeneratedKeys()) {
int i = 0;
while (keys.next()) {
persisted.add(pending.get(i++).withId(keys.getLong(1)));
}
}
pending.clear();
}
}import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
public class AppBootstrap {
private static DataSource buildDataSource() {
HikariConfig cfg = new HikariConfig();
// reWriteBatchedInserts lets the pg driver collapse the batch into multi-row inserts
cfg.setJdbcUrl("jdbc:postgresql://localhost:5432/app?reWriteBatchedInserts=true");
cfg.setUsername("app");
cfg.setPassword(System.getenv("DB_PASSWORD"));
cfg.setMaximumPoolSize(8);
return new HikariDataSource(cfg);
}
public static void main(String[] args) throws Exception {
DataSource ds = buildDataSource();
BatchUserRepository repository = new BatchUserRepository(ds);
List<User> toInsert = new ArrayList<>();
for (int i = 0; i < 25_000; i++) {
toInsert.add(User.of("user" + i + "@example.com", "User " + i));
}
long start = System.nanoTime();
List<User> saved = repository.saveAll(toInsert);
long ms = (System.nanoTime() - start) / 1_000_000;
System.out.printf("Inserted %d users in %d ms (first id=%d)%n",
saved.size(), ms, saved.get(0).id());
}
}Inserting thousands of rows one INSERT at a time is one of the most common performance traps in JDBC code: every statement pays a network round-trip and, without an explicit transaction, an implicit commit per row. This snippet shows the standard cure — grouping statements into batches with PreparedStatement.addBatch() and flushing them with executeBatch() inside a single transaction — wrapped behind a small repository so callers never touch raw JDBC.
The User record in the first tab is a plain immutable carrier for the data being persisted. It intentionally carries no persistence logic; keeping the domain object dumb means the batching mechanics live entirely in the repository layer where they can be tuned in one place.
BatchUserRepository is the core of the example. In saveAll, a connection is taken from a DataSource (typically pool-backed such as HikariCP) and setAutoCommit(false) is called so the whole load is one atomic unit — either every row lands or none does, and no per-row commit overhead is incurred. A single PreparedStatement is reused across all rows: for each User the parameters are bound and addBatch() queues the row without sending it. The code flushes with executeBatch() every BATCH_SIZE rows rather than accumulating the entire dataset, which bounds memory and driver-side buffering — an important pitfall when loading millions of rows. Statement.RETURN_GENERATED_KEYS plus getGeneratedKeys() after each flush recovers the database-assigned ids and rebuilds User instances with them. A catch block issues an explicit rollback() so a mid-load failure leaves the table untouched.
A subtle but real-world detail: many drivers only truly pipeline batches when told to. The JDBC URL in AppBootstrap sets reWriteBatchedInserts=true, which lets the PostgreSQL driver rewrite the batch into multi-row INSERT statements — often a 5–10x speedup over naive execution. The bootstrap tab also demonstrates the intended call site: build a list, hand it to saveAll, and read back the persisted rows with their ids.
The trade-offs are worth noting. Batching sacrifices per-row error granularity — a BatchUpdateException reports which entries failed via getUpdateCounts(), but recovery is coarser than single inserts. It also holds a transaction and connection longer, so BATCH_SIZE is a tuning knob balancing throughput against lock duration. For typical bulk-load paths, though, this pattern is the pragmatic default.
Related snips
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
class SignupForm
include ActiveModel::Model
include ActiveModel::Attributes
attribute :account_name, :string
attribute :email, :stringShallow Controller, Deep Params: Form Object Pattern
Rails.application.configure do
config.after_initialize do
Bullet.enable = true
Bullet.alert = false
Bullet.bullet_logger = true
Bullet.console = trueN+1 query detection with Bullet gem
# Vulnerable: user input is concatenated directly into SQL.
email = params[:email]
password = params[:password]
sql = "SELECT * FROM users WHERE email = '#{email}' AND password_hash = '#{password}'"
user = ActiveRecord::Base.connection.execute(sql).firstSQL injection prevention with unsafe and safe query patterns
# BAD: N+1 query problem
@users = User.all
@users.each do |user|
puts user.posts.count # Fires query for each user!
endActiveRecord query optimization and N+1 prevention
Share this code
Here's the card — post it anywhere.
