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

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.

🔗 Related Posts

🏷️ Spring REST Annotations

Understand key annotations like @GetMapping, @PostMapping, and @RequestBody in Spring Boot REST APIs.

🔍 @RestController vs @Controller

Learn why @RestController is preferred for REST APIs and how it's different from @Controller.

📦 Return JSON Responses

Learn how to return clean and structured JSON responses from your Spring Boot APIs.

🚨 Global Exception Handling

Handle errors like "User Not Found" gracefully using @ControllerAdvice and return proper HTTP codes.