Spring Boot 3.2 is now GA and we got another nice way to call relational databases by JDBC: The JdbcClient.
Historically, the Spring framework used the JdbcTemplate class to access relational databases through SQL statements. The JdbcTemplate has a large number of (overloaded) methods that cover almost any case to execute an SQL statement. However, the number of methods is huge and sometimes it is quite confusing which version to use.
Similar to the new RestClient, the new JdbcClient now offers a fluent API to make database calls, that is quite easy to understand, but still powerful enough to cover all cases you can probably think of.
Maven Setup
To use the JdbcClient in your Spring Boot application simply put the spring-boot-starter-jdbc into your pom file:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>Since this is a Spring Boot 3.2 feature, you will also need the Spring Boot 3.2 parent (or higher) configured:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.2.0</version>
<relativePath/>
</parent>Assuming you have also a database configured in your pom, you can now simply inject the JdbcClient into any of your spring beans. It will be created with the datasource configured to the database in your pom and application properties.
@Repository
class CustomerRepository {
@Autowired
private JdbcClient jdbc;
}You can also create a client yourself. There are static factory methods on the JdbcClient class to create a client from a Datasource and from a JdbcTemplate.
The Datamodel
For this example, I am going to to use a simple database setup to showcase the new features. The data structure just has one table that stores customer data. The customer will only have two properties: the id and the name of the customer. The database will just be a good old in-memory H2 database to keep things simple, but the JdbcClient works with any other database as well.
To include an H2 database in your application, insert this dependency in your pom:
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>To create the database schema, create a file called schema.sql in the src/main/resources folder of your Spring Boot application:
create table if not exists customers (id integer auto_increment, name varchar);To create some dummy data, put the following insert statements into a file called data.sql into the src/main/resources folder:
delete from customers;
insert into customers (name) values('Ernie');
insert into customers (name) values('Bert');These two files will be executed on every start of the application.
In the Java code, the data is stored in a simple Java record that holds the two customer properties:
public record Customer(int id, String name) {}The JdbcClient in Action
As mentioned earlier, the JdbcClient uses a fluent API that consists of the following steps to execute a SQL statement:
- Specify the SQL statement (required)
- Provide parameters for the statement (optional)
- execute the statemant (required)
- map the result to a Java type (optional)
- specify a data structure for the results, if it is more than one row (List, Set, Stream or Optional) (optional)
Select Statements
This sounds quite complicated but it is actually easy if you see it in action. Let’s look at a simple example for a select statement executed by a JdbcClient:
@Repository
class CustomerRepository {
@Autowired
private JdbcClient jdbc;
public List<Customer> getAllCustomers(){
return jdbc.sql("select * from customers")
.query(mapper)
.list();
}
}In line (1) and (2), I specify a simple Spring bean that will be used to encapsulate database access by the JdbcClient, which is injected in line (5).
In line (8) we can see the JdbcClient in action. You can start an SQL call with the sql() method, which takes a String as an argument containing the statement to execute. In our case we want to query all customers from the database. After that, we call the query() method to execute the statement. The query method takes a RowMapper implementation, that maps the resulting rows of the query to the Customer record. Finally, we use the list() method in line (10), to put all resulting Customer objects into a list.
Alternatively, there is also the set() method, which returns a Set or the stream() method that returns a Stream<Customer>.
The RowMapper implementation is similar to one we could use for a JdbcTemplate and maps a single row to a Customer object:
private RowMapper<Customer> mapper = (ResultSet rs, int rowNum) ->
new Customer(rs.getInt("id"), rs.getString("name"));SELECT Statement with ParameterS
Next, let’s use a parameter inside the SQL statement. In this example we will query a single customer from the DB:
public Customer getOneCustomer(int id) {
return jdbc.sql("select * from customers where id= ?")
.param(1, id)
.query(mapper)
.single();
}The example starts with the sql() method that uses an SQL statement that contains a ? as a placeholder. To pass a value for this parameter we use the param() method next, which takes the index of the placeholder and the value to use in the statement. In the Java JDBC API the index of placeholders start with 1 not 0. After that, we start the query again, passing the RowMapper. Finally, we can only get a single Customer by using the single() method. Of course the query should also only return a single row, but that is up to the developer to make sure.
If you are not sure if the single row exists, you can also end with the optional() method to return an Optional<Customer>.
Insert Statement with Parameter
The next example is an insert, which follows the same pattern as the select statements:
public int insertCustomer(Customer customer) {
return jdbc.sql("insert into customers (name) values (?)")
.param(1, customer.name())
.update();
}The SQL statement contains one placeholder, which is passed by the param() method. To execute an update, simply use the update() method, which returns the number of row updated by the statement.
Update Statement WITH NAMED PARAMETER
Let’s look at an update statement and a second way to pass parameters:
public int updateCustomer(Customer customer) {
return jdbc.sql("update customer set name = :name where id = :id")
.param("id",customer.id())
.param("name", customer.name())
.update();
}In this example, I specify the parameters with the Spring syntax for named placeholders, which is :[nameOfTheParam]. If you are using named parameters, you can use an overloaded param() method, which takes the name and value of the parameter for the statement.
I will skip to showcase the delete statement at this point, because it equal to an update just with an delete command in the SQL string.
Conclusion
Using the new client is definitely much easier than using the old JdbcTemplate. The method naming convention is quite good and the chaining of the methods makes it almost intuitive to use. Not much reason to use the old JDBCTemplate anymore.
If you want a working example of my code snippets, just check out this github project.
Schreibe einen Kommentar