Spring JDBC & JdbcTemplate practice questions

Worked examples for Spring JDBC & JdbcTemplate

1. Question 1

   What is the role of a RowMapper passed to a JdbcTemplate query?

   1. A. It converts the entire ResultSet into one object in a single call

      Converting the whole ResultSet at once in a single call is the role of ResultSetExtractor, not RowMapper, which works one row at a time.

   2. B. It supplies the SQL string the template will execute

      A RowMapper does not define or supply SQL; the query string is passed separately to the JdbcTemplate method.

   3. C. It opens and commits the surrounding database transaction

      A RowMapper does not manage transactions; opening and committing the surrounding transaction is handled elsewhere, not by the row-mapping callback.

   4. D. Its mapRow(rs, rowNum) is invoked once per row to convert that single ResultSet row into an objectCorrect answer

      RowMapper is a per-row callback: JdbcTemplate invokes mapRow for each row, and the mapper reads columns from the current row and returns a domain object (Spring Framework 5.3 Reference, RowMapper / ResultSetExtractor).

   Explanation

   A RowMapper is a per-row mapping callback: the template invokes it once for each row of the ResultSet, and the mapper reads that single row's columns and returns a domain object. It is not responsible for consuming the entire ResultSet in one call (that is ResultSetExtractor's role), nor for supplying SQL or managing the surrounding transaction.

2. Question 2

   A repository method issues a single SQL query that joins ORDERS to ORDER_ITEMS, so an order with three line items comes back as three rows that share the same order id. The method must return one fully populated `Order` object whose `items` collection holds all three line items, and the `JdbcTemplate` query call itself must produce that `Order` as its return value. Which `JdbcTemplate` callback interface is designed for this job, and why?

   1. A. `RowMapper`, because Spring invokes it once per row and the mapper can advance the `ResultSet` itself to pull in the remaining rows for the same order

      Misconception that a RowMapper may drive the cursor. `RowMapper.mapRow` is called once per row by the template, must map only the current row, and must not call `next()` on the ResultSet; a `query` with a RowMapper also returns a `List` of per-row objects, not one aggregate object.

   2. B. `RowCallbackHandler`, because it is the only callback that can accumulate state across rows and hand the finished object back as the query's return value

      Confuses stateful row processing with producing a return value. `RowCallbackHandler.processRow` returns `void` and the corresponding `JdbcTemplate.query` overload also returns `void`, so the result can only be dug out of the handler afterwards — it cannot be the query call's return value. It is intended for streaming side effects (writing a file, accumulating a running total) rather than building a returned object graph.

   3. C. `ResultSetExtractor`, because it is handed the entire `ResultSet` once and is responsible for iterating it, so it can fold many rows into a single returned objectCorrect answer

      Correct. `ResultSetExtractor.extractData` is invoked exactly once with the whole `ResultSet`; the implementation owns the iteration and returns an arbitrary object of type `T`, which `JdbcTemplate.query` returns directly. That is precisely the documented use case for mapping a one-to-many join into one aggregate root.

   4. D. `PreparedStatementCallback`, because collapsing multiple rows into one object requires working below the row-mapping layer, directly on the JDBC statement

      Confuses the low-level `execute` callbacks with result-set extraction. `PreparedStatementCallback` (like `ConnectionCallback`) is passed to `JdbcTemplate.execute` and gives you the raw `PreparedStatement` for operations the query/update API does not cover; the ordinary query API already exposes whole-ResultSet access, so dropping to this level is unnecessary here.

   Explanation

   Spring's JDBC query callbacks differ in how many times the template invokes them and in what the call returns. `ResultSetExtractor` is called once with the entire `ResultSet`, makes the implementation responsible for iterating, and returns any object the implementation builds — which is what lets rows from a one-to-many join be folded into a single aggregate that `query` then returns. A per-row mapper is called once per row and is contractually forbidden from moving the cursor, so it cannot consume sibling rows and yields a list rather than one object. A void row-processing handler can hold state but returns nothing from the query call, making it a fit for streaming side effects instead. The statement-level callbacks belong to the `execute` API for operations the query API cannot express, which is not the case here (Spring Framework Reference — Data Access: JdbcTemplate; `ResultSetExtractor` and `RowMapper` javadoc).

