A Guide to Spring JdbcClient API

1. Overview

In this tutorial, we’ll learn about the JdbcClient interface, the latest addition to Spring Framework 6.1. It provides a fluent interface with a unified facade for JdbcTemplate and NamedParameterJdbcTemplate. This means that now it supports a chaining kind of operation. We can now define the query, set the parameters, and perform the DB operation in the fluent API style.

This feature simplifies JDBC operations, making them more readable and easier to understand. However, we must resort to the older JdbcTemplate and NamedParameterJdbcTemplate classes for JDBC batch operations and stored procedure calls.

Throughout this article, we’ll use the H2 Database to showcase the ability of JdbcClient.

2. Prerequisite Database Setup

Let’s begin by taking a look at the student table which we’ll refer to while exploring JdbcClient:

CREATE TABLE student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(255) NOT NULL,
    age INT,
    grade INT NOT NULL,
    gender VARCHAR(10) NOT NULL,
    state VARCHAR(100) NOT NULL
);
-- Student 1
INSERT INTO student (student_name, age, grade, gender, state) VALUES ('John Smith', 18, 3, 'Male', 'California');

-- Student 2
INSERT INTO student (student_name, age, grade, gender, state) VALUES ('Emily Johnson', 17, 2, 'Female', 'New York');

--More insert statements...

The above SQL script creates a student table and inserts records in it.

3. Create JdbcClient

Spring Boot framework auto-discovers the DB connection properties in the application.properties and creates the JdbcClient bean during the application startup. After this, the JdbcClient bean can be autowired in any class.

Following is an example, where we’re injecting a JdbcClient bean in the StudentDao class:

@Repository
class StudentDao {

    @Autowired
    private JdbcClient jdbcClient;
}

We’ll use StudentDao throughout this article to define our methods for understanding the JdbcClient interface.

However, there are also static methods like create(DataSource dataSource), create(JdbcOperations jdbcTemplate), and create(NamedParameterJdbcOperations jdbcTemplate) in the interface that can create an instance of JdbcClient.

4. Perform DB Query With JdbcClient

As mentioned earlier, JdbcClient is a unified facade for JdbcTemplate and NamedParameterJdbcTemplate. Hence, we’ll see how it supports both of them.

4.1. Support Positional Parameters Implicitly

In this section, we’ll discuss the support for binding positional SQL statement parameters with the placeholder ?. Basically, we’ll see how it supports the features of JdbcTemplate.

Let’s take a look at the below method in the class StudentDao:

List<Student> getStudentsOfGradeStateAndGenderWithPositionalParams(int grade, String state, String gender) {
    String sql = "select student_id, student_name, age, grade, gender, state from student"
            + " where grade = ? and state = ? and gender = ?";
    return jdbcClient.sql(sql)
      .param(grade)
      .param(state)
      .param(gender)
      .query(new StudentRowMapper()).list();
}

In the above method the parameters grade, state, and gender are registered implicitly in the order in which they are assigned to the method param(). Finally, when the method query() is called, the statement is executed, and the results are retrieved with the help of the RowMapper just like in JdbcTemplate.

The method query() also supports ResultSetExtractor and RowCallbackHandler arguments. We’ll see the related examples in the upcoming sections.

Interestingly, until the method list() is called, no results are retrieved. There are other terminal operations as well that are supported like optional(), set(), single(), and stream()

Now, we’ll see how it works:

@Test
void givenJdbcClient_whenQueryWithPositionalParams_thenSuccess() {
    List<Student> students = studentDao.getStudentsOfGradeStateAndGenderWithPositionalParams(1, "New York", "Male");
    assertEquals(6, students.size());
}

Let’s see how the same can be done with the help of Varargs:

Student getStudentsOfGradeStateAndGenderWithParamsInVarargs(int grade, String state, String gender) {
    String sql = "select student_id, student_name, age, grade, gender, state from student"
      + " where grade = ? and state = ? and gender = ? limit 1";
    return jdbcClient.sql(sql)
      .params(grade, state, gender)
      .query(new StudentRowMapper()).single();
}

As we see above, we’ve replaced the method param() with params() which takes the Varargs argument. Also, we used the method single() to retrieve just one record.

Let’s see how it works:

@Test
void givenJdbcClient_whenQueryWithParamsInVarargs_thenSuccess() {
    Student student = studentDao.getStudentsOfGradeStateAndGenderWithParamsInVarargs(1, "New York", "Male");
    assertNotNull(student);
}

Further, the method params(), also has an overloaded version that takes a List of parameters. Let’s see an example:

Optional<Student> getStudentsOfGradeStateAndGenderWithParamsInList(List params) {
    String sql = "select student_id, student_name, age, grade, gender, state from student"
      + " where grade = ? and state = ? and gender = ? limit 1";
    return jdbcClient.sql(sql)
      .params(params)
      .query(new StudentRowMapper()).optional();
}

Apart from params(List<?> values), we also see the method optional(), which returns the Optional<Student> object. Here’s the above method in action:

@Test
void givenJdbcClient_whenQueryWithParamsInList_thenSuccess() {
    List params = List.of(1, "New York", "Male");
    Optional<Student> optional = studentDao.getStudentsOfGradeStateAndGenderWithParamsInList(params);
    if(optional.isPresent()) {
        assertNotNull(optional.get());            
    } else {
        assertThrows(NoSuchElementException.class, () -> optional.get());
    }
}

