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 Spring JDBC module. This means that either JPA starter or JDBC starter will bring this support. As we don’t need JPA features for this example, We are using JDBC starter in out spring boot application.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>I’m also importing h2 dependency for embedded database. However, it is up to you to use 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 auto configuration.
create table user_entity (
id integer generated by default as identity,
first_name varchar(255),
last_name varchar(255),
primary key (id)
);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');
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
}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 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 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.setId(resultSet.getInt("last_name"));
return userDto;
}
});
}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(1));
userDto.setId(resultSet.getInt("id"));
userDto.setFirstName(resultSet.getString("first_name"));
userDto.setId(resultSet.getInt("last_name"));
return userDto;
}
});
}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.setId(resultSet.getInt(3));
return userDto;
});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(). Over all, 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 update. 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);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 is 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");
});
}Also, delete is some form of update in a way. For this reason, spring boot doesn’t provide any methods for delete.
public void delete(){
int rowsDeleted = jdbcTemplate.update("delete from user_entity where id<10");
System.out.println("Number of rows deleted = " + rowsDeleted);
}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);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;
}Conclusion
To summarize, we learned how to use jdbctemplate with Spring boot and how to configure and provide a custom jdbctemplate bean configuration with an example.