Spring JdbcTemplate

Introduction

Spring JdbcTemplate is a powerful mechanism provided by the Spring Framework that simplifies the use of JDBC (Java Database Connectivity) and helps to avoid common errors. It handles the resource acquisition, connection management, error handling, and statement cleanup, so you can focus on executing SQL statements and processing results.

Working directly with JDBC requires writing a lot of boilerplate code to:

Open database connections
Create statements
Handle exceptions
Close connections and resources
Process result sets

JdbcTemplate abstracts away these tedious tasks and provides a cleaner, more intuitive API for database operations.

Why Use Spring JdbcTemplate?

Simplified Exception Handling: Converts JDBC's checked exceptions to Spring's unchecked data access exceptions
Resource Management: Automatically handles opening and closing database connections
Boilerplate Reduction: Eliminates repetitive code for common database operations
SQL Error Handling: Provides meaningful error messages for SQL issues
Integration with Spring: Works well with Spring's transaction management

Getting Started with JdbcTemplate

Dependencies

To use Spring JdbcTemplate, you need to add the following dependencies to your project:

xml
<!-- For Maven -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.23</version> <!-- Use the latest version -->
</dependency>

<!-- You'll also need a database driver, for example MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>

For Gradle:

groovy
implementation 'org.springframework:spring-jdbc:5.3.23'
implementation 'mysql:mysql-connector-java:8.0.30'

Configuring JdbcTemplate

There are multiple ways to configure JdbcTemplate in Spring:

1. Using Java Configuration

java
@Configuration
public class DatabaseConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
        dataSource.setUsername("user");
        dataSource.setPassword("password");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

2. Using XML Configuration

xml
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/mydb" />
    <property name="username" value="user" />
    <property name="password" value="password" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

3. Using Spring Boot

With Spring Boot, the configuration is even simpler. Just add the spring-boot-starter-jdbc dependency and configure your database properties in application.properties or application.yml:

properties
# application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=user
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Then, you can autowire JdbcTemplate wherever needed:

java
@Service
public class UserService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // Use jdbcTemplate here
}

Basic CRUD Operations with JdbcTemplate

Let's explore the fundamental operations you can perform with JdbcTemplate using a simple User entity:

java
public class User {
    private int id;
    private String name;
    private String email;
    private int age;
    
    // Getters and setters
    // Constructor
}

Creating a Table

While you typically define tables in database scripts or using migration tools, you can create tables programmatically with JdbcTemplate:

java
jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS users (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY, " +
                    "name VARCHAR(100) NOT NULL, " +
                    "email VARCHAR(100) NOT NULL UNIQUE, " +
                    "age INT)");

INSERT Operation

java
public void addUser(User user) {
    String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
    
    int rowsAffected = jdbcTemplate.update(sql, 
                                         user.getName(), 
                                         user.getEmail(), 
                                         user.getAge());
                                         
    if (rowsAffected > 0) {
        System.out.println("A new user has been inserted.");
    }
}

UPDATE Operation

java
public void updateUser(User user) {
    String sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
    
    int rowsAffected = jdbcTemplate.update(sql, 
                                         user.getName(), 
                                         user.getEmail(), 
                                         user.getAge(),
                                         user.getId());
                                         
    if (rowsAffected > 0) {
        System.out.println("User with ID " + user.getId() + " has been updated.");
    }
}

DELETE Operation

java
public void deleteUser(int id) {
    String sql = "DELETE FROM users WHERE id = ?";
    
    int rowsAffected = jdbcTemplate.update(sql, id);
    
    if (rowsAffected > 0) {
        System.out.println("User with ID " + id + " has been deleted.");
    }
}

SELECT Operations

Query for a Single Object

java
public User getUserById(int id) {
    String sql = "SELECT * FROM users WHERE id = ?";
    
    try {
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new UserRowMapper());
    } catch (EmptyResultDataAccessException e) {
        return null; // User not found
    }
}

// RowMapper to convert result set to User object
class UserRowMapper implements RowMapper<User> {
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setEmail(rs.getString("email"));
        user.setAge(rs.getInt("age"));
        return user;
    }
}

Query for Multiple Objects

java
public List<User> getAllUsers() {
    String sql = "SELECT * FROM users";
    return jdbcTemplate.query(sql, new UserRowMapper());
}

Query for Scalar Values

java
public int getUserCount() {
    String sql = "SELECT COUNT(*) FROM users";
    return jdbcTemplate.queryForObject(sql, Integer.class);
}

public String getUserNameById(int id) {
    String sql = "SELECT name FROM users WHERE id = ?";
    try {
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, String.class);
    } catch (EmptyResultDataAccessException e) {
        return null; // User not found
    }
}

