Working with Spring Boot JdbcTemplate

By Dhiraj Ray, 04 February,2017  

This is yet another post of using jdbctemplate to fetch records from DB.In this post we will see how we can perform different crud operations using jdbctemplate.We will be creating examples of how to create and read data from DB using spring jdbc provided jdbctemplate.We will be using the artifact spring-boot-starter-jdbc provided by spring boot to configure our spring jdbc configurations.

Environment Setup

1. JDK 8 2. Spring Boot 3. Intellij Idea/ eclipse 4. Maven

Maven Dependencies

spring-boot-starter-parent: provides useful Maven defaults. It also provides a dependency-management section so that you can omit version tags for existing dependencies.

spring-boot-starter-jdbc: provides all the maven dependecies for using JDBC with the Tomcat JDBC connection pool.

pom.xml
<properties> <java.version>1.8</java.version> </properties> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.3.3.RELEASE</version> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build>

Spring Bean Configuration

Application.java
package com.devglan.config; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication(scanBasePackages = "com.devglan") public class Application { public static void main(String[] args) { SpringApplication.run(Application.class, args); } }

Following is the model class with getters and setters.

User.java
package com.devglan.model; public class User { private int id; private String name; private String address; private String email; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } }

Now let us define our properties class which will be considered by spring-boot automatically to create the bean for datasource at run time. application.properties

spring.datasource.url=jdbc:mysql://localhost/devglan spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Now let us define our Dao class which will have implementation of jdbctemplate to insert rows in the db.We are using KeyHolder to hold the auto generated primary key.

UserDaoIml
package com.devglan.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.stereotype.Repository; import com.devglan.dao.UserDao; import com.devglan.model.User; @Repository public class UserDaoImpl implements UserDao { private final String INSERT_SQL = "INSERT INTO USERS(name,address,email) values(?,?,?)"; private final String FETCH_SQL = "select record_id, name, address, email from users"; private final String FETCH_SQL_BY_ID = "select * from users where record_id = ?"; @Autowired private JdbcTemplate jdbcTemplate; public User create(final User user) { KeyHolder holder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); ps.setString(1, user.getName()); ps.setString(2, user.getAddress()); ps.setString(3, user.getEmail()); return ps; } }, holder); int newUserId = holder.getKey().intValue(); user.setId(newUserId); return user; } public List findAll() { return jdbcTemplate.query(FETCH_SQL, new UserMapper()); } public User findUserById(int id) { return jdbcTemplate.queryForObject(FETCH_SQL_BY_ID, new Object[] { id }, new UserMapper()); } } class UserMapper implements RowMapper { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setId(rs.getInt("record_id")); user.setName(rs.getString("name")); user.setEmail(rs.getString("address")); user.setEmail(rs.getString("email")); return user; } }

Now, it is time to define our test cases to test whether the insert record in database with spring boot jdbctemplate.If you need to learn more on spring Boot Junit integration, then visit my earlier post Spring Boot Junit Integration Example

UserDaoTest.java
package com.devglan.dao.test; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertTrue; import java.util.List; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.SpringApplicationConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import com.devglan.config.Application; import com.devglan.dao.UserDao; import com.devglan.model.User; @RunWith(SpringJUnit4ClassRunner.class) @SpringApplicationConfiguration(Application.class) public class UserDaoTest { @Autowired private UserDao userDao; @Test public void createUser() { User savedUser = userDao.create(getUser()); User userFromDb = userDao.findUserById(savedUser.getId()); assertEquals(savedUser.getName(), userFromDb.getName()); assertEquals(savedUser.getEmail(), userFromDb.getEmail()); } @Test public void findAllUsers() { List users = userDao.findAll(); assertNotNull(users); assertTrue(users.size() > 0); } @Test public void findUserById() { User user = userDao.findUserById(1); assertNotNull(user); } private User getUser() { User user = new User(); user.setAddress("Bangalore, Karnataka"); user.setEmail("johndoe@gmail.com"); user.setName("John Doe"); return user; } }

SQL

CREATE TABLE users (record_id bigint NOT NULL AUTO_INCREMENT, name varchar(100), address varchar(250), email varchar(100), PRIMARY KEY (record_id));

Run Application

Run the test case defined as Junit test case and check the result.You can also verify that a single user row is created in db.

spring-boot-jdbc-test

Conclusion

I hope this article served you that you were looking for. If you have anything that you want to add or share then please share it below in the comment section.

Download the source

References

Spring Bot JDBC reference doc

Suggest more topics in suggestion section or write your own article and share with your colleagues.

Is this page helpful to you? Please give us your feedback below. We would love to hear your thoughts on these articles, it will help us improve further our learning process.

Further Reading: