Using the JdbcTemplate class in Spring with the Oracle Database and UCP

Juarez Junior
Oracle Developers
Published in
7 min readFeb 12, 2024

--

The new features of JdbcTemplate in Spring 6.1.x

by Juarez Junior

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

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!

Code sample — Java app components and sample application.properties file

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.

Spring Boot — bootstrap your app

Next, we’ll configure the project’s metadata section.

Project Metadata

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.

Project dependencies — Maven

Lastly, you have to click the GENERATE button.

Generate your app

Save the ZIP file to your target directory, then unzip it as required. The project’s directory structure you should expect is shown below.

Project structure from start.spring.io

If you open the pom.xml file, you will see that your dependencies have been added as required.

Maven — The Oracle JDBC Driver as a dependency

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.

Oracle JDBC Driver

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.

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).

Code sample — Java app components and sample application.properties file

Employee.java

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.

Employee.java

EmployeeApplication.java

This is the main entry point and class used to bootstrap and launch a Spring application from a Java main method.

EmployeeApplication.java

EmployeeController.java

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.

EmployeeController.java

EmployeeService.java

As usual, this is the interface that defines the contract and services as provided by the sample application.

EmployeeService.java

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.

EmployeeServiceJdbcTemplate.java

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.

Oracle Universal Connection Pool (UCP) as a native Spring Datasource

First, we have to configure the connection URL, username, and password.

Spring Datasource connection details

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.

Spring Boot 3.2.2 app started successfully

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.

DB records retrieved with Spring’s JdbcTemplate

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

Spring Framework 6.1.3

Spring Boot 3.2.2

Spring Boot

Oracle SQL Developer Extension for VSCode

Oracle SQL Developer for VS Code — Documentation

Oracle SQL Developer

Oracle JDBC Driver

Develop Java applications with Oracle Database

Oracle Database 23c Free — Developer Release

Oracle Autonomous Database

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!

--

--