16. Database Connectivity with JDBC

Almost every non-trivial application outlives the process that created its data, which means the data lives in a database rather than in the heap. This chapter covers JDBC, the standard Java API for talking to relational databases, from opening a connection and reading a result set through to transactions, and ends with the object-relational mapping tools built on top of it. The material here is where correctness bugs become expensive, because a mistake in a transaction boundary or a string-concatenated query corrupts or leaks real data.

<a id="161-jdbc-fundamentals"></a>

16.1 JDBC Fundamentals

This subchapter covers the shape of the JDBC API: how a driver is found and loaded, how a connection is opened, how statements produce result sets, how SQL types map onto Java types, and how the resources involved are reliably released.

JDBC Architecture and Driver Types

Relational databases are separate processes, usually on separate machines. They speak their own wire protocols: PostgreSQL, MySQL, Oracle, and SQL Server each have a different one, and none of them is Java. Without a standard, every application would be written against one vendor's client library, and switching databases would mean rewriting every line of data access code.

JDBC (Java Database Connectivity) solves this the same way the List interface solves the problem of having many list implementations. It defines a set of interfaces in java.sql, and each vendor ships a driver: a JAR containing classes that implement those interfaces by speaking that vendor's protocol. Your code is written against Connection, Statement, and ResultSet; the objects you actually hold at runtime are org.postgresql.jdbc.PgConnection or similar, and you never name that class.

The analogy is a power socket. Appliances are built against the socket's shape, not against a particular power station. The plug adapter is the driver.

  Your application code
  +-----------------------------------------------+
  |  Connection  Statement  ResultSet  (interfaces)|   java.sql
  +-----------------------------------------------+
                      |  implemented by
                      v
  +-----------------------------------------------+
  |  DriverManager / DataSource  (finds a driver)  |
  +-----------------------------------------------+
        |                 |                  |
        v                 v                  v
  +-----------+     +-----------+      +-----------+
  | Postgres  |     |  MySQL    |      |  Oracle   |     vendor drivers
  |  driver   |     |  driver   |      |  driver   |
  +-----------+     +-----------+      +-----------+
        |                 |                  |
        v                 v                  v
   wire protocol      wire protocol      wire protocol
        |                 |                  |
  +-----------+     +-----------+      +-----------+
  | Postgres  |     |  MySQL    |      |  Oracle   |     database servers
  +-----------+     +-----------+      +-----------+

Historically JDBC defined four driver types. Only one of them matters today, but the numbering still appears in interview questions and old documentation.

Type	Name	How it works	Status
1	JDBC-ODBC bridge	Translates JDBC calls into ODBC calls on the local machine	Removed in Java 8
2	Native-API	Java wrapper over a vendor's native C client library	Obsolete; needs native code
3	Network protocol	Talks to a middleware server that talks to the database	Rare; adds a hop
4	Thin / pure Java	Implements the vendor wire protocol directly in Java	The only one in use

A type 4 driver is a plain JAR with no native dependencies. It works on any platform the JVM runs on, and it is what every current database ships. When you add org.postgresql:postgresql to a build file, you are adding a type 4 driver.

One more distinction is worth fixing early, because the names are confusing. JDBC is the API. A driver is an implementation of it. DriverManager and DataSource are two different ways of obtaining a Connection from a driver, and 16.2 explains why DataSource is the one you should use in real applications.

Key Takeaways

• JDBC is a set of interfaces in java.sql; vendors supply implementations called drivers.
• Your code names only the interfaces, which is what makes database code portable.
• Four driver types were defined historically; only type 4, pure-Java thin drivers, remain relevant.
• A type 4 driver is an ordinary JAR dependency with no native components.
• Portability is at the API level, not the SQL level: dialects still differ.

🧪 Practice

1. Find the JDBC driver dependency for PostgreSQL, MySQL, and SQLite, and note the group and artifact identifier of each.
2. Open a driver JAR with jar tf and locate a class that implements java.sql.Connection. Confirm your own code never needs to name it.
3. Explain why a type 2 driver makes a build non-portable across operating systems, while a type 4 driver does not.
4. Interview: If JDBC makes database code portable, why does switching from MySQL to PostgreSQL still usually break an application? (Hint: the API is standardised, but what travels through it as a string is not.)

Connection URLs and Driver Loading

Before a driver can be used, two things must happen: the driver class must be loaded into the JVM, and the application must state which database it wants. Both are handled through a single string, the JDBC URL.

A JDBC URL always has three parts.

   jdbc:postgresql://db.internal:5432/orders?ssl=true&connectTimeout=5
   ^--^ ^--------^   ^---------------------^ ^-------------------------^
    |       |                  |                        |
  scheme  subprotocol     database-specific        driver properties
          (which driver)   location and name       (vendor-defined)

The scheme is always jdbc. The subprotocol selects the driver. Everything after that is defined by the vendor, which is why the URLs look so different.

Database	Example URL
PostgreSQL	jdbc:postgresql://localhost:5432/shopdb
MySQL	jdbc:mysql://localhost:3306/shopdb?serverTimezone=UTC
Oracle	jdbc:oracle:thin:@//localhost:1521/ORCLPDB1
SQL Server	jdbc:sqlserver://localhost:1433;databaseName=shopdb
H2, memory	jdbc:h2:mem:testdb
SQLite	jdbc:sqlite:/var/data/app.db

Driver loading used to be manual. Code written before Java 6 begins with a line whose purpose is easy to misread.

// LEGACY: forces the class to load, which runs its static initialiser.
// That initialiser calls DriverManager.registerDriver(new Driver()).
// The returned Class object is deliberately discarded.
Class.forName("org.postgresql.Driver");

Since JDBC 4.0 this is unnecessary. DriverManager uses the service loader mechanism: every driver JAR contains a file at META-INF/services/java.sql.Driver naming its driver class, and DriverManager reads those files from the classpath and registers what it finds. Writing Class.forName today is harmless but marks the code as dated.

// MODERN: no explicit loading. The driver on the classpath registers itself.
String url  = "jdbc:postgresql://localhost:5432/shopdb";
String user = "app";
String pass = System.getenv("DB_PASSWORD");   // never hard-code credentials

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    System.out.println(conn.getCatalog());
}

DriverManager chooses a driver by asking each registered driver whether it accepts the URL, in registration order, and using the first that says yes. If none accepts it, you get No suitable driver found for ..., which almost always means the driver JAR is missing from the runtime classpath rather than that the URL is malformed.

Credentials do not belong in the URL or in source code. Embedding a password in a string literal puts it in version control, in build artifacts, and in any stack trace that prints the URL. Read them from environment variables, a secrets manager, or configuration outside the repository.

Key Takeaways

• A JDBC URL has the form jdbc:<subprotocol>:<vendor-specific part>.
• The subprotocol is what selects the driver; the remainder is vendor-defined.
• Since JDBC 4.0 drivers self-register through META-INF/services, so Class.forName is obsolete.
• No suitable driver found normally means a missing classpath entry, not a bad URL.
• Keep passwords out of URLs and source; read them from the environment or a secret store.

🧪 Practice

1. Write the JDBC URL for a PostgreSQL database named inventory on host 10.0.0.5, port 6432, with SSL required.
2. Extract META-INF/services/java.sql.Driver from a driver JAR and read its contents. Explain how DriverManager uses it.
3. Run a program with the driver JAR removed from the classpath and record the exact exception message, so you recognise it later.
4. Interview: A colleague insists Class.forName is still required or the driver will not load. How would you check who is right? (Hint: what does DriverManager.drivers() report before any connection is opened?)

Opening Connections with DriverManager

A Connection represents a single, stateful session with the database. It carries the authenticated user, the current transaction, the auto-commit flag, the isolation level, and the schema in use. Every other JDBC object, statements and result sets alike, is created from a connection and dies with it.

The important thing to internalise is that a connection is expensive. Opening one involves a TCP handshake, often a TLS handshake, authentication, and server-side session setup. On a typical network that is tens of milliseconds, which is longer than most of the queries you will run through it.

  Opening a connection                  Running a simple query
  ------------------------------        -----------------------
  TCP handshake        ~1 ms            send SQL         ~0.2 ms
  TLS handshake       ~10 ms            server executes  ~0.5 ms
  authentication       ~5 ms            read rows        ~0.3 ms
  session setup        ~2 ms            ------------------------
  ------------------------------        total           ~1 ms
  total              ~18 ms

That ratio, roughly twenty to one, is the entire justification for connection pooling, which 16.2 covers. Until then, treat DriverManager.getConnection as a teaching tool and as the right choice for scripts and tests.

There are three overloads, and the two-argument form reads the credentials from a Properties object, which is useful when the driver needs extra settings.

// 1. URL only: credentials embedded in the URL or not required (e.g. H2 mem).
Connection a = DriverManager.getConnection("jdbc:h2:mem:testdb");

// 2. URL with user and password as separate arguments. The common form.
Connection b = DriverManager.getConnection(url, "app", password);

// 3. URL with a Properties bag: credentials plus driver-specific settings.
Properties props = new Properties();
props.setProperty("user", "app");
props.setProperty("password", password);
props.setProperty("ApplicationName", "order-service");  // shows up in pg_stat_activity
props.setProperty("connectTimeout", "5");               // seconds, driver-specific
Connection c = DriverManager.getConnection(url, props);

A connection must be closed. An unclosed connection holds a socket and a server-side session until a timeout fires, and databases enforce a hard limit on concurrent connections. Leak them in a loop and the application stops being able to reach the database at all, usually reported as FATAL: sorry, too many clients already. The try-with-resources form covered later in this subchapter is the only reliable way to avoid this.

Connections are also not thread-safe. A connection carries one transaction, so sharing it across threads means two units of work committing each other's changes. One thread, one connection, for the duration of one unit of work.

A useful sanity check after connecting is to interrogate the metadata, which tells you what you are actually talking to.

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    DatabaseMetaData meta = conn.getMetaData();
    System.out.printf("%s %s via driver %s%n",
            meta.getDatabaseProductName(),        // e.g. "PostgreSQL"
            meta.getDatabaseProductVersion(),     // e.g. "16.2"
            meta.getDriverVersion());
    System.out.println("valid: " + conn.isValid(2));   // round-trip, 2s timeout
}

Key Takeaways

• A Connection is one authenticated, stateful session and owns one transaction at a time.
• Opening one costs tens of milliseconds, far more than a typical query.
• Every statement and result set is created from a connection and is invalid once it closes.
• Connections must always be closed, or the server's client limit is exhausted.
• Connections are not thread-safe; never share one between threads.
• DatabaseMetaData and isValid are the quickest way to confirm what you connected to.

🧪 Practice

1. Connect to an in-memory H2 database, print its product name and version, and close the connection using try-with-resources.
2. Write a loop that opens 200 connections without closing them against a local database and record the exact failure. Then fix it.
3. Measure the time to open a connection versus the time to run SELECT 1, averaged over 50 iterations, and compare the two numbers.
4. Interview: Why is it wrong to store a Connection in a static field shared by a web application's request threads? (Hint: name two pieces of state a connection carries that belong to one unit of work.)

Statement Execution and ResultSet Traversal

With a connection in hand, work is done by creating a statement, executing SQL through it, and reading whatever comes back. JDBC splits execution into three methods because SQL statements come in two shapes: those that return rows and those that return a count.

Method	Use for	Returns
executeQuery	SELECT	a ResultSet
executeUpdate	INSERT, UPDATE, DELETE, DDL	int rows affected (0 for DDL)
execute	Statements whose kind is not known	boolean: true if rows follow

A ResultSet is not a collection. It is a cursor: a pointer that starts just before the first row and is advanced by next(), which returns false when there are no more rows. Rows are typically streamed from the server as you read them, which is what makes it possible to process a million-row result without a million-row list in memory.

              +--------+--------+-----------+
   cursor --> |  (before first row)          |   next() -> true
              +--------+--------+-----------+
              |   1    | Ada    | 1815-12-10|   <-- cursor after first next()
              +--------+--------+-----------+
              |   2    | Alan   | 1912-06-23|
              +--------+--------+-----------+
              |   3    | Grace  | 1906-12-09|
              +--------+--------+-----------+
              |  (after last row)            |   next() -> false
              +------------------------------+

The canonical read loop is therefore a while over next().

String sql = "SELECT id, name, born FROM people ORDER BY name";

