Spring Boot + Data JPA + Oracle One to Many Example

In this article we’ll see an example of Spring Boot + Data JPA + Oracle DB + Spring REST. In the example two tables are used to demonstrate One-to-Many and Many-to-One relationship.
In the Spring Data JPA repositories used in the example, custom methods are also used to show how you can write methods to generate queries automatically for “Between” and “Greater than” by using these keywords in the methods in the data JPA repositories. We’ll also see how you can write query yourself by using @Query annotation with Spring Data JPA.

DB Tables

There are two tables Customer and Transaction, since a customer can have many transactions that means there is one-to-many relationship between Customer and Transaction.

one to many JPA
Queries for creating tables

CREATE TABLE "TEST"."CUSTOMER" 
   (	"CUST_NAME" VARCHAR2(20 BYTE), 
	"CUST_EMAIL" VARCHAR2(20 BYTE), 
	"CUST_ADDR" VARCHAR2(30 BYTE), 
	"CUST_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	 CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUST_ID")
);
CREATE TABLE "TEST"."TRANSACTION" 
   (	"TXN_DATE" DATE, 
	"TXN_AMOUNT" NUMBER(10,2), 
	"CUST_ID" NUMBER, 
	"TXN_ID" NUMBER(4,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	 CONSTRAINT "TRANSACTION_PK" PRIMARY KEY ("TXN_ID"), 
	 CONSTRAINT "TRANSACTION_FK" FOREIGN KEY ("CUST_ID")
	  REFERENCES "TEST"."CUSTOMER" ("CUST_ID")
   );

As you can see Id is automatically created in both the tables using Identity.
In Transaction table there is a foreign key constraint referencing CUST_ID of the Customer table.

Maven Dependencies – pom.xml

This Spring Boot Rest service example uses Data JPA and Oracle DB so the dependencies for these have to be added.

<?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>2.2.5.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.knpcode</groupId>
  <artifactId>jpademo</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>SBJPADemo</name>
  <description>Project for JPA</description>

  <properties>
    <java.version>1.8</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-web</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-devtools</artifactId>
      <scope>runtime</scope>
      <optional>true</optional>
    </dependency>
    <!--Oracle driver -->
    <dependency>
      <groupId>com.oracle.ojdbc</groupId>
      <artifactId>ojdbc8</artifactId>
      <version>19.3.0.0</version>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
      <exclusions>
        <exclusion>
          <groupId>org.junit.vintage</groupId>
          <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
  </dependencies>

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

JPA Entity classes

There are two entity classes which map to Customer and Transaction tables respectively.

@Entity 
@Table(name="CUSTOMER")
public class Customer implements Serializable{
  private static final long serialVersionUID = -7496362624106858939L;
  // Primary key 
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name="CUST_ID")
  private int custId;
  @Column(name="CUST_NAME")
  private String custName;
  @Column(name="CUST_ADDR")
  private String custAddr;
  @Column(name="CUST_EMAIL")
  private String custEmail;
  // One to many mapping with transactions	
  @OneToMany(cascade = CascadeType.ALL)
  @Fetch(FetchMode.JOIN)
  @JoinColumn(name="CUST_ID")
  private Set<Transaction> transactions;
  public Set<Transaction> getTransactions() {
    return transactions;
  }
  public void setTransactions(Set<Transaction> transactions) {
    this.transactions = transactions;
  }
  public int getCustId() {
    return custId;
  }
  public void setCustId(int custId) {
    this.custId = custId;
  }
  public String getCustName() {
    return custName;
  }
  public void setCustName(String custName) {
    this.custName = custName;
  }
  public String getCustAddr() {
    return custAddr;
  }
  public void setCustAddr(String custAddr) {
    this.custAddr = custAddr;
  }
  public String getCustEmail() {
    return custEmail;
  }
  public void setCustEmail(String custEmail) {
    this.custEmail = custEmail;
  }	
}
@Entity 
@Table(name="TRANSACTION")
public class Transaction implements Serializable{
  private static final long serialVersionUID = 6392890629580631252L;
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name="TXN_ID")
  private int txnId;
  @Column(name="TXN_DATE")
  private LocalDateTime txnDate;
  @Column(name="TXN_AMOUNT")
  private double txnAmount;
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name="CUST_ID", nullable=false)
  private Customer customer;
  public int getTxnId() {
    return txnId;
  }
  public void setTxnId(int txnId) {
    this.txnId = txnId;
  }
  public LocalDateTime getTxnDate() {
    return txnDate;
  }
  public void setTxnDate(LocalDateTime txnDate) {
    this.txnDate = txnDate;
  }
  public double getTxnAmount() {
    return txnAmount;
  }
  public void setTxnAmount(double txnAmount) {
    this.txnAmount = txnAmount;
  }
  @JsonIgnore
  public Customer getCustomer() {
    return customer;
  }

  public void setCustomer(Customer customer) {
    this.customer = customer;
  }
}

