Platform & Advanced APIs

JDBC & Database Access

JDBC is the SQL access layer everything else (JPA, jOOQ, MyBatis) builds on: DataSource → Connection → PreparedStatement → ResultSet, with connection pooling and transactions as the production concerns.

Always PreparedStatement with ? parameters — string-concatenated SQL is injection
Pool connections (HikariCP is the de facto standard) — physical connects cost ~ms each
try-with-resources every Connection/Statement/ResultSet — leaks exhaust the pool
Transactions: setAutoCommit(false) → work → commit(); rollback in the catch
Batch inserts (addBatch/executeBatch) are 10–100× faster than row-at-a-time
ResultSet is a cursor: while (rs.next()), typed getters, wasNull for primitives

The core pattern

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(
             "SELECT id, name, salary FROM employees WHERE dept = ? AND salary > ?")) {
    ps.setString(1, dept);
    ps.setBigDecimal(2, minSalary);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            result.add(new Employee(rs.getLong("id"), rs.getString("name"),
                                    rs.getBigDecimal("salary")));
        }
    }
}

Transactions and batching

conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO audit(user_id, action, at) VALUES (?, ?, ?)")) {
    for (AuditEvent e : events) {
        ps.setLong(1, e.userId());
        ps.setString(2, e.action());
        ps.setObject(3, e.at());              // java.time works directly (JDBC 4.2)
        ps.addBatch();
    }
    ps.executeBatch();
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw new AuditStoreException(e);
}

Isolation levels (Connection.TRANSACTION_READ_COMMITTED default in most DBs, up to SERIALIZABLE) trade anomaly protection for concurrency — know which anomalies (dirty/non-repeatable/phantom reads) your logic tolerates. In practice most Java code drives this through a framework's @Transactional; the JDBC semantics underneath are unchanged, and leaking connections or holding transactions across remote calls remain your bugs (pool exhaustion).