Spring Boot, Oracle 26ai and Spring Data JPA examples

Spring Boot in data access.

➔ Spring Boot provides a production-ready environment for application development, and developers can focus directly on business logic development instead of tedious application configuration and setup.

Spring Boot with Oracle Database.

➔ Oracle is a very popular and robust RDBMS (Relational Database Management System). It is an open source and free software developed and maintained by Oracle.

➔ The Oracle database is ideal for organizations of all sizes, from small to large enterprise, because it is highly reliable, extremely fast at data processing, and easy to use.

➔ Supported and compatible with standard SQL (Structured Query Language).

➔ Oracle supports the Oracle SQL Developer tool, which is a user-friendly graphical user interface tool that allows you to easily manage databases, tables and other properties.

➔ Oracle supports AI Vector Search and Large Language Models (LLMs)integration.

Spring Boot and Spring Data JPA.

Spring boot can connect to and work with any database using Spring Data JPA, so Spring boot can naturally work seamlessly with popular and robust databases like Oracle. Database connections can also be established through various conventional methods.

Creating a Spring Boot project using the popular IDE IntelliJ IDEA..

Step 01: Create a Spring Boot Project. File -> New -> Project

Step 02: Spring Boot Project setup

Step 03: Spring Boot Project dependency

Spring Boot DevTools, Spring Web, Spring Data JPA, Oracle Driver

Step 04: Add spring-boot-starter-validation dependency to pom.xml

Project dependencies pom.xml file:

➔ pom.xml (Project Object Model) is the main configuration file for Maven-based Spring Boot applications, which defines project metadata, manages project dependencies, and configures the build process.

➔ The pom.xml file must be in the root directory of your project and is an ideal location for Maven-based applications.

Now final pom.xml code

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>4.0.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.exam</groupId>
    <artifactId>sboraclespringjpa</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sboraclespringjpa</name>
    <description>sboraclespringjpa</description>
    <url/>
    <licenses>
        <license/>
    </licenses>
    <developers>
        <developer/>
    </developers>
    <scm>
        <connection/>
        <developerConnection/>
        <tag/>
        <url/>
    </scm>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webmvc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc11</artifactId>
            <scope>runtime</scope>
        </dependency>

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webmvc-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Step 05: Find an verify oracle service name to be used in Spring Boot URL property.

If the Oracle database server is not already installed and database is not created then see topic Creating a database and table in Oracle26ai.

SQL code

sqlplus / as sysdba
SHOW PDBS;
ALTER PLUGGABLE DATABASE demopdb OPEN;
ALTER SESSION SET CONTAINER = demopdb;
SELECT name FROM v$services;

spring boot spring-boot-starter-validation

Step 06: Spring Boot application.properties file:

➔ To establish a database connection and perform data access operations, a Spring Boot application needs to configure Oracle database properties and Hibernate properties through its application.properties file.

➔ Most of the entries in the application.properties file are self-explanatory.

Step 05: Spring Boot application.properties

application.properties code

spring.application.name=sboraclespringjpa

server.port=8090

# Spring DATASOURCE DataSourceProperties
spring.datasource.url=jdbc:oracle:thin:@//localhost:1521/demopdb
spring.datasource.username = demopdbuser
spring.datasource.password = demopdbuser
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# Hibernate Properties
spring.jpa.database-platform=org.hibernate.dialect.OracleDialect
# Hibernate ddl-auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Organize code into packages:

➔ The project code can be organized into packages according to function, such as the models, repositories, services, controllers listed below, and some classes and interfaces can be created inside these packages.

➔ Using packages to organize coding files in a project is a good programming practice.

07 Spring Boot Project structure (create packages)

Entity or Model class:

➔ In Spring Boot, the terms 'Model' and 'Entity' are often used to describe data-holding classes, but their function can be described slightly differently.

➔ Entity: An Entity is a Java class that is directly mapped to a database table, and the main function of the Spring Data JPA layer is to represent the data in the database table.

➔ Model: Model is a broad term for classes that represent data for business logic or user interfaces, and these classes are used to exchange data between the frontend or backend, or both.