try (Connection conn = DriverManager.getConnection(url, user, pass);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {

    while (rs.next()) {                       // advance; false ends the loop
        long id      = rs.getLong("id");      // read by column label
        String name  = rs.getString("name");
        LocalDate born = rs.getObject("born", LocalDate.class);
        System.out.printf("%d %s %s%n", id, name, born);
    }
}   // rs, stmt and conn closed in reverse order

Columns can be addressed by index or by label. Indexes are 1-based, not 0-based, which is the single most common beginner error in JDBC.

String name = rs.getString(2);        // second column, 1-based
String same = rs.getString("name");   // clearer, survives column reordering

Prefer labels. Index access breaks silently the moment someone changes the SELECT list, whereas a wrong label throws. The counter-argument is performance, and it is negligible in almost every application.

Read each column once, in order, if you care about the last drop of performance, because some drivers stream column data and do not buffer it. This matters for large CLOB and BLOB columns and almost nowhere else.

For a single-row query, the loop collapses to an if, and it is worth being explicit about the empty case rather than leaving a variable at its default.

try (ResultSet rs = stmt.executeQuery("SELECT count(*) FROM people")) {
    if (!rs.next()) {
        throw new IllegalStateException("count(*) returned no row");
    }
    long total = rs.getLong(1);
}

Result sets have a type and a concurrency setting that control whether the cursor can move backwards and whether rows can be updated through it.

Statement stmt = conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,   // allows previous(), absolute(), last()
        ResultSet.CONCUR_READ_ONLY);         // no updateXxx() through the cursor

Scrollable and updatable result sets exist, but they are rarely the right tool: they force the driver to buffer, and an explicit UPDATE is clearer than mutating a cursor. The default, TYPE_FORWARD_ONLY and CONCUR_READ_ONLY, is the one to use unless you have a specific reason not to.

One practical control worth knowing is the fetch size, the number of rows the driver pulls per round trip. The default is driver-specific and sometimes means "all of them", which turns a large query into an out-of-memory error.

stmt.setFetchSize(500);   // pull 500 rows per round trip instead of everything

Key Takeaways

• executeQuery returns rows, executeUpdate returns a count, execute handles the unknown case.
• A ResultSet is a forward-moving cursor, not an in-memory collection.
• next() both advances the cursor and reports whether a row is present.
• Column indexes start at 1; labels are safer and should be the default.
• The default result set is forward-only and read-only, which is what you normally want.
• setFetchSize bounds how many rows are buffered per round trip on large queries.

🧪 Practice

1. Create a people table, insert three rows with executeUpdate, and print the returned counts.
2. Write a query loop that reads columns by label, then change the order of the SELECT list and confirm the code still works. Repeat with indexes.
3. Write a method long countRows(Connection, String table) that returns the row count, throwing if the query yields no row.
4. Load 100,000 rows with the default fetch size and with setFetchSize(1000) while watching heap usage, and describe the difference.
5. Interview: Why does rs.getString("name") throw after the statement that created the result set is closed? (Hint: what does the result set actually hold, and where do the remaining rows live?)

Mapping SQL Types to Java Types

SQL and Java have separate type systems that were designed decades apart, and they do not line up. SQL has NUMERIC(12,2), TIMESTAMP WITH TIME ZONE, and a first-class NULL that applies to every column. Java has int, BigDecimal, OffsetDateTime, and a null that only applies to references. JDBC's job is to bridge the two, and most of the bugs in this area come from taking the bridge for granted.

The mappings that matter in practice:

SQL type	Best Java type	Getter
INTEGER, INT	int / Integer	getInt
BIGINT	long / Long	getLong
NUMERIC, DECIMAL	BigDecimal	getBigDecimal
REAL, DOUBLE PRECISION	double	getDouble
VARCHAR, TEXT	String	getString
BOOLEAN	boolean / Boolean	getBoolean
DATE	LocalDate	getObject(col, LocalDate.class)
TIME	LocalTime	getObject(col, LocalTime.class)
TIMESTAMP	LocalDateTime	getObject(col, LocalDateTime.class)
TIMESTAMP WITH TIME ZONE	OffsetDateTime	getObject(col, OffsetDateTime.class)
UUID	java.util.UUID	getObject(col, UUID.class)
BLOB, BYTEA	byte[] or InputStream	getBytes / getBinaryStream
ARRAY	java.sql.Array	getArray

Money is never a double. Binary floating point cannot represent 0.10 exactly, so sums drift. Use NUMERIC in the database and BigDecimal in Java.

// WRONG: 0.1 + 0.2 is not 0.3 in binary floating point.
double total = rs.getDouble("amount");

// RIGHT: exact decimal arithmetic, matching the column's scale.
BigDecimal total = rs.getBigDecimal("amount");

Use java.time, not java.sql.Date. The legacy java.sql.Date, java.sql.Time, and java.sql.Timestamp classes extend java.util.Date, are mutable, and silently apply the JVM's default time zone. Since JDBC 4.2 the generic getObject(column, Class) and setObject handle java.time types directly, which removes a whole family of off-by-one-day bugs.

// LEGACY: timezone-sensitive, mutable, and awkward to work with.
java.sql.Date old = rs.getDate("born");

// MODERN: unambiguous and immutable.
LocalDate born = rs.getObject("born", LocalDate.class);

NULL is the trap that catches everyone. The primitive getters cannot return null, so they return 0 or false instead, and the only way to distinguish "the value was zero" from "the value was NULL" is to ask afterwards.

int discount = rs.getInt("discount_percent");   // NULL silently becomes 0
if (rs.wasNull()) {                             // must be called immediately after
    // the column really was NULL
}

wasNull() refers to the most recently read column, so it must be called before reading anything else. The cleaner approach is to avoid primitives for nullable columns entirely.

// getObject returns a real null for a NULL column, so Optional works.
Integer discount = rs.getObject("discount_percent", Integer.class);
Optional<Integer> maybe = Optional.ofNullable(discount);

Writing a null has its own wrinkle: the driver often needs to know the column's SQL type, because a bare null carries no type information.

ps.setNull(3, Types.INTEGER);          // explicit and portable
ps.setObject(3, null, Types.INTEGER);  // equivalent

Key Takeaways

• SQL and Java type systems differ; JDBC bridges them but does not hide the gaps.
• Use BigDecimal with NUMERIC for money; double loses exactness.
• Prefer java.time types through getObject/setObject over the legacy java.sql date classes.
• Primitive getters turn SQL NULL into 0 or false without warning.
• wasNull() describes only the column read immediately before it.
• Use wrapper types via getObject for nullable columns so null stays null.
• Writing a null usually requires setNull with an explicit java.sql.Types constant.

🧪 Practice

1. Create a table with a nullable INTEGER column, insert a row with NULL and a row with 0, and show that getInt cannot tell them apart.
2. Rewrite that reading code using getObject(col, Integer.class) and Optional, and confirm the two rows are now distinguishable.
3. Store 19.99 in a DOUBLE PRECISION column and in a NUMERIC(10,2) column, sum each a thousand times, and compare the totals.
4. Read a TIMESTAMP WITH TIME ZONE column into both java.sql.Timestamp and OffsetDateTime while running the JVM with -Duser.timezone=Asia/Tokyo, and explain the difference.
5. Interview: A report shows a total of 0 for customers who have never ordered, and the team cannot tell those apart from customers whose total is genuinely zero. Where would you look? (Hint: which getter was used, and what does it do with NULL?)

Handling SQLException

Every JDBC method that touches the database declares SQLException, a checked exception. That is deliberate: the database is a separate process across a network, and failure is a normal outcome rather than a programming error. A connection can be dropped mid-query, a constraint can be violated by data the code could not have predicted, and a deadlock can be chosen for rollback by the server.

The problem is that SQLException is one class covering everything from "your SQL has a typo" to "the network cable was unplugged". Treating those the same way is why so much JDBC code is written with a bare catch (SQLException e) that logs and continues. To respond intelligently you need the three pieces of information the exception carries.

Accessor	Meaning
getMessage()	Vendor's human-readable text; useful for logs, not for logic
getErrorCode()	Vendor-specific numeric code; precise but not portable
getSQLState()	Five-character SQL standard code; portable across vendors

SQLState is the one to branch on. Its first two characters are a class, and the remaining three a subclass.

SQLState	Meaning	Typical response
08xxx	Connection exception	Retry, possibly against a replica
23000	Integrity constraint violation	Report to the caller as invalid data
23505	Unique violation	"That email is already registered"
40001	Serialization failure	Retry the whole transaction
40P01	Deadlock detected (PostgreSQL)	Retry the whole transaction
42xxx	Syntax error or access rule violation	Fix the code; never retry

Since Java 6 there is a friendlier alternative to parsing states by hand: a hierarchy of subclasses that a good driver throws.

  SQLException
  |
  +-- SQLTransientException            retrying may succeed
  |   +-- SQLTransientConnectionException
  |   +-- SQLTimeoutException
  |   +-- SQLTransactionRollbackException     deadlock, serialization failure
  |
  +-- SQLNonTransientException         retrying will fail the same way
  |   +-- SQLSyntaxErrorException
  |   +-- SQLIntegrityConstraintViolationException
  |   +-- SQLDataException
  |   +-- SQLFeatureNotSupportedException
  |
  +-- SQLRecoverableException          may succeed after recovery steps

The single most useful distinction is transient versus non-transient, and it maps directly onto "retry or do not retry".

try {
    repository.save(order);
} catch (SQLIntegrityConstraintViolationException e) {
    // The caller sent data that conflicts with a constraint. Not a bug, not
    // retryable: translate it into a domain error.
    throw new DuplicateOrderException(order.reference(), e);
} catch (SQLTransientException e) {
    // Network blip, timeout, or a deadlock victim. Safe to try again.
    throw new RetryableDataAccessException(e);
} catch (SQLException e) {
    // Everything else: a bug, a schema mismatch, or a permissions problem.
    log.error("SQLState={} code={}", e.getSQLState(), e.getErrorCode(), e);
    throw new DataAccessException(e);
}

A single failure can carry several exceptions, because a statement may produce a chain of warnings and errors. SQLException implements Iterable, and only iterating shows the whole picture.

catch (SQLException e) {
    for (Throwable t : e) {              // walks the chain, not just the cause
        log.error("  {}", t.getMessage());
    }
}

Two habits are worth adopting. Never swallow the exception: an empty catch block turns a data-loss incident into a mystery. And never put SQL state handling in the calling layer: translate database exceptions into your own domain exceptions at the boundary of the data access layer, so that business code never imports java.sql.

Key Takeaways

• SQLException is checked because database failure is expected, not exceptional.
• Branch on getSQLState() for portable handling; getErrorCode() is vendor-specific.
• SQLTransientException means retry is reasonable; SQLNonTransientException means it is not.
• Constraint violations are user-input errors and should become domain exceptions.
• Iterate the exception, since one failure may chain several.
• Translate at the data access boundary so upper layers never see java.sql types.

🧪 Practice

1. Provoke a unique constraint violation and print the message, error code, and SQL state. Compare them across two different databases.
2. Write a catch chain that turns a unique violation into a domain exception and leaves everything else to propagate.
3. Implement a retry helper that re-runs a unit of work up to three times only for SQLTransientException, with a short backoff between attempts.
4. Interview: Why is retrying a SQLSyntaxErrorException always wrong, while retrying a 40001 is often right? (Hint: what would have to change between the two attempts for the result to differ?)

Closing Resources with try-with-resources

Connections, statements, and result sets all hold resources that the garbage collector does not manage: sockets, server-side cursors, and server-side sessions. The JVM will happily collect a Connection object while the database still believes the session is open. Releasing those resources is the programmer's responsibility, and it must happen even when something throws.

Before Java 7 the correct code was notoriously verbose and almost always wrong in at least one place.

// LEGACY: this is what try-with-resources replaced. Note the nesting,
// the null checks, and that a failure in rs.close() hides the real exception.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
    conn = DriverManager.getConnection(url, user, pass);
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT 1");
    ...
} finally {
    if (rs != null)   { try { rs.close(); }   catch (SQLException ignored) {} }
    if (stmt != null) { try { stmt.close(); } catch (SQLException ignored) {} }
    if (conn != null) { try { conn.close(); } catch (SQLException ignored) {} }
}

