Spring Boot CRUD Example with Oracle Database: CRUD REST API Step-By-Step Guide

In this tutorial, we will build a Spring Boot CRUD (Create, Read, Update, Delete) application using the Oracle Database.

Spring Boot Three-Layer Architecture

+-----------+      +-----------------+
|           |      | Controller Layer|
|  Postman  +----->+ (Handles HTTP   |
| (Client)  |      |  requests and   |
+-----------+      |  responses)     |
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   | Service Layer   |
                   | (Implements     |
                   |  business logic |
                   |  and data       |
                   |  transformations)|
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   | Repository Layer|
                   | (Manages data   |
                   |  operations with|
                   |  Oracle Database)|
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   |   Oracle        |
                   | (Database for   |
                   |  storing data)  |
                   +-----------------+

• Postman (Client): Serves as the external client that sends HTTP requests to the Spring Boot application, ideal for testing the API endpoints.
• Controller Layer: The first layer that interacts with incoming requests, responsible for directing these requests to appropriate services and sending back the HTTP responses.
• Service Layer: Contains the core business logic and data transformation rules. It processes data and coordinates with the repository layer to execute data operations.
• Repository Layer: Dedicated to data interaction with the Oracle Database, it performs CRUD operations directly on the database.
• Oracle Database: The backend database system used for storing and managing all application data securely and robustly.

What You’ll Learn:

• Setting up a Spring Boot project with Oracle Database.
• Configuring Spring Boot to connect to Oracle.
• Implementing CRUD operations with Spring Data JPA.
• Using Java record for DTOs to transfer data.
• Testing REST APIs using Postman.

Prerequisites

Before starting, ensure you have:

• Java Development Kit (JDK) 17 or later
• Apache Maven (for project management)
• Oracle Database (installed locally or accessible via a cloud service like Oracle Cloud)
• Oracle JDBC Driver (Download from Oracle’s website)
• IDE (e.g., IntelliJ IDEA, Eclipse, or VS Code)
• Postman (to test APIs)

Step 1: Setting Up the Project

1.1 Create a Spring Boot Project

1. Open Spring Initializr.
2. Configure the project metadata:
   • Project: Maven
   • Language: Java
   • Spring Boot Version: Latest (3.x)
   • Group: com.example
   • Artifact: spring-boot-oracle-crud
   • Java Version: 17 or later
3. Add the following dependencies:
   • Spring Web: For building RESTful web services.
   • Spring Data JPA: For interacting with Oracle using JPA (Java Persistence API).
   • Oracle JDBC Driver: You’ll need to manually download and add this to your project.
4. Click Generate to download the project, extract the zip file, and open it in your IDE.

Explanation:

Spring Initializr generates a pre-configured Spring Boot project. We’ll use Oracle as the external database, and since Spring Initializr does not directly include the Oracle JDBC driver, you must manually download and configure it in your Maven project.

Step 2: Adding the Oracle JDBC Driver

2.1 Manually Add Oracle JDBC Driver to Your Project

Oracle JDBC drivers are not available in the Maven Central repository. After downloading the driver (ojdbc8.jar) from Oracle’s website, follow these steps:

1. Install the driver in your local Maven repository by running the following command:

   mvn install:install-file -Dfile=path/to/ojdbc8.jar -DgroupId=com.oracle.database.jdbc -DartifactId=ojdbc8 -Dversion=19.8.0.0 -Dpackaging=jar
   

2. Add the following dependency to your pom.xml:

   <dependency>
       <groupId>com.oracle.database.jdbc</groupId>
       <artifactId>ojdbc8</artifactId>
       <version>19.8.0.0</version>
   </dependency>
   

Explanation:

• Oracle JDBC Driver: Required to connect your Spring Boot application to an Oracle Database. You must manually add it to your Maven project because it is not publicly available in Maven Central.

Step 3: Configuring Oracle Database

3.1 Create an Oracle Database (or Use an Existing One)

