Spring JdbcTemplate CRUD Operations
0 Comments1. JdbcTemplate Introduction
If we want to perform CRUD operations on a relational database the Spring ecosystem provides Spring Data JPA and Spring Support for JDBC. Spring Support for JDBC focuses on the JdbcTemplate
class, which is internally based on the JDBC API of the Java SDK.
Anyone who has ever worked with the JDBC API knows that using it creates a lot of boilerplate code, making it difficult to read and maintain. The JDBC API has the following downsides:
- Before and after executing an SQL query, we need to write a lot of code, such as creating connections, statements, closing result sets and connections, and so on
- Due to checked exceptions, exception handling must take place in database logic
- We have to completely take care of the transaction handling ourselves
Springs JdbcTemplate
class fixes all the above problems of the JDBC API and takes care of all the low-level details for us:
- It provides methods to directly write the queries
- Checked exceptions are converted to runtime exceptions, so we are not forced to handle or pass on exceptions
- The resource handling, e.g. opening and closing of connections, is taken from us
- transaction handling
2. Project setup
Before we really get started, I would like to take a closer look at the following points of the project setup:
- used dependencies
- POJO class Employee
- Pre-loading of sample data records
2.1 Dependencies used
For the JdbcTemplate
demo project we need the following two dependencies in our Spring Boot based application:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</scope> </dependency>
The Dependency spring-boot-starter-jdbc
is a starter for using JDBC in Spring Boot. This dependency contains the class JdbcTemplate
and also allows us to connect to an embedded database.
To keep the demo project as simple and uncomplicated as possible, we use the In-Memory SQL database H2. This eliminates the need to set up a MySQL server and configuration of the connection. Thanks to the autoconfiguration functionality of Spring Boot, the H2 database is available to us immediately after the application starts. Nothing else needs to be configured.
If you still want to try the example with MySQL, please have a look at blog post Configuring Spring Boot for MySQL or you can use a docker container.
2.2 POJO class Employee
Our POJO class Employee
, which will accompany us through the example, looks like this:
@Data @Builder public class Employee { private long id; private String firstName; private String lastName; private long yearlyIncome; }
Thanks to Lombok
and the @Data
annotation we can save a lot of boiler code here. @Data
annotation and a respective Lombok plugin for the used IDE add the code for getter and setter methods automatically as well as implementations for equals()
, hashCode()
, toString()
and an AllArgsConstructor
. @Builder
allows us to initialize an object in BuilderPattern style.
2.3 Pre-loading of sample records
The use of an in-memory database is particularly practical for tests or small demo projects. However, it is important to note that data in the database is not persisted permanently. The database lives only during the runtime of the application. If the application is terminated, all data in the database is irrevocably lost. For this reason, we will ensure that an SQL table is automatically generated and sample data records are inserted when the application is started.
To generate a database schema at application start, the file schema.sql
with the corresponding SQL commands for schema creation must be stored in the resources folder.
CREATE TABLE `employees` ( `id` bigint(20) generated by default as identity NOT NULL, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, `yearly_income` bigint(20) NOT NULL );
The same is also possible with data records. To do this, write the corresponding insert commands to the file data.sql
, which must also be in the resources folder.
INSERT INTO employees (first_name, last_name, yearly_income) VALUES ('John', 'Doe', 80000); INSERT INTO employees (first_name, last_name, yearly_income) VALUES ('Mary', 'Jackson', 75000); INSERT INTO employees (first_name, last_name, yearly_income) VALUES ('Peter', 'Grey', 60000);
Spring will automatically search for schema.sql
and data.sql
files at application startup and execute the statements they contain.
3. JdbcTemplate demonstration
The following examples show how CRUD operations can be performed against a relational database with the class JdbcTemplate
. All examples shown here can be found in our GitHub Repository.
3.1 Repository class
We start our example with a simple DAO class annotated with Spring Annotation @Repository
.
@Repository public class EmployeeRepository { private final JdbcTemplate jdbcTemplate; @Autowired public EmployeeRepository(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } }
@Repository
is a stereotype annotation that ensures that our class is included in the Spring Component scan and instantiated as a bean in the Spring Application context. The JdbcTemplate
is already configured automatically by Spring, so we can simply inject it into the constructor and use it afterward. JdbcTemplate
is thread-safe, so the following examples also work in a multithreading environment without adjustments.
3.2 Create
Let’s first look at a simple insert statement:
public void save(Employee employee) { String sqlQuery = "insert into employees(first_name, last_name, yearly_income) " + "values (?, ?, ?)"; jdbcTemplate.update(sqlQuery, employee.getFirstName(), employee.getLastName(), employee.getYearlyIncome()); }
First, we define the SQL statement. Instead of writing the employees values directly into the statement, we use ?
as a placeholder. Then we call the method update()
. The method expects the SQL query as the first parameter and a corresponding value for each ?
placeholder. The return value of the method is an int, which indicates how many records were affected by the operation.
If you prefer to work with named parameters, I recommend taking a look at the class NamedParameterJdbcTemplate
.
At this point, I would like to explicitly point out that variables should always be worked with ?
as a placeholder. JdbcTemplate
then uses PreparedStatement
in the background that protects against SQL injections.
If we want to know the ID of the generated employee, JdbcTemplate
makes it a bit more complicated, which shows the following code snippet:
public long saveAndReturnId(Employee employee) { String sqlQuery = "insert into employees(first_name, last_name, yearly_income) " + "values (?, ?, ?)"; KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(connection -> { PreparedStatement stmt = connection.prepareStatement(sqlQuery, new String[]{"id"}); stmt.setString(1, employee.getFirstName()); stmt.setString(2, employee.getLastName()); stmt.setLong(3, employee.getYearlyIncome()); return stmt; }, keyHolder); return keyHolder.getKey().longValue(); }
Wow. This is a lot of code that is necessary to get the ID. Let’s have a look at the method implementation in detail. As before, we first define the SQL statement. Then we create a KeyHolder
. KeyHolder
is an interface used for automatically generated keys returned by JDBC insert statements.
The update()
method now expects a PreparedStatementCreator
as the first parameter, which we implement via a lambda expression. In PreparedStatement stmt = connection.prepareStatement(sqlQuery, new String[]{"id"});
, it is essential that we pass a string array containing the names or indexes of all key columns as the last parameter.
As a second parameter, the update()
method expects the mentioned KeyHolder
. Once the statement has been executed, the KeyHolder contains the ID of the newly inserted record.
Much easier we do this with the class SimpleJdbcInsert
:
public long simpleSave(Employee employee) { SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate) .withTableName("employees") .usingGeneratedKeyColumns("id"); return simpleJdbcInsert.executeAndReturnKey(employee.toMap()).longValue(); }
SimpleJdbcInsert
wraps JdbcTemplate
and makes it much easier to add new records to a table. When creating the object we define the table name as well as the name of the ID column.
The method executeAndReturnKey()
inserts a new record into the table and returns the ID of the new record. As a parameter, the method expects a map whose key is the column name and the value is the corresponding column value. In our example, the POJO class knows how to convert itself into a map:
public Map<String, Object> toMap() { Map<String, Object> values = new HashMap<>(); values.put("first_name", firstName); values.put("last_name", lastName); values.put("yearly_income", yearlyIncome); return values; }
3.3 Read
The queryForObject()
method is intended for a query whose result set is exactly one record or no record at all:
public Employee findOne(long id) { String sqlQuery = "select id, first_name, last_name, yearly_income " + "from employees where id = ?"; return jdbcTemplate.queryForObject(sqlQuery, this::mapRowToEmployee, id); }
We first define our SQL statement – a simple select query that should give us all the information of an employee for the given ID.
The queryForObject()
method expects the SQL query, a RowMapper
and all placeholder values as parameters. The RowMapper
is a functional interface and defines how the ResultSet
of the query is converted to a domain object (Employee
in our example). We have several possibilities to implement the RowMapper
:
- Create your own class that implements the interface
- Implementation as an anonymous class
- Implementation via lambda expression with method reference
The following code snippet shows the implementation of the RowMapper
as a private method within our repository class. Since the method has the same signature as the RowMapper
interface, we can delegate queryForObject()
to our private method via Method reference.
private Employee mapRowToEmployee(ResultSet resultSet, int rowNum) throws SQLException { return Employee.builder() .id(resultSet.getLong("id")) .firstName(resultSet.getString("first_name")) .lastName(resultSet.getString("last_name")) .yearlyIncome(resultSet.getLong("yearly_income")) .build(); }
To query a variable number of records, we use the query()
method, which returns a typed list of objects. This method also expects the SQL statement and a RowMapper
as parameters. The method is overloaded several times, so the following example is only one way to query all records of a table:
public List<Employee> findAll() { String sqlQuery = "select id, first_name, last_name, yearly_income from employees"; return jdbcTemplate.query(sqlQuery, this::mapRowToEmployee); }
As already mentioned, the queryForObject()
and query()
methods are overloaded several times. Instead of RowMapper
, ResultSetExtractor
or RowCallbackHandler
can also be used as parameters. All three interfaces are functional interfaces. The differences are as follows:
ResultSetExtractor
: Converts all rows of a ResultSet into a single domain object. Especially useful when the information for a domain object spans over multiple rows (for example joins between tables with a1:n
relationship).RowCallbackHandler
: The return type of the interface method is void. It can be used if theResultSet
is to be written directly to a file or collection and the return of a domain object is not necessary.RowMapper
: Converts each row of aResultSet
into a domain object. Most commonly used.
3.4 Update
Firing an update statement is very simple:
public void update(Employee employee) { String sqlQuery = "update employees set " + "first_name = ?, last_name = ?, yearly_income = ? " + "where id = ?"; jdbcTemplate.update(sqlQuery , employee.getFirstName() , employee.getLastName() , employee.getYearlyIncome() , employee.getId()); }
First, we define the SQL statement again and pass it including all parameter values to the update()
method. The return value of the method is an int, which indicates how many records were affected by the operation.
3.5 Delete
Firing a delete statement is also very simple.
public boolean delete(long id) { String sqlQuery = "delete from employees where id = ?"; return jdbcTemplate.update(sqlQuery, id) > 0; }
The defined delete statement is passed to the update()
method. The method returns an int, which indicates how many records were affected by the operation. If the return value is greater than 0, one record was deleted.
4. Summary
In this blog post, we looked at how we work with the class JdbcTemplate
. Let’s review the content once again:
- Using H2 as a relational database
- generating data records, in particular from the point of view of returning the ID of the generated data record
- Reading records using a
RowMapper
- Updating and deleting records
Visit our GitHub repository to get all the source code used for this blog post. There you’ll also find a test class, which we didn’t discuss here.