Plain JDBC can require a surprising amount of code for a simple database operation. JdbcTemplate is a Spring tool that simplifies access to relational databases using JDBC. It depends on a data source to obtain database connections, while application code supplies the SQL and handles application-specific results. section 12.2 section 12.5
JdbcTemplate is a practical choice for a small application or a persistence layer that does not need another persistence framework. It can also make existing JDBC code shorter without changing to an object-relational mapping approach. section 12.2 section 1.4.4
A typical repository uses JdbcTemplate in two ways:
- Call update() with SQL and parameter values to change table data. section 12.5 section 12.2
- Call query() with SQL and a RowMapper to retrieve data. JdbcTemplate obtains a connection, sends the query, and calls the mapper for each result row. section 12.2 section 12.5
Listing 12.6 Using JdbcTemplate to add a new record to a table
@Repository
public class PurchaseRepository {
private final JdbcTemplate jdbc;
public PurchaseRepository(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
public void storePurchase(Purchase purchase) { #A
String sql = #B
"INSERT INTO purchase (`product`, `price`) VALUES (?, ?)";
jdbc.update(sql, #C
purchase.getProduct(),
purchase.getPrice());
}
}The repository receives a JdbcTemplate through its constructor. Its storePurchase method supplies an INSERT statement and values for the two placeholders. section 12.2
Listing 12.7 Using JdbcTemplate to select records from a database
@Repository
public class PurchaseRepository {
// Omitted code
public List<Purchase> findAllPurchases() { #A
String sql = "SELECT * FROM purchase"; #B
RowMapper<Purchase> purchaseRowMapper = (r, i) -> { #C
Purchase rowObject = new Purchase(); #D
rowObject.setId(r.getInt("id")); #D
rowObject.setProduct(r.getString("product")); #D
rowObject.setPrice(r.getBigDecimal("price")); #D
return rowObject; #D
};
return jdbc.query(sql, purchaseRowMapper); #E
}
}The RowMapper turns each returned row into a Purchase object; query() returns the resulting list. section 12.2
A REST controller can delegate its endpoint actions to the repository, keeping database operations in one place. Spring Boot generally supplies default data source and JdbcTemplate objects, but custom beans can replace them. An application can also associate separate JdbcTemplate objects with multiple data sources, using @Qualifier to distinguish objects of the same type. section 12.2 section 12.5