All three JDBC types implement AutoCloseable, so a single try-with-resources header replaces the whole thing. Resources are closed in reverse order of declaration, which is exactly the order JDBC needs.

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

    while (rs.next()) { ... }

}   // closes rs, then ps, then conn, even if the body throws

  declaration order        closing order
  -----------------        -------------
  conn   (1)               rs     (3)
  ps     (2)       ==>     ps     (2)
  rs     (3)               conn   (1)

Suppressed exceptions are the second thing this construct fixes. If the body throws and a close() also throws, the close failure is attached to the original exception rather than replacing it, and both appear in the stack trace under a Suppressed: heading. The legacy code above simply lost one of them.

Two details are worth knowing. First, closing a Statement closes any ResultSet it produced, and closing a Connection closes its statements, so listing all three is belt and braces rather than strictly required. Listing them is still the better habit, because it keeps the code correct when the connection comes from a pool and outlives the statement.

Second, close() on a pooled connection does not close the socket. The pool's wrapper intercepts it and returns the connection to the pool. This is precisely why the same try-with-resources code is correct in both worlds, and why "always close the connection" remains true even when pooling.

A common mistake is putting a resource creation expression inside the body, where it is not managed.

// WRONG: this ResultSet is never closed.
try (Connection conn = ds.getConnection();
     Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery(sql);   // not in the resource list
    while (rs.next()) { ... }
}

Here the result set survives only because closing stmt closes it, which is true today and fragile tomorrow. Declare every resource in the header.

Finally, an existing variable can be used directly in the header since Java 9, provided it is final or effectively final.

Connection conn = pool.borrow();
try (conn) {              // Java 9+: manage an already-created resource
    ...
}

Key Takeaways

• JDBC resources are operating-system and server resources, not garbage that the collector reclaims.
• Connection, Statement, and ResultSet all implement AutoCloseable.
• try-with-resources closes in reverse declaration order, which matches JDBC's required order.
• A failure in close() is suppressed and attached to the original exception rather than replacing it.
• Declare every resource in the header; creating one in the body leaves it unmanaged.
• Closing a pooled connection returns it to the pool instead of closing the socket.

🧪 Practice

1. Rewrite the legacy finally block above using try-with-resources and count the lines removed.
2. Write code where both the body and close() throw, then print the stack trace and locate the Suppressed: section.
3. Add logging to a custom AutoCloseable wrapper around a statement to prove the closing order is the reverse of declaration.
4. Interview: A service using a connection pool exhausts it after a few hours under load, with no errors in the logs. What single class of bug would you look for first? (Hint: which object must return to the pool, and what happens if an early return skips it?)

<a id="162-safe-and-efficient-data-access"></a>

16.2 Safe and Efficient Data Access

This subchapter covers the tools that turn working JDBC code into code fit for production: parameterised statements that close the largest security hole in web applications, batching that collapses thousands of round trips into a handful, and pooling that removes the cost of opening connections.

PreparedStatement and Query Parameters

Almost every real query varies by some value: an identifier, a search term, a date range. The obvious way to supply it is string concatenation, and the obvious way is wrong for three separate reasons. PreparedStatement is the right way, and it fixes all three at once.

A prepared statement is SQL with placeholders, written as ?, sent to the database before the values are known. The database parses it, checks it, builds an execution plan, and hands back a handle. Values are then bound to the placeholders and the statement is executed, possibly many times.

  Statement (concatenated)              PreparedStatement
  ------------------------              -----------------
  build SQL string with value           send SQL with ? once
        |                                     |
  send whole string  ------> parse       parse, plan, cache
                             plan              |
                             execute     bind values -----> execute
                                               |
  (repeat: parse and plan again)         (repeat: bind and execute only)

The three problems it solves:

Problem	With concatenation	With parameters
Security	Values can change the statement's meaning	Values are never parsed as SQL
Correctness	Manual quoting, escaping, date formatting	Driver handles types and escaping
Performance	Every distinct string is parsed and planned	Parsed once, executed many times

String sql = "SELECT id, email FROM users WHERE department = ? AND active = ?";

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setString(1, department);     // parameters are 1-based, like columns
    ps.setBoolean(2, true);

    try (ResultSet rs = ps.executeQuery()) {   // note: no argument here
        while (rs.next()) { ... }
    }
}

Two details catch people out. Parameter indexes are 1-based. And executeQuery() on a PreparedStatement takes no argument, because the SQL was supplied at creation time; calling the inherited executeQuery(String) throws.

Placeholders stand for values, not for identifiers. This is the boundary that surprises people: you cannot parameterise a table name, a column name, a sort direction, or an operator, because those change the structure of the statement rather than the data it acts on.

// INVALID: the driver will not substitute a column name.
ps = conn.prepareStatement("SELECT * FROM users ORDER BY ?");

// VALID approach: validate against a whitelist you control, then concatenate.
private static final Set<String> SORTABLE = Set.of("name", "email", "created_at");

String column = SORTABLE.contains(requested) ? requested : "name";
String sql = "SELECT * FROM users ORDER BY " + column;   // safe: closed set

Binding a collection needs care, because one ? binds one value. The portable solution is to generate the right number of placeholders.

String placeholders = ids.stream()
        .map(id -> "?")
        .collect(Collectors.joining(", "));          // "?, ?, ?"
String sql = "SELECT * FROM users WHERE id IN (" + placeholders + ")";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    int i = 1;
    for (Long id : ids) {
        ps.setLong(i++, id);                          // bind each element
    }
    ...
}

Generating a different SQL string per list size defeats plan caching, so on PostgreSQL the better form is a single array parameter.

Array array = conn.createArrayOf("bigint", ids.toArray());
ps.setArray(1, array);      // one placeholder: WHERE id = ANY(?)

Finally, a practical note on when not to reach for PreparedStatement: for DDL run once at startup, or a static query with no parameters, a plain Statement is equally safe and marginally simpler. The rule is about values from outside the program, and every such value belongs in a parameter.

Key Takeaways

• PreparedStatement separates the statement's structure from its values.
• Parameters prevent injection, handle escaping and type conversion, and allow plan reuse.
• Parameter indexes start at 1, and executeQuery() takes no argument.
• Placeholders bind values only; identifiers must come from a whitelist you control.
• An IN clause needs one placeholder per element, or a single array parameter.
• Any value originating outside the program belongs in a parameter, without exception.

🧪 Practice

1. Convert a concatenated SELECT ... WHERE name = '" + name + "' into a parameterised query and verify it handles a name containing an apostrophe.
2. Write a method that takes a list of identifiers and builds the correct number of placeholders, then binds them.
3. Attempt to parameterise a table name, record the error, and implement the whitelist alternative.
4. Time 10,000 executions of the same query using a reused PreparedStatement versus a new Statement each time.
5. Interview: A search endpoint allows sorting by any column name supplied in the query string. How would you make it safe without losing the feature? (Hint: placeholders are not available here, so what property must the set of accepted values have?)

SQL Injection and Its Prevention

SQL injection has been at or near the top of every application security list for twenty years, and the mechanism is simple enough to explain in a sentence: if user input is concatenated into a SQL string, the user can write SQL.

Consider a login check built by concatenation.

// VULNERABLE. Never write this.
String sql = "SELECT id FROM users WHERE email = '" + email
           + "' AND password_hash = '" + hash + "'";

With an ordinary email the statement is what the author intended. With the email ' OR '1'='1' -- the database receives something else entirely.

  Intended:
    SELECT id FROM users WHERE email = 'ada@example.com'
                           AND password_hash = 'abc123'

  Received:
    SELECT id FROM users WHERE email = '' OR '1'='1' --' AND password_hash = '...'
                                       ^^^^^^^^^^^^^ ^^
                                       always true   rest is now a comment

The -- comments out the password check, '1'='1' is always true, and the query returns every user. The first row is typically the administrator.

The damage is not limited to reading. Depending on the driver's settings and the statement's position, an attacker may be able to terminate the statement and append another, extract data through a UNION, or infer data one bit at a time through timing or error differences ("blind" injection).

Attack form	Payload shape	Effect
Authentication bypass	' OR '1'='1' --	Condition always true
Union-based	' UNION SELECT card_number ...	Leaks other tables into results
Stacked queries	'; DROP TABLE users; --	Runs a second statement
Blind boolean	' AND substr(pw,1,1)='a' --	Extracts data one character at a time
Time-based blind	' AND pg_sleep(5) --	Extracts data via response time

The fix is parameterisation, and it works because of where the split happens. With a prepared statement, the SQL text is parsed into a plan before any value is supplied. The value is transmitted separately, in a typed protocol field, and is placed into the already-built plan as data. There is no parsing step left for it to influence. It is the difference between handing someone a filled-in form and letting them rewrite the form.

// SAFE. The input is data, never syntax, whatever it contains.
String sql = "SELECT id FROM users WHERE email = ? AND password_hash = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, email);     // "' OR '1'='1' --" is just a strange email
    ps.setString(2, hash);
    ...
}

Defences that do not work, or do not work alone:

• Escaping quotes by hand. Easy to get wrong, and useless for numeric contexts where no quotes are involved at all.
• Blacklisting words like DROP or UNION. Bypassed with comments, case changes, and encoding. It also breaks legitimate input.
• Client-side validation. The attacker does not use your client.
• Stored procedures. Safe only if they parameterise internally; a procedure that concatenates is just as vulnerable.
• An ORM. Safe for generated queries, but native query strings in an ORM are concatenated like any other string.

Defence in depth still matters, because parameterisation protects the query and nothing else. Give the application's database user only the privileges it needs, so a successful injection cannot read tables it never uses or run DDL. Validate input for shape and length because it is good practice anyway. Log failures without echoing the offending SQL back to the user, since error messages are an attacker's primary feedback channel.

A practical way to audit a codebase: search for string concatenation adjacent to SQL keywords.

# Any '+' inside a string that also looks like SQL is worth a manual look.
grep -rEn '"(SELECT|INSERT|UPDATE|DELETE)[^"]*"\s*\+' --include='*.java' src/

Key Takeaways

• SQL injection happens when input is concatenated into a statement and is then parsed as SQL.
• It enables authentication bypass, data theft, data destruction, and character-by-character extraction.
• Parameterisation is the fix because parsing happens before values arrive.
• Manual escaping, blacklists, and client-side validation are not defences.
• Stored procedures and ORMs are safe only where they parameterise internally.
• Restrict the application's database privileges so a breach has limited reach.
• Never return raw SQL error text to a caller.

🧪 Practice

1. Build a deliberately vulnerable login query in a local test database and bypass it with ' OR '1'='1' --. Then fix it with parameters and retry.
2. Show why escaping single quotes fails to protect a query whose parameter is numeric and unquoted.
3. Write a grep or static-analysis rule that flags SQL string concatenation, and run it over a project you have.
4. Create a database role with SELECT only on two tables, connect as it, and confirm an injected DROP TABLE fails.
5. Interview: A team says their ORM makes injection impossible. What would you ask to check that claim? (Hint: what escape hatch does every ORM provide for queries it cannot express?)

CallableStatement and Stored Procedures

A stored procedure is code that lives in the database and runs there. It is written in the database's own procedural language, PL/pgSQL for PostgreSQL or PL/SQL for Oracle, and is invoked by name. JDBC calls them through CallableStatement, a sub-interface of PreparedStatement that adds support for output parameters: values the procedure writes back to the caller.

Why would logic live in the database at all? Two honest reasons. First, some operations touch many rows and moving the logic to the data avoids shipping those rows across the network. Second, when several applications in different languages share one database, a procedure is the only place shared rules can live. The counter-arguments are just as real: procedural SQL is hard to unit test, hard to version alongside application code, and ties you to one vendor.

The call syntax is a JDBC escape sequence, in braces, which the driver translates into whatever the vendor expects.

  {call procedure_name(?, ?, ?)}          procedure, no return value
  {? = call function_name(?, ?)}          function, first ? is the return
       ^ registered as an OUT parameter

Each parameter is one of three kinds.

Kind	Direction	Setup required
IN	Caller to procedure	setXxx(index, value)
OUT	Procedure to caller	registerOutParameter(index, sqlType)
INOUT	Both	Both of the above

Here is a procedure that applies a discount and reports the new total.