If you have Oracle installed locally or accessible via Oracle Cloud, create a new database or use an existing one. Here’s a SQL command to create a new schema:

CREATE USER productdb IDENTIFIED BY yourpassword;
GRANT ALL PRIVILEGES TO productdb;

3.2 Configure application.properties

In the src/main/resources/application.properties file, configure Spring Boot to connect to Oracle:

# Oracle database configuration
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=productdb
spring.datasource.password=yourpassword
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# JPA configurations
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Replace yourpassword with the password for your Oracle database.

Explanation:

• spring.datasource.url: JDBC URL to connect to Oracle. The format is jdbc:oracle:thin:@[host]:[port]:[SID].
• spring.datasource.username and password: Your Oracle schema credentials.
• spring.jpa.hibernate.ddl-auto=update: Automatically updates the database schema based on the entity mappings.

Step 4: Creating the Product Entity

4.1 Create the Product Entity

In the model package, create a Java class named Product to represent the product entity in the Oracle database.

package com.example.springbootoraclecrud.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String description;
    private double price;

    // Getters and Setters
    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 getDescription() { return description; }
    public void setDescription(String description) { this.description = description; }

    public double getPrice() { return price; }
    public void setPrice(double price) { this.price = price; }
}

Explanation:

• @Entity: Marks this class as a JPA entity that maps to a table in the Oracle database.
• @Id: Specifies the primary key (id), which is auto-generated using @GeneratedValue with the IDENTITY strategy.
• Fields: The product entity has fields: name, description, and price.

Step 5: Creating the Repository

5.1 Create ProductRepository

In the repository package, create an interface ProductRepository that extends JpaRepository:

package com.example.springbootoraclecrud.repository;

import com.example.springbootoraclecrud.model.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
}

Explanation:

• JpaRepository: Provides built-in methods like save(), findAll(), findById(), and deleteById() for CRUD operations.
• @Repository: Marks this interface as a Spring Data repository.

Step 6: Using Java record for DTO

6.1 Create the ProductDTO Class

In the model package, create a Java record called ProductDTO:

package com.example.springbootoraclecrud.model;

public record ProductDTO(Long id, String name, String description, double price) {}

Explanation:

• Java record: Automatically generates constructors, getters, toString(), equals(), and hashCode() methods, making it ideal for DTOs to transfer data between the client and server.

Step 7: Creating the Service Layer

7.1 Create ProductService Interface

In the service package, define an interface ProductService:

package com.example.springbootoraclecrud.service;

import com.example.springbootoraclecrud.model.ProductDTO;

import java.util.List;
import java.util.Optional;

public interface ProductService {
    List<ProductDTO> getAllProducts();
    Optional<ProductDTO> getProductById(Long id);
    ProductDTO saveProduct(ProductDTO productDTO);
    ProductDTO updateProduct(Long id, ProductDTO productDTO);
    void deleteProduct(Long id);
}

7.2 Implement ProductService in ProductServiceImpl

In the service implementation, add the conversion logic between Product (entity) and ProductDTO (DTO):

package com.example.springbootoraclecrud.service;

import com.example.springbootoraclecrud.model.Product;
import com.example.springbootoraclecrud.model.ProductDTO;
import com.example.springbootoraclecrud.repository.ProductRepository;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

@Service
public class ProductServiceImpl implements ProductService {

    private final ProductRepository productRepository;

    public ProductServiceImpl(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }

    @Override
    public List<ProductDTO> getAllProducts() {
        return productRepository.findAll().stream()
                .map(this::convertToDTO)
               

 .collect(Collectors.toList());
    }

    @Override
    public Optional<ProductDTO> getProductById(Long id) {
        return productRepository.findById(id).map(this::convertToDTO);
    }

    @Override
    public ProductDTO saveProduct(ProductDTO productDTO) {
        Product product = convertToEntity(productDTO);
        Product savedProduct = productRepository.save(product);
        return convertToDTO(savedProduct);
    }