Step 08: User.class (Entity Layer)

code

package com.exam.sboraclespringjpa.entity;

import jakarta.persistence.*;

@Entity
@Table(name = "myuser")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long userId;
    private String name;
    private String email;

    public User() {
    }

    public User(Long userId) {
        this.userId = userId;
    }

    public User(Long userId, String name, String email) {
        this.userId = userId;
        this.name = name;
        this.email = email;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

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

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

JpaRepository

➔ JpaRepository is a Java Persistence API (JPA) interface that extends the CrudRepository and PagingAndSortingRepository interfaces.

➔ So, JpaRepository has full API support for basic CRUD operations as well as API support for pagination and sorting.

➔ In addition to the basic predefined standard queries, it is also possible to implement custom queries using the query method naming convention or the @Query annotation.

Step 09: Repository interface UserRepository.java (Repository Layer)

code

package com.exam.sboraclespringjpa.repository;


import com.exam.sboraclespringjpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User,Long> {
}

Service Layer

➔ Process business logic and core application rules.

➔ Works with domain models, DTOs, Entities, and Java objects.

➔ Calls the repository layer to process data.

Step 10: Service class UserService.java (Service Layer)

code

package com.exam.sboraclespringjpa.service;



import com.exam.sboraclespringjpa.entity.User;
import com.exam.sboraclespringjpa.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class UserService {
    @Autowired
    private UserRepository repository;

    public List<User> fetchUsers() {
        return repository.findAll();
    }

    public Optional<User> fetchUsersById(Long id) {
        return repository.findById(id);
    }

    public User insertUser(User user) {
        return repository.save(user);
    }

    public User editUser(User user) {
        return repository.save(user);
    }

    public String removeUser(Long id) {
        repository.deleteById(id);
        return "Deleted Successfully";
    }
}

Controller Layer

➔ Handles HTTP requests and communicates with the presentation layer.

➔ Works with HTTP Requests, RequestParam, PathVariable, and JSON.

➔ The controller calls the service layer to process the data.

Step 11: Controller class UserController.java (Controller Layer)

code

package com.exam.sboraclespringjpa.controller;


import com.exam.sboraclespringjpa.entity.User;
import com.exam.sboraclespringjpa.service.UserService;
import jakarta.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

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

@RestController
@RequestMapping("api")
public class UserController {
    @Autowired
    private UserService service;

    // Insert operation
    @PostMapping("/user")
    public User insertUser(@Valid @RequestBody User user)
    {
        return service.insertUser(user);
    }
    //Fetch operation
    @GetMapping("/user")
    public List<User> fetchUsers()
    {
        return service.fetchUsers();
    }
    //Fetch operation
    @GetMapping("/user/{id}")
    public User fetchUsersById(@PathVariable Long id)
    {
        Optional<User> user = service.fetchUsersById(id);
        if(user.isPresent()){
            return  user.get();
        }
        return null;
    }

    //Edit operation
    @PutMapping("user/{id}")
    public User editUser(@PathVariable Long id, @RequestBody User frontendUser) {
        Optional<User> user = service.fetchUsersById(id);
        if (user.isPresent()) {
            User backendUser = user.get();
            backendUser.setName(frontendUser.getName());
            backendUser.setEmail(frontendUser.getEmail());
            return service.editUser(backendUser);
        }
        return null;
    }

    //Remove operation
    @DeleteMapping("user/{id}")
    public String removeUser(@PathVariable Long id) {
        return service.removeUser(id);
    }
}

Step 12: Run Project by Right click on SbspringdatamysqlApplication.java class annotated with @SpringBootApplication

Step 13: Verify Oracle Table and Data

SQL code

sqlplus / as sysdba
SHOW PDBS;
ALTER PLUGGABLE DATABASE demopdb OPEN;
ALTER SESSION SET CONTAINER = demopdb;
CONNECT demopdbuser/demopdbuser@//localhost:1521/demopdb;
SELECT table_name FROM user_tables;
COLUMN email FORMAT A20
COLUMN name FORMAT A20
SELECT * FROM MYUSER;