-- PostgreSQL
CREATE PROCEDURE apply_discount(
        IN  p_order_id   BIGINT,
        IN  p_percent    NUMERIC,
        OUT p_new_total  NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE orders
       SET total = total * (1 - p_percent / 100)
     WHERE id = p_order_id;

    SELECT total INTO p_new_total FROM orders WHERE id = p_order_id;
END;
$$;

String call = "{call apply_discount(?, ?, ?)}";

try (Connection conn = dataSource.getConnection();
     CallableStatement cs = conn.prepareCall(call)) {

    cs.setLong(1, orderId);                              // IN
    cs.setBigDecimal(2, new BigDecimal("10"));           // IN
    cs.registerOutParameter(3, Types.NUMERIC);           // OUT: declare type first

    cs.execute();                                        // run the procedure

    BigDecimal newTotal = cs.getBigDecimal(3);           // read the OUT value
    System.out.println("new total " + newTotal);
}

Parameters can also be bound by name, which is far more readable when a procedure has eight of them, though support varies by driver.

cs.setLong("p_order_id", orderId);
cs.registerOutParameter("p_new_total", Types.NUMERIC);

A procedure may also return one or more result sets, in which case you read them as usual and, if there are several, advance with getMoreResults().

boolean hasResultSet = cs.execute();     // true if the first result is rows
while (hasResultSet) {
    try (ResultSet rs = cs.getResultSet()) {
        while (rs.next()) { ... }
    }
    hasResultSet = cs.getMoreResults();  // move to the next result, if any
}

Where stored procedures sit today. Most new systems keep business logic in the application, where it can be tested, reviewed, and deployed with the rest of the code, and use procedures sparingly for genuinely set-based work. You will still meet them constantly in established systems, so knowing CallableStatement is not optional.

Key Takeaways

• A stored procedure is named, precompiled code executing inside the database.
• CallableStatement extends PreparedStatement with OUT and INOUT parameter support.
• The call uses JDBC escape syntax, {call name(?, ?)}, translated by the driver.
• Every OUT parameter must be registered with its SQL type before execution.
• Results are read by index or, where supported, by parameter name.
• Procedures reduce network traffic and centralise rules but resist testing and version control, and bind you to a vendor.

🧪 Practice

1. Write a procedure that takes a customer id and returns their order count as an OUT parameter, then call it from Java.
2. Convert that procedure into a function and call it with the {? = call ...} form.
3. Write a procedure returning two result sets and read both using getMoreResults().
4. Interview: Give one argument for and one against moving an order-validation rule into a stored procedure. (Hint: think about who else writes to the same table, and about what your test suite can execute.)

Batch Updates for Bulk Operations

Inserting ten thousand rows one statement at a time is slow, and the reason is not the database. Each executeUpdate is a network round trip: send, wait, receive. At a latency of one millisecond, ten thousand inserts spend ten seconds waiting on the network no matter how fast the database is.

Batching collects statements on the client and sends them in one go.

  Without batching                      With batching
  ----------------                      -------------
  app ---- insert 1 ----> db            app  ---- 1000 inserts ----> db
  app <--- result 1 ----- db            app  <--- 1000 results ----- db
  app ---- insert 2 ----> db
  app <--- result 2 ----- db            one round trip, not 1000
  ... 10,000 times

The API is three methods: addBatch() to queue, executeBatch() to send, and clearBatch() to discard.

String sql = "INSERT INTO readings (sensor_id, taken_at, value) VALUES (?, ?, ?)";

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {

    conn.setAutoCommit(false);            // one transaction for the whole load

    int count = 0;
    for (Reading r : readings) {
        ps.setLong(1, r.sensorId());
        ps.setObject(2, r.takenAt());
        ps.setBigDecimal(3, r.value());
        ps.addBatch();                    // queue this row's parameters

        if (++count % 1000 == 0) {        // flush periodically: bound memory
            ps.executeBatch();
        }
    }
    ps.executeBatch();                    // send whatever remains
    conn.commit();
}

Three things in that example matter as much as addBatch itself. Auto-commit is off, so the load is one transaction rather than ten thousand; leaving it on forces a disk flush per statement and destroys the benefit. The batch is flushed every thousand rows, because an unbounded batch buffers every row's parameters in client memory. And the final executeBatch outside the loop sends the remainder, which is the most commonly forgotten line.

executeBatch() returns an int[] with one entry per queued statement.

Value	Meaning
>= 0	Rows affected by that statement
Statement.SUCCESS_NO_INFO	Succeeded; the driver does not know the count
Statement.EXECUTE_FAILED	That statement failed

Failure handling is the subtle part. If one statement in a batch fails, the driver may stop there or continue with the rest, and JDBC permits both. The failure arrives as BatchUpdateException, which carries the counts for the statements that were processed.

try {
    ps.executeBatch();
} catch (BatchUpdateException e) {
    int[] counts = e.getUpdateCounts();       // may be shorter than the batch
    for (int i = 0; i < counts.length; i++) {
        if (counts[i] == Statement.EXECUTE_FAILED) {
            log.error("statement {} in the batch failed", i);
        }
    }
    conn.rollback();                          // the transaction saves you here
    throw e;
}

Because the behaviour varies, the reliable pattern is to run the batch inside a transaction and roll the whole thing back on any failure, rather than trying to reason about which statements survived.

One driver-specific detail worth knowing: MySQL ignores batching unless you ask for it. Without rewriteBatchedStatements=true in the URL, its driver sends the statements individually and the speedup never appears.

  jdbc:mysql://localhost:3306/app?rewriteBatchedStatements=true

Realistic gains for a bulk insert are large enough to change designs: it is routine to go from a few hundred rows per second to tens of thousands.

Key Takeaways

• Batching exists to eliminate per-statement network round trips.
• addBatch() queues, executeBatch() sends, and the return array reports per-statement counts.
• Always disable auto-commit around a batch, or each statement commits separately.
• Flush every few hundred or thousand statements so client memory stays bounded.
• The final executeBatch() after the loop is required and easy to forget.
• BatchUpdateException carries partial counts; rolling back the transaction is the dependable response.
• MySQL needs rewriteBatchedStatements=true for batching to take effect.

🧪 Practice

1. Insert 50,000 rows one at a time and measure the elapsed time. Repeat with batches of 1,000 and compare.
2. Repeat the batched load with auto-commit left on and explain the difference you observe.
3. Deliberately violate a constraint on the 500th row of a batch, catch BatchUpdateException, and report which statements succeeded.
4. Add a batch-size parameter and chart throughput for sizes 1, 10, 100, 1000, and 10000. Identify where the gains flatten.
5. Interview: Why can a batch of 1,000,000 statements be slower than ten batches of 100,000? (Hint: what is held on the client between addBatch and executeBatch?)

Generated Keys and Metadata

Most tables have a surrogate primary key generated by the database: a sequence, an identity column, or an auto-increment. After inserting a row you almost always need that key, to insert child rows or to return the new resource's identifier.

The tempting approach is to insert and then query for the maximum id, which is wrong under concurrency: another transaction may have inserted between the two statements, and you get its key instead of yours. JDBC provides the correct mechanism, generated keys, which returns the key produced by your insert.

String sql = "INSERT INTO orders (customer_id, placed_at) VALUES (?, ?)";

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(
             sql, Statement.RETURN_GENERATED_KEYS)) {   // must ask at creation

    ps.setLong(1, customerId);
    ps.setObject(2, OffsetDateTime.now());
    ps.executeUpdate();

    try (ResultSet keys = ps.getGeneratedKeys()) {      // a normal ResultSet
        if (!keys.next()) {
            throw new IllegalStateException("no generated key returned");
        }
        long orderId = keys.getLong(1);                 // first generated column
    }
}

The request must be made when the statement is created; you cannot ask for keys afterwards. An alternative overload names the columns you want, which is more portable than relying on column 1 and is required on some drivers.

// Name the columns explicitly. Clearer and safer across vendors.
PreparedStatement ps = conn.prepareStatement(sql, new String[] { "id" });

Behaviour varies more than you would like. Some drivers return only the primary key, others every default-valued column; some return keys for a batch, others only for the last row. PostgreSQL offers a native alternative that sidesteps the whole question and returns anything you ask for.

INSERT INTO orders (customer_id, placed_at) VALUES (?, ?) RETURNING id, created_at

That is an ordinary query as far as JDBC is concerned, so you run it with executeQuery and read the result set.

Metadata is the second topic here, and it answers questions about the shape of data rather than its content. There are two kinds.

ResultSetMetaData describes the columns of a result you just obtained, which is what lets generic code handle a query it did not write.

ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
for (int i = 1; i <= columns; i++) {                     // 1-based again
    System.out.printf("%s : %s (%s), nullable=%d%n",
            md.getColumnLabel(i),      // the AS alias, or the column name
            md.getColumnTypeName(i),   // vendor type name, e.g. "varchar"
            md.getColumnClassName(i),  // Java class getObject will return
            md.isNullable(i));
}

This is exactly how a tool like a database browser or a generic CSV exporter works: no knowledge of the schema, just the metadata of whatever came back.

DatabaseMetaData describes the database itself: its tables, columns, keys, supported features, and version.

DatabaseMetaData meta = conn.getMetaData();

// Catalog, schema, table-name pattern, table types. null means "any".
try (ResultSet tables = meta.getTables(null, "public", "%", new String[]{"TABLE"})) {
    while (tables.next()) {
        System.out.println(tables.getString("TABLE_NAME"));
    }
}

try (ResultSet cols = meta.getColumns(null, "public", "orders", "%")) {
    while (cols.next()) {
        System.out.println(cols.getString("COLUMN_NAME") + " "
                         + cols.getString("TYPE_NAME"));
    }
}

Metadata queries are how schema migration tools, code generators, and object mappers discover what they are working with. They are also slow and should not appear on a request path.

Key Takeaways

• RETURN_GENERATED_KEYS must be requested when the statement is prepared.
• getGeneratedKeys() returns a result set holding the keys your insert produced.
• Selecting max(id) after an insert is a race condition, not an alternative.
• Naming the key columns explicitly is more portable than relying on index 1.
• PostgreSQL's RETURNING clause is a simpler, more capable option where available.
• ResultSetMetaData describes a result's columns and enables schema-agnostic code.
• DatabaseMetaData describes the database and underpins migration and code generation tools.

🧪 Practice

1. Insert an order and its three line items in one transaction, using the generated order id as the foreign key on each line.
2. Write a generic method that prints any ResultSet as aligned columns using only ResultSetMetaData.
3. List every table and its columns in a schema using DatabaseMetaData, and compare the output with the database's own catalogue view.
4. Implement the same insert using RETURNING id and compare the code with the getGeneratedKeys version.
5. Interview: Two users register at the same moment and one receives the other's account id. What implementation would produce that? (Hint: what happens between an insert and a separate query for the newest row?)

Connection Pooling with DataSource

16.1 established that opening a connection costs tens of milliseconds while a query costs one. A web application handling a hundred requests per second cannot pay that cost per request, and databases cannot accept thousands of concurrent connections anyway because each one consumes server memory and a process or thread.

A connection pool solves both problems by opening a small number of connections at startup and lending them out. A request borrows one, uses it, and returns it. The expensive setup happens once per connection, not once per request.

   Without a pool                     With a pool
   --------------                     -----------
   request -> open  (18 ms)           request -> borrow (~0.05 ms)
              query ( 1 ms)                      query  ( 1 ms)
              close ( 1 ms)                      return (~0.05 ms)
   --------------------------          ---------------------------
   20 ms, and the DB sees one          1.1 ms, and the DB sees a
   new session per request             fixed set of long-lived sessions

The pool is exposed through javax.sql.DataSource, the interface that should replace DriverManager in every application. It is a factory for connections, and crucially it hides where they come from.

// A DataSource is injected as an interface. Application code never knows
// whether it is pooled, nor which database it points at.
public class OrderRepository {
    private final DataSource dataSource;

    public OrderRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Optional<Order> findById(long id) throws SQLException {
        String sql = "SELECT id, customer_id, total FROM orders WHERE id = ?";
        try (Connection conn = dataSource.getConnection();     // borrow
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }   // close() returns the connection to the pool
    }
}

Note that the code is identical to the unpooled version. close() on a pooled connection is intercepted by the pool's proxy and returns it rather than closing the socket, which is why try-with-resources remains mandatory: a connection that is not closed is never returned, and a few leaks under load exhaust the pool and hang the application.

HikariCP is the de facto standard pool, chosen for being small and fast.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shopdb");
config.setUsername("app");
config.setPassword(System.getenv("DB_PASSWORD"));

