This article is a Spring Boot Oracle Connection Example. Oracle (Oracle database/RDBMS) is a proprietary multi-model database management system made and marketed by Oracle Corporation. It’s one of the most popular databases used in the world. This article will show you how to connect Spring Boot to an Oracle database.
1. Tools and Requirements
- Oracle XE free download – requires an Oracle account
- Eclipse Oxygen
- Oracle SQL Developer download – requires an Oracle account
- Apache Maven
It is also assumed that the reader has basic understanding of the above tools (e.g. has done some coding using Eclipse IDE).
2. Oracle Database XE Setup
I’ve downloaded the Win64 Oracle XE setup file as I’m on a Windows 10 machine. Follow the setup/InstallShield Wizard instructions for the Oracle 18c Express Edition. Take note of the password (e.g. root) for the SYS, SYSTEM and PDBADMIN accounts. Make a note of the Oracle Database Express Edition Connection Information, for example:
- Multitenant container database: localhost:1521
- Pluggable database: localhost:1521/XEPDB1
- EM Express URL: https://localhost5500/em
We can check if the service is running by using the Oracle Instance Manager (Start Menu -> Oracle folder) or via the Windows Services. You should have something like below:
3. Oracle SQL Developer Setup
Now that we have the Oracle database server up and running, let’s connect to it using SQL Developer and create a table with data. We could use SQL plus (Start Menu -> Oracle folder) to create our table but we’ll do it in SQL Developer.
After following the setup instructions for SQL Developer, you should have something like below:
Let’s connect to the database server and create a table with data. Click the plus sign (New Connection) and let’s name it LOCAL. Supply the following details in the User Info tab.
- Authentication Type: Default
- Username: sys
- Role: SYSDBA
- Password: depends on what you provided during the installation process (e.g. root)
- Save Passord: ticked
- Connection Type: Basic
- Hostname: localhost
- Port: 1521
- SID: xe
Run the below SQL script in the Query Builder. Make sure to select all of the SQL script, otherwise, it will only execute the first line:
SQL Script
1 2 3 4 5 6 7 8 9 | CREATE TABLE movies ( id NUMBER GENERATED as IDENTITY, title VARCHAR2(255), description VARCHAR2(255));INSERT INTO movies (title, description) VALUES ('Mr. Deeds', 'Comedy');INSERT INTO movies (title, description) VALUES ('Mad Max Fury Road', 'Science Fiction');INSERT INTO movies (title, description) VALUES ('We Were Soldiers', 'War'); |
If all goes well, then you’ll have something like below:
4. Spring Boot Oracle Connection Implementation
Let’s head over to Spring Initializr and bootstrap our application. Below are our configuration details:
Project: Maven
Language: Java
Spring Boot: 2.2.2
Group: com.javacodegeeks.example
Artifact: spring-boot-oracle-conneciton
Name: spring-boot-oracle-connection
Description: Demo project for Spring Boot
Package name: com.javacodegeeks.example
Packaging: Jar
Java: 8
Dependencies: JDBC API, Oracle Driver
You should have something like below:
Click Generate and import the Maven project into Eclipse. Edit the application.properties file for the Oracle configuration.
application.properties
1 2 3 4 5 | # Oracle settingsspring.datasource.url=jdbc:oracle:thin:@localhost:1521:xespring.datasource.username=sys as sysdbaspring.datasource.password=rootspring.datasource.driver-class-name=oracle.jdbc.OracleDriver |
Now, let’s write the code to read the data.
SpringBootOracleConnectionApplication.java
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | package com.javacodegeeks.example;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.CommandLineRunner;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.jdbc.core.JdbcTemplate;@SpringBootApplicationpublic class SpringBootOracleConnectionApplication implements CommandLineRunner { @Autowired JdbcTemplate jdbcTemplate; public static void main(String[] args) { SpringApplication.run(SpringBootOracleConnectionApplication.class, args); } public void run(String... args) throws Exception { System.out.println("Reading movie records..."); System.out.printf("%-30.30s %-30.30s%n", "Title", "Description"); jdbcTemplate.query("SELECT * FROM movies", (rs)-> { System.out.printf("%-30.30s %-30.30s%n", rs.getString("title"), rs.getString("description")); }); }} |
The @SpringBootApplication annotation is a convenient alternative to @Configuration, @EnableAutoConfiguration, and @ComponentScan. This annotation tells Spring Boot to scan for other components, add beans based on the classpath, and tags the class as a source of bean definitions.
We implemented the CommandLineRunner because we want to execute the run method after the application context is loaded.
Spring Boot automatically creates JdbcTemplate because we are using the Spring JDBC module (remember the spring-boot-starter-jdbc dependency?). @Autowired automatically loads JdbcTemplate.
We then used the query method to execute a SELECT statement and a lambda expression handles the printing of the movie records. Lastly, the records are printed in a nice column.
5. Spring Boot Oracle Connection Output
Let’s run the program, right-click on the SpringBootOracleConnectionApplication.java file > Run As > Java Application. And our output looks like the one below.
Console Output
1 2 3 4 5 6 7 8 9 | Reading movie records...Title Description 2020-01-04 22:04:00.980 INFO 14940 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...2020-01-04 22:04:02.194 INFO 14940 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.Mr. Deeds Comedy Mad Max Fury Road Science Fiction We Were Soldiers War 2020-01-04 22:04:02.370 INFO 14940 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...2020-01-04 22:04:02.388 INFO 14940 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed. |
There you have it! That was quick and easy thanks to Spring Boot.
6. Spring Boot Oracle Connection Summary
To summarize, we included the spring-boot-starter-jdbc and ojdbc8 dependencies to make available all the Spring modules we need to make JDBC operations and the Oracle driver. We then use JdbcTemplate to perform our SQL commands. That’s all there is to it. Cracking!!
7. Download the Source Code
This is an example about Spring Boot Oracle Connection.
You can download the source code of this example here: Spring Boot Oracle Connection Example
Hi, I tried to do the same with an Exisiting spring boot application
and it throw a null pointer exception
ervlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.NullPointerException] with root cause
java.lang.NullPointerException: null
at com.balsam.oasis.drugWS.domain.configuration.WsConfiguration.<init>(WsConfiguration.java:29)
this line is the line
jdbcTemplate.query("SELECT * FROM movies"
, (rs)-> {
System.out.printf(
"%-30.30s %-30.30s%n"
, rs.getString(
"title"
), rs.getString(
"description"
));
});
Hi Mostafa,
Double check your “com.balsam.oasis.drugWS.domain.configuration.WsConfiguration”. Can’t help you with this as this is not part of the example.
Cheers,
Joel
Hi,
The following is the last few lines of my console output, I can only get the column names but not the data, Thanks for helping.
Reading movie records…
Title Description
2020-06-26 21:52:58.867 INFO 19288 — [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 – Starting…
2020-06-26 21:52:59.162 INFO 19288 — [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 – Start completed.
Hi Derek,
The column names come from line 17 which is a System.out.printf. Meaning the jdbcTemplate query didn’t succeed. Since you don’t mention any exceptions, I’m going to guess that the call to the Oracle database got stuck. Did you run your program while there was another application connected to Oracle XE? Say for example sqlplus? Try running the program again without any existing connections to Oracle XE.
Cheers,
Joel
HI i tryed to do it, with other custom oracle user but it doesn’t work, only works with sys or system users, do you have that problem?
Did you “GRANT” permissions to your custom Oracle user?