    @Override
    public ProductDTO updateProduct(Long id, ProductDTO productDTO) {
        Product product = productRepository.findById(id).orElseThrow();
        product.setName(productDTO.name());
        product.setDescription(productDTO.description());
        product.setPrice(productDTO.price());
        Product updatedProduct = productRepository.save(product);
        return convertToDTO(updatedProduct);
    }

    @Override
    public void deleteProduct(Long id) {
        productRepository.deleteById(id);
    }

    // Convert Product Entity to ProductDTO
    private ProductDTO convertToDTO(Product product) {
        return new ProductDTO(product.getId(), product.getName(), product.getDescription(), product.getPrice());
    }

    // Convert ProductDTO to Product Entity
    private Product convertToEntity(ProductDTO productDTO) {
        Product product = new Product();
        product.setName(productDTO.name());
        product.setDescription(productDTO.description());
        product.setPrice(productDTO.price());
        return product;
    }
}

Explanation:

• The service layer is responsible for converting ProductDTO to Product and vice versa.
• convertToDTO: Converts a Product entity into a ProductDTO.
• convertToEntity: Converts a ProductDTO into a Product entity.

Step 8: Creating the REST Controller

In the controller package, create the ProductController to expose REST APIs:

package com.example.springbootoraclecrud.controller;

import com.example.springbootoraclecrud.model.ProductDTO;
import com.example.springbootoraclecrud.service.ProductService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Optional;

@RestController
@RequestMapping("/api/products")
public class ProductController {

    private final ProductService productService;

    public ProductController(ProductService productService) {
        this.productService = productService;
    }

    @GetMapping
    public List<ProductDTO> getAllProducts() {
        return productService.getAllProducts();
    }

    @GetMapping("/{id}")
    public ResponseEntity<ProductDTO> getProductById(@PathVariable Long id) {
        Optional<ProductDTO> product = productService.getProductById(id);
        return product.map(ResponseEntity::ok).orElseGet(() -> ResponseEntity.notFound().build());
    }

    @PostMapping
    public ProductDTO createProduct(@RequestBody ProductDTO productDTO) {
        return productService.saveProduct(productDTO);
    }

    @PutMapping("/{id}")
    public ResponseEntity<ProductDTO> updateProduct(@PathVariable Long id, @RequestBody ProductDTO productDTO) {
        try {
            ProductDTO updatedProduct = productService.updateProduct(id, productDTO);
            return ResponseEntity.ok(updatedProduct);
        } catch (Exception e) {
            return ResponseEntity.notFound().build();
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteProduct(@PathVariable Long id) {
        productService.deleteProduct(id);
        return ResponseEntity.noContent().build();
    }
}

Explanation:

• The controller exposes REST API endpoints for CRUD operations on products.
• The business logic is handled by the service layer, keeping the controller simple.

Step 9: Running and Testing the Application

9.1 Running the Application

To run the application, open SpringBootOracleCrudApplication.java and click the Run button in your IDE, or run the following command in the terminal:

./mvnw spring-boot:run

9.2 Testing with Postman

You can test the REST APIs using Postman:

• GET all products:
  • URL: http://localhost:8080/api/products
  • Response: A list of product DTOs.
• GET product by ID:
  • URL: http://localhost:8080/api/products/{id}
  • Response: The product data for the given ID.
• POST create a new product:
  • URL: http://localhost:8080/api/products
  • Body:

    {
      "name": "Laptop",
      "description": "A high-performance laptop.",
      "price": 1200.00
    }
    

• PUT update an existing product:
  • URL: http://localhost:8080/api/products/{id}
  • Body:

    {
      "name": "Updated Laptop",
      "description": "An updated high-performance laptop.",
      "price": 1300.00
    }
    

• DELETE a product:
  • URL: http://localhost:8080/api/products/{id}
  • Response: No content (HTTP 204).

Conclusion

In this tutorial, we built a Spring Boot CRUD REST API with Oracle Database. This guide provides a comprehensive approach to building a CRUD application with Spring Boot and Oracle, ensuring clean and maintainable code by following industry best practices.