config.setMaximumPoolSize(10);            // the single most important setting
config.setMinimumIdle(10);                // keep the pool a fixed size
config.setConnectionTimeout(3_000);       // ms to wait for a free connection
config.setIdleTimeout(600_000);           // ms before an idle connection closes
config.setMaxLifetime(1_800_000);         // ms before a connection is retired
config.setLeakDetectionThreshold(20_000); // ms held before a warning is logged

DataSource dataSource = new HikariDataSource(config);

Setting	What it controls	Typical value
maximumPoolSize	Concurrent connections allowed	10 to 20 per instance
minimumIdle	Connections kept ready	Equal to the maximum
connectionTimeout	Wait before giving up borrowing	2 to 5 seconds
maxLifetime	Retire connections before the server does	Under the DB's timeout
leakDetectionThreshold	Warn about connections held too long	20 to 60 seconds

Pools should be small, which is counter-intuitive. A database executes work on a limited number of cores and disks; beyond that point more concurrent connections mean more context switching and lock contention, not more throughput. A pool of ten usually outperforms a pool of a hundred. Size it from measurement, starting near cores * 2 on the database, and remember that the total across all application instances is what the server sees.

When the pool is exhausted, borrowing blocks until connectionTimeout elapses and then throws. That is the correct behaviour, and the symptom to recognise: requests suddenly taking exactly three seconds and then failing means a leak or a query holding connections far too long, not a slow network.

Key Takeaways

• A pool reuses a small set of long-lived connections instead of opening one per request.
• DataSource is the interface to depend on; DriverManager belongs in scripts and tests.
• Closing a pooled connection returns it, so try-with-resources is still required.
• A leaked connection is never returned, and enough leaks hang the application.
• Small pools usually outperform large ones, since the database's real parallelism is bounded.
• Set maxLifetime below the database's own idle timeout to avoid handing out dead connections.
• Timeouts on borrowing are the visible symptom of leaks and long-running queries.

🧪 Practice

1. Replace DriverManager with a HikariCP DataSource in an existing program and measure the change in request latency.
2. Write a handler that deliberately fails to close its connection, run it under load, and observe the pool exhausting.
3. Enable leakDetectionThreshold and confirm the warning identifies the leaking code path.
4. Benchmark identical load against pool sizes of 5, 10, 50, and 200 and plot throughput against size.
5. Interview: A service with maximumPoolSize=100 is slower than the same service with maximumPoolSize=10. Explain how that is possible. (Hint: what does the database do with 100 simultaneous requests for the same disk and the same locks?)

<a id="163-transactions-and-integrity"></a>

16.3 Transactions and Integrity

This subchapter covers the mechanism that keeps data consistent when operations span several statements and several users at once: how transactions are controlled from JDBC, what isolation levels actually buy you, and where transaction boundaries belong in a layered application.

Auto-Commit Mode and Manual Control

A transaction is a group of statements that succeed or fail as one. The standard example is a transfer: debit one account, credit another. If the process crashes between the two statements, money has vanished. A transaction makes that impossible, because until it commits, nothing it did is visible or permanent, and if it never commits, everything it did is undone.

Transactions are conventionally described by four properties, ACID.

Property	Guarantee
Atomicity	All statements take effect, or none do
Consistency	Constraints hold before and after; the database rejects violations
Isolation	Concurrent transactions do not see each other's partial work
Durability	Once committed, the change survives a crash

JDBC connections start in auto-commit mode, meaning every statement is wrapped in its own transaction and committed the moment it completes. That is convenient for single-statement work and catastrophic for multi-statement work, because it removes exactly the guarantee you need.

// BROKEN: auto-commit is on, so these are two independent transactions.
// A failure or crash between them leaves money destroyed.
stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
// <-- committed already; a crash here loses 100 permanently
stmt.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

Turning it off makes the connection wait for an explicit commit().

try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false);                 // begin a manual transaction

    try (PreparedStatement debit = conn.prepareStatement(
             "UPDATE accounts SET balance = balance - ? WHERE id = ?");
         PreparedStatement credit = conn.prepareStatement(
             "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

        debit.setBigDecimal(1, amount);
        debit.setLong(2, fromId);
        debit.executeUpdate();

        credit.setBigDecimal(1, amount);
        credit.setLong(2, toId);
        credit.executeUpdate();

        conn.commit();                         // both take effect, atomically
    } catch (SQLException e) {
        conn.rollback();                       // neither takes effect
        throw e;
    }
}

There is no beginTransaction() in JDBC. Setting auto-commit to false is the begin, and a transaction implicitly starts again after each commit() or rollback().

  setAutoCommit(false)
        |
        v
   [ transaction open ]  --- statements ---+
        |                                  |
        +--- commit()   --> durable -------+--> new transaction opens
        |                                  |
        +--- rollback() --> discarded -----+

Two failure modes are worth naming. Forgetting commit() means the work is silently discarded when the connection closes or returns to the pool, which presents as "the insert ran but the row is not there". And leaving auto-commit off on a pooled connection leaks that state to the next borrower, which is why every pool resets it on return. Restoring it yourself is good manners in code that does not control the pool.

boolean previous = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
    ...
    conn.commit();
} finally {
    conn.setAutoCommit(previous);      // restore whatever the caller had
}

Keep transactions short. An open transaction holds locks and prevents the database from cleaning up old row versions. Never make an HTTP call, wait on a queue, or prompt a user inside one.

Key Takeaways

• A transaction makes several statements atomic: all of them apply, or none.
• ACID names the four guarantees: atomicity, consistency, isolation, durability.
• JDBC connections default to auto-commit, one transaction per statement.
• setAutoCommit(false) is how a transaction begins; there is no explicit begin method.
• A missing commit() silently discards the work.
• Restore auto-commit before releasing a connection you did not create.
• Hold transactions open for as little time as possible, and never across network calls to other systems.

🧪 Practice

1. Implement a transfer between two accounts with auto-commit on, kill the process between the two updates, and inspect the balances.
2. Repeat with auto-commit off and a rollback on failure, and confirm the balances are unchanged.
3. Write code that inserts a row and returns without committing, then query for the row from another connection and explain the result.
4. Interview: A developer reports that their insert "works in the test but the row disappears". What would you check first? (Hint: what happens to an uncommitted transaction when the connection is returned to a pool?)

Commit, Rollback, and Savepoints

commit() and rollback() are all-or-nothing for the whole transaction, which is usually what you want. Sometimes it is too coarse. Consider importing a thousand records where a handful are malformed: rolling back the entire import because of three bad rows wastes the other nine hundred and ninety-seven.

A savepoint is a named marker inside a transaction that you can roll back to, undoing everything after it while keeping everything before it.

  BEGIN
    insert A            <-- kept
    SAVEPOINT s1 -------+
    insert B            |   rollback(s1) undoes only this
    insert C  (fails)   |
    rollback(s1) -------+
    insert D            <-- kept
  COMMIT                    A and D are durable; B and C never happened

conn.setAutoCommit(false);
int imported = 0, skipped = 0;

for (Record record : records) {
    Savepoint sp = conn.setSavepoint("row");      // marker before this row
    try {
        insert(conn, record);
        imported++;
        conn.releaseSavepoint(sp);                // discard the marker
    } catch (SQLException e) {
        conn.rollback(sp);                        // undo this row only
        skipped++;
        log.warn("skipped {}: {}", record.id(), e.getMessage());
    }
}
conn.commit();                                    // everything kept is durable

releaseSavepoint matters in long loops: each savepoint consumes server resources, and thousands of unreleased ones are a real cost. Note also that on PostgreSQL any error aborts the transaction until you roll back to a savepoint, so the pattern above is not an optimisation there, it is the only way to continue after a failed statement.

The three savepoint operations:

Method	Effect
setSavepoint()	Creates an unnamed marker and returns it
setSavepoint(String)	Creates a named marker, easier to read in logs
rollback(Savepoint)	Undoes work after the marker; transaction stays open
releaseSavepoint(sp)	Discards the marker; work done since it is unaffected

Rollback is not free and it is not magic. It undoes database changes only. Anything else the code did inside the transaction persists: files written, emails sent, messages published, in-memory caches updated. This is the single most common source of inconsistency in systems that look transactional.

conn.setAutoCommit(false);
try {
    orderRepository.insert(conn, order);
    emailService.sendConfirmation(order);     // WRONG: cannot be rolled back
    conn.commit();
} catch (SQLException e) {
    conn.rollback();     // the order is gone; the customer still got the email
    throw e;
}

The fix is to move side effects after the commit, or to record the intent in the same transaction and let a separate process act on it afterwards.

conn.setAutoCommit(false);
try {
    orderRepository.insert(conn, order);
    outbox.enqueue(conn, new ConfirmationEmail(order));  // same transaction
    conn.commit();
} catch (SQLException e) {
    conn.rollback();     // the intent to email is rolled back with the order
    throw e;
}
// a separate worker reads the outbox and sends, after the commit

One last rule: always roll back on any exception, not just SQLException. A NullPointerException halfway through leaves a transaction open holding locks until the connection is reclaimed.

Key Takeaways

• commit() makes the whole transaction durable; rollback() discards it.
• A savepoint marks a point you can partially roll back to without ending the transaction.
• Partial rollback lets a bulk import skip bad records while keeping good ones.
• Release savepoints in long loops to avoid accumulating server-side state.
• On PostgreSQL, a failed statement aborts the transaction unless a savepoint is used.
• Rollback undoes database work only; emails, files, and messages are not reversed.
• Defer side effects until after commit, or record them transactionally in an outbox.

🧪 Practice

1. Import ten records where the fourth violates a constraint, using savepoints so the other nine are committed.
2. Show that without savepoints on PostgreSQL, statements after the failure raise "current transaction is aborted".
3. Write a transaction that inserts a row and then throws a RuntimeException, and confirm the row is absent only if you roll back in a catch (Exception) rather than catch (SQLException).
4. Restructure a method that sends an email mid-transaction so that the email cannot be sent for a rolled-back order.
5. Interview: An order-placement endpoint occasionally sends a confirmation for an order that does not exist. What design would cause that, and how would you fix it? (Hint: which of the two actions can be undone?)

Isolation Levels and Concurrency Anomalies

Isolation is the "I" in ACID, and it is the only one of the four that comes with a dial. Perfect isolation, where concurrent transactions behave exactly as if they ran one after another, is expensive: it requires locking that blocks other work. So SQL defines four levels, each permitting more anomalies in exchange for more concurrency.

To choose a level you need the vocabulary of what can go wrong.

Dirty read: reading a row another transaction has modified but not yet committed. If that transaction rolls back, you acted on data that never existed.

  T1: UPDATE accounts SET balance = 0 WHERE id = 7
  T2:                     SELECT balance FROM accounts WHERE id = 7  --> 0
  T1: ROLLBACK                                                    (was never 0)

Non-repeatable read: reading the same row twice in one transaction and getting different values, because another transaction committed in between.

  T1: SELECT balance FROM accounts WHERE id = 7   --> 500
  T2: UPDATE accounts SET balance = 100 WHERE id = 7 ; COMMIT
  T1: SELECT balance FROM accounts WHERE id = 7   --> 100   (changed underneath)

Phantom read: re-running the same query and getting different rows, because another transaction inserted or deleted rows matching the condition.

  T1: SELECT count(*) FROM orders WHERE total > 100   --> 12
  T2: INSERT INTO orders (total) VALUES (250) ; COMMIT
  T1: SELECT count(*) FROM orders WHERE total > 100   --> 13   (a new row appeared)

Lost update: two transactions read the same value, both compute a new one from it, and the second write overwrites the first.

  T1: read stock = 10
  T2: read stock = 10
  T1: write stock = 9   (sold one)
  T2: write stock = 9   (sold one)   --> two sales, one unit deducted

The four levels and what each permits:

Level	Dirty read	Non-repeatable read	Phantom read	Cost
READ_UNCOMMITTED	Possible	Possible	Possible	Lowest
READ_COMMITTED	Prevented	Possible	Possible	Low
REPEATABLE_READ	Prevented	Prevented	Possible*	Moderate
SERIALIZABLE	Prevented	Prevented	Prevented	Highest

*The standard permits phantoms at REPEATABLE_READ; PostgreSQL's snapshot-based implementation prevents them anyway, which is a good illustration of how much real behaviour differs from the standard.

Setting the level in JDBC is one call, made before the transaction's first statement.

conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
try {
    // every read in this transaction sees a consistent snapshot
    ...
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

Defaults differ by vendor, which is a frequent source of behaviour that changes when a system is migrated.

Database	Default level
PostgreSQL	READ_COMMITTED
Oracle	READ_COMMITTED
SQL Server	READ_COMMITTED
MySQL/InnoDB	REPEATABLE_READ

SERIALIZABLE does not mean "no errors". On PostgreSQL it is implemented optimistically: transactions run without extra blocking, and one is aborted with SQL state 40001 if the engine detects that the outcome could not have arisen from any serial order. Using it therefore requires a retry loop.

for (int attempt = 1; attempt <= 3; attempt++) {
    try {
        runSerializableTransaction();
        return;
    } catch (SQLException e) {
        if (!"40001".equals(e.getSQLState()) || attempt == 3) throw e;
        Thread.sleep(50L * attempt);     // back off and try the whole thing again
    }
}

The practical guidance is unglamorous: stay on the default, usually READ_COMMITTED, and solve specific problems with specific tools. Raise the level only for a transaction that demonstrably needs it, and handle lost updates with the locking strategies in the next topic rather than by turning the dial up globally.

Key Takeaways

• Isolation trades concurrency against the anomalies a transaction may observe.
• Dirty reads see uncommitted data; non-repeatable reads see a row change; phantoms see the row set change.
• A lost update happens when two read-modify-write cycles overlap.
• The four levels form a ladder from READ_UNCOMMITTED to SERIALIZABLE.
• Defaults differ by vendor, so behaviour can change on migration.
• PostgreSQL's SERIALIZABLE aborts conflicting transactions with state 40001 and needs a retry loop.
• Keep the default and address concrete problems with locking rather than raising the level everywhere.

🧪 Practice

1. Open two connections and reproduce a non-repeatable read at READ_COMMITTED, then show it disappears at REPEATABLE_READ.
2. Reproduce a lost update with two transactions that both read stock, subtract one, and write it back.
3. Print the default isolation level for two different databases and explain what would change for an application moved between them.
4. Implement a retry loop for 40001 and prove it works by forcing a serialization conflict.
5. Interview: Why can raising every transaction to SERIALIZABLE reduce throughput without removing all bugs? (Hint: what new failure does the application now have to handle, and what happens if it does not?)

Optimistic and Pessimistic Locking

The lost update from the previous topic is the defining concurrency problem in application code: two users load the same record, both edit it, and the second save silently discards the first. Isolation levels do not solve it by themselves, because the two transactions may not even overlap in time. Two strategies do, and choosing between them is a design decision about how often conflicts actually occur.

Pessimistic locking assumes conflict is likely. It takes a lock when reading and holds it until the transaction ends, so nobody else can modify the row in the meantime. In SQL this is SELECT ... FOR UPDATE.

conn.setAutoCommit(false);

// FOR UPDATE takes a row-level write lock. Any other transaction trying to
// read this row FOR UPDATE, or to update it, blocks until this one ends.
String sql = "SELECT stock FROM products WHERE id = ? FOR UPDATE";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setLong(1, productId);
    try (ResultSet rs = ps.executeQuery()) {
        rs.next();
        int stock = rs.getInt("stock");
        if (stock < quantity) throw new OutOfStockException(productId);
    }
}

try (PreparedStatement ps = conn.prepareStatement(
        "UPDATE products SET stock = stock - ? WHERE id = ?")) {
    ps.setInt(1, quantity);
    ps.setLong(2, productId);
    ps.executeUpdate();
}
conn.commit();      // lock released here

Add NOWAIT to fail immediately instead of blocking, or SKIP LOCKED to ignore locked rows, which is the standard way to build a work queue in a database.

SELECT id FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED

Optimistic locking assumes conflict is rare. It takes no lock. Instead each row carries a version number, and an update only succeeds if the version is still what the reader saw. If it is not, somebody else has written and the update affects zero rows.

  User A reads product 7, version 3        User B reads product 7, version 3
  A writes: WHERE id=7 AND version=3       B writes: WHERE id=7 AND version=3
     --> 1 row updated, version becomes 4      --> 0 rows updated: conflict
                                               B must reload and retry

String sql = """
        UPDATE products
           SET stock = ?, version = version + 1
         WHERE id = ? AND version = ?
        """;                                  // version is the guard

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setInt(1, newStock);
    ps.setLong(2, product.id());
    ps.setLong(3, product.version());         // the version we read earlier

    if (ps.executeUpdate() == 0) {            // zero rows means someone else won
        throw new OptimisticLockException(product.id());
    }
}

The zero-row result is the entire mechanism, and the caller's response is normally to reload the record and either retry automatically or tell the user the data changed.

Aspect	Pessimistic (FOR UPDATE)	Optimistic (version column)
Cost when no conflict	Lock overhead on every read	None
Cost when conflict	Waiting	Wasted work, then a retry
Failure mode	Blocking, timeouts, deadlocks	0 rows updated
Works across requests	No: locks die with the transaction	Yes: the version travels with the data
Best for	Short transactions, contended rows	Web forms, low contention

The row about spanning requests is the decisive one for web applications. A user loading an edit form, making coffee, and saving ten minutes later cannot hold a database lock for those ten minutes. Optimistic locking works because the version is carried in the form and checked at save time.

Deadlocks are the hazard of pessimistic locking: two transactions each holding a lock the other needs. The database detects the cycle and kills one with a serialization or deadlock error.

  T1: lock row A  ------------> wants row B
                                     ^
  T2: lock row B  ------------> wants row A
                (cycle: the database aborts one of them)

The usual mitigation is consistent lock ordering: if every transaction locks accounts in ascending id order, no cycle can form. Combine that with short transactions and a retry for the aborted victim.

Key Takeaways

• Lost updates occur when two read-modify-write cycles overlap, and need an explicit strategy.
• Pessimistic locking blocks others with SELECT ... FOR UPDATE for the life of the transaction.
• NOWAIT and SKIP LOCKED refine that behaviour, the latter underpinning database work queues.
• Optimistic locking adds a version column and guards updates with it, detecting conflict by a zero-row result.
• Only optimistic locking works across separate requests, which is why web applications use it.
• Pessimistic locking risks deadlocks; consistent lock ordering prevents the cycles.

🧪 Practice

1. Add a version column to a table and implement optimistic update checking, then prove two concurrent updaters cannot both succeed.
2. Implement the same protection with SELECT ... FOR UPDATE and compare the two under a load test with high and low contention.
3. Build a job queue using FOR UPDATE SKIP LOCKED and run three workers against it, confirming no job is processed twice.
4. Construct a deadlock with two transactions locking two rows in opposite orders, capture the error, and fix it by ordering the locks.
5. Interview: A wiki loses edits when two people edit the same page. Which strategy fits, and what does the user see when a conflict occurs? (Hint: how long is the gap between reading the page and saving it?)

Transaction Boundaries in Layered Code

Knowing how to commit is easy. Knowing where to commit is the design question, and getting it wrong produces systems that are correct in every unit test and inconsistent in production.

The rule is: a transaction boundary belongs at the boundary of a business operation, not at the boundary of a database call. "Place an order" is one operation and therefore one transaction, even though it inserts an order, three line items, and a stock adjustment.

The most common anti-pattern puts a transaction inside each repository method.

// WRONG: each method opens its own connection and commits independently.
public class OrderService {
    public void placeOrder(Order order) {
        orderRepository.insert(order);        // transaction 1: commits
        for (Line line : order.lines()) {
            lineRepository.insert(line);      // transaction 2..n: commit
        }
        stockRepository.decrement(order);     // transaction n+1: may fail
    }                                         // leaves an order with no stock taken
}

Each call is atomic on its own, and the operation as a whole is not atomic at all. A failure in decrement leaves a committed order whose stock was never reduced.

The fix is to let the service layer own the transaction and have repositories participate in one they are given.

  +---------------------------------------------------+
  |  Controller / handler    no transaction knowledge  |
  +---------------------------------------------------+
  |  Service    <-- transaction begins and ends HERE   |
  +---------------------------------------------------+
  |  Repository   uses the connection it is given      |
  +---------------------------------------------------+
  |  Database                                           |
  +---------------------------------------------------+

Passing the connection explicitly is the plainest way to express this.

public class OrderService {
    private final DataSource dataSource;
    private final OrderRepository orders;
    private final StockRepository stock;

    public void placeOrder(Order order) throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);
            try {
                long id = orders.insert(conn, order);      // same connection
                orders.insertLines(conn, id, order.lines());
                stock.decrement(conn, order.lines());       // same transaction
                conn.commit();                              // one boundary
            } catch (Exception e) {
                conn.rollback();
                throw e;
            }
        }
    }
}

The connection parameter on every repository method is explicit and noisy, which is why frameworks hide it. Spring's @Transactional binds a connection to the current thread so repositories can obtain "the current transaction's connection" without it being passed, and 17.3 covers the mechanics. The design is identical; only the plumbing differs.

Four rules make boundaries behave in practice.

Do not nest transactions accidentally. A service method that calls another transactional service method must not start a second transaction; it should join the existing one. This is exactly the problem propagation settings address in Spring.

Keep remote calls outside the boundary. A payment gateway call inside a transaction holds locks for the duration of someone else's network latency, and cannot be rolled back anyway.

// The external call happens first, outside the transaction.
PaymentResult payment = paymentGateway.charge(card, amount);   // slow, external
try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false);
    orders.insert(conn, order.withPayment(payment.reference()));
    conn.commit();                                             // fast, local
}

Read-only work should say so. Marking a transaction read-only lets the database and pool skip bookkeeping, and documents intent.

conn.setReadOnly(true);    // a hint: some drivers route to a replica

Let exceptions cross the boundary. The service decides to roll back; the layer above decides what the user sees. A repository that catches and logs a SQLException without rethrowing hides the very failure that should have triggered the rollback.

Key Takeaways

• A transaction should span one business operation, not one database call.
• Repositories must participate in a transaction, never start their own.
• The service layer is the natural place to open, commit, and roll back.
• Passing the connection explicitly makes the boundary visible; frameworks hide it but keep the design.
• Never call an external system inside a transaction; do it before or after.
• Mark read-only work as read-only to save bookkeeping and express intent.
• Swallowing exceptions in a repository defeats rollback entirely.

🧪 Practice

1. Refactor a service whose repository methods each commit so that one transaction spans the whole operation.
2. Write a test that makes the last step of a multi-step operation fail and asserts that nothing was persisted.
3. Move an external HTTP call out of a transaction and describe what improves, naming the resource that is no longer held.
4. Sketch the method signatures a repository needs when the connection is passed explicitly, and then when a framework binds it to the thread.
5. Interview: Where would you place the transaction boundary for "transfer funds, then notify both account holders", and why? (Hint: which of those three actions can be undone by a rollback?)

<a id="164-object-relational-mapping"></a>

16.4 Object-Relational Mapping

This subchapter covers why objects and tables fit together badly, the pattern that isolates data access from the rest of an application, and the mapping tools built on JDBC that automate most of the translation, along with the performance traps they introduce.

The Object-Relational Impedance Mismatch

Object-oriented programs and relational databases are both excellent models, and they disagree about almost everything. "Impedance mismatch" is borrowed from electrical engineering, where connecting two circuits with different impedance loses power at the junction. Connecting objects to tables loses something similar, and every mapping tool is an attempt to minimise the loss.

Five specific disagreements account for most of the difficulty.

Concern	Object model	Relational model
Identity	Reference equality plus equals	Primary key value
Structure	Nested objects, arbitrary graphs	Flat rows joined by foreign keys
Inheritance	Built into the type system	No concept of it at all
Association	Directional references, collections	Symmetric foreign keys and join tables
Navigation	Follow a reference, then another	Join everything needed in one query

Identity. Java has three notions: the same reference, equal by equals, and the same database row. A row loaded twice in one program can produce two objects that are equals but not ==, or two objects representing one row with divergent state. Mapping tools solve this with an identity map, guaranteeing one object per row per session.

Structure and granularity. An Order containing a list of OrderLine objects and an embedded Address is one concept in Java and three or four tables in SQL. Reassembling one object from several rows is the mapper's core job.

  Object graph                        Relational tables
  ------------                        -----------------
  Order                               orders
   +-- customer: Customer              id | customer_id | shipping_...
   +-- lines: List<OrderLine>         ------------------------------
   |     +-- product: Product          order_lines
   +-- shippingAddress: Address         id | order_id | product_id | qty
        (no table of its own)          ------------------------------
                                       customers, products

