Simplify Spring JDBC with the New Fluent JdbcClient API – A Hands‑On Guide

Environment: Spring 6.1.2 + JDK 17

1. Introduction

Since version 6.1, NamedParameterJdbcTemplate's named‑parameter statements and the classic JdbcTemplate's positional‑parameter statements can be used through a unified client API with a fluent interaction model. This makes JDBC operations more readable and easier to understand, although stored procedures or batch operations still require the original JdbcTemplate or NamedParameterJdbcTemplate.

2. Practical Example

2.1 Prepare the Environment

Dependency:

<code>&lt;dependency&gt;
  &lt;groupId&gt;org.springframework&lt;/groupId&gt;
  &lt;artifactId&gt;spring-jdbc&lt;/artifactId&gt;
  &lt;version&gt;6.1.2&lt;/version&gt;
&lt;/dependency&gt;</code>

Table definition:

<code>CREATE TABLE `t_users` (
  `id` varchar(255) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `enabled` int(11) DEFAULT '1',
  `locked` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</code>

Data source configuration:

<code>@Bean
public HikariDataSource dataSource() {
    HikariDataSource dataSource = new HikariDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/xxoo?serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&useSSL=false");
    dataSource.setUsername("root");
    dataSource.setPassword("xxoo");
    dataSource.setPoolName("PackDs");
    dataSource.setMaximumPoolSize(2);
    dataSource.setMinimumIdle(2);
    dataSource.setConnectionTestQuery("SELECT 1");
    return dataSource;
}</code>

Bean definitions:

<code>@Bean
public JdbcClient jdbcClient(DataSource dataSource) {
    return JdbcClient.create(dataSource);
}

@Bean
public UserService userService() {
    return new UserService();
}</code>

UserService definition:

<code>public class UserService {
    @Resource
    private JdbcClient jdbcClient;
}</code>

2.2 Placeholder Queries

Examples of positional, indexed, var‑args and List‑based parameter binding:

<code>public void query1(String username, String password) {
    String sql = "select * from t_users t where t.username = ? and t.password = ?";
    List<User> list = this.jdbcClient
        .sql(sql)
        .param(username)
        .param(password)
        .query(User.class)
        .list();
    System.out.println(list);
}

public void query2(String username, String password) {
    String sql = "...";
    List<User> list = this.jdbcClient.sql(sql)
        // specify parameter index
        .param(1, username)
        .param(2, password)
        .query(User.class)
        .list();
    System.out.println(list);
}

public void query3(String username, String password) {
    String sql = "...";
    List<User> list = this.jdbcClient
        .sql(sql)
        // replace placeholders with var‑args
        .params(username, password)
        .query(User.class)
        .list();
    System.out.println(list);
}

public void query4(List<Object> values) {
    String sql = "...";
    List<User> list = this.jdbcClient
        .sql(sql)
        // replace placeholders with a List
        .params(values)
        .query(User.class)
        .list();
    System.out.println(list);
}</code>

2.3 Named‑Parameter Queries

<code>public void query5(String username, String password) {
    String sql = "select * from t_users t where t.username = :un and t.password = :pwd";
    List<User> list = this.jdbcClient
        .sql(sql)
        .param("un", username)
        .param("pwd", password)
        .query(User.class)
        .list();
    System.out.println(list);
}

public void query6(Map<String, Object> params) {
    String sql = "select * from t_users t where t.username = :un and t.password = :pwd";
    List<User> list = this.jdbcClient
        .sql(sql)
        .params(params)
        .query(User.class)
        .list();
    System.out.println(list);
}

// Invocation example
Map<String, Object> params = Map.of("un", "user1", "pwd", "123123");
userService.query6(params);
</code>

2.4 Custom Result Mapping

<code>public void query7(Map<String, Object> params) {
    String sql = "select * from t_users t where t.username = :un and t.password = :pwd";
    List<UserDTO> list = this.jdbcClient
        .sql(sql)
        .params(params)
        // custom row mapping
        .query(new RowMapper<UserDTO>() {
            @Override
            public UserDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                UserDTO dto = new UserDTO();
                dto.setUsername(rs.getString("username"));
                dto.setPassword(rs.getString("password"));
                return dto;
            }
        })
        .list();
    System.out.println(list);
}

public void query8(Map<String, Object> params) {
    String sql = "select * from t_users t where t.username = :un and t.password = :pwd";
    List<User> list = this.jdbcClient
        .sql(sql)
        .params(params)
        .query(new ResultSetExtractor<List<User>>() {
            @Override
            public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<User> list = new ArrayList<>();
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getString("id"));
                    user.setUsername(rs.getString("username"));
                    user.setPassword(rs.getString("password"));
                    user.setLocked(rs.getInt("locked"));
                    user.setEnabled(rs.getInt("enabled"));
                    list.add(user);
                }
                return list;
            }
        });
    System.out.println(list);
}
</code>

2.5 CUD Operations

Insert, update and delete examples using the fluent API:

<code>public void insert(User user) {
    String sql = "insert into t_users values(?, ?, ?, ?, ?)";
    int ret = this.jdbcClient.sql(sql)
        .param(1, user.getId(), Types.VARCHAR)
        .param(2, user.getPassword(), Types.VARCHAR)
        .param(3, user.getUsername(), Types.VARCHAR)
        .param(4, user.getEnabled(), Types.NUMERIC)
        .param(5, user.getLocked(), Types.NUMERIC)
        .update();
    System.out.println(ret);
}

public void update(Map<String, Object> params) {
    String sql = "update t_users t set t.username = :un, t.password = :pwd where t.id = :id";
    int ret = this.jdbcClient.sql(sql)
        .params(params)
        .update();
    System.out.println(ret);
}

public void delete(String id) {
    String sql = "delete from t_users where id = ?";
    int ret = this.jdbcClient.sql(sql)
        .param(id)
        .update();
    System.out.println(ret);
}
</code>

2.6 Other Operations

Single‑result query and optional handling:

<code>public void other(String id) {
    String sql = "select * from t_users where id = ?";
    User user = this.jdbcClient.sql(sql)
        .param(id)
        .query(User.class)
        .single();
    System.out.println(user);
}

public void other2(String id) {
    String sql = "select * from t_users where id = ?";
    Optional<User> opUser = this.jdbcClient.sql(sql)
        .param(id)
        .query(User.class)
        .optional();
    System.out.println(opUser.orElseGet(() -> null));
}
</code>

Conclusion: Using JdbcClient greatly simplifies JDBC code compared with JdbcTemplate, improving readability and maintainability.