Spring JdbcTemplate Tutorial

Spring JdbcTemplate is part of the Spring Framework, and it simplifies database interactions, making it easier to work with relational databases in Java applications. JdbcTemplate abstracts away much of the boilerplate code associated with JDBC (Java Database Connectivity), providing an easy-to-use interface for querying and updating databases.

In this tutorial, we’ll walk through the basics of using Spring JdbcTemplate to interact with a relational database, with an emphasis on setting up JdbcTemplate, performing common CRUD operations, and handling exceptions.

Prerequisites

To follow this tutorial, you need:

1. Spring Framework: Ensure that you have the latest version of Spring in your project. If you’re using Spring Boot, Spring JdbcTemplate is already included.
2. Database: A relational database like MySQL, PostgreSQL, or H2.
3. JDBC Driver: Include the relevant JDBC driver for your database in your pom.xml or build.gradle if using Maven or Gradle respectively.

Step 1: Setting Up Spring JdbcTemplate

First, ensure that your Spring project is correctly set up with the required dependencies. In a Maven-based project, you’ll need to add the following dependencies to your pom.xml:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.20</version>  <!-- Use the latest version -->
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.3.20</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.3.20</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</version>  <!-- Database dependency (H2 as an example) -->
</dependency>

In your applicationContext.xml (or Java configuration), configure a DataSource and JdbcTemplate bean:

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value="org.h2.Driver"/>
    <property name="url" value="jdbc:h2:mem:testdb"/>
    <property name="username" value="sa"/>
    <property name="password" value=""/>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="dataSource"/>
</bean>

Alternatively, if you’re using Java-based configuration, you can configure the DataSource and JdbcTemplate in a configuration class:

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:mem:testdb");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        return dataSource;
    }

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

Step 2: Defining a Model

Let’s assume you’re working with a simple User model that contains the following properties: id, name, and email.

public class User {
    private int id;
    private String name;
    private String email;

    // Getters and setters
}

Step 3: Basic CRUD Operations Using JdbcTemplate

1. Create Operation: Insert Data

To insert a new record into the database, we can use JdbcTemplate.update().

public class UserDao {
    private JdbcTemplate jdbcTemplate;

    public UserDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int addUser(User user) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        return jdbcTemplate.update(sql, user.getName(), user.getEmail());
    }
}

2. Read Operation: Query Data

To retrieve data from the database, we use JdbcTemplate.queryForObject() for a single result or JdbcTemplate.query() for multiple results. For this, we’ll map the result set to a User object.

public User getUserById(int id) {
    String sql = "SELECT * FROM users WHERE id = ?";
    return jdbcTemplate.queryForObject(sql, new Object[]{id}, (rs, rowNum) ->
        new User(rs.getInt("id"), rs.getString("name"), rs.getString("email"))
    );
}

3. Update Operation: Modify Data

To update an existing record, use JdbcTemplate.update() again.

public int updateUser(User user) {
    String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
    return jdbcTemplate.update(sql, user.getName(), user.getEmail(), user.getId());
}

4. Delete Operation: Remove Data

To delete a record, use JdbcTemplate.update() with the appropriate SQL query.

public int deleteUser(int id) {
    String sql = "DELETE FROM users WHERE id = ?";
    return jdbcTemplate.update(sql, id);
}

Step 4: Handling Exceptions

JdbcTemplate wraps exceptions in its own hierarchy. You don’t need to worry about SQLException. Instead, you can catch DataAccessException, which is the base class for exceptions in Spring’s JDBC framework.

try {
    int result = userDao.addUser(user);
    if (result > 0) {
        System.out.println("User added successfully.");
    }
} catch (DataAccessException e) {
    System.err.println("Error while adding user: " + e.getMessage());
}

Step 5: Using NamedParameterJdbcTemplate (Optional)

For complex queries or when you prefer to use named parameters instead of positional ones, you can use NamedParameterJdbcTemplate.

NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(dataSource);
String sql = "SELECT * FROM users WHERE name = :name";
Map<String, Object> params = new HashMap<>();
params.put("name", "John");

User user = namedTemplate.queryForObject(sql, params, (rs, rowNum) ->
    new User(rs.getInt("id"), rs.getString("name"), rs.getString("email"))
);

Conclusion

Spring JdbcTemplate simplifies database operations by eliminating repetitive JDBC code. This tutorial covered the basic steps for setting up JdbcTemplate and performing common CRUD operations in a Spring-based application. With JdbcTemplate, developers can focus on the core logic of the application while Spring handles the boilerplate database interactions.

In a real-world application, you would likely use Spring Data or other advanced features, but JdbcTemplate remains a powerful and lightweight option for database operations in Spring.