Inheritance has no relational equivalent. If CreditCardPayment and BankTransferPayment both extend Payment, there are three ways to store them, each with real costs.

Strategy	Layout	Trade-off
Single table	One table, a discriminator column, nullable columns for every subtype	Fast, no joins; cannot enforce NOT NULL on subtype fields
Joined table	A base table plus one table per subtype	Properly normalised; every read is a join
Table per class	One complete table per concrete subtype	No joins; querying the base type needs a union, and keys must be shared

Navigation differs fundamentally. In Java you write order.customer().address().city(), following one reference at a time. Done naively against a database that is three queries, and inside a loop over a hundred orders it is hundreds of queries. SQL wants one join. This single difference produces the N+1 problem covered at the end of this subchapter.

The mismatch cannot be eliminated, only managed. There are three honest responses, and mature systems use more than one.

• Write SQL by hand with JDBC or a thin helper. Full control, no surprises, more code.
• Use a mapper such as Hibernate. Far less code for ordinary cases, at the price of understanding what it generates.
• Stop mapping and store documents or use a database whose model matches the data, when the data genuinely is not relational.

The mistake is believing a tool makes the mismatch disappear. It moves it: the questions become "which fetch strategy" and "why did that generate 200 queries", which are the same old questions in new clothes.

Key Takeaways

• Objects and relations model identity, structure, inheritance, association, and navigation differently.
• One object often spans several tables, so reassembly is the mapper's central job.
• Relational databases have no inheritance; the three mapping strategies each trade normalisation against join cost.
• Reference-by-reference navigation conflicts with set-based joins and is the root of N+1 queries.
• Mapping tools manage the mismatch rather than removing it.

🧪 Practice

1. Model an Order with lines, a customer, and an embedded address, then draw the tables required and mark where one object spans several rows.
2. Write the DDL for a Payment hierarchy under all three inheritance strategies and list one query each makes awkward.
3. Load the same row twice into two objects, modify one, and describe what the program now believes about the row.
4. Interview: Name two things an object model expresses that a relational schema cannot, and say how each is usually worked around. (Hint: consider subtypes and the direction of a reference.)

The Data Access Object Pattern

Scattering JDBC across an application couples business logic to the database. A service that opens connections, writes SQL, and reads result sets cannot be tested without a database, cannot be reused against a different store, and mixes two entirely different concerns in one method.

The Data Access Object (DAO) pattern, also called the Repository pattern in domain-driven designs, puts all persistence for one type behind an interface expressed in domain terms.

  Service layer       "save this order"       knows nothing about SQL
        |
        v
  +-------------------+
  | OrderRepository   |   interface: findById, save, findByCustomer
  +-------------------+
        ^         ^
        |         |
  JdbcOrderRepo   InMemoryOrderRepo        one for production, one for tests
        |
        v
    database

The interface speaks about orders, not about rows.

public interface OrderRepository {
    Optional<Order> findById(long id);
    List<Order> findByCustomer(long customerId);
    long save(Order order);
    void delete(long id);
}

Notice what is absent: no Connection, no SQLException, no SQL. The interface is a contract the rest of the application can depend on, and the implementation is free to satisfy it with JDBC, with an ORM, or with a hash map in a test.

public class JdbcOrderRepository implements OrderRepository {

    private static final String FIND_BY_ID =
            "SELECT id, customer_id, total, placed_at FROM orders WHERE id = ?";

    private final DataSource dataSource;

    public JdbcOrderRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Optional<Order> findById(long id) {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(FIND_BY_ID)) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
            }
        } catch (SQLException e) {
            // Translate at the boundary: callers never import java.sql.
            throw new DataAccessException("finding order " + id, e);
        }
    }

    // One place that knows how a row becomes an Order.
    private Order mapRow(ResultSet rs) throws SQLException {
        return new Order(
                rs.getLong("id"),
                rs.getLong("customer_id"),
                rs.getBigDecimal("total"),
                rs.getObject("placed_at", OffsetDateTime.class));
    }
}

Four design points are doing the work here.

The row mapper is extracted. mapRow is the single place that knows the column-to-field correspondence, so a schema change touches one method rather than every query.

Exceptions are translated. SQLException is caught and rethrown as an unchecked domain exception, so the service layer never handles SQL states and never imports java.sql. This is the boundary discussed in 16.1.

SQL is a constant, not an inline string. Named constants make the statements greppable and keep methods readable.

Transactions are not started here. As 16.3 established, boundaries belong in the service layer, so methods that participate in a larger operation take a Connection parameter.

// Two overloads: one standalone, one participating in a caller's transaction.
public long save(Order order) {
    try (Connection conn = dataSource.getConnection()) {
        return save(conn, order);
    } catch (SQLException e) {
        throw new DataAccessException("saving order", e);
    }
}

public long save(Connection conn, Order order) throws SQLException { ... }

The payoff is testability. A service depending on OrderRepository can be tested against an in-memory implementation in microseconds, while the JDBC implementation is tested separately against a real database, typically in a container.

class OrderServiceTest {
    @Test
    void rejectsOrderBelowMinimum() {
        OrderRepository repo = new InMemoryOrderRepository();   // no database
        OrderService service = new OrderService(repo);

        assertThrows(OrderTooSmallException.class,
                () -> service.place(anOrderTotalling("1.00")));
    }
}

A caution: do not let the interface leak the database back in. A method named findByCustomerIdJoinLinesOrderByPlacedAtDesc is SQL spelled in Java. Keep the vocabulary of the domain.

Key Takeaways

• A DAO hides all persistence for one type behind a domain-shaped interface.
• The interface exposes no Connection, no SQL, and no SQLException.
• A single row-mapping method localises knowledge of the schema.
• Translate SQLException into an unchecked domain exception at this boundary.
• Offer an overload taking a Connection so the DAO can join a caller's transaction.
• The main payoff is testing services without a database.
• Method names should describe domain questions, not the SQL that answers them.

🧪 Practice

1. Define a CustomerRepository interface and implement it with JDBC, keeping all SQL inside the implementation.
2. Write an in-memory implementation of that interface and use it to test a service class with no database running.
3. Extract a reusable RowMapper<T> functional interface and rewrite two queries to use it.
4. Add a save(Connection, Customer) overload and use it from a service method that saves a customer and an address in one transaction.
5. Interview: What signals that a repository interface has leaked persistence concerns into the domain? (Hint: read the method names aloud and ask whether a domain expert would recognise them.)

Introduction to JPA and Hibernate

The DAO pattern organises JDBC but does not reduce it. Every entity still needs its mapping method, its insert, its update, and its select, and most of that code is mechanical. Object-relational mapping tools automate it.

Hibernate is the ORM that popularised this in Java. JPA, the Jakarta Persistence API, is the standard it inspired: a set of interfaces and annotations, with Hibernate, EclipseLink, and others as implementations. The relationship mirrors JDBC and its drivers.

   Your code
      |  annotations and EntityManager      (jakarta.persistence)
      v
   +--------------------------------------------+
   |            JPA specification                |
   +--------------------------------------------+
      |                    |
      v                    v
   Hibernate          EclipseLink        implementations
      |                    |
      +---------+----------+
                v
              JDBC  ->  driver  ->  database

ORMs sit on top of JDBC. They do not replace it; they generate the SQL and call the same PreparedStatement you would have written.

The central object is the EntityManager, obtained from an EntityManagerFactory configured once at startup.

EntityManagerFactory emf = Persistence.createEntityManagerFactory("shop");
EntityManager em = emf.createEntityManager();

em.getTransaction().begin();

Order order = new Order(customer, OffsetDateTime.now());
order.addLine(new OrderLine(product, 2));

em.persist(order);                  // no INSERT is written by you

em.getTransaction().commit();       // Hibernate issues the INSERTs here
em.close();

The contrast with the JDBC version is the point:

Operation	JDBC	JPA
Insert	Write SQL, bind parameters, read generated keys	em.persist(entity)
Find by key	Write SQL, execute, map the row	em.find(Order.class, id)
Update	Write SQL, bind changed columns	Modify the object; it is detected
Delete	Write SQL, bind the key	em.remove(entity)
Query	SQL, mapped by hand	JPQL, mapped automatically

The update row is the one that surprises people. Inside a transaction, changing a managed object's field is enough; the provider compares it against the state it loaded and issues an UPDATE at flush time. This is dirty checking, and the next topics explain the machinery behind it.

Queries are written in JPQL, which looks like SQL but names entities and fields rather than tables and columns.

// Entity name 'Order', field 'placedAt' -- not table and column names.
List<Order> recent = em.createQuery(
        "SELECT o FROM Order o WHERE o.customer.id = :cid AND o.placedAt > :since",
        Order.class)
    .setParameter("cid", customerId)      // named parameters, still bound safely
    .setParameter("since", cutoff)
    .getResultList();

What you give up is worth stating plainly. SQL is generated, so you must be able to read the generated SQL to diagnose performance. Behaviour depends on whether an object is managed, which is new state to reason about. And the learning curve is real: lazy loading, flush timing, and cascade rules all bite before they help.

A pragmatic position, common in production systems: use JPA for the routine create-read-update-delete work that is most of an application, and drop to SQL, through a native query or a JDBC template, for reporting and anything performance-critical. The two coexist happily on the same connection and transaction.

Key Takeaways

• JPA is the specification; Hibernate and EclipseLink are implementations.
• ORMs generate SQL and run it through JDBC rather than replacing JDBC.
• EntityManager is the entry point, obtained from a factory built at startup.
• persist, find, and remove replace hand-written statements, and updates are detected automatically.
• JPQL queries name entities and fields, not tables and columns.
• The cost is indirection: you must read generated SQL and track entity state.
• Mixing JPA for routine work with SQL for reporting is normal and healthy.

🧪 Practice

1. Configure a JPA persistence unit against an in-memory H2 database and persist one entity.
2. Enable SQL logging and record how many statements a single persist of a parent with three children produces.
3. Write the same "find orders for a customer since a date" query in JDBC and in JPQL, and compare the amount of code.
4. Load an entity, change a field inside a transaction without calling any save method, and confirm the change reaches the database.
5. Interview: If an ORM generates the SQL, why should a developer still be able to read SQL? (Hint: what do you do when a page that should take 50 milliseconds takes 5 seconds?)

Entities, Identifiers, and Relationships

An entity is a class whose instances correspond to rows in a table. In JPA it is an ordinary class with annotations describing that correspondence, and the requirements are modest: the @Entity annotation, an identifier, and a no-argument constructor the provider uses when building instances from rows.

@Entity
@Table(name = "orders")                       // needed: "order" is a SQL keyword
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)   // the database assigns it
    private Long id;

    @Column(name = "placed_at", nullable = false)
    private OffsetDateTime placedAt;

    @Column(precision = 12, scale = 2)        // maps to NUMERIC(12,2)
    private BigDecimal total;

    @Enumerated(EnumType.STRING)              // store the name, never the ordinal
    private OrderStatus status;

    protected Order() { }                     // required by JPA, not by callers

    public Order(OffsetDateTime placedAt) { this.placedAt = placedAt; }
}

@Enumerated(EnumType.STRING) deserves the comment it has. The default, ORDINAL, stores the enum's position, so inserting a new constant in the middle silently reinterprets every existing row.

Identifier generation has four strategies, and the choice affects batching and performance more than it first appears.

Strategy	Mechanism	Note
IDENTITY	Auto-increment column; the key is known only after insert	Disables JDBC batching of inserts
SEQUENCE	Database sequence, read before insert	Preferred where supported; allows batching
TABLE	A dedicated table simulating a sequence	Portable, slow, contended
AUTO	The provider chooses	Convenient, but hides the real behaviour

SEQUENCE is the default recommendation on PostgreSQL and Oracle precisely because the provider can fetch keys in advance and batch the inserts, which IDENTITY prevents.

Relationships are where entities stop being simple. Four annotations cover the cardinalities.

  @OneToOne     Order  ------- 1:1 -------  Invoice
  @ManyToOne    OrderLine  ---- N:1 ------  Order      (FK lives here)
  @OneToMany    Order  ------- 1:N -------  OrderLine
  @ManyToMany   Product  ----- N:M -------  Category   (join table)

