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

In this guide, we'll walk through creating a simple CRUD (Create, Read, Update, Delete) REST API using Spring Boot and Oracle Database. This project will include:

Spring Boot Project Setup
Oracle Database Integration
JPA Entity
Repository Layer
Service Layer
REST Controller

1. Project Setup

Include the following dependencies in your pom.xml:

<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. Oracle Database Table

Create the employee table in Oracle Database with the following SQL:

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

3. application.properties

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

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

4. JPA Entity

import jakarta.persistence.*;

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

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

    private String name;
    private String email;

    // Getters and Setters
}

5. Repository Layer

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

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

6. Service Layer

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) {
        return repository.findById(id).orElse(null);
    }

    public Employee saveEmployee(Employee emp) {
        return repository.save(emp);
    }

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

7. REST Controller

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;

    @GetMapping
    public List<Employee> getAll() {
        return service.getAllEmployees();
    }

    @GetMapping("/{id}")
    public Employee getById(@PathVariable Long id) {
        return service.getEmployeeById(id);
    }

    @PostMapping
    public Employee create(@RequestBody Employee emp) {
        return service.saveEmployee(emp);
    }

    @PutMapping("/{id}")
    public Employee update(@PathVariable Long id, @RequestBody Employee emp) {
        emp.setId(id);
        return service.saveEmployee(emp);
    }

    @DeleteMapping("/{id}")
    public void delete(@PathVariable Long id) {
        service.deleteEmployee(id);
    }
}

8. API Payload Examples

Here are the example payloads for each of the API endpoints:

GET /api/employees

Response:
[
    {
        "id": 1,
        "name": "John Doe",
        "email": "john.doe@example.com"
    },
    {
        "id": 2,
        "name": "Jane Smith",
        "email": "jane.smith@example.com"
    }
]

GET /api/employees/{id}

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

POST /api/employees

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

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

PUT /api/employees/{id}

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

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

DELETE /api/employees/{id}

Response: No Content (204)

9. Run and Test the API

Use tools like Postman or cURL to test your API:

GET /api/employees
GET /api/employees/{id}
POST /api/employees
PUT /api/employees/{id}
DELETE /api/employees/{id}

Conclusion

You've now built a complete Spring Boot CRUD API integrated with Oracle Database. This is a solid foundation for building enterprise-ready APIs.

🧩 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.

Spring Java Lab

📂 Explore Topics

🔥 Featured Posts