本文目录导读：
我将为您详细介绍JPA（Java Persistence API）关联查询的完整案例，包括一对多、多对一、多对多等关系。
实体类设计
1 用户实体（一方）
@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, unique = true)
private String username;
private String email;
// 一对多：一个用户有多个订单
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<Order> orders = new ArrayList<>();
// 多对多：用户和角色
@ManyToMany
@JoinTable(
name = "user_roles",
joinColumns = @JoinColumn(name = "user_id"),
inverseJoinColumns = @JoinColumn(name = "role_id")
)
private Set<Role> roles = new HashSet<>();
}
// 角色实体
@Entity
@Table(name = "roles")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Role {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true)
private String name;
@ManyToMany(mappedBy = "roles")
private Set<User> users = new HashSet<>();
}
2 订单实体（多方）
@Entity
@Table(name = "orders")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String orderNo;
private LocalDateTime createTime;
private BigDecimal totalAmount;
// 多对一：多个订单属于一个用户
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_id")
private User user;
// 一对多：一个订单有多个商品
@OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<OrderItem> items = new ArrayList<>();
}
// 订单明细实体
@Entity
@Table(name = "order_items")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class OrderItem {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String productName;
private Integer quantity;
private BigDecimal price;
// 多对一：多个订单明细属于一个订单
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "order_id")
private Order order;
}
Repository层实现
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// 1. 方法名推导 - 关联查询
List<User> findByOrdersOrderNo(String orderNo);
List<User> findByRolesName(String roleName);
// 2. @Query JPQL查询
@Query("SELECT u FROM User u JOIN u.orders o WHERE o.orderNo = :orderNo")
List<User> findUsersByOrderNo(@Param("orderNo") String orderNo);
// 3. 多条件关联查询
@Query("SELECT u FROM User u JOIN u.roles r WHERE u.username = :username AND r.name = :roleName")
User findUserByNameAndRole(@Param("username") String username, @Param("roleName") String roleName);
// 4. 关联查询并排序
@Query("SELECT u FROM User u LEFT JOIN u.orders o GROUP BY u ORDER BY COUNT(o) DESC")
List<User> findUsersOrderByOrderCountDesc();
// 5. 分页关联查询
@Query(value = "SELECT u FROM User u JOIN u.orders o WHERE o.totalAmount > :amount",
countQuery = "SELECT COUNT(u) FROM User u JOIN u.orders o WHERE o.totalAmount > :amount")
Page<User> findUsersWithOrderAmount(@Param("amount") BigDecimal amount, Pageable pageable);
// 6. 投影查询（只查询部分字段）
@Query("SELECT u.id as id, u.username as username, COUNT(o) as orderCount FROM User u LEFT JOIN u.orders o GROUP BY u.id")
List<UserOrderCount> findUserOrderCounts();
}
// 投影接口
public interface UserOrderCount {
Long getId();
String getUsername();
Long getOrderCount();
}
Service层实现
@Service
@Transactional
public class UserService {
@Autowired
private UserRepository userRepository;
@Autowired
private OrderRepository orderRepository;
// 关联查询用户及订单
public List<UserWithOrdersDTO> getUserWithOrders(Long userId) {
User user = userRepository.findById(userId)
.orElseThrow(() -> new RuntimeException("User not found"));
// 初始化懒加载集合
Hibernate.initialize(user.getOrders());
return convertToDTO(user);
}
// 多对多关联查询
public List<User> getUsersByRole(String roleName) {
return userRepository.findByRolesName(roleName);
}
// 关联查询用户及其订单、订单明细
@Query("SELECT DISTINCT u FROM User u " +
"LEFT JOIN FETCH u.orders o " +
"LEFT JOIN FETCH o.items WHERE u.id = :id")
public User getUserWithCompleteDetails(@Param("id") Long userId) {
return userRepository.findUserWithComleteDetails(userId);
}
// 批量查询优化（避免N+1问题）
public List<User> getUsersWithOrdersEfficiently() {
return userRepository.findAllWithOrders();
}
// 在UserRepository中添加
@Query("SELECT u FROM User u LEFT JOIN FETCH u.orders")
List<User> findAllWithOrders();
// 多级联查
@Query("SELECT u FROM User u " +
"LEFT JOIN FETCH u.orders o " +
"LEFT JOIN FETCH o.items WHERE u.username = :username")
User findUserWithOrderAndItems(@Param("username") String username);
}
DTO转换
@Data
@Builder
public class UserWithOrdersDTO {
private Long id;
private String username;
private String email;
private List<OrderDTO> orders;
public static UserWithOrdersDTO fromEntity(User user) {
List<OrderDTO> orderDTOs = user.getOrders().stream()
.map(OrderDTO::fromEntity)
.collect(Collectors.toList());
return UserWithOrdersDTO.builder()
.id(user.getId())
.username(user.getUsername())
.email(user.getEmail())
.orders(orderDTOs)
.build();
}
}
@Data
@Builder
public class OrderDTO {
private Long id;
private String orderNo;
private BigDecimal totalAmount;
private List<OrderItemDTO> items;
public static OrderDTO fromEntity(Order order) {
List<OrderItemDTO> itemDTOs = order.getItems().stream()
.map(OrderItemDTO::fromEntity)
.collect(Collectors.toList());
return OrderDTO.builder()
.id(order.getId())
.orderNo(order.getOrderNo())
.totalAmount(order.getTotalAmount())
.items(itemDTOs)
.build();
}
}
Controller层实现
@RestController
@RequestMapping("/api/users")
public class UserController {
@Autowired
private UserService userService;
// 查询用户及其订单
@GetMapping("/{id}/orders")
public ResponseEntity<UserWithOrdersDTO> getUserWithOrders(@PathVariable Long id) {
return ResponseEntity.ok(userService.getUserWithOrders(id));
}
// 按角色查询用户
@GetMapping("/role/{roleName}")
public ResponseEntity<List<User>> getUsersByRole(@PathVariable String roleName) {
return ResponseEntity.ok(userService.getUsersByRole(roleName));
}
// 分页关联查询
@GetMapping("/with-orders")
public ResponseEntity<Page<User>> getUsersWithOrders(
@RequestParam BigDecimal minAmount,
@RequestParam int page,
@RequestParam int size) {
Pageable pageable = PageRequest.of(page, size);
return ResponseEntity.ok(userService.getUsersWithOrderAmount(minAmount, pageable));
}
}
性能优化建议
1 使用Entity Graph优化
@Entity
@NamedEntityGraph(
name = "User.complete",
attributeNodes = {
@NamedAttributeNode("orders"),
@NamedAttributeNode("roles")
}
)
public class User {
// ... 实体字段
}
// 使用Entity Graph查询
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@EntityGraph(value = "User.complete", type = EntityGraph.EntityGraphType.FETCH)
@Query("SELECT u FROM User u WHERE u.id = :id")
Optional<User> findByIdWithCompleteData(@Param("id") Long id);
}
2 避免N+1查询
// 推荐使用JOIN FETCH
@Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.orders")
List<User> findAllWithOrdersEfficient();
// 或使用@EntityGraph
@Query("SELECT u FROM User u")
@EntityGraph(attributePaths = {"orders", "roles"})
List<User> findAllWithRelations();
3 批量抓取
@Query("SELECT u FROM User u")
@BatchSize(size = 10)
List<User> findAllBatch();
常见查询场景示例
// 1. 嵌套关联三张表
@Query("SELECT o FROM Order o " +
"JOIN o.user u " +
"JOIN o.items i " +
"WHERE u.username = :username AND i.productName LIKE %:productName%")
List<Order> findOrdersByUserAndProduct(@Param("username") String username,
@Param("productName") String productName);
// 2. 关联查询统计
@Query("SELECT r.name, COUNT(u.id) as userCount " +
"FROM Role r LEFT JOIN r.users u " +
"GROUP BY r.id")
List<Object[]> countUsersByRole();
// 3. 子查询关联
@Query("SELECT u FROM User u " +
"WHERE u.id IN (SELECT o.user.id FROM Order o WHERE o.totalAmount > :amount)")
List<User> findUsersWithLargeOrders(@Param("amount") BigDecimal amount);
// 4. 动态关联查询
@Query("SELECT u FROM User u " +
"WHERE (:username IS NULL OR u.username = :username) " +
"AND u.orders IS NOT EMPTY")
Page<User> findUsersDynamic(@Param("username") String username, Pageable pageable);
注意事项
- 懒加载与序列化：使用LAZY时注意避免序列化问题
- 性能优化：避免N+1查询，合理使用JOIN FETCH
- 数据一致性：注意双向关联时的一致性维护
- 分页优化：关联查询分页时使用countQuery
- 缓存应用：合理使用一级缓存和二级缓存
这个完整案例覆盖了JPA关联查询的主要场景,包括一对多、多对多关系，以及各种查询方式如JPQL、方法名推导、Entity Graph等。
