Using the JdbcTemplate class in Spring with the Oracle Database and UCP
Introduction
This blog post takes you to explore Spring’s JdbcTemplate class, as it simplifies JDBC and helps avoid common errors.
JdbcTemplate (org.springframework.jdbc.core.JdbcTemplate) does this by encapsulating core JDBC operations, leaving application code to provide SQL and extract results. So, JdbcTemplate is the central delegate in the Spring’s JDBC core package.
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 JdbcTemplate class
Although it’s still available, currently the official advice from the Javadoc documentation is to use the new JdbcClient in Spring 6.1 as the preferred client as it provides a great fluent interface as a high-level abstraction for working with JDBC as released in Spring Boot 3.2.x and Spring 6.1.x. Nevertheless, it’s important to understand how to use the Spring JdbcTemplate class.
Check this blog post — Getting to know the New Spring JDBC Client with the Oracle Database 23c — to get more information about the new JdbcClient in Spring 6.1.x.
So, concerning the JdbcTemplate class, 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.
EmployeeServiceJdbcTemplate.java
That’s the class that implements the EmployeeService.java interface above and uses the JdbcTemplate class to interact with the target Oracle Database instance, as expected.
The official documentation states that JdbcTemplate is still the central class in Spring’s JDBC core package, supporting almost any JDBC operation. However, as cited previously, the JdbcClient as of Spring 6.1 represents a more convenient JDBC facade.
Note that all SQL operations performed by the JdbcTemplate class are logged at debug level using org.springframework.jdbc.core.JdbcTemplate, as a logging category, and you can turn it off by adding the relevant configuration to your application.properties file as usual.
Check my previous blog post — Getting to Know the New Spring JDBC Client with the Oracle Database — if you want to learn about the new JdbcClient and the release notes for Spring 6.1 — What’s New in Spring Framework 6.x — to get all the details regarding the interplay between JdbcTemplate and JdbcClient in Spring 6.1.
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:runYou 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 access its 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 Spring JdbcTemplate class with the Oracle Database. 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!