Advanced JdbcTemplate Features

Batch Updates

Batch operations allow executing multiple SQL statements in a single database call, which improves performance:

java
public void batchInsertUsers(List<User> users) {
    String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
    
    List<Object[]> batchArgs = new ArrayList<>();
    for (User user : users) {
        Object[] userArgs = {user.getName(), user.getEmail(), user.getAge()};
        batchArgs.add(userArgs);
    }
    
    int[] rowsAffected = jdbcTemplate.batchUpdate(sql, batchArgs);
    
    System.out.println("Number of users inserted: " + rowsAffected.length);
}

Named Parameter JDBC Template

The NamedParameterJdbcTemplate class extends the capabilities of JdbcTemplate by allowing you to use named parameters instead of the traditional "?" placeholders:

java
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public User getUserByEmail(String email) {
    String sql = "SELECT * FROM users WHERE email = :email";
    
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("email", email);
    
    try {
        return namedParameterJdbcTemplate.queryForObject(sql, params, new UserRowMapper());
    } catch (EmptyResultDataAccessException e) {
        return null; // User not found
    }
}

public void updateUserAge(int id, int newAge) {
    String sql = "UPDATE users SET age = :age WHERE id = :id";
    
    Map<String, Object> params = new HashMap<>();
    params.put("age", newAge);
    params.put("id", id);
    
    namedParameterJdbcTemplate.update(sql, params);
}

Retrieving Auto-Generated Keys

When inserting records, you often want to retrieve the auto-generated primary key:

java
public int addUserAndGetId(User user) {
    String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
    
    KeyHolder keyHolder = new GeneratedKeyHolder();
    
    jdbcTemplate.update(connection -> {
        PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, user.getName());
        ps.setString(2, user.getEmail());
        ps.setInt(3, user.getAge());
        return ps;
    }, keyHolder);
    
    return keyHolder.getKey().intValue();
}

Best Practices

Use Parameter Binding: Always use parameter binding (? or named parameters) instead of string concatenation to prevent SQL injection attacks.
Implement Proper Exception Handling: While JdbcTemplate converts checked exceptions to unchecked ones, you should still handle these exceptions appropriately.
Close Resources: Although JdbcTemplate handles resource cleanup, be mindful of any resources you open manually.
Use Transactions: Combine JdbcTemplate with Spring's transaction management for atomic operations.

java
@Service
@Transactional
public class UserService {
    // JdbcTemplate operations wrapped in transactions
}

Consider Using NamedParameterJdbcTemplate: Makes SQL queries more readable and maintainable, especially for complex queries with many parameters.
Define Row Mappers as Reusable Components: Create reusable row mappers for entities to avoid code duplication.
Use Connection Pooling: Configure a connection pool (like HikariCP, DBCP, or C3P0) for better performance in production environments.

Real-World Example: User Management System

Let's integrate all we've learned into a simple but realistic user management system:

java
@Service
public class UserService {
    
    private final JdbcTemplate jdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private final UserRowMapper userRowMapper = new UserRowMapper();
    
    @Autowired
    public UserService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    }
    
    // Create a new user and return the generated ID
    public int createUser(User user) {
        String sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("name", user.getName())
                .addValue("email", user.getEmail())
                .addValue("age", user.getAge());
        
        namedParameterJdbcTemplate.update(sql, params, keyHolder, new String[]{"id"});
        
        return keyHolder.getKey().intValue();
    }
    
    // Find user by ID
    public Optional<User> findById(int id) {
        try {
            String sql = "SELECT * FROM users WHERE id = ?";
            User user = jdbcTemplate.queryForObject(sql, new Object[]{id}, userRowMapper);
            return Optional.ofNullable(user);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }
    
    // Find users by age range
    public List<User> findByAgeRange(int minAge, int maxAge) {
        String sql = "SELECT * FROM users WHERE age BETWEEN :minAge AND :maxAge";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("minAge", minAge)
                .addValue("maxAge", maxAge);
        
        return namedParameterJdbcTemplate.query(sql, params, userRowMapper);
    }
    
    // Update user details
    public boolean updateUser(User user) {
        String sql = "UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("name", user.getName())
                .addValue("email", user.getEmail())
                .addValue("age", user.getAge())
                .addValue("id", user.getId());
        
        int rowsAffected = namedParameterJdbcTemplate.update(sql, params);
        return rowsAffected > 0;
    }
    
    // Delete user
    @Transactional
    public boolean deleteUser(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        int rowsAffected = jdbcTemplate.update(sql, id);
        return rowsAffected > 0;
    }
    
    // Get user statistics
    public Map<String, Object> getUserStats() {
        Map<String, Object> stats = new HashMap<>();
        
        stats.put("totalUsers", jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class));
        stats.put("averageAge", jdbcTemplate.queryForObject("SELECT AVG(age) FROM users", Double.class));
        stats.put("minAge", jdbcTemplate.queryForObject("SELECT MIN(age) FROM users", Integer.class));
        stats.put("maxAge", jdbcTemplate.queryForObject("SELECT MAX(age) FROM users", Integer.class));
        
        return stats;
    }
    
    // Batch operations
    @Transactional
    public int[] batchInsertUsers(List<User> users) {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        
        List<Object[]> batchArgs = users.stream()
                .map(user -> new Object[]{user.getName(), user.getEmail(), user.getAge()})
                .collect(Collectors.toList());
        
        return jdbcTemplate.batchUpdate(sql, batchArgs);
    }
    
    static class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setEmail(rs.getString("email"));
            user.setAge(rs.getInt("age"));
            return user;
        }
    }
}

