在Spring Data JPA开发中，虽然提供了丰富的Repository接口和@Query注解，但面对复杂的业务逻辑时，原生SQL查询依然是不可或缺的利器。本文将详细介绍如何在Spring Data JPA中使用Native Query来处理复杂业务场景。
Native Query基础用法
首先，我们创建一个包含复杂查询的Repository接口：
public interface OrderRepository extends JpaRepository<Order, Long> {
@Query(value = "SELECT o.id, o.order_date, SUM(oi.quantity * oi.price) as total_amount " +
"FROM orders o " +
"JOIN order_items oi ON o.id = oi.order_id " +
"WHERE o.user_id = :userId " +
"GROUP BY o.id, o.order_date", nativeQuery = true)
List<OrderSummary> findUserOrderSummaries(@Param("userId") Long userId);
}
对应的实体类结构：
@Entity
@Table(name = "orders")
public class Order {
@Id
private Long id;
@Column(name = "order_date")
private LocalDateTime orderDate;
@ManyToOne
@JoinColumn(name = "user_id")
private User user;
@OneToMany(mappedBy = "order", cascade = CascadeType.ALL)
private List<OrderItem> orderItems;
}
public class OrderSummary {
private Long id;
private LocalDateTime orderDate;
private BigDecimal totalAmount;
// 构造函数、getter、setter
}
复杂业务场景示例
当需要处理跨表关联、复杂聚合计算时，Native Query的优势明显：
@Query(value = "SELECT p.name, p.category, SUM(oi.quantity) as total_sold, "+
"COUNT(DISTINCT o.id) as order_count, AVG(oi.price) as avg_price "+
"FROM products p "+
"JOIN order_items oi ON p.id = oi.product_id "+
"JOIN orders o ON oi.order_id = o.id "+
"WHERE o.order_date BETWEEN :startDate AND :endDate "+
"GROUP BY p.id, p.name, p.category "+
"HAVING SUM(oi.quantity) > :minQuantity "+
"ORDER BY total_sold DESC", nativeQuery = true)
List<ProductSalesReport> findProductSalesReport(
@Param("startDate") LocalDateTime startDate,
@Param("endDate") LocalDateTime endDate,
@Param("minQuantity") Integer minQuantity);
执行步骤
- 在Repository接口中定义@Query注解，设置nativeQuery=true
- 使用@Param注解传递参数
- 通过EntityManager或直接调用Repository方法执行查询
- 结果自动映射到自定义DTO类中
这种方案特别适用于需要复杂聚合、多表关联、性能要求高的场景，是JPA生态中处理复杂业务逻辑的有效手段。
讨论