Spring Boot + Data JPA + Oracle一对多示例
时间：2020-01-09 10:44:30 来源:igfitidea点击:
在本文中，我们将看到一个Spring Boot + Data JPA + Oracle DB + Spring REST的示例。在示例中，两个表用于演示一对多和多对一关系。
在示例中使用的Spring Data JPA存储库中，自定义方法还用于显示如何编写方法以通过在数据JPA存储库中的方法中使用这些关键字来自动生成"介于"和"大于"之间的查询。我们还将看到如何通过在Spring Data JPA中使用@Query注释自己编写查询。
数据库表
客户和交易有两个表，因为客户可以进行许多交易，这意味着客户和交易之间存在一对多的关系。
创建表的查询
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")
);
如我们所见，Id是使用Identity在两个表中自动创建的。
在事务表中，有一个外键约束引用了客户表的CUST_ID。
Maven依赖关系– pom.xml
这个Spring Boot Rest服务示例使用Data JPA和Oracle DB，因此必须添加它们的依赖项。
<?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.theitroad</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
@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;
}
}
在这里，@ JsonIgnore注释用于避免事务获取客户数据的无限循环，而后者又会获取事务数据，依此类推。
DTO班
有一个DTO类同时具有Transaction和Customer字段，如果同时需要Customer和Transaction的数据，则可以填充并返回此DTO。
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;
}
}
控制器类
带有REST API方法的控制器类映射到URL路径。
客户控制器
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.theitroad.dto.CustomerTransactionDTO;
import com.theitroad.entities.Customer;
import com.theitroad.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.theitroad.entities.Transaction;
import com.theitroad.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储存库
由于使用了Spring数据，因此我们只需要创建Repository接口。
客户资料库
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.theitroad.entities.Customer;
import com.theitroad.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);
}
除了CustomerRepository通过扩展JpaRepository继承的常规CRUD方法之外，还有一个自定义方法getAllCustomersByTxnAmountGreaterThan()，该方法以@Query方法进行注释，并且查询带有该注释。
交易库
import java.time.LocalDateTime;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import com.theitroad.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);
}
在TransactionRepository中，有三种自定义方法
- findTransactionByTxnAmountBetween –查找具有给定范围内的交易金额的所有交易。
- findTransactionByTxnAmountGreaterThan –查找所有交易金额大于通过金额的交易。
- findTransactionsByTxnDateBetween –查找过去日期范围之间的所有交易。
这些方法未提供查询。Spring数据本身通过解析方法名称来生成查询。或者，我们可以使用@Query注释自行提供查询。
服务等级
客户服务接口
import java.util.List;
import com.theitroad.dto.CustomerTransactionDTO;
import com.theitroad.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类
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.theitroad.dao.CustomerRepository;
import com.theitroad.dto.CustomerTransactionDTO;
import com.theitroad.entities.Customer;
import com.theitroad.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;
}
}
在CustomerServiceImpl类的getAllCustomersByAmount()方法中，我们可以看到CustomerTransactionDTO的用法。
TransactionService接口
import java.time.LocalDateTime;
import java.util.List;
import com.theitroad.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类
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.theitroad.dao.TransactionRepository;
import com.theitroad.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);
}
}
数据库配置
可以将数据库配置(例如连接URL，用户，密码)放在src / main / resources中的application.properties文件中。请根据配置更改值。
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
应用类别
应用程序类具有main方法。
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);
}
}
运行应用程序并访问REST服务
我们可以通过将应用程序类作为Java应用程序(或者Spring引导应用程序)运行来启动示例。
Spring Boot会将应用程序自动配置为Spring Boot Rest服务+ Data JPA应用程序。一旦看到嵌入式Tomcat服务器已启动的消息，就可以使用Postman测试方法。
建立客户
我们可以在DB表中验证是否插入了相应的客户和交易记录。
更新现有客户
获取交易金额大于通过金额的客户和交易数据
通过ID获取交易
获取给定金额范围内的交易
