JNR-026

Spring JDBC

5 Lessons
2.5 h practice (estimate)

Start module

On this page 8 topics

Overview
Learning outcomes
Lessons
Lab and exercises
Quiz
Common mistakes
Assessment
Career relevance

Overview

Topics

JdbcTemplate
parameter binding
mapping query results

Learn first

2.5 h of practice (estimate)

Learning outcomes

Configure a DataSource through Spring Boot properties.
Query and update with JdbcClient/JdbcTemplate and named parameters.
Map rows to records with RowMapper or automatic mapping.
Explain how Spring translates SQLExceptions into its DataAccessException hierarchy.

Lessons

Lab and exercises

Lab

JNR-026-LAB1 · Products on PostgreSQL

Outcomes: 1–4 · Time: ~2.5 h

Add the JDBC starter and driver; configure the datasource in application.yml with credentials from environment variables.
Implement ProductRepository (find by id, list, save, update, delete) with JdbcClient and named parameters, behind the existing interface.
Use one RowMapper shared by all queries.
Translate a duplicate SKU to a domain exception in the repository.
Write an integration test against a real PostgreSQL database.

Acceptance: no controller or service changes needed; integration test passes against a real database.

Exercises (assessment: implement a reporting query repository method)

ID	Outcome	Task
JNR-026-EX01	1	Write the `application.yml` datasource block without a hard-coded password.
JNR-026-EX02	2	Write `update` and `delete` methods with named parameters and return whether a row was affected.
JNR-026-EX03	3	Map a `product` row to a record with a `RowMapper`, then do it again with `query(Product.class)`.
JNR-026-EX04	4	Name the exception Spring throws for a unique violation and for `.single()` with no row.
JNR-026-EX05	2, 3	Reporting method `salesByCategory()` returning `record CategoryTotal(String category, BigDecimal total)` from a `group by` query.
JNR-026-EX06	4	Explain why catching `DataAccessException` in every repository method is a mistake.

Show solutions

EX01 url: jdbc:postgresql://localhost:5432/shop, username: ${DB_USER}, password: ${DB_PASSWORD}.
EX02 int n = jdbc.sql("update product set price = :p where id = :id").param("p", p).param("id", id).update(); return n == 1;
EX03 (rs, i) -> new Product(rs.getLong("id"), rs.getString("name"), rs.getBigDecimal("price")) versus jdbc.sql(sql).query(Product.class).list().
EX04 DuplicateKeyException (a DataIntegrityViolationException); EmptyResultDataAccessException.
EX05 select c.name as category, sum(p.price) as total from product p join category c on c.id = p.category_id group by c.name mapped with .query(CategoryTotal.class).list().
EX06 It hides real failures, repeats code, and leaves nobody able to decide; catch a specific subclass where you can act and let the rest reach the global handler.

Quiz

Outcomes: O1 DataSource configuration · O2 JdbcClient/JdbcTemplate · O3 row mapping · O4 exception translation.

Show answers

Q01: D
Q02: B
Q03: A
Q04: C
Q05: B
Q06: A
Q07: C
Q08: D
Q09: B
Q10: C

Practical part

Six exercises JNR-026-EX01 to EX06 plus the lab; the assessment is implementing a reporting query repository method (EX05). Pass: at least five exercises correct and the lab integration test green.

Common mistakes

Leaking ResultSet handling into services; catching DataAccessException everywhere.

Assessment

Implement a reporting query repository method.

Career relevance

JDBC remains the right choice for reporting and bulk work (MID-022).