Here @JsonIgnore annotation is used to avoid the infinite cycle of Transaction fetching Customer data which in turn fetches Transaction data and so on.

DTO class

There is a DTO class having fields of both Transaction and Customer, this DTO can be populated and returned if data from both Customer and Transaction is needed.

public class CustomerTransactionDTO {
  private int txnId;
  private LocalDateTime txnDate;
  private double txnAmount;
  private int customerId;
  private String customerName;
  public int getTxnId() {
    return txnId;
  }
  public void setTxnId(int txnId) {
    this.txnId = txnId;
  }
  public LocalDateTime getTxnDate() {
    return txnDate;
  }
  public void setTxnDate(LocalDateTime txnDate) {
    this.txnDate = txnDate;
  }
  public double getTxnAmount() {
    return txnAmount;
  }
  public void setTxnAmount(double txnAmount) {
    this.txnAmount = txnAmount;
  }
  public int getCustomerId() {
    return customerId;
  }
  public void setCustomerId(int customerId) {
    this.customerId = customerId;
  }
  public String getCustomerName() {
    return customerName;
  }
  public void setCustomerName(String customerName) {
    this.customerName = customerName;
  }
}

Controller classes

Controller classes with the REST API methods mapping to the URL path.

CustomerController

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;
import com.knpcode.dto.CustomerTransactionDTO;
import com.knpcode.entities.Customer;
import com.knpcode.service.CustomerService;

@RestController
public class CustomerController {
  @Autowired
  CustomerService customerService;
  
  // insert customer
  @PostMapping("/customer")
  @ResponseStatus(HttpStatus.CREATED)
  public Customer addCustomer(@RequestBody Customer customer){
    return customerService.insertCustomer(customer);
  }
    
  // Get all customers
  @GetMapping("/customers")
  public List<Customer> getAllCustomers(){
    return customerService.getAllCustomers();
  }
    
  // Updating customer record
  @PutMapping("/updatecustomer")
  public Customer updateCustomer(@RequestBody Customer customer) {	
    return customerService.updateCustomer(customer);
  }
    
  // delete customer
  @DeleteMapping("/customer/{id}")
  @ResponseStatus(value=HttpStatus.OK, reason="Customer Deleted")
  public void deleteCustomer(@PathVariable int id){
    customerService.deleteCustomer(id);
  }
    
  // Get customers with transaction amount greater than the passed amount
  @GetMapping("/customers/amount/{amount}")
  public List<CustomerTransactionDTO> getAllCustomersByAmount(@PathVariable double amount){
    return customerService.getAllCustomersByAmount(amount);
  }
}

TransactionController

import java.time.LocalDateTime;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import com.knpcode.entities.Transaction;
import com.knpcode.service.TransactionService;

@RestController
public class TransactionController {
  @Autowired
  TransactionService transactionService;
    
