Spring Data JDBC with the Oracle Database 23c for Java Developers — Getting Started Guide
Introduction
Spring Data JDBC provides a high-level abstraction for working with JDBC by automating many of the low-level tasks involved in working with the API, such as creating and managing connections, preparing and executing SQL statements, mapping results to Java objects and other benefits.
Ultimately, Spring Data JDBC allows you to simplify your data access implementation by using plain JDBC so you do not have to add JPA (with Hibernate) to your projects. Besides, it may be a good fit for projects where you want a more direct approach to data access with complete flexibility to write SQL queries.
If you’re still wondering why you would choose Spring Data JDBC instead of Spring Data JPA, I advise you to check the official documentation and the explanation related to this topic — Why Spring Data JDBC?
A code sample is included if you want to test it locally.
So, without further ado, let’s explore how we can use Spring Data JDBC to access an Oracle Database 23c instance!
Prerequisites
- JDK — Java Development Kit 17+
- Your preferred Java IDE — Eclipse, IntelliJ, VS Code
- Apache Maven
- Oracle Database 23c Free — Developer Release running on Docker
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, as below.
Next, we’ll configure the project’s metadata section as below.
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 Spring Data JDBC as a dependency.
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 is shown below.
If you open the pom.xml file, you will see that your dependencies have been added as required. Now, modify the pom.xml file to add the Oracle JDBC Driver as a Maven dependency as shown below.
Connect to your DB 23C Free instance and run the DDL script
Now, you must connect to the DB 23C Free database instance and execute the DDL script to create the tables for our sample Spring Boot application.
Open the Oracle SQL Developer tool as explained previously, copy the DDL script from the Gist below — and execute it as expected.
Make sure the password for the user ORACLE_23C_USER follows Oracle’s requirements for DB passwords. Otherwise, an error will happen during the script execution process.
CREATE USER ORACLE_23C_USER IDENTIFIED BY <YOUR_PASSWORD>;
GRANT DB_DEVELOPER_ROLE TO ORACLE_23C_USER;
GRANT CREATE SESSION TO ORACLE_23C_USER;
GRANT UNLIMITED TABLESPACE TO ORACLE_23C_USER;
CREATE TABLE Employee
(id NUMBER(10) CONSTRAINT pk_employee PRIMARY KEY,
name VARCHAR2(20),
job VARCHAR2(20),
salary NUMBER(10),
commission NUMBER(10));
INSERT INTO Employee VALUES(7369,'JOHN','CLERK',7902,NULL);
INSERT INTO Employee VALUES(7499,'PETER','SALESMAN',7698,300);
INSERT INTO Employee VALUES(7521,'JEFF','SALESMAN',7698,500);
INSERT INTO Employee VALUES(7566,'MARK','MANAGER',7839,NULL);
INSERT INTO Employee VALUES(7654,'MARTIN','SALESMAN',7698,1400);
INSERT INTO Employee VALUES(7698,'ADAM','MANAGER',7839,NULL);
INSERT INTO Employee VALUES(7782,'CLARK','MANAGER',7839,NULL);
INSERT INTO Employee VALUES(7788,'SCOTT','ANALYST',7566,NULL);
INSERT INTO Employee VALUES(7839,'KING','PRESIDENT',NULL);
INSERT INTO Employee VALUES(7844,'TURNER','SALESMAN',0);
INSERT INTO Employee VALUES(7876,'ADAMS','CLERK',7788,NULL);
INSERT INTO Employee VALUES(7900,'JAMES','CLERK',7698,NULL);
INSERT INTO Employee VALUES(7902,'FORD','ANALYST',7566,NULL);
INSERT INTO Employee VALUES(7934,'MILLER','CLERK',7782,NULL);
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.0 documentation and code samples.
Our main goal is to show you how to properly configure and allow connections from a Spring Boot application that uses the Oracle UCP (Oracle Connection Pool) as an alternative to HikariCP (default connection pool implementation in Spring Boot) along with Spring Boot JDBC.
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, as you can see in the following screenshot it has the required Table and Id Java annotations.
Spring Boot 3 requires Spring Framework 6, which in turn requires Java 17 and Jakarta EE 9 and is compatible with the recently released Jakarta EE 10.
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 below the annotation that maps the HTTP GET requests to /employee that we’ll use to test the application.
This interface implements the Repository design pattern by extending the org.springframework.data.repository.CrudRepository interface. It will help to expose CRUD operations for the specific domain object, Employee.java.
Configure the Spring (JDBC) Datasource to connect to your DB23c instance
Our code sample uses Spring Boot 3.0, 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 use the properties below 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 as shown below.
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 Maven command below as usual. Note the extra argument to the JVM, which is to allow the Byte Buddy component to run with JDK 21 during the Maven build process.
Run the commands below.
cd C:\java-projects\23c-springboot3-jdbc
mvnw clean package -Dnet.bytebuddy.experimental=true
mvnw spring-boot:runYou will see some Spring and JDBC messages logged as below.
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 as below.
Test the Spring Boot application with httpie
You can also use the httpie tool to test it if you prefer.
Open the tool, then create the New draft option (plus sign +) as shown below, and provide the target URL http://localhost:8080/employees for a plain HTTP request with a GET HTTP method.
Now you can perform the same request from the tool. Just click the Send button and you will see the records returned from the application once again as shown below.
You will receive an HTTP 200 OK status code and the corresponding JSON response as the HTTP response body.
Debugging database apps — inspecting your Spring Data JDBC queries
The application.properties file has some properties to allow you to inspect the Spring Data JDBC queries so you can debug your applications if needed.
If you enable them, you will also be able to see the executed query below.
Wrapping it up
That’s it! You learned how to use Spring Data JDBC with Spring Boot 3 along with the specific parameters for Spring Boot supported by the Oracle Universal Connection Pool.
Such a combination allowed us to create a Spring Data JDBC application that connects to Oracle Database 23c Free — Dev Release.
I hope you enjoyed this blog post, so stay tuned!
References
Oracle Database 23c Free — Developer Release
Spring Data JDBC — Reference Documentation
Develop Java applications with Oracle 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!
