Spring JDBC
Spring JDBC is a framework that simplifies database access and reduces boilerplate code when working with relational databases. It provides a consistent way to interact with databases using JdbcTemplate, which abstracts common tasks like connection handling, query execution, and result mapping.
Configuring Spring’s JdbcTemplate
To use JdbcTemplate in Spring, you need to configure a DataSource and initialize the JdbcTemplate bean.
/* application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=username
spring.datasource.password=password
// often the following is not necessary in Spring Boot, since it can deduce it for most databases from the url
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
*/
@Configuration
public class DataSourceConfig {
@Bean
public DataSource dataSource() {
return new DriverManagerDataSource("jdbc:mysql://localhost:3306/mydb", "username", "password");
}
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
Spring Boot will auto-configure a JdbcTemplate bean when these properties are provided, so you don't need to define a dataSource explicitly.
Executing Queries Using JdbcTemplate
Insert/Update/Delete:
These are write operations in the database. The update() method is used for operations that modify the database. The placeholders ? are replaced with the arguments that follow by their respective order.
// Insert/Update/Delete:
jdbcTemplate.update("INSERT INTO users (id, name) VALUES (?, ?)", 1, "John");
jdbcTemplate.update("UPDATE users SET name = ? WHERE id = ?", "Jane", 1);
jdbcTemplate.update("DELETE FROM users WHERE id = ?", 1);Query for a Single Value
Use queryForObject() when you expect a single value as the result, such as a single column or aggregate value. The first parameter is the query itself, the second parameter is an object that helps us get what we want, by populating the ? placeholders. For the third parameter we set the return type.
// Query for a Single Value
String name = jdbcTemplate.queryForObject(
"SELECT name FROM users WHERE id = ?",
new Object[]{1},
String.class
);Mapping a Single Row (RowMapper)
Use queryForObject() with a custom RowMapper when a query returns exactly one row. The following query maps its result to a User object where id and name are retrieved from the result set. The lambda (rs, rowNum) -> new User(...) serves as a RowMapper.
User user = jdbcTemplate.queryForObject(
"SELECT id, name FROM users WHERE id = ?",
new Object[]{1},
(rs, rowNum) -> new User(rs.getInt("id"), rs.getString("name"))
);Mapping Multiple Rows (RowMapper)
When using query() with a RowMapper you can map multiple rows to a collection. The example query fetches all id and name values from the users table. Each row is mapped to a User object using the lambda function. The result is a List<User>.
List<User> users = jdbcTemplate.query(
"SELECT id, name FROM users",
(rs, rowNum) -> new User(rs.getInt("id"), rs.getString("name"))
);Using ResultSetExtractor
Use ResultSetExtractor when you need to manually process the result set, such as handling complex mappings or aggregations. The ResultSetExtractor manually iterates over the ResultSet (rs) to build a list of User objects. It is useful when the data mapping logic is more complex than what a RowMapper can handle.
List<User> users = jdbcTemplate.query(
"SELECT id, name FROM users",
rs -> {
List<User> userList = new ArrayList<>();
while (rs.next()) {
userList.add(new User(rs.getInt("id"), rs.getString("name")));
}
return userList;
}
);Query for a List of Values
Use queryForList() when you need a simple list of results (e.g., single-column data).
List<String> names = jdbcTemplate.queryForList(
"SELECT name FROM users",
String.class
);Handling Data Access Exceptions
Spring translates SQL exceptions into DataAccessException, a consistent, unchecked exception hierarchy. Common exceptions include:
- BadSqlGrammarException: Syntax errors in SQL.
- DuplicateKeyException: When a unique constraint is violated.
- DataIntegrityViolationException: Invalid data entry or constraint violation.
- EmptyResultDataAccessException: When no result is found for a query expecting one.
Here an example on how exceptions could be handled:
try {
jdbcTemplate.update("INSERT INTO users (id, name) VALUES (?, ?)", 1, "John");
} catch (DuplicateKeyException e) {
System.err.println("Duplicate key error: " + e.getMessage());
} catch (DataAccessException e) {
System.err.println("Database error: " + e.getMessage());
}Best Practices for Using JdbcTemplate
Use Parameterized Queries
- Always use ? placeholders with values to prevent SQL injection.
- Avoid concatenating user input into SQL queries.
Use RowMapper or BeanPropertyRowMapper for Result Mapping
- Use RowMapper for mapping query results to objects.
- Use BeanPropertyRowMapper for simple mappings to POJOs (where property names match column names).
Leverage Spring's Exception Translation
- Use DataAccessException for consistent handling of database-related errors.
- Avoid catching SQL exceptions directly.
Handle Transactions Properly
- Use Spring’s transaction management (@Transactional) to ensure data consistency in multi-step operations.
Reuse JdbcTemplate
- Define JdbcTemplate as a Spring Bean for reuse instead of creating it manually in every class.
Optimize Large Data Processing
- Use ResultSetExtractor for handling large datasets to minimize memory consumption.
Separate SQL Queries
- Keep SQL queries in a separate file or constants to improve readability and maintainability.
Leverage Connection Pooling
- spring.datasource.hikari.maximum-pool-size=10
Profile Query Performance
- Log queries and measure performance using tools or techniques like Spring Boot Actuator, AOP or database profiling tools.
Avoid Over-Retrieving Data
- Select only the columns you need instead of using SELECT *.
