The labs on this site need JavaScript to run. The same content is written out below.

Learn › Spring › Data access and transactions

JdbcTemplate and exception translation

What does JdbcTemplate do for you, and what does it translate exceptions to?

When you use JDBC directly, running one line of SQL means writing the code that gets a connection, closes it, and handles SQLException every time. JdbcTemplate does this repeated work for you, and it translates the SQLException thrown by the JDBC driver into a Spring exception (one from the DataAccessException hierarchy).

Checked against real execution · 18 combinations were run on the JdbcTemplate of Spring Framework 7.0.9 and H2 and checked against the results

Concepts in this lab

JdbcTemplate

This is a JDBC helper class provided by Spring that does the work that is repeated every time SQL is run. It handles internally the work of getting a connection, creating a PreparedStatement and setting the values, running the statement, reading the result, and closing the resources whether or not an exception is thrown, so the developer only has to pass in the SQL, the values to put into it, and the way to hold the result. update is used for inserts, updates, and deletes, queryForObject for queries whose result is exactly 1 row, and query for queries that return multiple rows as a list. queryForObject throws EmptyResultDataAccessException when the result has 0 rows and IncorrectResultSizeDataAccessException when it has 2 or more rows, so use it with care for queries where 0 rows can be a normal result.

Exception translation and DataAccessException

This is what Spring does when it converts the exceptions thrown by a data access technology into its own exception hierarchy. The SQLException of JDBC is a checked exception, and what the problem is can be known only from the codes inside it (the SQLState and the error code), but Spring translates it to a subclass of DataAccessException and throws that. A duplicate key becomes DuplicateKeyException, any other constraint violation becomes DataIntegrityViolationException, and invalid SQL becomes BadSqlGrammarException. DataAccessException is an unchecked exception, so no throws clause is needed, and the original exception is kept as the cause. Problems are told apart by the name of the exception class, so the service code is not tied to the error codes of a particular database product.

Connection pool

A technique that creates several database connections in advance and reuses them. Opening a new connection is an expensive operation, so instead of opening and closing a connection for every request, the application borrows one and returns it. If every connection is in use, a new request waits until one is returned, and an exception is thrown if it cannot get one within a set time (connectionTimeout in HikariCP, 30 seconds by default). The default implementation in Spring Boot is HikariCP.

Checked and unchecked exceptions

Unchecked exceptions are RuntimeException and its subclasses (in the specification, the Error classes are also unchecked), and the compiler does not check whether they are handled (examples are NullPointerException and IllegalArgumentException). Checked exceptions are all other Exception classes, and the code compiles only if they are handled with catch or declared with throws (examples are IOException and SQLException). By default, Spring's @Transactional rolls back only on RuntimeException and Error, and commits on checked exceptions.

Template Method pattern

A pattern in which the superclass fixes the overall flow of a task and subclasses implement only the steps within that flow that differ. It removes duplication of the common procedure. Spring's JdbcTemplate is built on the same idea: it fixes the flow of getting a connection, handling exceptions, and cleaning up resources, and it takes only the varying part as an argument.

Try these

• Default settings — JDBC is used directly, with close() on the last line. When an exception is thrown, the connection is not returned.
• Closing with try-with-resources — The connection is returned. But what the caller gets is still an SQLException and a code.
• Switching to JdbcTemplate — The code becomes one line, and the exception is translated to DuplicateKeyException.
• A typo in the SQL — The exception is translated to BadSqlGrammarException.
• queryForObject with 0 rows — You get an exception, not null.
• 2 rows with JDBC directly — Only the first row is returned, and the fact that there were 2 rows is lost.
• queryForObject with 2 rows — Compare this with the example just above. An exception tells you about it.

Running with the default settings

1. memberRepository.insert(1L, "choi", "Daejeon") is called: This method uses JDBC directly. Everything from getting a connection to closing it is written out in this method.
2. dataSource.getConnection(): One connection is borrowed from the connection pool. A borrowed connection must be closed (close) after use so that it is returned to the pool.
3. ps.executeUpdate(): The database reports an error. A row with id 1 already exists, so the primary key would be duplicated. The JDBC driver throws this as an SQLException, and what the error is can be found in the SQLState code inside it (23505).
4. The exception leaves the method: The calls written below the line where the exception was thrown, ps.close(), con.close(), do not run. The borrowed connection does not go back to the pool. When this keeps happening, the pool runs out of connections and other requests cannot get one.
5. The caller gets SQLException (or one of its subclasses). It is a checked exception, so every method that calls this method must either declare throws SQLException or catch it with try-catch. To find out whether the problem is a duplicate key, you often have to check a code such as SQLState 23505 yourself.

Result of this run

• When close() is at the end of the method, the connection cannot be closed if an exception is thrown partway through. When you use JDBC directly, you have to close with try-with-resources (or finally), and you have to write this in every method without missing one.

Code

// member table (id is the primary key, name is NOT NULL)
//   (1, kim, Seoul), (2, lee, Busan), (3, park, Seoul)
// Call: memberRepository.insert(1L, "choi", "Daejeon");

public int insert(Long id, String name, String city) throws SQLException {
    Connection con = dataSource.getConnection();           // get a connection
    PreparedStatement ps = con.prepareStatement(
        "insert into member(id, name, city) values (?, ?, ?)");
    ps.setLong(1, id);
    ps.setString(2, name);
    ps.setString(3, city);
    int rows = ps.executeUpdate();                          // run the SQL
    ps.close();
    con.close();                                            // return the connection
    return rows;
}

Assumptions of this model

All 18 combinations were run on this PC with code of the same structure as on the screen, using the JdbcTemplate of Spring Framework 7.0.9 (the version included in Spring Boot 4.1.1), the HikariCP connection pool, and H2 2.4.240, and three things were checked against the results: what the caller gets, whether the exception is a checked exception, and whether any connection is left unreturned to the pool after the call ends. Execution also confirmed that DuplicateKeyException is a subclass of DataIntegrityViolationException, that EmptyResultDataAccessException is a subclass of IncorrectResultSizeDataAccessException, that the cause of the translated exception holds the original SQLException, and that the connection has already been returned at the point where JdbcTemplate translates the exception. The SQLState values (23505, 23502, 42S22) are the values that H2 gave. That the codes can differ in other database products, and that Spring still translates them to the same exceptions, are statements that follow the Spring documentation, because execution on this PC used only H2. The order of the steps, in which queryForObject checks the row count after it has read the whole result and returned the connection, follows the Spring source code (execution confirmed only that the connection has been returned after the call ends). H2 reports a typo in the SQL when the PreparedStatement is created, but some databases report it only when the SQL is run. The screen does not distinguish the two and draws this as the "Run the SQL" step. Inside a transaction managed by Spring, JdbcTemplate does not return the connection to the pool right away. It returns it when the transaction ends. Only the case without a transaction is covered here.

Previous lab: Transaction propagation · Next lab: Connection pool and OSIV