  // Get transaction by ID
  @GetMapping("transaction/{id}")
  public Transaction getTransactionById(@PathVariable int id) {
    return transactionService.getTransactionById(id);
  }

  // Get transactions by Date
  @GetMapping("transactions/{date}")
  public List<Transaction> getTransactionsByDate(@PathVariable("date") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime date) {
    return transactionService.getTransactionsByDate(date);
  }
    
  // Get transactions between the passed amount range
  @GetMapping("/transactions/range/{amount}")
  public List<Transaction> getAllTransactionsBetweenAmount(@PathVariable("amount") String amountRange){
    return transactionService.getAllTransactionsBetweenAmount(amountRange);
  }

  // Get transactions greater than the passed amount
  @GetMapping("/transactions/amount/{amount}")
  public List<Transaction> getAllTransactionsByAmount(@PathVariable double amount){
    return transactionService.getAllTransactionsByAmount(amount);
  }
}

JPA Repositories

Since Spring data is used so you just need to create Repository interfaces.
CustomerRepository

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.knpcode.entities.Customer;
import com.knpcode.entities.Transaction;

public interface CustomerRepository extends JpaRepository<Customer, Integer>{

  @Query("select t from Transaction t where t.txnAmount > ?1")
  List<Transaction> getAllCustomersByTxnAmountGreaterThan(double amount);

  //List<Transaction> findByTransactionsTxnAmountGreaterThan(double amount);
}

Apart from the usual CRUD methods which CustomerRepository inherits by extending JpaRepository there is also a custom method getAllCustomersByTxnAmountGreaterThan() which is annotated with @Query method and the query is provided with that annotation.
TransactionRepository

import java.time.LocalDateTime;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import com.knpcode.entities.Transaction;

public interface TransactionRepository extends JpaRepository<Transaction, Integer> {
	List<Transaction> findTransactionByTxnAmountBetween(double fromAmount, double toAmount);
	List<Transaction> findTransactionByTxnAmountGreaterThan(double amount);
	List<Transaction> findTransactionsByTxnDateBetween(LocalDateTime fromDate, LocalDateTime toDate);
}

In the TransactionRepository there are three custom methods-

  • findTransactionByTxnAmountBetween– Find all the transactions having transaction amount between the given range.
  • findTransactionByTxnAmountGreaterThan– Find all the transactions having transaction amount greater than the passed amount.
  • findTransactionsByTxnDateBetween– Find all the transactions between the passed date range.

Query is not provided for these methods Spring data itself generates the query by parsing the method name. Alternatively you can provide query yourself by using @Query annotation.

Service classes

CustomerService interface

import java.util.List;
import com.knpcode.dto.CustomerTransactionDTO;
import com.knpcode.entities.Customer;

public interface CustomerService {
  Customer insertCustomer(Customer customer);
  List<Customer> getAllCustomers();
  Customer updateCustomer(Customer customer);
  void deleteCustomer(int id);
  List<CustomerTransactionDTO> getAllCustomersByAmount(double amount);
}

CustomerServiceImpl class

import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.knpcode.dao.CustomerRepository;
import com.knpcode.dto.CustomerTransactionDTO;
import com.knpcode.entities.Customer;
import com.knpcode.entities.Transaction;

@Service
public class CustomerServiceImpl implements CustomerService{
  @Autowired
  private CustomerRepository repository;
  @Override
  public Customer insertCustomer(Customer customer) {
    return repository.save(customer);
  }

  @Override
  public List<Customer> getAllCustomers() {
    return repository.findAll();
  }

  @Override
  public Customer updateCustomer(Customer customer) {
    Customer custDB = repository.findById(customer.getCustId()).get();
    custDB.setCustEmail(customer.getCustEmail());
    return repository.save(custDB);
  }

  @Override
  public void deleteCustomer(int id) {
    repository.deleteById(id);
  }

