Spring Boot – Oracle Database Connection

Today we have a short look at the configurations that need to be done to our Spring Boot Application to connect to a Oracle Database (instead to an h2 Database for example).
Spring Boot offers a very simple way so work with an in memory database. Especial during the development time or while testing this is a very confortable way to interact with a database.

But now we want to have a look at how redirect ou connections to a permanent database.

Preconditions

You need to have a running Oracle Database. If you have any dependencies or configurations that refer to an other database (configurations in your application.yml / properties or in dependencies in your pom.xml) please remove them.

Install the ojdbc Driver

Because this a Maven Project I don´t want to bind the ojdbc driver jar hardly to this project. A better approach is by adding it to our maven repository and bind it in our pom.xml. 

If you are not familiar with how this can be done, please visit this little blog post.

Entity and Repository

Just to make it more clear what will happen next I present to you the entity and repository I am using.

Entity

package com.javadevcorner.book.entity;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;

/**
 *
 * @author javadevcorner.com
 */
@Entity
public class Book implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(sequenceName = "bookIdSeq", 
                       name = "BOOK_SEQ", allocationSize = 1, initialValue = 1)
    private long id; 
    
    private String title;
    
    private int pages;
    
//    getter and setter stuff
}

Repository

package com.javadevcorner.book.entity;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 *
 * @author javadevcorner.com
 */
@Repository
public interface BookRepository extends JpaRepository<Book, Long> {
    
}

Now we can move on with our configuration

Modify pom.xml

As you can mention we need to add some dependencies to our pom.xml

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

The first one is the ojdbc dependency we have created before and the HikariCP is a lightweight JDBC connection pooling framework.

Create the Database Import Statements

If you don´t have an import.sql file you now need to add one.

Please navigate to your src/main/resources where also your application.properties (or .yml) is placed and add the file import.sql 

/**
 * Author:  javadevcorner.com
 */
INSERT INTO Book (Id, Title, Pages) 
       VALUES (1, 'The Fellowship of the Ring (The Lord of the Rings, Part 1)', 479)
INSERT INTO Book (Id, Title, Pages) 
       VALUES (2, 'The Two Towers (The Lord of the Rings, Part 2)', 416)
INSERT INTO Book (Id, Title, Pages) 
       VALUES (3, 'The Return of the King (The Lord of the Rings, Part 3)', 480)

Fill up the file with the code above and save it.

Modify application configuration file

In the following I will present to you the application.yml and application.properties version

application.yml

# Drops existing tables, recreates them and executes import.sql
spring:
    jpa:
        hibernate:
            ddl-auto: create-drop

# Settings for the oracle connection
    datasource:
        url:
            jdbc:oracle:thin:@localhost:1521:xe
        username:
            <your username>
        password:
            <your password>
        driver-class-oracle:
            jdbc:
                driver:
                    OracleDriver

application.properties

# Drops existing tables, recreates them and executes import.sql
spring.jpa.hibernate.ddl-auto=create-drop

# Settings for the oracle connection
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=<your username>
spring.datasource.password=<your password>
spring.datasource.driver-class-oracle.jdbc.driver.OracleDriver

 

Congrats – That´s it! 

Run the Application

Following we just run the application and print out our three books to the command line.

package com.javadevcorner.app;

import com.javadevcorner.book.control.BookService;
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.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

/**
 *
 * @author javadevcorner.com
 */
@SpringBootApplication
@ComponentScan("com.javadevcorner")
@EntityScan("com.javadevcorner")
@EnableJpaRepositories("com.javadevcorner")
public class AppStarter implements CommandLineRunner {

    @Autowired
    private BookService bookService;
    
    public static void main(String[] args) {
        SpringApplication.run(AppStarter.class, args);
        
    }

    @Override
    public void run(String... args) throws Exception {
        bookService.getAllBooks().forEach(book -> {
            System.out.println(book.getTitle());
        });
    }
}

After you have started the programm with

mvn spring-boot:run

your Output should look similiar to this

The Fellowship of the Ring (The Lord of the Rings, Part 1)
The Two Towers (The Lord of the Rings, Part 2)
The Return of the King (The Lord of the Rings, Part 3)

Conclusion

Now you can implement a connection to a permanent Oracle SQL Database.
We have looked at how to install the Oracle JDBC Driver. Subsequently we modified our pom.xml.
Furthermore we created the import.sql file and how it is called in the start up routine. After that we modified the application configuration file with the new connection. Last but not least we made our application run and watched the output.

 

Thank´s for reading and have fun using your Oracle SQL Database in your Spring Boot Application.

Feel free to share

Leave a Reply

Your email address will not be published. Required fields are marked *

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