Getting to Know the New Spring JDBC Client with the Oracle Database
Introduction
This blog post explores the new Spring JDBC Client (JdbcClient) available in Spring 6.1.x. It is an interesting addition to the framework as it provides a great fluent interface as a high-level abstraction for working with JDBC.
Given its elegant approach to JDBC, it is interesting for you as a Java developer to know about its details as it has such a Java fluent interface out-of-the-box for Java developers.
So without further ado, let’s get started!
Prerequisites
- JDK — Java Development Kit 21
- Your preferred Java IDE — Eclipse, IntelliJ, VS Code
- Apache Maven
- Oracle Database 23c Free — Developer Release running on Docker, or the Oracle Autonomous Database
The New JdbcClient
The new JdbcClient (org.springframework.jdbc.core.simple.JdbcClient) interface provides a unified facade for query/update statements with several parameters and results retrieval options.
Interestingly, the JdbcClient.StatementSpec interface nested by the new JdbcClient interface now has a new overloaded query(Class) method on it that provides flexible field mapping support in Spring 6.1.
As an example with an Employee class, the query(Class) method usage ends up being as simple as shown below.
public List<Employee> findAll() {
return jdbcClient.sql(EmployeeSqlStatements.FIND_ALL_EMPLOYEES.statement())
.query(Employee.class).list();
}To support it, there’s also a new SimplePropertyRowMapper, a RowMapper implementation that converts a row into a new instance of the specified mapped target class, as an alternative to specifying a custom implementation of RowMapper.
Behind the scenes, the JdbcClient delegates method calls to Java classes JdbcTemplate and NamedParameterJdbcTemplate.
Another example with a more elaborated query follows, which illustrates the nice fluent interface as a facade to the underlying implementations.
public Optional<Employee> findById(String id) {
return jdbcClient.sql(EmployeeSqlStatements.FIND_EMPLOYEE_BY_ID.statement())
.param(id, id).query(Employee.class).optional();
}I have created a complete code sample for you if you want to test it all right away. Otherwise, you can follow the steps beneath to create and configure your own project by following the instructions in the following sections.
Feel free to pick whichever option you prefer then!
Bootstrap your Spring Boot application
First, you have to bootstrap and configure your Spring Boot application by accessing start.spring.io
The first step is to configure the group and artefact IDs for your app, and then select Maven as the build tool.
Next, we’ll configure the project’s metadata section.
Next, you have to configure the dependencies for your app. As expected, you’ll use Spring Data JDBC, and other indirect dependencies included by default. On the Dependencies section, click the ADD DEPENDENCIES button, scroll down to the SQL section, and add the JDBC-related dependencies.
Lastly, you have to click the GENERATE button.
Save the ZIP file to your target directory, then unzip it as required. The project’s directory structure you should expect is shown below.
If you open the pom.xml file, you will see that your dependencies have been added as required.
This configuration above is the default one from Spring’s downloadable file. So let’s update it to use the latest JAR versions as provided by Oracle.
Now, modify the pom.xml file to replace the Oracle JDBC JAR configuration above with one with the latest version of the required Oracle JDBC Driver as a Maven dependency.
Connect to your Oracle Database instance and run the DDL script
Now, you must connect to the Oracle Database instance and execute the DDL script to create the tables for our sample Spring Boot application.
Open your preferred SQL tool like the new Oracle SQL Developer Extension for VSCode, or the Oracle SQL Developer tool, copy the DDL script and execute it as expected.
CREATE TABLE employee
(id NUMBER(10) CONSTRAINT pk_employee PRIMARY KEY,
name VARCHAR2(20),
role VARCHAR2(20),
salary NUMBER(10),
commission NUMBER(10));
COMMIT;
INSERT INTO employee VALUES(7520,'KARL','DEVELOPER',8500,500);
INSERT INTO employee VALUES(7521,'JEFF','SALESMAN',25000,500);
INSERT INTO employee VALUES(7522,'JOHN','ARCHITECT',9500,500);
INSERT INTO employee VALUES(7523,'PETER','SRE',7000,500);
INSERT INTO employee VALUES(7524,'TODD','DBA',11000,500);
INSERT INTO employee VALUES(7525,'MARK','ENGINEER',9000,500);
INSERT INTO employee VALUES(7526,'LUKE','PM',10000,500);
INSERT INTO employee VALUES(7527,'ERIC','DIRECTOR',20000,500);
COMMIT;The sample Spring Boot application
It’s beyond the scope of this blog post to teach you what Spring Boot is about. There are plenty of tutorials on this subject, including the official Spring Boot 3.2.x documentation and code samples.
As a bonus, this blog post also presents how to properly configure and allow connections from a Spring Boot application that uses the Oracle UCP (Universal Connection Pool) as an alternative to HikariCP (default connection pool implementation in Spring Boot) along with Spring Boot JDBC.
If you need an introduction to connection pools, the Oracle Universal Connection Pool (UCP), or HikariCP, there are a couple of interesting blog posts published at Oracle Developers on Medium.
- A Guide to Migrating from HikariCP to Oracle UCP (Universal Connection Pool)
- HikariCP Best Practices for Oracle Database and Spring Boot
Nevertheless, we’ll provide an overview of the main components of the code sample to allow you to understand what you should expect it to do.
Basically, the main application components are Spring Boot 3, Spring Data JDBC, and Oracle UCP (Oracle Connection Pool).
This class represents an entity that relates to the corresponding database table. The easiest way to get such a class is to use Java Records, so our class is simply a Java Record indeed.
This is the main entry point and class used to bootstrap and launch a Spring application from a Java main method.
This is a class that adheres to the Controller design pattern and implements the REST endpoints for our sample application. Note the annotation that maps the HTTP GET requests to /employee that we’ll use to test the application.
As usual, this is the interface that defines the contract and services as provided by the sample application.
EmployeeServiceJdbcClient.java
That’s the class that implements the EmployeeService.java interface above and uses the new JdbcClient to interact with the target Oracle Database instance, as expected.
Besides, it has another method with another usage example of the new query(Class) method.
@Override
public Optional<Employee> findById(String id) {
return jdbcClient.sql(EmployeeSqlStatements.FIND_EMPLOYEE_BY_ID.statement())
.param(EmployeeSqlStatements.ID.statement(), id).query(Employee.class).optional();
}And the use of JdbcClient’s fluent interface as per the explanation in a previous section.
@Override
public void create(Employee employee) {
var updated = jdbcClient.sql(EmployeeSqlStatements.CREATE_NEW_EMPLOYEE.statement())
.params(List.of(employee.id(), employee.name(), employee.role(), employee.salary(),
employee.commission()))
.update();
log.info("New Employee Created: " + employee.name());
Assert.state(updated == 1L, EmployeeMessages.EMPLOYEE_CREATION_FAILED.getMessage() + employee.name());
}Lastly, we have a couple of helper enums to better organize the SQL queries and messages used in the application defined as EmployeeMessages.java and EmployeeSqlStatements.java.
Configure the Spring (JDBC) Datasource to connect to your Oracle Database instance
Our code sample uses Spring Boot 3.2.2, which has a built-in mechanism for configuration using a file called application.properties. You can find the file under the src/main/resources folder.
Now it’s possible to use Oracle UCP Connection Pool as a native Spring Datasource. We’ll add the properties for using Oracle UCP (Universal Connection Pool. Note that such properties require a Spring Boot version greater than 2.4.0.
First, we have to configure the connection URL, username, and password.
We’ll use the following format for the spring.datasource.url parameter:
jdbc:oracle:thin@[hostname]:[port]/[DB service/name]Then, provide a username and password as well.
Run the Spring Boot application locally
Change to the root directory of your project, and run the related Maven command. An example for Windows follows, and you can adjust it to your target project directory and operating system.
cd C:\java-projects\jdbc-spring-jdbcclient-oracle
mvnw clean package
mvnw spring-boot:run You will see some Spring and JDBC messages logged.
Test the Spring Boot application from a browser
Provided that your application has started successfully as shown above.
Open a web browser and type your app’s URL http://localhost:8080/employees
If everything is working properly, you will see the database records returned as JSON.
Wrapping it up
That’s it! You learned how to use the new Spring 6.1 JDBC Client with the Oracle Database 23c. I hope you enjoyed this blog post, so stay tuned!
References
Oracle SQL Developer Extension for VSCode
Oracle SQL Developer for VS Code — Documentation
Develop Java applications with Oracle Database
Oracle Database 23c Free — Developer Release
Oracle Developers and Oracle OCI Free Tier
Join our Oracle Developers channel on Slack to discuss Java, JDK, JDBC, Microservices with Spring Boot, Helidon, Quarkus, Micronaut, Reactive Streams, GraalVM, Cloud, DevOps, IaC, and other topics!
Build, test, and deploy your applications on Oracle Cloud — for free! Get access to OCI Cloud Free Tier!
