How to Build Your First CRUD REST API in Spring Boot With Oracle DB

How to Build Your First CRUD REST API in Spring Boot With Oracle DB

Building a robust CRUD (Create, Read, Update, Delete) REST API is the foundational skill every backend Java developer must master. When building enterprise applications, Spring Boot is almost universally paired with heavy-duty relational databases like Oracle DB.

In this comprehensive guide, we are not just going to write code. We will walk through the complete architectural lifecycle of a Spring Boot application, exploring exactly how the Controller, Service, and Repository layers interact to securely persist data into an Oracle Database using Spring Data JPA.

📺 Visual Learner? Watch the Video Walkthrough!
While this written guide focuses on Oracle DB, the exact same Spring Boot architecture applies to MySQL. Watch my full step-by-step video tutorial to see this API built live in action!

▶ Watch the CRUD API Video Tutorial

1. Project Setup and Dependencies

To get started, generate a new Spring Boot project using Spring Initializr. You will need three core dependencies to bridge your Java application with the Oracle Database.

  • Spring Web: Provides the embedded Tomcat server and the REST annotations required to route HTTP requests.
  • Spring Data JPA: The abstraction layer over Hibernate that handles SQL generation and object-relational mapping (ORM).
  • Oracle JDBC Driver (ojdbc11): The official driver that allows Java applications to communicate natively with Oracle Database systems.
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <scope>runtime</scope>
</dependency>

2. Designing the Oracle Database Table

Unlike older versions of Oracle that required complex sequences and triggers for auto-incrementing primary keys, modern Oracle databases (12c and newer) support identity columns. We will use GENERATED BY DEFAULT ON NULL AS IDENTITY. This tells Oracle to automatically generate a unique sequential ID whenever a new employee is inserted without an explicit ID. 

CREATE TABLE employee (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    name VARCHAR2(255 CHAR),
    email VARCHAR2(255 CHAR)
);

3. Configuring application.properties

Next, we must configure Spring Boot to connect to the Oracle instance. We define the JDBC URL, credentials, and specifically instruct Hibernate to use the Oracle12cDialect. We also set ddl-auto=update, which is great for local development as it automatically updates your schema, but should never be used in production (use tools like Flyway or Liquibase instead). 

spring.datasource.url=jdbc:oracle:thin:@localhost:1521/XEPDB1
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# Hibernate Configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect

4. Creating the JPA Entity Layer

The Entity layer is a direct Java representation of our Oracle table. By utilizing the @Entity annotation, Hibernate knows it needs to map the fields of this class to the columns in the database. The @GeneratedValue(strategy = GenerationType.IDENTITY) annotation aligns perfectly with the Oracle identity column we created earlier. 

import jakarta.persistence.*;

@Entity
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String email;

    // Standard Getters and Setters omitted for brevity
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
}

5. The Repository Layer

The beauty of Spring Data JPA is that you rarely need to write standard SQL queries by hand. By creating an interface that extends JpaRepository, Spring Boot dynamically generates the implementation at runtime. This interface immediately provides us with powerful methods like save(), findById(), findAll(), and deleteById() out of the box. 

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

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    // Custom query methods (e.g., findByEmail) can be declared here if needed
}

6. The Business Service Layer

A common mistake among junior developers is injecting the Repository directly into the Controller. Enterprise architecture dictates that we use a Service Layer. This layer acts as a protective buffer where transaction management, data validation, and core business logic reside before touching the database. 

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class EmployeeService {

    @Autowired
    private EmployeeRepository repository;

    public List<Employee> getAllEmployees() {
        return repository.findAll();
    }

    public Employee getEmployeeById(Long id) {
        // Returns the employee if found, otherwise returns null
        return repository.findById(id).orElse(null);
    }

    public Employee saveEmployee(Employee emp) {
        // The save() method handles both INSERT and UPDATE operations
        return repository.save(emp);
    }

    public void deleteEmployee(Long id) {
        repository.deleteById(id);
    }
}

7. The REST Controller Layer

Finally, we expose our application to the outside world using a REST Controller. The @RestController annotation ensures that the Java objects returned by our methods are automatically serialized into JSON format using the Jackson library. We map standard HTTP verbs (GET, POST, PUT, DELETE) to our corresponding service methods. 

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/api/employees")
public class EmployeeController {

    @Autowired
    private EmployeeService service;

    // READ: Retrieve all employees
    @GetMapping
    public List<Employee> getAll() {
        return service.getAllEmployees();
    }

    // READ: Retrieve a specific employee by ID
    @GetMapping("/{id}")
    public Employee getById(@PathVariable Long id) {
        return service.getEmployeeById(id);
    }

    // CREATE: Add a new employee to the database
    @PostMapping
    public Employee create(@RequestBody Employee emp) {
        return service.saveEmployee(emp);
    }

    // UPDATE: Modify an existing employee
    @PutMapping("/{id}")
    public Employee update(@PathVariable Long id, @RequestBody Employee emp) {
        emp.setId(id); // Ensure the ID from the URL path is set on the object
        return service.saveEmployee(emp);
    }

    // DELETE: Remove an employee
    @DeleteMapping("/{id}")
    public void delete(@PathVariable Long id) {
        service.deleteEmployee(id);
    }
}

8. Example JSON API Payloads

Once your application is running (typically on http://localhost:8080), you can interact with it using an API client. Here is what the JSON payloads look like during standard CRUD operations:

Creating an Employee (POST /api/employees)

You only need to send the name and email. Oracle DB will generate the ID automatically.

{
    "name": "John Doe",
    "email": "john.doe@example.com"
}

Updating an Employee (PUT /api/employees/1)

Provide the updated fields in the request body. Spring Data JPA will perform an SQL UPDATE statement.

{
    "name": "John Doe Updated",
    "email": "john.doe.updated@example.com"
}

Conclusion

You have successfully built a full-stack backend architecture! By separating your application into Controller, Service, and Repository layers, your Spring Boot API is now clean, maintainable, and firmly integrated with an enterprise Oracle Database.

🧩 Build Production-Ready CRUD APIs with Spring Boot

A real-world Spring Boot CRUD API goes beyond basic database operations. It requires proper REST design, validation, error handling, pagination, and performance tuning. Explore these related topics to strengthen your backend skills.

🌐 Controller vs RestController

Understand which controller type is best suited for CRUD REST APIs.

🔄 Returning JSON Responses

Learn how CRUD APIs serialize entities and DTOs into JSON.

🏷️ REST API Annotations

Master annotations like @PostMapping, @PutMapping, and @DeleteMapping.

✅ REST API Validation

Validate request bodies before persisting data to Oracle DB.

🧩 Custom Validation Annotations

Implement domain-specific validation rules for CRUD operations.

🚨 Global Exception Handling

Return consistent JSON error responses for CRUD failures.

📘 Spring Boot JPA Basics

Revisit entities, repositories, and ORM fundamentals.

📄 Pagination & Sorting

Efficiently fetch large Oracle datasets using pagination.

🔍 Sorting & Filtering (JPA Specs)

Build flexible search APIs on top of CRUD endpoints.

🗄️ Database Performance Tuning

Optimize Oracle queries, indexes, and connection pools.