Controller Example

To complete the example, here's a simple REST controller using the service:

java
@RestController
@RequestMapping("/api/users")
public class UserController {

    private final UserService userService;
    
    @Autowired
    public UserController(UserService userService) {
        this.userService = userService;
    }
    
    @PostMapping
    public ResponseEntity<Map<String, Object>> createUser(@RequestBody User user) {
        int id = userService.createUser(user);
        Map<String, Object> response = Collections.singletonMap("id", id);
        return ResponseEntity.status(HttpStatus.CREATED).body(response);
    }
    
    @GetMapping("/{id}")
    public ResponseEntity<User> getUser(@PathVariable int id) {
        return userService.findById(id)
                .map(ResponseEntity::ok)
                .orElse(ResponseEntity.notFound().build());
    }
    
    @GetMapping("/age-range")
    public ResponseEntity<List<User>> getUsersByAgeRange(
            @RequestParam int min, @RequestParam int max) {
        List<User> users = userService.findByAgeRange(min, max);
        return ResponseEntity.ok(users);
    }
    
    @GetMapping("/stats")
    public ResponseEntity<Map<String, Object>> getUserStats() {
        return ResponseEntity.ok(userService.getUserStats());
    }
    
    @PutMapping("/{id}")
    public ResponseEntity<Void> updateUser(@PathVariable int id, @RequestBody User user) {
        user.setId(id);
        boolean updated = userService.updateUser(user);
        return updated ? ResponseEntity.ok().build() : ResponseEntity.notFound().build();
    }
    
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteUser(@PathVariable int id) {
        boolean deleted = userService.deleteUser(id);
        return deleted ? ResponseEntity.ok().build() : ResponseEntity.notFound().build();
    }
}

Summary

Spring JdbcTemplate provides a powerful abstraction over JDBC, making database operations simpler and less error-prone. It handles the boilerplate code for opening connections, executing SQL, processing results, and closing resources, all while providing meaningful exceptions.

Key takeaways:

JdbcTemplate reduces boilerplate JDBC code dramatically
It handles resource management and exception conversion
Parameter binding helps prevent SQL injection
NamedParameterJdbcTemplate makes complex queries more readable
Use row mappers to convert result sets to domain objects
JdbcTemplate integrates well with Spring's transaction management

While Spring JdbcTemplate is powerful, for more complex applications, you might want to explore higher-level abstractions like Spring Data JPA, which builds on top of JPA to simplify data access even further.

Additional Resources

Practice Exercises

Create a simple application that manages a product inventory using Spring JdbcTemplate.
Implement pagination functionality to retrieve users in chunks.
Build a query method that allows searching users by name patterns.
Implement an audit logging mechanism that tracks changes to the users table.
Create a method that performs a complex join operation between users and another table (e.g., orders).
Implement optimistic locking with JdbcTemplate to handle concurrent updates.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Why Use Spring JdbcTemplate?​

Getting Started with JdbcTemplate​

Dependencies​

Configuring JdbcTemplate​

1. Using Java Configuration​

2. Using XML Configuration​

3. Using Spring Boot​

Basic CRUD Operations with JdbcTemplate​

Creating a Table​

INSERT Operation​

UPDATE Operation​

DELETE Operation​

SELECT Operations​

Query for a Single Object​

Query for Multiple Objects​

Query for Scalar Values​

Advanced JdbcTemplate Features​

Batch Updates​

Named Parameter JDBC Template​

Retrieving Auto-Generated Keys​

Best Practices​

Real-World Example: User Management System​

Controller Example​

Summary​

Additional Resources​

Practice Exercises​