3. Question 3

   Which JdbcTemplate method is intended to fetch a single row mapped to a domain object, throwing if the query returns zero rows or more than one?

   1. A. batchUpdate(sql, batchArgs)

      batchUpdate runs a batch of update statements and returns per-statement affected-row counts; it does not map a single result row to a domain object.

   2. B. execute(sql) — for arbitrary/DDL statements

      execute runs arbitrary statements such as DDL and is not intended to fetch and map a single result row.

   3. C. query(sql, rowMapper) — returns a List and never throws based on row count

      query returns a List of any size and never throws based on the row count, so it cannot enforce the exactly-one-row contract required here.

   4. D. queryForObject(sql, rowMapper, args...)Correct answer

      queryForObject expects exactly one row and maps it via the RowMapper; it throws EmptyResultDataAccessException for zero rows and IncorrectResultSizeDataAccessException for more than one (Spring Framework 5.3 Reference, Querying with JdbcTemplate).

   Explanation

   Fetching a single row mapped to a domain object, with a hard guarantee that the query returned exactly one row, is the dedicated single-object query contract: zero rows and multiple rows are both surfaced as data-access exceptions. Methods that return a List, run arbitrary or DDL statements, or execute batched updates make no such row-count guarantee and are not meant to map one result row.

4. Question 4

   When is a ResultSetExtractor a better choice than a RowMapper?

   1. A. Never; RowMapper can always replace it

      A per-row RowMapper cannot cleanly fold multiple rows into one aggregated object, so ResultSetExtractor is not redundant.

   2. B. When you must process the entire ResultSet at once — e.g. fold many rows into a single result such as a Map of parent-to-childrenCorrect answer

      ResultSetExtractor receives the whole ResultSet in one extractData() call, so it can aggregate across rows into a single object such as a parent-to-children map.

   3. C. Only when the query returns exactly one row

      ResultSetExtractor is not limited to single-row results; its strength is precisely handling many rows together.

   4. D. For executing updates rather than queries

      ResultSetExtractor processes query results; updates are performed with update(), not a result-set callback.

   Explanation

   ResultSetExtractor hands you the entire ResultSet in a single extractData() call, letting you aggregate across rows into one composite result — for example folding parent and child rows into a single map. That whole-result view is something a callback invoked once per row cannot do cleanly, and it applies to queries, not to single-row cases or updates.

5. Question 5

   Compared with writing raw JDBC, what does JdbcTemplate take care of for you?

   1. A. Defining the object/relational mapping between entities and tables

      Object/relational mapping between entities and tables is provided by ORM frameworks such as JPA/Hibernate, not by JdbcTemplate.

   2. B. Acquiring and releasing the Connection, Statement, and ResultSet, and translating SQLExceptions into DataAccessExceptionCorrect answer

      JdbcTemplate removes the resource-management boilerplate by opening and closing the Connection, Statement, and ResultSet and applies exception translation from SQLException to DataAccessException, leaving you to write SQL and map results (Spring Framework 5.3 Reference, Using the JDBC Core Classes).

   3. C. Generating SQL automatically from repository method names

      Deriving SQL automatically from repository method names is a Spring Data feature, not something JdbcTemplate does; you still write the SQL yourself.

   4. D. Caching every query result indefinitely by default

      JdbcTemplate does not cache query results by default; result caching is a separate concern handled by other mechanisms.

   Explanation

   JdbcTemplate's core value over raw JDBC is handling the tedious resource lifecycle — acquiring and releasing connections, statements, and result sets — and translating vendor SQLExceptions into Spring's DataAccessException, while you still write the SQL and map the results. It deliberately does not add ORM entity mapping, method-name-derived SQL, or automatic result caching, which belong to other frameworks.

