1. Overview
1.概述
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.
在本教程中，我们将学习 Spring Framework 6.1 最新添加的 JdbcClient 接口。它为 JdbcTemplate 和 NamedParameterJdbcTemplate 提供了具有统一界面的流畅接口。这意味着它现在支持链式操作。我们现在可以定义查询、设置参数，并以流畅的 API 风格执行 DB 操作。
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.
该功能简化了 JDBC 操作，使其更易读、更易懂。但是，我们必须使用较早的 JdbcTemplate 和 NamedParameterJdbcTemplate 类来进行 JDBC 批量操作和存储过程调用。
Throughout this article, we’ll use the H2 Database to showcase the ability of JdbcClient.
在本文中，我们将使用 H2 数据库来展示 JdbcClient 的功能。
2. Prerequisite Database Setup
2.数据库设置的前提条件
Let’s begin by taking a look at the student table which we’ll refer to while exploring JdbcClient:
让我们先来看看 student 表，在探索 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.
上述 SQL 脚本创建了一个 student 表，并在其中插入了记录。
3. Create JdbcClient
3.创建JdbcClient</em
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.
Spring Boot 框架会自动发现 application.properties 中的 DB 连接属性，并在应用程序启动时创建 JdbcClient Bean。之后，JdbcClient Bean 可在任何类中自动连接。
Following is an example, where we’re injecting a JdbcClient bean in the StudentDao class:
下面是一个示例，我们将在 StudentDao 类中注入一个 JdbcClient Bean：
@Repository
class StudentDao {
@Autowired
private JdbcClient jdbcClient;
}
We’ll use StudentDao throughout this article to define our methods for understanding the JdbcClient interface.
我们将在本文中使用 StudentDao 来定义我们理解 JdbcClient 接口的方法。
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.
core.JdbcOperations)”>create(JdbcOperations jdbcTemplate)，以及 create(NamedParameterJdbcOperations jdbcTemplate) 接口中可创建 JdbcClient 实例的接口。
4. Perform DB Query With JdbcClient
4.使用 JdbClient 执行数据库查询
As mentioned earlier, JdbcClient is a unified facade for JdbcTemplate and NamedParameterJdbcTemplate. Hence, we’ll see how it supports both of them.
如前所述，JdbcClient 是 JdbcTemplate 和 NamedParameterJdbcTemplate 的统一界面。因此，我们将看到它是如何支持这两种模板的。
4.1. Support Positional Parameters Implicitly
4.1.隐式支持位置参数
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.
在本节中，我们将讨论对使用占位符 ? 绑定位置 SQL 语句参数的支持。基本上，我们将了解它如何支持 JdbcTemplate 的功能。
Let’s take a look at the below method in the class StudentDao:
让我们看看下面这个类 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.
在上述方法中，参数 grade、state 和 gender 按照分配给方法 param() 的顺序隐式注册。最后，当调用方法 query() 时，将执行语句，并在 RowMapper 的帮助下检索结果，就像在 JdbcTemplate 中一样。
The method query() also supports ResultSetExtractor and RowCallbackHandler arguments. We’ll see the related examples in the upcoming sections.
方法 query() 还支持 ResultSetExtractor 和 RowCallbackHandler 参数。我们将在接下来的章节中看到相关示例。
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()
有趣的是，在调用 list() 方法之前，不会检索到任何结果。还支持其他终端操作，如 选择()、设置()、单个()和 流()。
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:
让我们看看如何在 Vargs 的帮助下实现同样的功能：
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.
如上所示，我们将 param() 方法替换为 params() 方法，后者使用 Varargs 参数。此外，我们使用方法 single() 只检索一条记录。
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:
此外，方法 params() 也有一个重载版本，它接收一个 List 参数。让我们来看一个示例：
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:
除了 params(List<?> values)，我们还看到了 optional() 方法，该方法返回 Optional<Student> 对象。下面是上述方法的实际应用：
@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
4.2.用索引明确支持位置参数
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):
如果我们需要设置 SQL 语句参数的位置，该怎么办？为此，我们将使用方法 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).
在该方法中，明确指定了参数的位置索引。此外，我们还使用了 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
4.3.支持带名-值对的命名参数
JdbcClient also supports binding named SQL statement parameters with the placeholder :× which is a feature of NamedParameterJdbcTemplate.
JdbcClient 还支持使用占位符 :× 绑定命名的 SQL 语句参数，这是 NamedParameterJdbcTemplate 的一项功能。
The param() method can also take parameters as key-value pairs:
param()方法也可以将参数作为键值对接收：
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:
在上述方法中，我们使用了命名参数。此外，我们还使用了 query(RowCallbackHandler rch)。让我们看看它的实际效果：
@Test
void givenJdbcClient_whenQueryWithNamedParam_thenSuccess() {
Integer count = studentDao.getCountOfStudentsOfGradeStateAndGenderWithNamedParam(1, "New York", "Male");
assertEquals(6, count);
}4.4. Support Named Parameters With a Map
4.4.使用 Map 支持命名参数
Interestingly, we can also pass the parameter name-value pair in a map as well in the params(Map<String,?> paramMap) method:
有趣的是，我们还可以在 params(Map<String,?> paramMap) 方法中以 map 的形式传递参数名-值对：
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
5.使用 JdbClient 执行数据库操作
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.
与查询一样，JdbClcient 也支持数据库操作，如创建、更新和删除记录。与前面的章节类似，我们也可以通过各种重载版本的 param() 和 params() 方法绑定参数。因此，我们不再赘述。
However, for executing the SQL statements instead of calling the query() method, we’ll call the update() method.
但是，在执行 SQL 语句时，我们不会调用 query() 方法，而是会调用 update() 方法。
Here’s an example of inserting records into the student table:
下面是向 student 表插入记录的示例：
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.
上述方法使用 param(String name, Object value, int sqlType) 绑定参数。它有一个额外的 sqlType 参数，用于指定参数的数据类型。此外，update() 方法会返回受影响的行数。
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.
在上述方法中，getSampleStudent() 返回一个 student 对象。然后，将 student 对象传递给方法 insertWithSetParamWithNamedParamAndSqlType() 以在 student 表中创建新记录。
Similar to JdbcTemplate, JdbcClient has the method update(KeyHolder generatedKeyHolder) to retrieve auto-generated keys created while executing insert statements.
与 JdbcTemplate 类似，JdbcClient 具有 update(KeyHolder generatedKeyHolder) 方法，用于检索在执行 insert 语句时创建的自动生成的键。
6. Conclusion
6.结论
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.
在本文中，我们了解了 Spring Framework 6.1 中引入的新接口 JdbcClient。我们看到了这一个接口如何执行以前由 JdbcTemplate 和 NamedParameterJdbcTemplate 执行的所有操作。此外，由于采用了流畅的 API 风格，代码也变得更易于阅读和理解。
As usual, the code used in this article can be found over on GitHub.