使用 JPA操作MySQL数据库__基于SpringBoot3.0+

国内开发主流使用的orm框架大都是Mybatis/Mybatis-Plus，然而JPA也是一款非常出色的orm框架，接下来就演示如何使用JPA来实现基本的CRUD操作。

JDK版本：JDK 21.0.3

数据库：MySQL 8.0.37

SpringBoot版本：Spring Boot 3.3.0

源码Github地址：https://github.com/ashe-c0de/curd_jpa

 

分页查询（关联表）

在JPA中表与表之间relation是通过实体Entity来建立联系的，比如Orders表和Customers表，其中Orders表字段customer_id对应了Customers表的主键customer_id，属于《多对一》

CREATE TABLE Orders (
  order_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '订单唯一标识符，主键',
  customer_id BIGINT COMMENT '客户唯一标识符',
  order_date DATE COMMENT '订单日期',
  total_amount INT COMMENT '订单总金额，单位分',
  currency VARCHAR(10) COMMENT '订单金额使用的货币',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '订单创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '订单更新时间'
) COMMENT '订单表，用于存储订单信息';

CREATE TABLE Customers (
  customer_id BIGINT PRIMARY KEY COMMENT '客户唯一标识符，主键',
  customer_name VARCHAR(10) COMMENT '客户名',
  address VARCHAR(200) COMMENT '收货地址',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '客户创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '客户更新时间'
)COMMENT'客户表，用于存储客户信息';

这种多对一的映射关系，是在实体Entity中体现的，通过@ManyToOne注解来显式声明

@Getter
@Setter
@Entity
@Table(name = "Orders")
public class Order {

    @Id
    @Column(name = "order_id", nullable = false)
    private Long orderId;

    @ManyToOne
    @JoinColumn(name = "customer_id", nullable = false)
    private Customer customer;

    @Column(name = "order_date", nullable = false)
    private LocalDate orderDate;

    @Column(name = "total_amount", nullable = false)
    private Integer totalAmount;

    @Column(name = "currency", length = 10, nullable = false)
    private String currency;

    @CreationTimestamp
    @Column(name = "created_at", updatable = false)
    private LocalDateTime createdAt;

    @UpdateTimestamp
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

}

@Getter
@Setter
@Entity
@Table(name = "Customers")
public class Customer {

    @Id
    @Column(name = "customer_id", nullable = false)
    private Long customerId;

    @Column(name = "customer_name")
    private String customerName;

    @Column(name = "address")
    private String address;

    @CreationTimestamp
    @Column(name = "created_at", updatable = false)
    private LocalDateTime createdAt;

    @UpdateTimestamp
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

}

然后通过Orders表没有的customer_name字段来关联查询订单的分页结果

            Pageable pageable = PageRequest.of(dto.getPageNumber(), dto.getPageSize());
            // Specification对象构建where条件
            Specification<Order> spec = Specification.where(null);
            if (dto.getCustomerName() != null) {
                // 会根据关联的customer_id连接customer_name关联查询
                spec = spec.and(OrdersSpecification.hasCustomerName(dto.getCustomerName()));
            }
            if (dto.getCurrency() != null) {
                spec = spec.and(OrdersSpecification.hasCurrency(dto.getCurrency()));
            }
            if (dto.getOrderDate() != null) {
                spec = spec.and(OrdersSpecification.hasOrderDate(dto.getOrderDate()));
            }
            Page<Order> pages = orderRepository.findAll(spec, pageable);
            // Order显然是实体类，返给前端的对象通常仅需要暴露其需要的属性，因此如下做了对象转换
            PagedModel<OrderRsp> pagedModel = new PagedModel<>(pages.map(OrderMapper::OrderRsp));

插入/修改

    @Transactional
    public RestResult create(OrderReq request) {
        try {
            Customer customer = customerRepository.findById(request.getCustomerId()).orElseThrow(() -> new RuntimeException("不存在的客户"));
            Order order = new Order();
            order.setOrderId(snowflakeIdGenerator.nextId());
            order.setCustomer(customer);
            order.setOrderDate(LocalDate.now());
            order.setTotalAmount(request.getTotalAmount());
            order.setCurrency(request.getCurrency());
            order.setCreatedAt(LocalDateTime.now());
            order.setUpdatedAt(LocalDateTime.now());
            orderRepository.save(order);
        } catch (Exception e) {
            return RestResult.err(e.getMessage());
        }
        return RestResult.ok();
    }