One of the key features of Spring Boot is its support for the JDBC Template, a powerful tool for interacting with relational databases. In this article, we will discuss how to use the JDBC Template in a Spring Boot application to simplify the process of interacting with a database.
Set up the Spring Boot Project
In the example, we are going to use the H2 in-memory database, which is a good option for testing purposes.
You can bootstrap your Spring Boot JDBCTemplate project from the online initializer by including the following dependencies:
Alternatively, you can set up your project using the spring CLI as follows:
spring init -d=jdbc,h2,web -g=com.example -a=jdbc-app --package-name=com.example -name=jdbc-app -x
Finally, import the Project in your IDE and verify that it includes the following dependencies:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
Adding the Model
Firstly, we will add a Java Class to encapsulate the Database table where we persist our data. Add the following Person domain class to our project:
public class Person {
Long id;
String name;
String surname;
public Person(Long id, String name, String surname) {
super();
this.id = id;
this.name = name;
this.surname = surname;
}
public Person() {
super();
}
public Long getId() {
return id;
}
private void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSurname() {
return surname;
}
public void setSurname(String surname) {
this.surname = surname;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", surname=" + surname + "]";
}
}
As you can see, this is a plain Java class with no Hibernate/JPA annotation as we will be using JDBC Template instead of an ORM Framework.
Adding the Service Class
Next, we will add the @Service Class which contains the business functionalities. In this example, we will wire the JdbcTemplate in the Class in order to perform CRUD Operations:
@Service
public class PersonService {
@Autowired
private JdbcTemplate jdbcTemplate;
public void createPersonTable() {
jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS person(id SERIAL, name VARCHAR(255), surname VARCHAR(255))");
}
public void insert(Person person) {
jdbcTemplate.update("INSERT INTO person(id, name, surname) VALUES(?,?,?)", person.getId(), person.getName(), person.getSurname());
}
public void update(Person person) {
jdbcTemplate.update("UPDATE person SET name = ?, surname = ? WHERE id = ?", person.getName(), person.getSurname(), person.getId());
}
public void delete(long id) {
jdbcTemplate.update("DELETE FROM person WHERE id = ?", id);
}
public Person findById(long id) {
return jdbcTemplate.queryForObject("SELECT * FROM person WHERE id = ?", new Object[] { id }, (rs, rowNum) -> {
Person person = new Person();
person.setId(rs.getLong("id"));
person.setName(rs.getString("name"));
person.setSurname(rs.getString("surname"));
return person;
});
}
public List<Person> findAll() {
return jdbcTemplate.query("SELECT * FROM person", (rs, rowNum) -> {
Person person = new Person();
person.setId(rs.getLong("id"));
person.setName(rs.getString("name"));
person.setSurname(rs.getString("surname"));
return person;
});
}
}
As you can see from this code, the JDBCTemplate provides a simple, efficient way to access a database through JDBC. It eliminates the need to write repetitive, boilerplate code to handle common database operations, such as opening and closing connections, handling exceptions, and processing results. Instead, it provides a set of methods for performing these operations in a consistent, simplified manner.
Coding a Test Class for the JDBCTemplate
Finally, we will add a JUnit 5 Test Class to test the CRUD methods of the Service.
@SpringBootTest
public class PersonServiceTest {
@Autowired
private PersonService personService;
@BeforeEach
public void setup(){
personService.createPersonTable();
}
@Test
public void testInsert() {
Person person = new Person(1L,"John","Doe");
personService.insert(person);
Person personResult = personService.findById(1L);
assertThat(personResult).isEqualToComparingFieldByField(person);
Person person2 = new Person(2L,"Gabriel","Fox");
personService.insert(person2);
assertThat(personService.findAll()).hasSize(2);
}
@Test
public void testUpdate() {
Person person = new Person(1L,"Steve","Smith");
personService.update(person);
Person personResult = personService.findById(1L);
assertThat(personResult).isNotNull();
}
@Test
public void testDelete() {
Person person = new Person(1L,"John","Doe");
personService.insert(person);
personService.delete(1L);
assertThat(personService.findAll()).isEmpty();
}
}
You can Test the application directly from the IDE. For example:
Alternatively, you can bootstrap and test from the Terminal as follows:
mvn install
Advantages and Disadvantages of Spring Boot JDBC Template
After this example, let’s have a quick review of all pros and cons in using Spring Boot JDBC Template:
Advantages of using Spring Boot JDBC Template:
- Simplified Database Access: The JDBC Template provides a simple, efficient way to access a database through JDBC, eliminating the need to write repetitive, boilerplate code for common database operations.
- Consistent and Efficient: The JDBC Template provides a consistent and efficient way to perform operations such as opening and closing connections, handling exceptions, and processing results.
- Easy to Use: The JDBC Template is easy to use, with a clear and intuitive interface.
- Good abstraction layer over the raw JDBC: It abstracts a lot of repetitive and error-prone low-level details of JDBC and allows you to concentrate on your business logic.
- Provides Built-in Exception Handling: The JDBC Template provides built-in exception handling, which makes it easier to handle errors and exception in an organized manner.
- Integrates with Spring’s Transaction Management: The JDBC Template seamlessly integrates with Spring’s transaction management capabilities, allowing you to easily manage transactions in your application.
Disadvantages of using Spring Boot JDBC Template:
- Limited flexibility: The JDBC Template may not provide enough flexibility for complex and advanced use cases.
- Not suitable for ORM: If you are looking for Object-Relational Mapping (ORM) functionality, the JDBC Template may not be the best choice.
- May not work well with NoSQL databases: The JDBC Template is optimized for relational databases and may not work well with other types of databases, such as NoSQL databases.
- May not be performant enough for large scale: JDBC Template is good for most cases but If you have very high concurrency and very high transaction rate, it may not perform as well as using raw JDBC or other libraries optimized for high-performance scenarios.
- May add unnecessary overhead: Using JDBC Template may add unnecessary overhead for simple or small projects, or for developers who are already proficient in working with JDBC.
Conclusion
In this article we discussed how to use the JDBC Template in a Spring Boot application to simplify the process of interacting with a database.
Source code: https://github.com/fmarchioni/masterspringboot/tree/master/jdbc/jdbctemplate-h2
