JDBC Best Practices

JDBC is Java's lowest-level database API. Higher-level tools (JPA, Spring Data) sit on top of it. Despite the prevalence of ORMs, raw JDBC remains useful for: performance-critical paths, complex queries the ORM doesn't express well, batch operations, and integration tests.

This page covers the patterns that make raw JDBC code sustainable.

Connection management

JDBC connections are expensive. Open, close per operation produces production-grade scalability problems quickly.

Always use a connection pool

Production code should use a connection pool (HikariCP is the standard; Spring Boot uses it by default). The pool keeps a fixed number of connections open and lends them out on request.

Direct JDBC code should never do DriverManager.getConnection() per request — that opens a new connection every call.

Try-with-resources

try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql);
     ResultSet rs = stmt.executeQuery()) {

    while (rs.next()) {
        // process row
    }
}

Resources close automatically. Replaces the verbose try-finally pattern. Always use this; never manually close JDBC resources.

Prepared statements

// Wrong: SQL injection vulnerable
String sql = "SELECT * FROM orders WHERE customer_id = '" + customerId + "'";

// Right: parameterized
String sql = "SELECT * FROM orders WHERE customer_id = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, customerId);
    try (ResultSet rs = stmt.executeQuery()) {
        // ...
    }
}

Parameterized queries:

Always use prepared statements. Hand-built SQL strings are a vulnerability waiting to happen.

Batching

For bulk inserts or updates:

String sql = "INSERT INTO orders (id, amount) VALUES (?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    for (Order order : orders) {
        stmt.setString(1, order.id());
        stmt.setBigDecimal(2, order.amount());
        stmt.addBatch();
    }
    stmt.executeBatch();
}

Dramatic speedup vs. individual INSERTs — often 10-100× for typical workloads. The exact speedup depends on the database and network.

For PostgreSQL specifically, additional batching options:

Transactions

conn.setAutoCommit(false);
try {
    // multiple statements
    insertOrder(conn, order);
    insertOrderItems(conn, items);
    conn.commit();
} catch (Exception e) {
    conn.rollback();
    throw e;
}

Transaction management at the JDBC level is verbose; framework-level transaction management (Spring's @Transactional) is usually preferred for application code.

For batch operations, transactions are essential — committing every row defeats batching.

Result set handling

try (ResultSet rs = stmt.executeQuery()) {
    while (rs.next()) {
        String id = rs.getString("id");
        BigDecimal amount = rs.getBigDecimal("amount");
        OrderStatus status = OrderStatus.valueOf(rs.getString("status"));
        // ...
    }
}

Notes:

For null handling, primitives are tricky:

int count = rs.getInt("count");
if (rs.wasNull()) {
    // count was actually NULL in the database
}

Or use getObject(...) and check for null directly.

Mapping ResultSet to objects

Three approaches:

Manual

private Order mapRow(ResultSet rs) throws SQLException {
    return new Order(
        rs.getString("id"),
        rs.getBigDecimal("amount"),
        OrderStatus.valueOf(rs.getString("status"))
    );
}

Verbose but explicit. Errors are clear.

Spring's JdbcTemplate

List<Order> orders = jdbcTemplate.query(sql, args, this::mapRow);

Reduces the connection management boilerplate. Still requires manual mapping.

Reflection-based libraries

JOOQ, jOOQ, MyBatis, etc. Generate or reflect-map; less code, more magic. Trade-offs depend on library.

Specific patterns

Pagination

SELECT * FROM orders
ORDER BY created_at, id
LIMIT 100 OFFSET 200

For most cases, OFFSET is fine. For deep pagination, cursor-based pagination is faster:

SELECT * FROM orders
WHERE (created_at, id) > (?, ?)
ORDER BY created_at, id
LIMIT 100

Streaming large result sets

For result sets too large to load into memory, use setFetchSize and stream:

stmt.setFetchSize(1000);
try (ResultSet rs = stmt.executeQuery()) {
    while (rs.next()) {
        // process row, don't accumulate
    }
}

The driver fetches rows in batches; memory stays bounded.

IN clauses with large lists

// Bad: SQL with many parameters fails
String sql = "WHERE id IN (?, ?, ?, ..., ?)"; // up to 100s of placeholders

// Better: temp table or array parameter (database-specific)

PostgreSQL supports array parameters: WHERE id = ANY(?::uuid[]). Other databases vary.

Database time and locale

PreparedStatement.setTimestamp() and ResultSet.getTimestamp() interact with timezone conversion. Be deliberate about which timezone the database stores in (usually UTC) and how the application converts.

Common failure patterns

Further Reading