Spring Boot CRUD Example with Oracle Database

In this tutorial, we will create a Spring Boot CRUD (Create, Read, Update, Delete) application with an Oracle database. We will cover setting up the project, configuring Oracle, creating the necessary entities and repositories, implementing the service layer, and creating RESTful controllers to handle CRUD operations using a Product entity.

Prerequisites

Before we start, ensure you have the following:

• Java Development Kit (JDK) installed
• Apache Maven installed
• Oracle Database installed and running
• An IDE (such as IntelliJ IDEA, Eclipse, or VS Code) installed

Step 1: Setting Up the Project

1.1 Create a Spring Boot Project

1. Open Spring Initializr:

   • Go to Spring Initializr in your web browser.

2. Configure Project Metadata:

   • Project: Maven Project
   • Language: Java
   • Spring Boot: Select the latest version of Spring Boot
   • Group: com.example
   • Artifact: spring-boot-oracle-crud
   • Name: spring-boot-oracle-crud
   • Description: Spring Boot CRUD example with Oracle
   • Package Name: com.example.springbootoraclecrud
   • Packaging: Jar
   • Java Version: 17 (or your preferred version)
   • Click Next.

3. Select Dependencies:

   • On the Dependencies screen, select the dependencies you need. For a basic Spring Boot CRUD application, you can start with:
     • Spring Web
     • Spring Data JPA
     • Oracle Driver
   • Click Next.

4. Generate the Project:

   • Click Generate to download the project zip file.
   • Extract the zip file to your desired location.

5. Open the Project in Your IDE:

   • Open your IDE and import the project as a Maven project.

1.2 Project Structure

After importing the project, you will see the following structure in your IDE:

spring-boot-oracle-crud
├── src
│   ├── main
│   │   ├── java
│   │   │   └── com
│   │   │       └── example
│   │   │           └── springbootoraclecrud
│   │   │               ├── SpringBootOracleCrudApplication.java
│   │   │               ├── controller
│   │   │               ├── model
│   │   │               ├── repository
│   │   │               └── service
│   ├── main
│   │   └── resources
│   │       ├── application.properties
│   └── test
│       └── java
│           └── com
│               └── example
│                   └── springbootoraclecrud
│                       └── SpringBootOracleCrudApplicationTests.java
└── pom.xml

Step 2: Configuring Oracle Database

2.1 Create an Oracle Database

1. Open your Oracle SQL client (SQL*Plus, SQL Developer, etc.).
2. Create a new user and schema for the project:

   CREATE USER springbootuser IDENTIFIED BY yourpassword;
   GRANT CONNECT, RESOURCE, DBA TO springbootuser;
   

2.2 Configure Spring Boot to Use Oracle

Open the application.properties file located in the src/main/resources directory and add the following configuration:

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

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

Replace yourpassword with the password for your Oracle springbootuser.

Step 3: Creating the Entity

3.1 Create the Product Entity

In the model package, create a new Java class named Product:

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;
    }
}

Step 4: Creating the Repository

4.1 Create the ProductRepository Interface

In the repository package, create a new Java interface named ProductRepository:

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> {
}

Step 5: Creating the Service

5.1 Create the ProductService Interface

In the service package, create a new Java interface named ProductService:

package com.example.springbootoraclecrud.service;

import com.example.springbootoraclecrud.model.Product;

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

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

5.2 Implement the ProductService Interface

In the service package, create a new Java class named ProductServiceImpl:

package com.example.springbootoraclecrud.service;

import com.example.springbootoraclecrud.model.Product;
import com.example.springbootoraclecrud.repository.ProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class ProductServiceImpl implements ProductService {

    @Autowired
    private ProductRepository productRepository;

    @Override
    public List<Product> getAllProducts() {
        return productRepository.findAll();
    }

    @Override
    public Optional<Product> getProductById(Long id) {
        return productRepository.findById(id);
    }

    @Override
    public Product saveProduct(Product product) {
        return productRepository.save(product);
    }

    @Override
    public Product updateProduct(Product product) {
        return productRepository.save(product);
    }

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

Step 6: Creating the Controller

6.1 Create the ProductController Class

In the controller package, create a new Java class named ProductController:

package com.example.springbootoraclecrud.controller;

import com.example.springbootoraclecrud.model.Product;
import com.example.springbootoraclecrud.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
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 {

    @Autowired
    private ProductService productService;

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

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

    @PostMapping
    public Product createProduct(@RequestBody Product product) {
        return productService.saveProduct(product);
    }

    @PutMapping("/{id}")
    public ResponseEntity<Product> updateProduct(@PathVariable Long id, @RequestBody Product productDetails) {
        Optional<Product> productOptional = productService.getProductById(id);
        if (productOptional.isPresent()) {
            Product product = productOptional.get();
            product.setName(productDetails.getName());
            product.setDescription(productDetails.getDescription());
            product.setPrice(productDetails.getPrice());
            return ResponseEntity.ok(productService.updateProduct(product));
        } else {
            return ResponseEntity.notFound().build();
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteProduct(@PathVariable Long id) {
        Optional<Product> product = productService.getProductById(id);
        if (product.isPresent()) {
            productService.deleteProduct(id);
            return ResponseEntity.noContent().build();
        } else {
            return ResponseEntity.notFound().build();
        }
    }
}

Step 7: Running the Application

1. Run the Application:
   • Open the SpringBootOracleCrudApplication class in the src/main/java/com/example/springbootoraclecrud directory

.

• Click the green Run button in your IDE or use the terminal to run the application:

  ./mvnw spring-boot:run
  

2. Verify the Application:
   • Open your web browser or a tool like Postman and navigate to http://localhost:8080/api/products.

You can now perform CRUD operations on the Product entity using the following endpoints:

• GET /api/products: Get all products
• GET /api/products/{id}: Get product by ID
• POST /api/products: Create a new product
• PUT /api/products/{id}: Update an existing product
• DELETE /api/products/{id}: Delete a product

Conclusion

In this tutorial, we have walked through the process of creating a Spring Boot CRUD application with an Oracle database. We configured the project, set up Oracle, created the necessary entities, repositories, services, and controllers, and tested the CRUD operations. This setup provides a solid foundation for developing more complex Spring Boot applications with Oracle.