Spring JdbcTemplate CRUD Operations

Home › Blog › Spring JdbcTemplate CRUD Operations
Spring Framework Guru

Spring JdbcTemplate CRUD Operations

By Daniel Wagner Java, Spring, Spring Boot
1 Comment

Last Updated on July 1, 2019 by Simanta

1. 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 a 1:n relationship).
    • RowCallbackHandler: The return type of the interface method is void. It can be used if the ResultSet 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 a ResultSet 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.

10

About Daniel Wagner

My name is Daniel and I'm a passionate Java developer. Most of my free time is devoted to training in new and exciting topics that the Java ecosystem has to offer.

You May Also Like

One comment

  1. September 29, 2023 at 10:03 am

    Thank you Daniel for such a good and neat lesson!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.