Using JdbcTemplate with Spring Boot

Introduction

Spring Boot provides support to typical JDBC operations via jdbcTemplate. With JDBC templates, you can perform complex database operations which are not possible through JPA.

Starter Dependencies

JDBC templates feature is part of the Spring JDBC module. This means that either the JPA starter or the JDBC starter will bring this support. As we don’t need JPA features for this example, We are using JDBC starter in our spring boot application.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
Code language: HTML, XML (xml)

I’m also importing the h2 dependency for the embedded database. However, it is up to you to use a different database.

Initial Data for Setup

To demonstrate the jdbcTemplate methods, I am loading some initial data using schema.sql and data.sql via Spring boot autoconfiguration.

create table user_entity (
    id integer generated by default as identity,
    first_name varchar(255),
    last_name  varchar(255),
    primary key (id)
);
Code language: SQL (Structured Query Language) (sql)

insert into user_entity (id,first_name,last_name) values (1,'Homer','Simpson');
insert into user_entity (id,first_name,last_name) values (2,'Bart','Simpson');
insert into user_entity (id,first_name,last_name) values (3,'Lisa','Simpson');
Code language: SQL (Structured Query Language) (sql)

CRUD with JdbcTemplate in Spring boot

The auto-configuration makes sure that a JdbcTemplate bean exists in the application context. We can auto wire this bean wherever we like. For instance, You can autowire it into any component as shown below.

@Service
public class TestService {

    private final JdbcTemplate jdbcTemplate;

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

   //use jdbcTemplate in your methods for insert, query, update and delete

}
Code language: Java (java)

Alternatively, you can use @Autowired for autowiring beans, but that’s just bad programming.

the jdbcTemplate provides various helper methods to deal with CRUD operations in our Spring Boot application.

Query with JdbcTemplate

To read one or more records from the database, You need to use the jdbcTemplate.query() methods. These methods provide variations toward how you can handle the retrieved results. For instance, the simplest way to query and handle results is via the query(String, RowMapper) method.

public List<UserDto> queryFromDatabase() {

    return jdbcTemplate.query("select id,first_name,last_name from user_entity where id < 3", new RowMapper<UserDto>() {
        @Override
        public UserDto mapRow(ResultSet resultSet, int i) throws SQLException {
            UserDto userDto = new UserDto();
            userDto.setId(resultSet.getInt("id"));
            userDto.setFirstName(resultSet.getString("first_name"));
            userDto.setLastName(resultSet.getString("last_name"));
            return userDto;
        }
    });

}
Code language: Java (java)

This method uses RowMapper to automatically map the results into a DTO object.

Furthermore, Spring Boot provides variations to these jdbcTemplate methods

public List<UserDto> queryFromDatabaseUsingPreparedStatement(int idLessThan) {

    return jdbcTemplate.query("select id,first_name,last_name from user_entity where id < ? ",
            new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setInt(1, idLessThan);
                }
            }, new RowMapper<UserDto>() {
                @Override
                public UserDto mapRow(ResultSet resultSet, int i) throws SQLException {
                    UserDto userDto = new UserDto();
                    userDto.setId(resultSet.getInt("id"));
                    userDto.setFirstName(resultSet.getString("first_name"));
                    userDto.setLastName(resultSet.getString("last_name"));
                    return userDto;
                }
            });

}
Code language: Java (java)

Also, we can rewrite these implementations as java 8 lambda expressions as shown below.

return jdbcTemplate.query("select id,first_name,last_name from user_entity where id<? ",
        preparedStatement -> preparedStatement.setInt(1, idLessThan), 
        (resultSet, i) -> {
            UserDto userDto = new UserDto();
            userDto.setId(resultSet.getInt(1));
            userDto.setFirstName(resultSet.getString(2));
            userDto.setLastName(resultSet.getString(3));
            return userDto;
        });
Code language: Java (java)

You can even query for a single record using the queryForObject methods the same way.

Insert using JdbcTemplate

Spring JDBC provides two template methods for data insertion. They are, execute() and update(). Overall, both methods help insert data. But, the update method can provide how many rows it inserted or updated. For this reason, you should always use updates. For example, take a look at this insert using jdbcTemplate.

int rowsInserted = jdbcTemplate.update("insert into user_entity (id,first_name,last_name) values (5, 'Marge','Simpson'),(6, 'Maggie','Simpson')");

System.out.println("Number of rows updated = " + rowsInserted);
Code language: Java (java)

The above snippet should ideally print 2. Also, you can use prepared statements as shown earlier in select statements.

Update and Deletec operations

As you have seen before, you can use the update methods for both inserts and updates. With the help of prepared statements, update operations are easy to perform.

public void update(){
    jdbcTemplate.update("update user_entity set last_name = 'Griffin' where first_name = 'Peter'",preparedStatement -> {
        preparedStatement.setString(1,"Griffin");
        preparedStatement.setString(2,"Peter");
    });
}
Code language: Java (java)

Also, delete is some form of update in a way. For this reason, spring boot doesn’t provide any delete methods.

public void delete(){
    int rowsDeleted = jdbcTemplate.update("delete from user_entity where id<10");
    System.out.println("Number of rows deleted = " + rowsDeleted);
}
Code language: Java (java)

Stored procedures in Spring Boot

One important place where the JdbcTemplate overcomes JPA is its ease of use. the JdbcTemplate help towards complex operations like database functions and procedures. In cases like these, the execute() method seem more appropriate.

jdbcTemplate.update("call MY_STORED_PROC(?, ?)", param1, param2);
Code language: Java (java)

Manual jdbcTemplate Configuration

Even though the autowired instance is more than enough, you may need to provide your own template in some cases. For instance, you may want to timeout your queries sooner. Or, you may want to set the result set fetch-size to be lower for performance improvements. You can achieve all these by providing a custom bean of type jdbcTemplate as shown here.

@Bean
JdbcTemplate getJdbcTemplate(DataSource dataSource) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    jdbcTemplate.setDataSource(dataSource);
    jdbcTemplate.setQueryTimeout(20); //20 seconds
    jdbcTemplate.setFetchSize(10);  //fetch 10 rows at a time
    return jdbcTemplate;
}
Code language: Java (java)

Conclusion

To summarize, we learned how to use the jdbcTemplate with Spring boot and how to configure and provide a custom jdbcTemplate bean configuration with an example.