JDBC
JDBC is the underlying Java database API. Modern apps use it via JPA / Hibernate / Spring Data JDBC / jOOQ — but understanding JDBC directly matters when you need raw control: streaming results, bulk inserts, vendor-specific features. Pair with a connection pool (HikariCP).
Connection pool + prepared statements + transactions
EXAMPLE
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;
import java.util.*;
public class JdbcDemo {
// 1) Connection pool — created once at startup
private static final HikariDataSource DS = createPool();
private static HikariDataSource createPool() {
var cfg = new HikariConfig();
cfg.setJdbcUrl(System.getenv("DATABASE_URL"));
cfg.setUsername(System.getenv("DB_USER"));
cfg.setPassword(System.getenv("DB_PASS"));
cfg.setMaximumPoolSize(20);
cfg.setConnectionTimeout(5_000);
cfg.setIdleTimeout(30_000);
cfg.addDataSourceProperty("prepStmtCacheSize", "250");
cfg.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
cfg.addDataSourceProperty("cachePrepStmts", "true");
return new HikariDataSource(cfg);
}
record Order(long id, long customerId, long totalCents, String status) {}
// 2) Parameterised query
public static Order findById(long id) throws SQLException {
var sql = "SELECT id, customer_id, total_cents, status FROM orders WHERE id = ?";
try (var c = DS.getConnection();
var ps = c.prepareStatement(sql)) {
ps.setLong(1, id);
try (var rs = ps.executeQuery()) {
if (!rs.next()) return null;
return new Order(
rs.getLong("id"),
rs.getLong("customer_id"),
rs.getLong("total_cents"),
rs.getString("status")
);
}
}
}
// 3) Insert + RETURNING (Postgres flavour)
public static long createOrder(long customerId, long totalCents) throws SQLException {
var sql = "INSERT INTO orders (customer_id, total_cents, status) VALUES (?, ?, ?) RETURNING id";
try (var c = DS.getConnection();
var ps = c.prepareStatement(sql)) {
ps.setLong(1, customerId);
ps.setLong(2, totalCents);
ps.setString(3, "new");
try (var rs = ps.executeQuery()) {
rs.next();
return rs.getLong(1);
}
}
}
// 4) Transactions — explicit BEGIN/COMMIT
public static long placeOrder(String email, String name, long totalCents) throws SQLException {
try (var c = DS.getConnection()) {
c.setAutoCommit(false);
try {
long customerId;
try (var ps = c.prepareStatement(
"INSERT INTO customers (email, name) VALUES (?, ?) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name RETURNING id")) {
ps.setString(1, email);
ps.setString(2, name);
try (var rs = ps.executeQuery()) {
rs.next();
customerId = rs.getLong(1);
}
}
long orderId;
try (var ps = c.prepareStatement(
"INSERT INTO orders (customer_id, total_cents, status) VALUES (?, ?, ?) RETURNING id")) {
ps.setLong(1, customerId);
ps.setLong(2, totalCents);
ps.setString(3, "new");
try (var rs = ps.executeQuery()) {
rs.next();
orderId = rs.getLong(1);
}
}
c.commit();
return orderId;
} catch (SQLException e) {
c.rollback();
throw e;
} finally {
c.setAutoCommit(true);
}
}
}
// 5) Bulk insert via batch
public static void bulkInsert(List<Order> orders) throws SQLException {
var sql = "INSERT INTO orders (id, customer_id, total_cents, status) VALUES (?, ?, ?, ?)";
try (var c = DS.getConnection()) {
c.setAutoCommit(false);
try (var ps = c.prepareStatement(sql)) {
for (var o : orders) {
ps.setLong(1, o.id());
ps.setLong(2, o.customerId());
ps.setLong(3, o.totalCents());
ps.setString(4, o.status());
ps.addBatch();
}
ps.executeBatch();
c.commit();
} catch (SQLException e) {
c.rollback();
throw e;
} finally {
c.setAutoCommit(true);
}
}
}
// 6) Streaming large result set
public static void streamOrders(java.util.function.Consumer<Order> onRow) throws SQLException {
try (var c = DS.getConnection()) {
c.setAutoCommit(false); // required for server-side cursor on Postgres
try (var ps = c.prepareStatement("SELECT id, customer_id, total_cents, status FROM orders",
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
ps.setFetchSize(1000);
try (var rs = ps.executeQuery()) {
while (rs.next()) {
onRow.accept(new Order(
rs.getLong(1), rs.getLong(2), rs.getLong(3), rs.getString(4)
));
}
}
}
c.setAutoCommit(true);
}
}
// 7) PreparedStatement is the SQLi defence
// NEVER: "SELECT * FROM users WHERE email = " + email
// ALWAYS: prepareStatement("... WHERE email = ?"); ps.setString(1, email);
// 8) Modern alternatives
// - JPA (Hibernate): entity-centric, lots of magic
// - Spring Data JDBC / JdbcTemplate: thinner, less magic
// - jOOQ: type-safe DSL generated from schema; great for complex SQL
// - MyBatis: SQL templates + Java mapping
// Default: jOOQ or JdbcTemplate; reach for JPA when you accept its tradeoffs.
// 9) Pitfalls
// - new Connection per request -> connection storm; use a pool
// - Forgetting to close ResultSet / Statement / Connection -> leak (try-with-resources)
// - Statement instead of PreparedStatement -> SQLi risk + no caching
// - Big SELECT * in a transaction without setFetchSize -> OOM
// - Mismatched auto-commit handling -> stale data
}
Why it matters
HikariCP at module scope + parameterised PreparedStatements + transactions inside try-with-resources is the JDBC trifecta. Combine with jOOQ or JdbcTemplate for type safety on top, and you stop fighting the JDBC API entirely — the boilerplate disappears, the safety remains.
Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.
Example
Example
try (Connection c = DriverManager.getConnection(url, user, pw);
PreparedStatement ps = c.prepareStatement("SELECT * FROM users WHERE id = ?")) {
ps.setLong(1, id);
try (ResultSet rs = ps.executeQuery()) { /* … */ }
}
Try it Yourself »
Discussion
Loading…