JDBC: the four objects

Almost nobody writes JDBC directly any more — Spring Data, an ORM or a template sits on top. Knowing what those are sitting on is still worth an afternoon, because when a connection leaks, a ResultSet throws after its statement closed, or a nullable column silently becomes zero, the explanation is here and nowhere above it.

#Four objects, and what each one owns

• Driver — knows how to speak one database's wire protocol. It registers itself when its jar is on the classpath; since JDBC 4 there is no Class.forName to write.
• Connection — one session with the database. Expensive to create, which is the entire reason connection pools exist.
• Statement — one query in flight on that connection.
• ResultSet — a cursor over the rows, not a list of them.

The URL is what selects the driver: jdbc:postgresql://host:5432/db, jdbc:h2:mem:demo. Everything after the scheme is that driver's business.

#A ResultSet is a cursor, and it starts before the first row

try (Statement s = c.createStatement();
     ResultSet rs = s.executeQuery("select id, name, price from item order by id")) {
    while (rs.next()) {
        System.out.println(rs.getInt("id") + " " + rs.getString("name"));
    }
}

###   columns: 3
###   row -> 1 widget 1999
###   row -> 2 bolt 250
###   next() after the last row: false

next() moves the cursor and returns whether it landed on a row. That is why the loop is while (rs.next()) and not while (rs.hasNext()) — there is no such method, and calling a getter before the first next() is an error rather than the first row.

The cursor may still be streaming from the server, which is why the next section matters more than it looks.

#Everything closes, in reverse order

ResultSet, Statement and Connection all hold resources on both sides of the wire. try-with-resources closes them in reverse order of declaration, which is the order you want.

What happens when you let one escape:

ResultSet leaked;
try (Statement s = c.createStatement()) {
    leaked = s.executeQuery("select id from item");
    leaked.next();                       // fine, inside
}
leaked.next();                           // outside

###   inside the try, first id = 1
###   SQLException: The object is already closed [90007-214]

Closing a Statement closes its ResultSet. Closing a Connection closes everything on it. So a ResultSet is not something to return from a method — map it to your own objects while it is open, and hand back those.

#getInt on a NULL column returns zero

This one is quiet and expensive:

###   getInt(1)   = 0
###   wasNull()   = true
###   getObject(1)= null

The primitive getters cannot return null, so they return 0, false, 0.0. A nullable price column becomes a price of zero, and nothing throws.

Two ways to be correct, and the second is usually better:

int price = rs.getInt("price");
if (rs.wasNull()) { /* it was NULL, not zero */ }
 
Integer price = (Integer) rs.getObject("price");   // null stays null

wasNull() refers to the last column read, so it has to be called immediately after the getter and before reading anything else. That ordering requirement is why getObject — or getObject(column, Integer.class) on modern drivers — is the safer habit.

#Transactions are a Connection setting

JDBC is autocommit by default: every statement is its own transaction.

c.setAutoCommit(false);
try {
    // several statements, one unit of work
    c.commit();
} catch (SQLException e) {
    c.rollback();
    throw e;
}

Two details worth carrying:

• Autocommit off is per connection, not per block. In a pool, a connection returned with autocommit still disabled poisons whoever gets it next — which is why frameworks reset it, and why hand-rolled pooling goes wrong.
• This is what @Transactional is doing. The proxy from the Spring course borrows a connection, turns autocommit off, runs your method, and commits or rolls back. Knowing that makes the self-invocation trap obvious rather than magical: no proxy, no setAutoCommit(false), no transaction.

#Reading what went wrong

SQLException carries more than a message, and the extra is what makes an error actionable:

• getSQLState() — a five-character standard code. 23505 is a unique-constraint violation on PostgreSQL, 23000 the standard integrity-violation class.
• getErrorCode() — the database's own number, more specific and less portable.
• getNextException() — a chain. Batch failures in particular put the useful one further down, and a printStackTrace() on the first can be nearly content-free.

Spring's DataAccessException hierarchy is a translation of exactly these into vendor-neutral types — DuplicateKeyException rather than state 23505 — which is most of what JdbcTemplate buys you beyond the boilerplate.

#What you will actually use

For anything but a script, use the layer above:

• JdbcTemplate — JDBC without the closing, the checked exceptions or the loop. queryForObject, query with a RowMapper, update.
• JdbcClient (Spring 6.1+) — the same with a fluent API.
• JPA — when you want entities and a persistence context, with everything that course described.

None of them removes what is on this page. They make the correct thing the default, and the failures underneath still surface with these names.