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:
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:
Spring Boot: 2.2.2
Description: Demo project for Spring Boot
Package name: com.javacodegeeks.example
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.
Now, let’s write the code to read the data.
@SpringBootApplication annotation is a convenient alternative to
@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
@Autowired automatically loads
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.
There you have it! That was quick and easy thanks to Spring Boot.
6. Spring Boot Oracle Connection Summary
To summarize, we included the
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