Spring JdbcClient made simple

The Spring JdbcClient API is a new addition to Spring 6, providing a more modern and streamlined way to interact with databases using Spring JDBC. It aims to simplify database operations by offering a fluent API for executing SQL queries and updates.

Overview of the JdbcClient API

Many Spring developers are already familiar with the JDBCTemplate interface to access the Database using SQL Queries: See this article to learn more about it: Using the JDBC Template with Spring Boot applications

As of version 6.1 of the Spring Framework, there is a unified JDBC access facade available in the form of org.springframework.jdbc.core.simple.JdbcClient.
The JdbcClient provides a fluent API style for common JDBC queries/updates with flexible use of indexed or named parameters. Behind the hoods, It delegates to JdbcOperations/NamedParameterJdbcOperations} for actual execution.

A Proof of Concept example of JDBCClient API

Firstly, we need to hook the latest version of Spring Boot in order to be able to use this API:

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.0-M1</version>
        <relativePath/> <!-- lookup parent from repository -->
</parent>

Next, let’s set up a basic set of libraries we need to run the JDBCClient API:

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
</dependencies>

As you can see from the above dependencies, we will be using H2 Database for testing purposes. To create the initial database structure, let’s add a resources/schema.sql script in our project:

 DROP TABLE Customers IF EXISTS;
 CREATE TABLE Customers(name VARCHAR(255), address VARCHAR(255));

We are done with the project set up, let’s write a simple JDBC Client that will create and query records from a table available on the H2 Database.

Coding the JDBC Client

In order to manage the Customer objects, we will use the Java Record type, which will reduce the amount of boilerplate code for our example:

record Customer(String name, String address) { }

Then, here is the main Spring Boot application:

@SpringBootApplication
public class ClientsApplication {

    public static void main(String[] args) {
        SpringApplication.run(ClientsApplication.class, args);
    }

    @Bean
    JdbcClient jdbcClient(DataSource dataSource) {
        return JdbcClient.create(dataSource);
    }

    @Bean
    ApplicationRunner runner(JdbcClient jdbcClient) {
        return args -> {
            RowMapper<Customer> planetRowMapper = (rs, rowNum) -> new Customer(rs.getString("name"), rs.getString("address"));

            List<String> list1 = new ArrayList<>(Arrays.asList("John Smith", "5th Avenue New york"));


            var insert = jdbcClient
                    .sql("insert into Customers (name,address ) values(?,?)")
                    .params(list1)
                    .update( );
           
            jdbcClient.sql("select * from Customers" )
                    .query(planetRowMapper)
                    .list()
                    .forEach(System.out::println);
        };
    }
 
}

Inside the ApplicationRunner runner method we are performing a set of obvious actions:

• Creates a list with two elements representing a customer’s name and address.
• Calls the jdbcClient.sql("insert into Customers (name, address) values(?,?)") method to prepare an SQL insert statement with placeholders for parameters.
• Uses params(list1) to set the parameters for the SQL insert statement.
• Calls update() to execute the SQL statement and insert the data into the database.

Finally, we are executing a SELECT query to retrieve all customers from the Customers table. It uses jdbcClient.sql("select * from Customers") to prepare the query, query(rowMapper) to execute the query and map the results to Customer objects using the RowMapper, list() to get the list of results, and forEach(System.out::println) to print each customer’s details.

Execute the Spring Boot application with:

mvn install spring-boot:run

By running the above code, you should be able to see on the Console the Customer Record:

Conclusion

Overall, this Spring Boot application demonstrates how to use Spring JDBC to perform basic CRUD operations on a database (inserting and querying data). It also showcases the usage of the new Java record feature to create simple data classes with minimal boilerplate code.

Source code: https://github.com/fmarchioni/masterspringboot/tree/master/jdbc/jdbc-client