JDBC is the API every Java database access sits on — Hibernate, Spring Data, jOOQ and every ORM eventually call it. You will rarely write it directly, and knowing what it does is what lets you diagnose the layers above it.
The shape
class Demo {
void run() throws SQLException {
String url = "jdbc:postgresql://localhost:5432/shop";
try (Connection connection = DriverManager.getConnection(url, "user", "password");
PreparedStatement statement =
connection.prepareStatement("SELECT id, name FROM customers WHERE active = ?")) {
statement.setBoolean(1, true);
try (ResultSet rows = statement.executeQuery()) {
while (rows.next()) {
System.out.println(rows.getLong("id") + " " + rows.getString("name"));
}
}
}
}
}
Four objects: a Connection to the database, a PreparedStatement holding
the SQL, a ResultSet to walk the rows, and DriverManager to make the
connection.
Every one of them must be closed, which is why try-with-resources is not optional here. A leaked connection does not fail immediately — it fails an hour later when the pool is exhausted, which is a much harder problem to trace. Note also that parameter indexes start at 1, not 0.
Always use a PreparedStatement
class Demo {
// NEVER do this
void unsafe(Connection connection, String name) throws SQLException {
String sql = "SELECT * FROM customers WHERE name = '" + name + "'";
try (Statement statement = connection.createStatement()) {
statement.executeQuery(sql);
}
}
// Always do this
void safe(Connection connection, String name) throws SQLException {
try (PreparedStatement statement =
connection.prepareStatement("SELECT * FROM customers WHERE name = ?")) {
statement.setString(1, name);
statement.executeQuery();
}
}
}
Pass '; DROP TABLE customers; -- as name to the first method and the
database does exactly what it was told. That is SQL injection, and it remains one of
the most common serious vulnerabilities because the unsafe version is the one that looks
simpler.
A PreparedStatement sends the SQL and the values separately, so a value can never be
read as SQL. It is also faster — the database parses the statement once and reuses the plan. There is
no case where string concatenation is the better choice.
Writing, and transactions
class Demo {
void transfer(Connection connection, long from, long to, BigDecimal amount)
throws SQLException {
connection.setAutoCommit(false); // off by default is ON — one commit per statement
try (PreparedStatement debit = connection.prepareStatement(
"UPDATE accounts SET balance = balance - ? WHERE id = ?");
PreparedStatement credit = connection.prepareStatement(
"UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
debit.setBigDecimal(1, amount);
debit.setLong(2, from);
debit.executeUpdate();
credit.setBigDecimal(1, amount);
credit.setLong(2, to);
credit.executeUpdate();
connection.commit(); // both, or neither
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
connection.setAutoCommit(true);
}
}
}
This is the reason transactions exist. Without setAutoCommit(false) each
executeUpdate commits on its own, and a failure between them leaves money debited from
one account and credited to none.
BigDecimal rather than double, for the reason the
data types post gives.
Batching
class Demo {
void insertAll(Connection connection, List<String> names) throws SQLException {
try (PreparedStatement statement =
connection.prepareStatement("INSERT INTO customers (name) VALUES (?)")) {
for (String name : names) {
statement.setString(1, name);
statement.addBatch(); // queue, do not send
}
int[] results = statement.executeBatch(); // one round trip
System.out.println(results.length + " rows inserted");
}
}
}
A thousand individual inserts means a thousand network round trips. Batching sends them together and is routinely an order of magnitude faster. It is the first thing to reach for when a bulk import is slow.
Connection pooling
Opening a connection is expensive — a TCP handshake, authentication, session setup. Doing it per request is one of the most common causes of a slow application.
A pool keeps connections open and hands them out. DriverManager.getConnection as shown
above is for examples; real applications use a DataSource backed by a pool such as
HikariCP, and connection.close() then returns it to the pool rather than closing it.
Nothing else about the code changes, which is the point of the interface.
Reading results safely
Two ResultSet details that cause real bugs. First, getInt and friends
return 0 for a SQL NULL rather than throwing, so a null column silently
becomes a zero:
class Demo {
void run(ResultSet rows) throws SQLException {
int discount = rows.getInt("discount");
if (rows.wasNull()) { // the only way to tell 0 from NULL
System.out.println("no discount set");
}
// Or use the object form, which returns null properly
Integer maybe = rows.getObject("discount", Integer.class);
System.out.println(maybe == null ? "none" : maybe.toString());
}
}
Second, prefer column names to indexes. rows.getString(2) breaks silently the day
someone reorders the SELECT; rows.getString("name") does not.
And avoid SELECT * in application code for the same reason — the column set becomes
whatever the table happens to have, so adding a column changes what your query returns.
The N+1 problem
Worth knowing here because it is an ORM symptom with a JDBC cause. Loading 100 customers and then their orders one customer at a time issues 101 queries. The fix is one query with a join, or a batched fetch.
When an ORM-backed page is mysteriously slow, turn on SQL logging and count the statements. It is almost always this.
What to take from it
- Close everything, with try-with-resources.
- Parameterise every value. No exceptions.
- Be explicit about transaction boundaries — know what commits and when.
- Batch bulk writes, and pool connections.
- Watch the query count, not just the query time.
Every one of those still applies when a framework is writing the JDBC for you. The framework removes the boilerplate, not the semantics.
Next
Logging is next — recording what happened, in a form that is useful at 3am.