4.2. Support Positional Parameters Explicitly With Index

What if we need to set the position of the SQL statement parameters? To do this we’ll use the method param(int jdbcIndex, Object value):

List<Student> getStudentsOfGradeStateAndGenderWithParamIndex(int grade, String state, String gender) {
    String sql = "select student_id, student_name, age, grade, gender, state from student"
      + " where grade = ? and state = ? and gender = ?";
    return jdbcClient.sql(sql)
      .param(1, grade)
      .param(2, state)
      .param(3, gender)
      .query(new StudentResultExtractor());
}

In the method, the positional indexes of the parameters are specified explicitly. Additionally, we’ve also used the method query(ResultSetExtractor rse).

Let’s see this in action:

@Test
void givenJdbcClient_whenQueryWithParamsIndex_thenSuccess() {
    List<Student> students = studentDao.getStudentsOfGradeStateAndGenderWithParamIndex(
      1, "New York", "Male");
    assertEquals(6, students.size());
}

4.3. Support Named Parameters With Name-Value Pair

JdbcClient also supports binding named SQL statement parameters with the placeholder :× which is a feature of NamedParameterJdbcTemplate.

The param() method can also take parameters as key-value pairs:

int getCountOfStudentsOfGradeStateAndGenderWithNamedParam(int grade, String state, String gender) {
    String sql = "select student_id, student_name, age, grade, gender, state from student"
      + " where grade = :grade and state = :state and gender = :gender";
    RowCountCallbackHandler countCallbackHandler = new RowCountCallbackHandler();
    jdbcClient.sql(sql)
      .param("grade", grade)
      .param("state", state)
      .param("gender", gender)
      .query(countCallbackHandler);
    return countCallbackHandler.getRowCount();
}

In the above method, we used named parameters. Additionally, we also used query(RowCallbackHandler rch). Let’s see it in action:

@Test
void givenJdbcClient_whenQueryWithNamedParam_thenSuccess() {
    Integer count = studentDao.getCountOfStudentsOfGradeStateAndGenderWithNamedParam(1, "New York", "Male");
    assertEquals(6, count);
}

4.4. Support Named Parameters With a Map

Interestingly,  we can also pass the parameter name-value pair in a map as well in the params(Map<String,?> paramMap) method:

List<Student> getStudentsOfGradeStateAndGenderWithParamMap(Map<String, ?> paramMap) {
    String sql = "select student_id, student_name, age, grade, gender, state from student"
      + " where grade = :grade and state = :state and gender = :gender";
    return jdbcClient.sql(sql)
      .params(paramMap)
      .query(new StudentRowMapper()).list();
}

Moving on, let’s see how it works:

@Test
void givenJdbcClient_whenQueryWithParamMap_thenSuccess() {
    Map<String, ?> paramMap = Map.of(
      "grade", 1,
      "gender", "Male",
      "state", "New York"
    );
    List<Student> students = studentDao.getStudentsOfGradeStateAndGenderWithParamMap(paramMap);
    assertEquals(6, students.size());
}

5. Perform DB Manipulation With JdbcClient

Just like queries, JdbcClient also supports DB manipulations like creating, updating, and deleting records. Similar to the earlier sections, we can also bind parameters through the various overloaded versions of the param() and params() methods. Hence, we won’t repeat them.

However, for executing the SQL statements instead of calling the query() method, we’ll call the update() method.

Here’s an example of inserting records into the student table:

Integer insertWithSetParamWithNamedParamAndSqlType(Student student) {
    String sql = "INSERT INTO student (student_name, age, grade, gender, state)"
      + "VALUES (:name, :age, :grade, :gender, :state)";
    Integer noOfrowsAffected = this.jdbcClient.sql(sql)
      .param("name", student.getStudentName(), Types.VARCHAR)
      .param("age", student.getAge(), Types.INTEGER)
      .param("grade", student.getGrade(), Types.INTEGER)
      .param("gender", student.getStudentGender(), Types.VARCHAR)
      .param("state", student.getState(), Types.VARCHAR)
      .update();
    return noOfrowsAffected;
}

The above method uses param(String name, Object value, int sqlType) to bind the parameters. It has an additional sqlType argument to specify the data type of the parameter. Also, the update() method returns the number of rows affected.

Let’s see the method in action:

@Test
void givenJdbcClient_whenInsertWithNamedParamAndSqlType_thenSuccess() {
    Student student = getSampleStudent("Johny Dep", 8, 4, "Male", "New York");
    assertEquals(1, studentDao.insertWithSetParamWithNamedParamAndSqlType(student));
}

In the above method, getSampleStudent() returns a student object. The student object is then passed to the method insertWithSetParamWithNamedParamAndSqlType() to create a new record in the student table.

Similar to JdbcTemplate, JdbcClient has the method update(KeyHolder generatedKeyHolder) to retrieve auto-generated keys created while executing insert statements.

6. Conclusion

In this article, we learned about the new interface JdbcClient introduced in Spring Framework 6.1. We saw how this one interface can perform all the operations earlier performed by JdbcTemplate and NamedParameterJdbcTemplate. Additionally, because of the fluent API style, the code has also become simpler to read and understand.