The most important is @ManyToOne, because it is the side that owns the foreign key and therefore the side that controls what is written.

@Entity
public class OrderLine {

    @ManyToOne(fetch = FetchType.LAZY)        // ALWAYS lazy: the default is EAGER
    @JoinColumn(name = "order_id", nullable = false)   // the FK column
    private Order order;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "product_id")
    private Product product;

    private int quantity;
}

@Entity
public class Order {
    // mappedBy names the field on the OTHER side that owns the foreign key.
    // Without it, JPA assumes a join table and creates one.
    @OneToMany(mappedBy = "order",
               cascade = CascadeType.ALL,      // persist/remove lines with the order
               orphanRemoval = true)           // removing from the list deletes the row
    private List<OrderLine> lines = new ArrayList<>();

    // Keep both sides of the relationship consistent in one place.
    public void addLine(OrderLine line) {
        lines.add(line);
        line.setOrder(this);         // without this, order_id is written as NULL
    }
}

Three rules prevent most relationship bugs.

Make every association lazy. @ManyToOne and @OneToOne default to EAGER, meaning every load of an OrderLine also loads its Order and its Product, whether or not you touch them. Override both.

Set both sides. Java references are directional; a foreign key is not. If you add a line to the list but never set its order field, the owning side sees null and the database gets null. A helper method like addLine is the standard fix.

Understand cascade and orphanRemoval. Cascade propagates operations from parent to children, which is right for lines that cannot exist without their order and wrong for a Product shared by many lines. CascadeType.ALL on a shared entity will eventually delete something it should not.

Finally, equals and hashCode on entities are genuinely tricky, because the identifier is null before persisting and non-null afterwards. Basing them on the id breaks any HashSet an entity was added to before saving. The safe options are to use a business key, or to assign a UUID in the constructor.

Key Takeaways

• An entity needs @Entity, an @Id, and a no-argument constructor.
• Store enums with EnumType.STRING; ordinals break when constants are reordered.
• SEQUENCE generation allows insert batching; IDENTITY disables it.
• @ManyToOne owns the foreign key and controls what is written.
• mappedBy marks the inverse side; omitting it produces an unwanted join table.
• Override the EAGER default on @ManyToOne and @OneToOne.
• Keep both sides of a bidirectional association in sync with a helper method.
• Cascade suits parent-child ownership and corrupts shared references.

🧪 Practice

1. Map Order and OrderLine with a bidirectional one-to-many, then save an order with two lines and inspect the generated SQL.
2. Remove the line.setOrder(this) call and observe what is written to the foreign key column.
3. Store an enum with the default ORDINAL, add a new constant at the start, and show how existing rows change meaning.
4. Compare the SQL generated when inserting 100 entities with IDENTITY against SEQUENCE generation.
5. Interview: Why is implementing equals on an entity using its generated id dangerous? (Hint: what is the id's value between construction and commit, and what happens to an object already inside a hash-based collection?)

Persistence Context and Entity Lifecycle

The EntityManager is not a thin wrapper over SQL. It maintains a persistence context: an in-memory cache of the entities it is managing, plus a record of what they looked like when loaded. Almost every behaviour that feels like magic in JPA comes from this one structure.

The persistence context does three jobs.

It is an identity map. Within one context, one database row is represented by exactly one object. Loading the same order twice returns the same instance, so == holds, and there is no possibility of two divergent copies.

Order a = em.find(Order.class, 1L);     // SELECT issued
Order b = em.find(Order.class, 1L);     // no SELECT: served from the context
assert a == b;                          // the same object, guaranteed

It performs dirty checking. At flush time the provider compares each managed entity against its loaded snapshot and generates UPDATE statements for what changed. This is why there is no update method.

em.getTransaction().begin();
Order order = em.find(Order.class, 1L);
order.setStatus(OrderStatus.SHIPPED);   // just a setter on a plain object
em.getTransaction().commit();           // UPDATE orders SET status=? WHERE id=?

It buffers writes. persist does not necessarily issue an INSERT immediately; statements are queued and flushed before a query that might be affected, or at commit. This is what allows batching.

Entities move between four states, and knowing which state an object is in explains most confusing behaviour.

              new Order(...)
                    |
                    v
              [ TRANSIENT ]  not in the context, no database row
                    |
              persist()
                    v
              [ MANAGED ]  <----- find(), query results, merge()
                 |     ^          tracked; changes are written automatically
        detach() |     | merge()
        close()  |     |
                 v     |
              [ DETACHED ]  has a row, but no context is watching it
                    |
              remove()  (from MANAGED)
                    v
              [ REMOVED ]  scheduled for DELETE at flush

State	In the context	Has a row	Changes tracked
Transient	No	No	No
Managed	Yes	Yes (or will have)	Yes
Detached	No	Yes	No
Removed	Yes	Until flush	Deletion queued

The detached state causes the most confusion. Once the EntityManager closes, everything it managed becomes detached. Setters on a detached object change nothing in the database, and accessing an unloaded lazy association throws LazyInitializationException. This is exactly what happens when an entity loaded in a service is modified in a web controller after the transaction has ended.

// The context is closed, so this object is detached.
order.setStatus(OrderStatus.CANCELLED);    // changes nothing in the database

// merge() copies the state onto a managed instance and returns THAT instance.
em.getTransaction().begin();
Order managed = em.merge(order);           // note: order itself stays detached
managed.setStatus(OrderStatus.CANCELLED);  // this one is tracked
em.getTransaction().commit();

The comment on merge is the detail people miss: it returns a different object, and continuing to use the argument silently discards changes.

Flushing is the act of synchronising the context with the database, and it happens automatically at commit and before queries whose results the pending changes could affect. Calling em.flush() manually is occasionally necessary, for example to surface a constraint violation at a specific point.

Two cache levels are worth distinguishing, because "the cache" means different things to different people.

Level	Scope	Enabled
First	One persistence context	Always; cannot be turned off
Second	Shared across contexts	Opt-in, configured per entity

Finally, the context should be short-lived. It grows as entities are loaded, so a context that loads a hundred thousand rows holds a hundred thousand objects and their snapshots. In long loops, clear it periodically.

for (int i = 0; i < records.size(); i++) {
    em.persist(toEntity(records.get(i)));
    if (i % 500 == 0) {
        em.flush();     // send the queued INSERTs
        em.clear();     // detach everything: release the memory
    }
}

Key Takeaways

• The persistence context is an identity map plus a snapshot of loaded state.
• One row maps to exactly one object per context, so repeated find calls return the same instance.
• Dirty checking makes explicit update calls unnecessary inside a transaction.
• Entities are transient, managed, detached, or removed, and behaviour depends on which.
• Closing the context detaches everything, after which setters do nothing and lazy access throws.
• merge returns a managed copy; the object passed in stays detached.
• The first-level cache is always on; the second level is opt-in and shared.
• Flush and clear inside bulk loops to keep memory bounded.

🧪 Practice

1. Call find twice for the same id in one context and assert the two references are identical. Repeat across two contexts.
2. Modify an entity inside a transaction with no save call and confirm from the SQL log that an UPDATE was issued.
3. Load an entity, close the context, modify it, and show the change is lost. Then fix it with merge, using the returned instance.
4. Persist 100,000 entities without flush/clear, observe heap growth, then add them and compare.
5. Interview: A controller throws LazyInitializationException when rendering a template, though the service loaded the entity successfully. What is going on? (Hint: what state is the entity in by the time the template runs?)

Lazy Loading and the N+1 Query Problem

Lazy loading is the mechanism that makes ORMs usable, and the N+1 problem is the cost of using it without thinking. Both follow from the navigation mismatch described at the start of this subchapter.

Lazy loading means an association is not fetched until it is used. Loading an Order does not load its lines; the provider puts a proxy in the field, and the first method call on that proxy triggers a query.

  em.find(Order.class, 1)
        |
        v
   Order { id=1, lines = [ proxy, not yet loaded ] }      1 query
        |
  order.getLines().size()      <-- touching it
        |
        v
   SELECT * FROM order_lines WHERE order_id = 1           2nd query

That is efficient when you did not need the lines and disastrous in a loop.

// N+1: one query for the orders, then one per order for its lines.
List<Order> orders = em.createQuery("SELECT o FROM Order o", Order.class)
                       .getResultList();          // 1 query -> 100 orders

for (Order order : orders) {
    total = total.add(order.getLines().stream()   // 1 query PER ORDER
                           .map(OrderLine::subtotal)
                           .reduce(ZERO, BigDecimal::add));
}
// 101 queries, ~1 ms each, for work a single join does in ~5 ms

The signature of this problem in production is a page that is fast with test data and slow with real data, where the slowness scales linearly with row count and the database shows thousands of near-identical small queries.

There are four fixes, and they suit different situations.

A join fetch loads the association in the same query. It is the usual answer when you know you need the children.

List<Order> orders = em.createQuery("""
        SELECT DISTINCT o FROM Order o
        LEFT JOIN FETCH o.lines
        """, Order.class).getResultList();   // 1 query, lines already populated

DISTINCT is needed because the join multiplies the parent row by the number of children.

An entity graph expresses the same thing declaratively, which keeps the query reusable with different fetch plans.

EntityGraph<Order> graph = em.createEntityGraph(Order.class);
graph.addAttributeNodes("lines");

List<Order> orders = em.createQuery("SELECT o FROM Order o", Order.class)
        .setHint("jakarta.persistence.fetchgraph", graph)
        .getResultList();

Batch fetching does not eliminate the extra queries but collapses them, turning N queries into N/size queries using an IN clause.

@OneToMany(mappedBy = "order")
@BatchSize(size = 50)          // Hibernate: fetch lines for 50 orders at a time
private List<OrderLine> lines;

A projection avoids loading entities at all, which is often the best answer for reports and list views that only display a few fields.

// Select exactly what the screen needs, in one query, with no entities involved.
List<OrderSummary> summaries = em.createQuery("""
        SELECT new com.shop.OrderSummary(o.id, o.placedAt, sum(l.price * l.quantity))
        FROM Order o JOIN o.lines l
        GROUP BY o.id, o.placedAt
        """, OrderSummary.class).getResultList();

Fix	Queries	Best for
Join fetch	1	Known-needed associations on one query
Entity graph	1	The same query with different fetch plans
Batch size	1 + N/size	Associations needed sometimes
Projection	1	Read-only lists and reports

The eager trap. The instinct on meeting N+1 is to mark everything EAGER. That replaces one problem with a worse one: every load of an entity drags in its whole object graph, so fetching a single order pulls its customer, the customer's other orders, and their lines. Fetch strategy is a property of the use case, not of the mapping, which is why the association stays lazy and the query decides.

How to detect it. Turn on statement logging in development and count.

  hibernate.show_sql=true
  hibernate.format_sql=true
  logging.level.org.hibernate.SQL=DEBUG

Better still, assert on the count in tests, so a regression fails the build rather than a user's page.

// Using a query-counting datasource proxy such as datasource-proxy.
@Test
void listingOrdersUsesOneQuery() {
    SQLStatementCountValidator.reset();
    orderService.listRecent();
    SQLStatementCountValidator.assertSelectCount(1);   // fails if N+1 returns
}

Key Takeaways

• Lazy loading defers an association's query until the association is touched.
• N+1 means one query for a list plus one per element, and it scales with data volume.
• JOIN FETCH with DISTINCT solves it for a known query.
• Entity graphs express the fetch plan separately, so the query stays reusable.
• Batch fetching reduces N queries to N divided by the batch size.
• Projections skip entities entirely and are usually right for lists and reports.
• Marking associations EAGER trades N+1 for uncontrolled graph loading.
• Log and assert query counts so regressions fail tests, not users.

🧪 Practice

1. Build 100 orders with 5 lines each, load them lazily in a loop, and count the statements in the SQL log.
2. Rewrite the query with LEFT JOIN FETCH, confirm the count drops to one, and explain why DISTINCT is required.
3. Apply a batch size of 25 to the association and record the resulting query count.
4. Write a projection query returning only the fields a summary table displays, and compare its timing with loading full entities.
5. Add an automated test asserting the query count for one endpoint, then reintroduce the N+1 and confirm the test fails.
6. Interview: An endpoint takes 40 milliseconds in staging and 9 seconds in production with the same code. What would you look at first? (Hint: what differs between the two environments that would multiply a per-row cost?)

<a id="17-the-spring-framework"></a>