Home » Enterprise Java » spring » Boot » Spring Boot Oracle Connection Example

About Joel Patrick Llosa

Joel Patrick Llosa
I graduated from Silliman University in Dumaguete City with a degree in Bachelor of Science in Business Computer Application. I have contributed to many Java related projects at University of Southampton (iSolutions), Predictive Technologies, LLC., Confluence Service, North Concepts, Inc., NEC Telecom Software Philippines, Inc., and NEC Technologies Philippines, Inc. You can also find me in Upwork freelancing as a Java Developer.

Spring Boot Oracle Connection Example

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

  1. Oracle XE free download – requires an Oracle account
  2. Eclipse Oxygen
  3. Oracle SQL Developer download – requires an Oracle account
  4. 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:

Spring Boot Oracle - Instance Manager

Oracle Instance Manager

Spring Boot Oracle  - Windows Services

Windows Services

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.

SQL Plus

SQL Plus

After following the setup instructions for SQL Developer, you should have something like below:

SQL Developer

SQL Developer

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:

Movies Table

Movies Table

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:

Spring Initializr

Spring Initializr

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 settings
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=sys as sysdba
spring.datasource.password=root
spring.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;
@SpringBootApplication
public 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.

Download
You can download the source code of this example here: Spring Boot Oracle Connection Example

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of