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.

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(mappedBy = "customer", cascade = 	CascadeType.ALL)
  private Set<Transaction> transactions;

  public Set<Transaction> getTransactions() {
    return transactions;
  }
  public void setTransactions(Set transactions) {
    this.transactions = transactions;
    for(Transaction txn : transactions) {
      txn.setCustomer(this);
    }
  }
  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;
  }	
}

As you can notice with @OneToMany mapping here mappedBy attribute is used in the Customer class, making it the inverse side of the relationship.

One other thing to notice is the setTransactions() method where customer is explicitly set for the transactions, this is required to make it work while testing the REST API with postman or swagger, otherwise customerId won’t be saved in the Transaction table.

public void setTransactions(Set<Transaction> transactions) {
  this.transactions = transactions;
  for(Transaction txn : transactions) {
    txn.setCustomer(this);
  }
}

Another way to ensure that you can save both customer and transaction is by making Customer class as the owning side too, though that is not a recommended solution. That can be done by using the @OneToMany annotation in the following way.

// One to many mapping with transactions	
@OneToMany(cascade = CascadeType.ALL)
@Fetch(FetchMode.JOIN)
@JoinColumn(name="CUST_ID")
private Set transactions;

If you make Customer class also the owning side then you don’t need to explicitly set customer in the transaction instances so setTransactions() method can be written as-

public void setTransactions(Set<Transaction> transactions) {
  this.transactions = transactions;
}

Transaction Entity class

@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.

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){
    //  Should have some exception handling
    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<Customer> 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 distinct c from Customer c join fetch c.transactions t "
      + " where t.txnAmount > ?1")
  List<Customer> findAllCustomersByTxnAmountGreaterThan(double amount);
}

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

Query provided uses the fetch join which allows associations or collections of values to be fetched eagerly only for the current query. Since we need both Customer and transaction in the single select so fetch join is a good option here.

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<Customer> 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<Customer> getAllCustomersByAmount(double amount) {
    List<Customer> customers = repository.findAllCustomersByTxnAmountGreaterThan(amount);
    return customers;
  }
}

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

You can verify in the DB tables that the corresponding customer and transaction records are inserted.

Updating an existing customer

Getting Customer and Transaction data having transaction amount greater than passed amount

Get transaction by ID

Get transactions between the given amount range

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

KnpCode

July 2, 2021