From Raw JDBC to JdbcTemplate: The Evolution of Data Access
Talking to a database forces exactly two real decisions on you: what the SQL says, and what object each returned row becomes. Everything else — borrow a connection, bind parameters, execute, make sense of the failure, hand the connection back — is identical in every method you will ever write, and writing it a hundred times still means writing it a hundred times. Raw JDBC makes you do all of it by hand. JdbcTemplate does one thing: it moves the unchanging part into a template and leaves you the part that actually changes.
Pin down six terms first — they recur throughout:
Connection: an established channel between your app and the database, with a TCP socket underneath. Building it is expensive, which is why you borrow it and hand it backPreparedStatement: a pre-compiled statement carrying?placeholders. It is the only real defence against SQL injection, and incidentally a performance win, because the execution plan can be reusedSQLException: the checked exception the JDBC layer defines. Duplicate key, missing table and a cut network cable all arrive as this one type, which is precisely the problemDataSource: the "connection supplier" interface from the JDBC spec.JdbcTemplatenever builds connections; it only asks this for oneJdbcTemplate: Spring's template class. It wraps "borrow → prepare → bind → execute → translate → release" into methods and leaves you only the SQL and the mappingRowMapper: a one-row-to-one-object callback, signaturemapRow(ResultSet rs, int rowNum)
hand-written JDBC is washing dishes by hand. Clear the table, scrape the plates, run hot water, add detergent, scrub seven times, rinse, dry, put away — eight stages, none optional, and every single plate repeats all eight. JdbcTemplate is a dishwasher: you are responsible for two things only, what goes in (the SQL) and what comes out (the mapping); filling, heating, draining and drying are its job. Three correspondences are worth remembering. First, a dishwasher still finishes draining its water even when the power drops — that is the releaseConnection sitting in the template's finally. Second, you must not throw the order form in with the dirty plates: plates are data, the form is an instruction, and once mixed the machine cannot tell them apart — which is exactly the mistake concatenating SQL makes. Third, a dishwasher does not make one plate cleaner; it stops you from repeating eight stages per plate. That is the entire value of the template method.
Four generations, one thread: each one deletes a piece of the repetition the previous forced on you. This article walks station one to station two — refactoring raw JDBC into JdbcTemplate — and settles three fundamentals of data access along the way: injection, leaks and unreadable exceptions. Stations three and four (MyBatis and JPA) are articles 29 and 30; both stand on the ground prepared here.
After this article you should be able to answer three questions:
- Why does a
?placeholder stop injection, while "just escape the single quotes in the input" does not? try-with-resourcesalready fixed leaks — why isJdbcTemplatestill needed?- What is the actual difference between
catch (DataAccessException e)andcatch (SQLException e), and why does Spring dare to make data-access exceptions unchecked?
Hands on first, theory after. This sandbox lays four ways of writing the same query side by side: change the style on the left, and the right immediately reports lines of code, injection risk, and what you actually see when it fails.
6 effective lines, plus 7 more lines of finallyWhat the database receives: SELECT * FROM t_user WHERE name = 'a' OR '1'='1'Injection risk: fatal — the input rewrote the SQL structureOn failure you see: java.sql.SQLException: Unknown column 'x' in 'field list'
the column that really separates those four styles is "what you see on failure". Lines of code are a cost; exception types are a debt. Debts get paid eventually: concatenation borrows against security incidents, and SQLException borrows against a screen full of catch (Exception e) and one apologetic "something went wrong".
Before JdbcTemplate existed, fetching one record meant walking a full pipeline. You have probably seen this code — it is completely correct, and obviously painful:
public User findById(long id) throws SQLException { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { Class.forName("com.mysql.cj.jdbc.Driver"); // 1) load the driver conn = DriverManager.getConnection(URL, USER, PASS); // 2) get a connection ps = conn.prepareStatement( // 3) create the statement "SELECT id, username, email FROM t_user WHERE id = ?"); ps.setLong(1, id); // 4) bind parameters rs = ps.executeQuery(); // 5) run the query if (rs.next()) { // 6) iterate and map by hand User u = new User(); u.setId(rs.getLong("id")); u.setUsername(rs.getString("username")); u.setEmail(rs.getString("email")); return u; } return null; } finally { // 7) close the resources if (rs != null) rs.close(); if (ps != null) ps.close(); if (conn != null) conn.close(); }}Seven steps, not one of them optional. Yet the parts that actually relate to "find a user" are just the SQL on line 3 and the mapping on line 6 — the rest is pure repetition. Four pain points follow:
- Boilerplate explosion: every method repeats connection, statement and close; an insert ends up as long as a delete
- Resource leaks: miss one
close()and the connection never returns; over time the pool is drained - Unreadable exceptions:
SQLExceptionis a catch-all — duplicate key, missing table, network drop are all the same type, so acatchblock cannot tell them apart - SQL injection: the moment someone concatenates strings to save effort, the system is wide open (next section)
Unroll that life line and you have the seven steps above — note that step 5 (translating the exception) and step 6 (releasing the connection) are the two most often skipped in hand-written code:
Class.forName(...) has been optional since JDBC 4.0 (the driver is discovered via SPI), but plenty of old tutorials still show it because it was the "mandatory first step" for so long. It stays here so you can see just how verbose those seven steps are.
A real incident. Three months after an internal system went live, ops got a 2 a.m. page: CannotGetJdbcConnectionException: Failed to obtain JDBC Connection, most endpoints returning 500, instantly healthy after a restart, sick again within half an hour. The post-mortem was embarrassing — the team did write finally blocks everywhere; one refactor had simply moved an early return into an else branch. Miss one of seven close() calls and the pool loses one connection per day. What makes this class of bug dangerous is not that it is hard to write but that it is invisible: it never shows up in code review, only in month three of production. That is the ceiling of "let humans remember to close things".
First, a genuinely dangerous pattern — a login query assembled by string concatenation:
// DANGEROUS: user input is concatenated straight into the SQL textString sql = "SELECT * FROM t_user WHERE username = '" + username + "' AND password = '" + password + "'";Statement st = conn.createStatement();ResultSet rs = st.executeQuery(sql);if (rs.next()) { loginSuccess(); // it logged in!}Type ' OR '1'='1 into the username box and the assembled SQL becomes:
SELECT * FROM t_user WHERE username = '' OR '1'='1' AND password = '''1'='1' is always true, so the whole WHERE clause collapses — no password needed, login succeeds. Worse is admin' --: -- starts a SQL comment, which blanks out the password check entirely and drops you in as admin.
There is exactly one fix, and it has to become muscle memory — PreparedStatement placeholders:
// SAFE: parameters use ? placeholders; input is data and never changes the SQL structureString sql = "SELECT * FROM t_user WHERE username = ? AND password = ?";PreparedStatement ps = conn.prepareStatement(sql);ps.setString(1, username); // the input is bound as a VALUEps.setString(2, password);ResultSet rs = ps.executeQuery();Why do placeholders stop injection? Because PreparedStatement first sends the SQL text — the one containing ? — to the database to be pre-compiled into a fixed execution plan; the parameters are then transmitted separately as pure data and can only land in the ? slots. However many quotes, -- sequences or ORs a parameter contains, it remains a string value that can never alter the already-compiled structure. That is what "pre-compiled" actually means.
think of a SQL statement as the order slip you hand to the kitchen. Concatenation lets the customer write on the slip itself — "kung pao chicken, and also empty the cash register" — and the kitchen obeys, because every line on that slip counts as an instruction. PreparedStatement imposes two slips: the menu slip goes in first and fixes how the kitchen works; the customer's slip is forever treated as nothing but "the name of one dish", and whatever is written on it can never become an instruction. Preventing injection is not about filtering dangerous characters; it is about never letting data and instructions share one sheet of paper.
The half placeholders cannot reach. This is the part beginners miss and interviews love to probe: ? substitutes values, never structure. Table names, column names, ORDER BY fields and sort directions are all decided at prepare time and cannot become parameters:
// STILL DANGEROUS: the sort field comes from the front end and is spliced into the structureString sql = "SELECT id, username FROM t_user ORDER BY " + orderBy;// SAFE: whitelist mapping — what ends up in the SQL is a constant from your own codeprivate static final Map<String, String> SORTABLE = Map.of("id", "id", "username", "username", "createTime", "create_time");String column = SORTABLE.get(orderBy);if (column == null) { throw new IllegalArgumentException("Unsupported sort field: " + orderBy);}String sql = "SELECT id, username FROM t_user ORDER BY " + column;- The test is one sentence: is the final origin of what you are splicing user input? If yes, it must pass through a whitelist and become a constant inside your program first
- Do not trust "escape the single quotes": that assumes quotes are the only danger, while real attacks use comment markers,
UNION SELECTand hex-encoded literals — a blacklist game where you are always one step behind the attacker
Trap: storing cleartext passwords, or hashing them with MD5, is a second wound on top of the first. Blocking injection does not make passwords safe — they belong in a salted, slow hash such as BCrypt, and login compares hashes rather than selecting the row where the password equals. These two concerns get confused constantly.
A quick check, because most people get this wrong on the first pass:
With injection handled, the leak remains. Java 7's try-with-resources makes closing automatic:
public User findById(long id) { String sql = "SELECT id, username, email FROM t_user WHERE id = ?"; try (Connection conn = dataSource.getConnection(); // auto-closed PreparedStatement ps = conn.prepareStatement(sql)) { // auto-closed ps.setLong(1, id); try (ResultSet rs = ps.executeQuery()) { // auto-closed return rs.next() ? mapRow(rs) : null; } } catch (SQLException e) { throw new RuntimeException("Failed to query user", e); // note: still unreadable }}- Anything
AutoCloseabledeclared intry(...)is closed automatically, in reverse declaration order, whether the block returns or throws - The closing order is
ResultSet → PreparedStatement → Connection, the exact reverse of opening — not a stylistic quirk: closing aStatementcloses the result set it produced, and doing it the other way round earns driver warnings - But notice: leaks are solved while boilerplate and unreadable exceptions are not —
catch (SQLException)still cannot tell a duplicate key from a missing table
Who closes what, and what happens when nobody does — this is where the detail lives:
| Resource | AutoCloseable? | Who closes it | Cost of forgetting |
|---|---|---|---|
ResultSet | yes | Closed together with its Statement; still worth putting in try(...) | The server-side cursor keeps holding memory |
PreparedStatement / Statement | yes | try-with-resources | Statement handles leak; at the limit the driver throws Prepared statement count exceeded |
Connection | yes | try-with-resources, or the template's releaseConnection | The connection never returns, the pool drains → CannotGetJdbcConnectionException |
in the era of connection pools, returning a book should not mean carrying it back to the shelf yourself. A hand-written finally is like walking home in the rain with the book, hunting for the right shelf — twice out of ten times you forget, because you were in a hurry (that early return on an exception branch). try-with-resources is the drop-off box by the door: you toss the book in, and scanning, registering and reshelving happen regardless of whether you are tired, distracted or already halfway out the door. JdbcTemplate goes one step further and removes even "walk to the library" — that releaseConnection lives inside the template's finally, written once for the whole project.
See the missing finally for yourself. This demo is pinned to the one parameter that matters:
JdbcTemplate uses two classic patterns to absorb all of this pain at once:
- Template method: the fixed flow — get a connection, create a statement, execute, close — is written once, inside the template
- Callback: the parts that differ per business case, namely the SQL text and the result mapping, are extracted into callbacks that you write
Stripped to its bones, the core of JdbcTemplate looks like this:
public <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) { Connection con = DataSourceUtils.getConnection(dataSource); // borrow (or reuse the tx connection) try (PreparedStatement ps = con.prepareStatement(sql)) { ArgumentPreparedStatementSetter.setValues(ps, args); // uniform binding, no concatenation try (ResultSet rs = ps.executeQuery()) { return rse.extractData(rs); // ← the one business-specific step } } catch (SQLException ex) { throw translateException(ex); // uniformly into DataAccessException } finally { DataSourceUtils.releaseConnection(con, dataSource); // always returned }}- You supply exactly two things: the SQL text and the result-mapping callback. Everything else is handled
- Binding goes through
?placeholders, which blocks concatenation at the API level — there is no parameter through which you could splice a SQL string - Exceptions are translated uniformly (section 6), so no giant
SQLExceptionreaches your code - Connections come and go through
DataSourceUtils, which also supports transaction reuse — several statements in one transaction share one connection
"One half each" deserves a diagram before it deserves more prose. The left half is written once and reused by the whole project; the right half is what you fill in for every new query method. Whenever JdbcTemplate feels like extra work, check which half the work is actually on:
A diagram still leaves the real trap untouched: the order of execution. Your RowMapper does not "finish reading everything, then get its turn" — the template calls it back, once per row. Step through the skeleton above line by line:
Connection con = DataSourceUtils.getConnection(dataSource);PreparedStatement ps = con.prepareStatement(sql); // compile the SQL into a fixed planArgumentPreparedStatementSetter.setValues(ps, args); // parameters only ever hit the ? slotsResultSet rs = ps.executeQuery(); // the network round trip happens herereturn rse.extractData(rs); // <-- YOUR RowMapper is called here} catch (SQLException ex) { throw translateException(ex); // vendor dialect becomes a semantic exception} finally { DataSourceUtils.releaseConnection(con, dataSource); }| current thread | request-1 |
| tx connection present | no -> borrow from the pool |
| what arrives | an idle pooled connection |
JdbcTemplate.queryDataSourceUtils.getConnectionThe common methods each have a clearly bounded job:
| Method | Purpose | Returns |
|---|---|---|
execute(...) | Run any SQL with no result to extract, e.g. DDL | void |
update(...) | INSERT / UPDATE / DELETE | int (rows affected) |
query(...) | Query multiple rows | List<T> |
queryForObject(...) | Query a single row or single value | T (throws when not found) |
queryForList(...) | Query a simple list | List<Map<String, Object>> or List<T> |
batchUpdate(...) | Batched inserts/updates/deletes | int[] (rows affected per statement) |
update covers every write operation — the name wrongly suggests UPDATE only. And queryForObject means "this row must exist", which is the source of the trap in section 9.
Now open the kernel experiment and press the five parameters in order. This is not an animation to watch; it is the same template producing five genuinely different scenes:
How to read each parameter:
| Parameter | The scene you get | Its real-world counterpart |
|---|---|---|
query — one row | borrow → prepare → bind → read one row → release, one clean chain | The everyday path; this is what findById really does |
update — affected rows | The result is an integer count of rows, not a boolean | Never write if (jdbc.update(...) == true); a 0 usually means your WHERE matched nothing, which is a business signal |
batch | One statement reused across a set of parameters, one round trip | A loop of update versus batchUpdate differs by an order of magnitude |
map — ResultSet to object | Row-by-row callbacks; column-to-property pairing is yours to decide | Sections 4 and 5: RowMapper and BeanPropertyRowMapper |
leak — forgetting to release | A connection stays gripped; later callers queue, then time out | The full anatomy of that 2 a.m. page from section 1 |
Start with what you use every day — insert, read (hand-written RowMapper and automatic mapping), and batch:
@Repositorypublic class UserRepository { private final JdbcTemplate jdbc; public UserRepository(JdbcTemplate jdbc) { // injected by Boot auto-configuration this.jdbc = jdbc; } // Insert: update covers every write and returns rows affected public int save(User u) { return jdbc.update("INSERT INTO t_user(username, email) VALUES (?, ?)", u.getUsername(), u.getEmail()); } // One row, may not exist: hand-written RowMapper (a lambda) — the most flexible public Optional<User> findById(long id) { return jdbc.query("SELECT id, username, email FROM t_user WHERE id = ?", (rs, rowNum) -> new User(rs.getLong("id"), rs.getString("username"), rs.getString("email")), id) .stream().findFirst(); } // Many rows: BeanPropertyRowMapper maps column names to properties for you public List<User> findByStatus(String status) { return jdbc.query("SELECT id, username, email FROM t_user WHERE status = ?", new BeanPropertyRowMapper<>(User.class), status); } // Batch writes: an order of magnitude faster than looping update() public int[] batchInsert(List<User> users) { return jdbc.batchUpdate("INSERT INTO t_user(username, email) VALUES (?, ?)", users, 500, // flush every 500 rows (ps, u) -> { ps.setString(1, u.getUsername()); ps.setString(2, u.getEmail()); }); }}(rs, rowNum) -> ...isRowMapperwritten as a lambda: the input is the current row, the output is your domain objectBeanPropertyRowMapperaligns columns such asuser_namewith Java properties (better still with camel-case mapping on), but it demands a no-arg constructor and setters — records have neither, which is why teams on JDK 17 keep reverting to plain classes for mapped types- So
Userin this section is a POJO with setters; the exercise in section 11 uses arecordand hand-writtenRowMappereverywhere. Do not mix the two - The third argument of
batchUpdateis the batch size; withrewriteBatchedStatements=trueon the MySQL driver the payoff is largest
Once parameters multiply and IN lists appear, counting ? gets painful. Switch to named parameters:
@Repositorypublic class OrderRepository { private final NamedParameterJdbcTemplate namedJdbc; public OrderRepository(NamedParameterJdbcTemplate namedJdbc) { this.namedJdbc = namedJdbc; } public List<Order> search(String status, List<Long> userIds, int limit) { String sql = """ SELECT id, user_id, amount, status FROM t_order WHERE status = :status AND user_id IN (:ids) -- no manual placeholder building ORDER BY id DESC LIMIT :limit """; MapSqlParameterSource params = new MapSqlParameterSource() .addValue("status", status) .addValue("ids", userIds) // a List expands to (?, ?, ?) .addValue("limit", limit); return namedJdbc.query(sql, params, new BeanPropertyRowMapper<>(Order.class)); }}:namemakes the SQL self-explanatory; editing a condition no longer requires realigning positional numbers- Passing a
ListintoIN (:ids)expands automatically to exactly the right number of placeholders — precisely where the?version goes wrong NamedParameterJdbcTemplatewrapsJdbcTemplate; the template underneath is the same and every capability is inherited
That word "automatically" earns its own walkthrough — note that what expands is only the count of question marks, while the SQL text itself is never rewritten from external input. That is exactly why it stays safe:
Keep it in mind when you reach trap two in section 9 (never build that run of question marks yourself); the warning finally has a picture attached.
Result mapping deserves its own look, because a column-to-property mismatch surfaces as a 500 raised inside your callback. Back in the section 4 demo, switch to map to watch each row fed to the callback and see exactly which step a type mismatch stops at; switch to batch to see one statement reused across a set of parameters.
Remember the "unreadable exceptions" pain from section 1? Spring's answer is to take each vendor's SQLException — which carries nothing but SQLState and errorCode — and translate it into a semantically clear runtime exception.
| Situation | Vendor exception / SQLState | Spring exception |
|---|---|---|
| Unique constraint violation | SQLIntegrityConstraintViolationException / 23000 | DuplicateKeyException |
| Foreign key / integrity violation | SQLException / 23000 | DataIntegrityViolationException |
| Missing table or column | SQLSyntaxErrorException / 42S02 | BadSqlGrammarException |
| Deadlock / lock acquisition failure | SQLTransactionRollbackException / 40001 | CannotAcquireLockException |
| Query timeout | SQLTimeoutException | QueryTimeoutException |
| Cannot get a connection | SQLTransientConnectionException | CannotGetJdbcConnectionException |
| Expected one row, got none | — (JdbcTemplate semantics) | EmptyResultDataAccessException |
| Expected one row, got several | — (JdbcTemplate semantics) | IncorrectResultSizeDataAccessException |
- Every Spring data-access exception extends
DataAccessException, which is aRuntimeException— no more forcedthrows SQLExceptionanywhere in your signatures - The hierarchy is deliberate:
DuplicateKeyExceptionextendsDataIntegrityViolationException, so you maycatchcoarsely or finely - Global handling can then respond by meaning: duplicate key → "already exists", timeout → 503, instead of a blanket 500
Walk one unique-key conflict end to end and you will see why "switch database, keep your code" works:
each database vendor speaks a regional dialect. Oracle says ORA-00001, MySQL says Duplicate entry 'bee' for key 'username', PostgreSQL says duplicate key value violates unique constraint ... — three dialects, one fact. SQLException is the dialect itself: your front-line staff (business code) cannot understand it, so every call ends in a shrug and "system error". Spring's translator is the interpreter at head office: it listens to the dialect, consults the code table (sql-error-codes.xml) and files the call as a standard ticket type — DuplicateKeyException, BadSqlGrammarException, QueryTimeoutException. From then on every department works from ticket types, and moving to another vendor requires not one change to the script your staff reads.
Translation happens inside the template; in code you get one place that branches by meaning:
@RestControllerAdvicepublic class DataAccessAdvice { private static final Logger log = LoggerFactory.getLogger(DataAccessAdvice.class); /** Unique index conflict: a business rule doing its job, not a fault */ @ExceptionHandler(DuplicateKeyException.class) ResponseEntity<ApiError> onDuplicateKey(DuplicateKeyException e) { log.info("Unique key conflict: {}", e.getMostSpecificCause().getMessage()); return ResponseEntity.status(HttpStatus.CONFLICT) .body(new ApiError("USER_EXISTS", "That username is already registered")); } /** Missing foreign key or oversized column: the request itself is wrong, so 400 */ @ExceptionHandler(DataIntegrityViolationException.class) ResponseEntity<ApiError> onIntegrity(DataIntegrityViolationException e) { log.warn("Integrity constraint failed", e); return ResponseEntity.badRequest().body(new ApiError("BAD_DATA", "Submitted data violates a constraint")); } /** Bad SQL or missing table: a defect in the program — 500, error-level log, alert */ @ExceptionHandler(BadSqlGrammarException.class) ResponseEntity<ApiError> onGrammar(BadSqlGrammarException e) { log.error("SQL grammar or missing object; fix immediately", e); return ResponseEntity.internalServerError().body(new ApiError("INTERNAL", "System busy")); }}getMostSpecificCause()digs all the way down to the vendor's own sentence; logging it beatsgetMessage()hands down- Order matters:
DuplicateKeyExceptionis a subclass ofDataIntegrityViolationException, so reversing these two handlers makes the subclass branch unreachable - "Unchecked" does not mean "nobody handles it": Spring deliberately lets exceptions bubble to the outermost layer, so you must provide that one
@RestControllerAdvice— otherwise users meet a blank page
Tip: translation depends on a database-plus-error-code mapping table that differs per vendor. Spring ships tables for the mainstream ones; on an obscure database translation can fall short and you receive the catch-all UncategorizedSQLException. Supply your own SQLExceptionTranslator to close the gap.
What happens after the exception bubbles — who turns it into an HTTP status — is worth seeing as a scene, not as prose:
Look back at DataSourceUtils.getConnection(dataSource) in the section 4 skeleton — JdbcTemplate never creates connections; it asks a DataSource. That separation of duties is the whole point:
JdbcTemplateowns "how to write SQL, how to map results, how to translate exceptions"DataSourceowns "where connections come from, whether they may be reused, when they are recycled"
That is also why the constructor reads new JdbcTemplate(dataSource). In a real project that DataSource is almost always a connection pool (HikariCP), and connections are borrowed and returned over and over — the subject of article 28. Understand this seam and you understand data access as a whole.
Confirm one fact that surprises beginners: two consecutive queries through the same JdbcTemplate receive the same physical connection — it is borrowed from a pool and handed straight back:
Join the dots between those two queries and you get a loop — click ① through ⑤ and back; box ④ is the anatomy of that 2 a.m. page:
One step further: wrap the method in @Transactional and the borrow count collapses to one per transaction. That is not a performance tweak but a correctness requirement — a rollback can only apply to the connection that did the writing:
one sentence suffices for this section — JdbcTemplate takes a connection and returns one, but it never owns one. The owner is the DataSource (usually a pool), and the transaction manager decides when it is released. This seam gets reused constantly in article 28 (pools) and article 31 (transactions).
In Spring Boot you never write new JdbcTemplate(...), yet you inject it without ceremony. The mechanism is, once again, auto-configuration:
Add spring-boot-starter-jdbc (or spring-boot-starter-data-jpa) and JdbcTemplateAutoConfiguration takes effect — annotated with @ConditionalOnClass(DataSource.class) and @ConditionalOnSingleCandidate(DataSource.class), it registers a JdbcTemplate automatically as long as the container holds one DataSource and you have not defined your own JdbcOperations, and it prepares a NamedParameterJdbcTemplate in the same breath.
Before any of that, turn the paragraph above into pom lines you can see. Two ticks tell the whole story: jdbc decides whether spring-jdbc sits on the classpath at all, and h2 is the zero-install database section 11's exercise uses:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.3.4</version> <!-- 版本由 BOM 统管，子依赖不写 version -->
<relativePath/>
</parent>
<groupId>com.example</groupId>
<artifactId>demo-service</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<java.version>17</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>With both in place, the two auto-configurations on the chain have their preconditions: DataSourceAutoConfiguration builds the DataSource first, then JdbcTemplateAutoConfiguration builds the template on top of it (the ordering is pinned by @AutoConfigureAfter, and you never manage it by hand).
Your side of the bargain is four lines:
spring: datasource: url: jdbc:mysql://localhost:3306/demo?useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true username: root password: ${DB_PASSWORD} # injected from the environment, never committed driver-class-name: com.mysql.cj.jdbc.Driver # optional since JDBC 4, kept for clarityIn one sentence: you configure spring.datasource.url; DataSourceAutoConfiguration builds the DataSource, then JdbcTemplateAutoConfiguration builds the JdbcTemplate on top of it — chained conditional wiring, fully automatic. The takeover boundary is equally clear: define a bean of the same type yourself and auto-configuration steps aside (articles 18 and 19).
That dependency chain can be unfolded layer by layer in the container wiring lab, from the web tier down to the DataSource:
That chain is the point-and-click version; here is the type-it-yourself version. This console talks to the same kernel and every reply is computed there: start with beans to count the injection points, then cond jdbcOnClasspath false to pull spring-jdbc off the classpath — watch the template bean vanish with it, which is exactly what a failed @ConditionalOnClass looks like:
hit beans twice — once before and once after cond jdbcOnClasspath false; only the difference between the two listings is the content of this section. The line reading "@ConditionalOnClass not matched -> skipped" is conditional wiring being evaluated live (article 19 goes deeper), and the chain di prints is the console counterpart of the experiment above.
Boot also ships a free health check — with schema.sql / data.sql present, startup runs them, but since 2.5 that is off by default for non-embedded databases and needs spring.sql.init.mode=always. When "table doesn't exist" appears on your first run, check whether the DDL script ran at all.
queryForObject throws EmptyResultDataAccessException when nothing is found — it does not return null. Plenty of people write User u = jdbc.queryForObject(sql, mapper, id); if (u == null) {...} — that if never runs, because the exception is thrown first. For "may be empty" semantics use query(...) and take the first element (or wrap it as Optional) so "not found" stays clearly apart from "the query broke". The mirror image: if your WHERE is not actually unique and the data grows, you get IncorrectResultSizeDataAccessException: expected 1, actual 8. Adding LIMIT 1 only hides it — the question to answer is "what makes this condition unique?"
do not build ? placeholders yourself for IN lists. idList.stream().map(i -> "?").collect(joining(",")) spliced into the SQL looks clever but is concatenation again — error-prone as the count grows and a latent injection risk. Use NamedParameterJdbcTemplate with IN (:ids) and let the framework expand it. Guard the empty case too: an empty list expands to IN () and fails with a syntax error.
large result sets must stream through RowCallbackHandler, never into a List. Exporting a million rows with query(...) loads the whole table onto the heap and OOMs immediately. Stream row by row, writing as you go, and memory stops depending on result-set size:
// Export: heap usage no longer depends on result-set sizepublic void exportOrders(Path target) { try (BufferedWriter w = Files.newBufferedWriter(target, StandardCharsets.UTF_8)) { w.write("id,user_id,amount\n"); jdbc.query( "SELECT id, user_id, amount FROM t_order ORDER BY id", rs -> { // RowCallbackHandler, one row at a time try { w.write(rs.getLong("id") + "," + rs.getLong("user_id") + "," + rs.getBigDecimal("amount")); w.newLine(); } catch (IOException e) { // the callback signature only declares SQLException, so wrap IO failures throw new UncheckedIOException("Failed writing the export file", e); } }); } catch (IOException e) { throw new UncheckedIOException("Export failed: " + target, e); }}What makes the large-result-set trap sneaky is that the 100-thousand tier works: List<Order> costs roughly 200 MB, driver-side buffering doubles it, Young GC gets busy and P99 jitters past 3 s, yet nothing throws — tests stay green. Stack concurrent traffic on top after launch and it turns into a Full GC cascade; and because that connection cannot be returned during the whole read, pool pending spikes and unrelated endpoints start timing out too. Three million rows ends in OutOfMemoryError: Java heap space, or the driver's Packet for query is too large first. A large export should be a background job writing a file plus a download notification, and no single fetch should exceed ten thousand rows.
update returns rows affected, and 0 is a business signal, not success. "Updated the status but zero rows matched" means the WHERE hit nothing — the record is gone, or under concurrency somebody changed it first. Discarding that return value buries the problem in nobody's log. Optimistic locking lives on it: if (jdbc.update(sql, ...) == 0) throw new ConcurrentModifyException();
Every phrase below can be pasted straight into a search box. What beginners fear is not the concept but the wall of red text; identify the row first, then follow the last column.
| Error text (fragment) | Symptom | Actual cause | 30-second self-rescue |
|---|---|---|---|
CannotGetJdbcConnectionException: Failed to obtain JDBC Connection | Fails at startup or on first hit; every endpoint touching the database dies together | One of spring.datasource.url, the credentials or the driver dependency is wrong, or the database is down and the network or firewall is not | Prove the port is reachable, then connect once with those same credentials outside Spring. If HikariPool-1 - Start completed. never appears in the startup log, it is the connection string (article 28) |
java.sql.SQLSyntaxErrorException: Table 'demo.t_user' doesn't exist → BadSqlGrammarException | Works locally, dies after deploy, and the SQL in the message is genuinely correct | Wrong schema or database name, the DDL script never ran in the new environment, or the SQL hard-codes a database prefix | Run SHOW TABLES in the target to find where the table really lives, then put DDL under Flyway / Liquibase instead of running it by hand |
DuplicateKeyException: Duplicate entry 'bee' for key 'username' | Register/save endpoints fail sporadically and predictably on double submits | A unique index blocked one duplicate write — a business rule working, not a broken program | catch (DuplicateKeyException e) and return an "already exists" answer (HTTP 409), or express the intent explicitly with INSERT ... ON DUPLICATE KEY UPDATE |
DataIntegrityViolationException: could not execute statement | A form submit fails to save and the front end shows only "system error" | Nullable column, missing foreign key, oversized value or an overflowing DECIMAL precision — all in this family | Log e.getMostSpecificCause().getMessage(); the vendor sentence names the constraint. Do not smear it as "data error" |
EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0 | You clearly wrote a null check, and still get a 500 | queryForObject asserts "exactly one row"; zero rows throws before the check is ever reached | Switch to query(...) plus stream().findFirst(), or an Optional-shaped wrapper, so "not found" is a return value again |
IncorrectResultSizeDataAccessException: expected 1, actual 8 | The identical SQL worked yesterday and explodes today | The WHERE is not unique; as data grew the "business-unique" column turned out not to be | Ask what guarantees uniqueness. If several rows are legitimate, use query(...); LIMIT 1 only conceals the mismatch |
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure | The first request each morning fails, retries succeed; the log mentions last packet ... 28800 seconds | The database closed the idle connection unilaterally (wait_timeout) while the pool still believes it is alive | Set maxLifetime clearly below wait_timeout and enable keepaliveTime (article 28, section 10) |
java.sql.BatchUpdateException ... Batch entry 0 was aborted | batchUpdate dies mid-run with the first half already written | One row in the batch violates a constraint while the driver's default continueBatchOnError keeps going | Read getUpdateCounts() to see which rows landed; shrink the batch and the transaction boundary — never push 100,000 rows through one batch |
these errors share one pattern — the Spring class name states the meaning, and only getCause() states the detail. The troubleshooting order never changes: read the Spring type to classify, dig getMostSpecificCause() to identify the constraint, then read the SQL and its execution plan to find the root cause.
Row one of that table — "you clearly wrote a null check, and still get a 500" — has a complete stack trace worth clicking through. Do not read the notes yet: pick the frame you think is the culprit, then compare with trap one in section 9:
A user-detail endpoint returns a 500 every so often, while the code plainly reads User u = jdbc.queryForObject(sql, mapper, id); if (u == null) { return notFound(); }. It never reproduces locally, yet production sees it a few times a week, always with the same exception class.
One more reading-comprehension question — get this wrong and you will step into every trap from section 9:
Goal: with an in-memory H2 database (org.h2.Driver plus jdbc:h2:mem:demo, zero installation) run "create table → insert two rows → list them → provoke one unique-key conflict → look for a row that is not there". You need only two dependencies: spring-jdbc and com.h2database:h2.
package com.example.jdbc;/** The domain object: a JDK 17 record, the least boilerplate available */public record User(long id, String username, String email) {}package com.example.jdbc;import org.springframework.dao.DuplicateKeyException;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.datasource.DriverManagerDataSource;import java.util.List;import java.util.Optional;public class TwoGenerationsDemo { public static void main(String[] args) { DriverManagerDataSource ds = new DriverManagerDataSource(); ds.setDriverClassName("org.h2.Driver"); ds.setUrl("jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1"); // in-memory: gone when the JVM exits ds.setUsername("sa"); ds.setPassword(""); JdbcTemplate jdbc = new JdbcTemplate(ds); jdbc.execute(""" CREATE TABLE t_user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(32) NOT NULL UNIQUE, email VARCHAR(64) NOT NULL )"""); // 1) DDL via execute, nothing to return jdbc.update("INSERT INTO t_user(username, email) VALUES (?, ?)", "bee", "bee@example.com"); jdbc.update("INSERT INTO t_user(username, email) VALUES (?, ?)", "ant", "ant@example.com"); List<String> names = jdbc.queryForList("SELECT username FROM t_user ORDER BY id", String.class); System.out.println("names = " + names); // 2) list query try { // 3) hit the unique index jdbc.update("INSERT INTO t_user(username, email) VALUES (?, ?)", "bee", "again@example.com"); } catch (DuplicateKeyException e) { System.out.println("Duplicate username -> " + e.getClass().getSimpleName() + " / mostSpecificCause = " + e.getMostSpecificCause().getMessage()); } Optional<User> missing = jdbc.query( // 4) row that does not exist "SELECT id, username, email FROM t_user WHERE id = ?", (rs, rowNum) -> new User(rs.getLong("id"), rs.getString("username"), rs.getString("email")), 9999L) .stream().findFirst(); System.out.println("id=9999 -> " + missing); }}Expected console output (the vendor sentence varies slightly with the H2 version; the shape must match):
names = [bee, ant]Duplicate username -> DuplicateKeyException / mostSpecificCause = Unique index or primary key violation: "PUBLIC.PRIMARY_KEY_A ON PUBLIC.T_USER(ID)"id=9999 -> Optional.emptyConfirm four things against it:
- Step 1 uses
execute(...)— DDL has no result to extract, which is exactly the first row of the method table - At step 3 you wrote
catch (DuplicateKeyException e)without reading a single error code: that is the whole point of translation. On MySQL the vendor sentence isDuplicate entry 'bee' for key 'username'(code 1062) and Spring still raises the same class - Change step 4 to
queryForObject(...)and rerun: you will watchEmptyResultDataAccessExceptionarrive in person — trap one from section 9, staged live - The closing
Optional.emptyshows "not found" travelling through the return channel rather than the exception channel
Change one thing at a time, rerun, and record what you see:
- Rewrite the lookup as concatenation:
" ... WHERE username = '" + input + "'", then feed in' OR '1'='1. You will observe rows you never asked for coming back — the attack from section 2, now written by your own hand. - Change
WHERE id = ?toWHERE username LIKE ?and pass%. You will observe a full scan. Parameterisation guarantees structure, not performance; they are separate debts. - Add
queryForObject("SELECT ... WHERE status = ?", String.class, "ON")while eight rows have statusON. You will observeIncorrectResultSizeDataAccessException, and learn that uniqueness must be enforced by a constraint, never by "I remember there being only one". - Time 50,000 calls to
jdbc.updatein a loop against onejdbc.batchUpdateof 50,000 rows. You will observe an order-of-magnitude gap; withrewriteBatchedStatements=trueon MySQL it widens further. That is whybatchUpdateearns its place in section 5.
Requirement: a complete DAO over t_order with a presentable failure path. Acceptance checklist:
- [ ] The DDL contains one unique constraint and one foreign key (
user_id→t_user), because you need them to provoke two different exceptions - [ ]
OrderRepositoryoffers four methods:findById(long): Optional<Order>,findByUserIds(List<Long>): List<Order>(must useNamedParameterJdbcTemplatewithIN (:ids)),batchInsert(List<Order>): int[], andexportTo(Path): voidstreaming through aRowCallbackHandler - [ ] Empty-collection guard:
findByUserIds(List.of())returns an empty list instead of letting the SQL expand intoIN ()and fail with a syntax error - [ ] A
@RestControllerAdviceseparating at least three outcomes:DuplicateKeyException→ 409,DataIntegrityViolationException→ 400, any otherDataAccessException→ 500 with an error-level log - [ ] One test that pushes
' OR '1'='1into the search parameter and asserts an empty result set instead of the whole table — this test is the only guardrail against an injection regression - [ ] Run
EXPLAINon the SQLfindByUserIdsproduces and name the index it uses; if the access type isALL, add the index and rerun - [ ] Bonus: reimplement
findByIdonce with JPA and once with MyBatis, compare line counts and readability across the three, and write a five-line verdict — that verdict is the opening page of articles 29 and 30
Finish this tier and you no longer "know the JdbcTemplate API"; you own a complete data-access path: parameterised, mapped, semantically translated, measured and observable.
why does PreparedStatement stop injection? The answer lies in the two transmissions — the text with ? goes first and is compiled into a fixed plan; parameters follow as pure data and can only occupy the ? slots, never reshape the structure.
can an ORDER BY field be a ? parameter? No. Sorting targets are structure, fixed at prepare time; the answer is a whitelist that converts external input into an internal constant.
try-with-resources already fixed leaks — what does JdbcTemplate add? The disappearance of boilerplate (no borrowing and returning per call), semantic exceptions (the DataAccessException family), and connection reuse inside a transaction.
with queryForObject, what is thrown for zero rows and for many rows? EmptyResultDataAccessException (expected 1, actual 0) and IncorrectResultSizeDataAccessException (expected 1, actual N).
which of DuplicateKeyException and DataIntegrityViolationException is the parent? The latter. So the more specific handler must come first or it is unreachable.
does JdbcTemplate create connections? It never does — it asks the DataSource and hands them back through DataSourceUtils.releaseConnection. For who really owns them, read article 28.
keep instructions and data on separate sheets, connections and returns in separate hands, dialects and tickets in separate offices — those three separations are the unified answer to injection, leaks and unreadable exceptions.
the evolution runs raw JDBC (seven steps, manual resource management) → try-with-resources (leaks solved) → JdbcTemplate (template plus callback, killing boilerplate, injection and unreadable exceptions in one stroke). Four fundamentals to keep: 1) the only real defence against injection is parameterised placeholders that keep data as data, and structural input such as table or sort fields must go through a whitelist; 2) JdbcTemplate puts the fixed flow in the template and hands SQL and mapping back to you, taking its connection from a DataSource and returning it (next stop: the pool, article 28); 3) SQLException becomes a semantic, unchecked DataAccessException, so handle it at the layer that can actually make a decision; 4) queryForObject asserts exactly one row — it throws EmptyResultDataAccessException on none — and large exports must stream via RowCallbackHandler. Follow this line into MyBatis and JPA and you will find both standing on JdbcTemplate's shoulders.