  @Override
  public List<CustomerTransactionDTO> getAllCustomersByAmount(double amount) {
    List<Transaction> transactions = repository.getAllCustomersByTxnAmountGreaterThan(amount);
    CustomerTransactionDTO ct;
    List<CustomerTransactionDTO> ctList = new ArrayList<>();
    for(Transaction t : transactions) {
      ct = new CustomerTransactionDTO();
      ct.setCustomerId(t.getCustomer().getCustId());
      ct.setCustomerName(t.getCustomer().getCustName());
      ct.setTxnId(t.getTxnId());
      ct.setTxnDate(t.getTxnDate());
      ct.setTxnAmount(t.getTxnAmount());
      ctList.add(ct);
    }
    return ctList;
  }
}

In the getAllCustomersByAmount() method of the CustomerServiceImpl class you can see the use of the CustomerTransactionDTO.

TransactionService interface

import java.time.LocalDateTime;
import java.util.List;
import com.knpcode.entities.Transaction;

public interface TransactionService {
  Transaction getTransactionById(int id);
  List<Transaction> getTransactionsByDate(LocalDateTime date);
  List<Transaction> getAllTransactionsBetweenAmount(String amountRange);
  List<Transaction> getAllTransactionsByAmount(double amount);
}

TransactionServiceImpl class

import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.knpcode.dao.TransactionRepository;
import com.knpcode.entities.Transaction;

@Service
public class TransactionServiceImpl implements TransactionService{
  @Autowired
  private TransactionRepository repository;
  @Override
  public Transaction getTransactionById(int id) {
    return repository.findById(id).get();
  }

  @Override
  public List<Transaction> getTransactionsByDate(LocalDateTime fromDate) {
    // Passing the range for date- 00:00:00 to 23:59:00
    LocalDateTime toDate = LocalDateTime.of(fromDate.toLocalDate(), LocalTime.of(23, 59, 59));
    return repository.findTransactionsByTxnDateBetween(fromDate, toDate);
  }

  @Override
  public List<Transaction> getAllTransactionsBetweenAmount(String amountRange) {
    // Splitting the amount range passed in the form amt1-amt2
    String[] temp = amountRange.split("-");
    double fromAmount = Double.parseDouble(temp[0]);
    double toAmount = Double.parseDouble(temp[1]);
    System.out.println("fromAmount " + fromAmount);
    System.out.println("toAmount " + toAmount);
    return repository.findTransactionByTxnAmountBetween(fromAmount, toAmount);
  }

  @Override
  public List<Transaction> getAllTransactionsByAmount(double amount) {
    return repository.findTransactionByTxnAmountGreaterThan(amount);
  }
}

DB Configuration

DB configuration like connection URL, user, password can be put in the application.properties file residing in src/main/resources. Please change the values as per your configuration.

spring.datasource.url=jdbc:oracle:thin:@localhost:1521/XEPDB1
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
 
spring.jpa.show-sql=true
#spring.jpa.properties.hibernate.format_sql=true

Application class

Application class with the main method.

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SbjpaDemoApplication {
  public static void main(String[] args) {
    SpringApplication.run(SbjpaDemoApplication.class, args);
  }
}

Running the application and accessing the REST services

You can start the example by running the application class as a Java application (or Spring boot app).

Spring Boot automatically configures the application as a Spring Boot Rest service + Data JPA application. Once you see the message that the embedded Tomcat server is started you can test the methods using Postman.

Creating a Customer
Spring Boot JPA Postman
You can verify in the DB tables that the corresponding customer and transaction records are inserted.
Customer
Transaction
Updating an existing customer
Update record Data JPA
Getting Customer and Transaction data having transaction amount greater than passed amount

Get transaction by ID

Get transactions between the given amount range

Related Posts

That’s all for the topic Spring Boot + Data JPA + Oracle One to Many Example. If something is missing or you have something to share about the topic please write a comment.


You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.