JPA关联查询案例

本文目录导读：

1. 实体类设计
2. Repository层实现
3. Service层实现
4. DTO转换
5. Controller层实现
6. 性能优化建议
7. 常见查询场景示例
8. 注意事项

我将为您详细介绍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);

注意事项

1. 懒加载与序列化：使用LAZY时注意避免序列化问题
2. 性能优化：避免N+1查询，合理使用JOIN FETCH
3. 数据一致性：注意双向关联时的一致性维护
4. 分页优化：关联查询分页时使用countQuery
5. 缓存应用：合理使用一级缓存和二级缓存

这个完整案例覆盖了JPA关联查询的主要场景,包括一对多、多对多关系，以及各种查询方式如JPQL、方法名推导、Entity Graph等。