Spring Boot is a popular framework for developing microservices and web applications. With its powerful and flexible architecture, it has become a go-to framework for many developers. One of the critical aspects of developing applications is connecting them to a database. In this blog post, we will go over how to connect an Oracle database to your Spring Boot application.



Prerequisites

Before we dive into the actual code, let’s go over some of the prerequisites that you need to have in order to follow along with this tutorial.

  1. Oracle Database: You will need an Oracle database installed on your machine. If you don’t have one, you can download the Oracle Database Express Edition (XE) for free from the Oracle website.
  2. Spring Boot CLI: You will need the Spring Boot CLI installed on your machine. You can find instructions on how to install it on the Spring Boot website.
  3. Maven: You will need Maven installed on your machine. Maven is a build automation tool used for Java projects.


Configuring the Oracle Database

The first step in connecting to an Oracle database is to configure the database itself. You will need to create a new user and grant them the necessary permissions.

  1. Log in to the Oracle Database as the sys user:
sqlplus sys/password as sysdba
  1. Create a new user:
CREATE USER springuser IDENTIFIED BY password;
  1. Grant the necessary permissions to the new user:
GRANT CONNECT, RESOURCE, CREATE SESSION TO springuser;
  1. Log out of the sys user:
EXIT;
  1. Log in as the new user:
sqlplus springuser/password
  1. Create a new table:
CREATE TABLE employees (
  id NUMBER(10) PRIMARY KEY,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL,
  email VARCHAR2(50) NOT NULL
);
  1. Insert some data into the table:
INSERT INTO employees (id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'johndoe@example.com');

INSERT INTO employees (id, first_name, last_name, email)
VALUES (2, 'Jane', 'Doe', 'janedoe@example.com');


Creating the Spring Boot Application

Now that we have our Oracle database set up and configured, let’s create a Spring Boot application that will connect to it.

  1. Create a new Spring Boot project using the Spring Boot CLI:
spring init --dependencies=data-jpa myapp
  1. Navigate to the newly created project directory:
cd myapp
  1. Add the following dependencies to the pom.xml file:
<dependency>
  <groupId>com.oracle.ojdbc</groupId>
  <artifactId>ojdbc8</artifactId>
  <version>19.7.0.0</version>
</dependency><dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
  1. Create a new class called Employee.java with the following code:
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Employee {

  @Id
  private int id;
  private String firstName;
  private String lastName;
  private String email;

  // Getters and setters
}
  1. Create a new interface called EmployeeRepository.java with the following code:
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
}
  1. Create a new class called Application.java with the following code:
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

  public static void main(String[] args) {
    SpringApplication.run(Application.class, args);
  }
}
  1. Add the following properties to the application.properties file:
spring.datasource.url=jdbc:oracle:thin:@//localhost:1521/XE
spring.datasource.username=springuser
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
  1. Run the application:
./mvnw spring-boot:run


Testing the Connection

Now that we have our Spring Boot application set up and configured, let’s test the connection to the Oracle database.

  1. Create a new class called EmployeeService.java with the following code:
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmployeeService {

  @Autowired
  private EmployeeRepository employeeRepository;

  public List<Employee> getAllEmployees() {
    return employeeRepository.findAll();
  }
}
  1. Create a new class called EmployeeController.java with the following code:
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class EmployeeController {

  @Autowired
  private EmployeeService employeeService;

  @GetMapping("/employees")
  public List<Employee> getAllEmployees() {
    return employeeService.getAllEmployees();  }
}

3. Start the application and navigate to `http://localhost:8080/employees` in your browser. You should see a list of employees from the Oracle database.



Conclusion

In this blog post, we have learned how to connect an Oracle database to a Spring Boot application. By using Spring Boot’s JPA, we were able to quickly and easily connect to the database and retrieve data. With the steps outlined in this post, you should be able to get started on your own Spring Boot project that connects to an Oracle database.



Leave a Reply