6. Question 6

   A Spring application uses `JdbcTemplate` for all persistence. A developer notices that when a statement violates a unique constraint, the code receives a `DuplicateKeyException` rather than a `java.sql.SQLException`. Which statement best describes the purpose and nature of Spring's `DataAccessException` hierarchy that produces this behaviour?

   1. A. It is a hierarchy of checked exceptions that forces every caller of a DAO method to declare or catch the persistence failure, guaranteeing errors are handled at each layer.

      Confuses Spring's hierarchy with JDBC's checked `SQLException`. Spring made `DataAccessException` and all its subclasses unchecked precisely so callers are not forced to handle failures they usually cannot recover from.

   2. B. It is a hierarchy of unchecked exceptions that abstracts persistence failures away from the specific technology and vendor, letting callers catch only the failure categories they can actually handle.Correct answer

      Correct: the reference documentation describes `DataAccessException` as the root of a consistent, technology-agnostic hierarchy of runtime (unchecked) exceptions, so application code is decoupled from JDBC, JPA, or any vendor's error codes and need only catch what it can recover from.

   3. C. It replaces the JDBC driver's error reporting entirely, so the original `SQLException` and its vendor error code are discarded once translation has occurred.

      Assumes translation is lossy. The translated `DataAccessException` wraps the original exception as its cause, so the underlying `SQLException` and vendor error code remain available for diagnosis.

   4. D. It is specific to Spring JDBC: only `JdbcTemplate` and related JDBC helpers throw it, while JPA, Hibernate, and other supported technologies surface their own native exception types unchanged.

      Mistakes the hierarchy for a JDBC-only feature. The same hierarchy is used across Spring's data access support — JPA, Hibernate, and JDBC failures are all translated into the same `DataAccessException` subclasses, which is the point of a *consistent* hierarchy.

   Explanation

   Spring's consistent exception hierarchy is rooted at `DataAccessException`, an unchecked (runtime) exception. Its purpose is twofold: it hides the specific persistence API and vendor error codes behind a common set of meaningful subclasses such as `DuplicateKeyException` or `DataIntegrityViolationException`, and because the exceptions are unchecked, application code is not forced to catch failures it cannot meaningfully recover from. Making them checked would defeat that design goal; the translated exception retains the original `SQLException` as its cause rather than discarding it; and the hierarchy deliberately spans all supported data access technologies — JPA and Hibernate operations are translated into the very same exception types, not left as native ones.

7. Question 7

   Which JdbcTemplate method executes an INSERT, UPDATE, or DELETE and returns the number of affected rows?

   1. A. update(sql, args...)Correct answer

      update() is JdbcTemplate's DML method: it runs INSERT/UPDATE/DELETE and returns the number of affected rows.

   2. B. execute(sql), which returns the affected row count

      execute() is for arbitrary statements such as DDL; it does not return a DML affected-row count, so the described return value is wrong.

   3. C. query(sql, rowMapper)

      query() is for SELECT statements and returns mapped rows, not a count of modified rows.

   4. D. queryForObject(sql, type)

      queryForObject() is for reading a single value from a SELECT, not for executing DML or returning an affected-row count.

   Explanation

   Data-modifying statements go through update(), which executes the DML and returns the count of rows it affected. The query methods are reserved for SELECTs and return mapped data, while execute() runs arbitrary statements like DDL and does not report a DML row count.

8. Question 8

   An INSERT through JdbcTemplate violates a unique constraint. What is thrown?

   1. A. Nothing — the row is silently skipped

      A constraint violation is a genuine failure that JdbcTemplate surfaces as an exception; it is never silently ignored.

   2. B. javax.validation.ConstraintViolationException

      This exception comes from Bean Validation (JSR-303) on Java objects, not from a database-level constraint reported through JDBC.

   3. C. A checked java.sql.SQLException you are forced to catch

      The whole point of Spring's translation layer is that the raw checked SQLException is not propagated; callers get an unchecked exception instead.

   4. D. A subclass of the unchecked DataAccessException, such as DuplicateKeyExceptionCorrect answer

      Spring translates the vendor SQLException into its unchecked hierarchy, here DuplicateKeyException (a DataIntegrityViolationException).

   Explanation

   JdbcTemplate converts the vendor SQLException raised by a unique-constraint violation into Spring's unchecked DataAccessException hierarchy, yielding a DuplicateKeyException. Because the translated exception is unchecked, callers are not forced to catch a raw java.sql.SQLException, and the failure is neither ignored nor confused with the Bean Validation exception of the same style of name.