Spring Data JPA中如何用Projection等实现关联表聚合查询映射？

需求概述 #

需要通过Spring Data JPA的@Query和Projection，仅调用一次数据库，获取以下客户相关数据：

• 客户的name、surname
• 该客户所有账户的balance总和
• 该客户所有账户对应的transaction列表

相关实体关系：Customer拥有多个Account，Account拥有多个Transaction（已通过Hibernate正确配置关联）。实体代码如下：

Customer实体 #

public class Customer {
    private Long id;
    private String name;
    private String surname;
}

Account实体 #

public class Account {
    private Long id;
    private BigDecimal balance;
}

Transaction实体 #

public class Transaction {
    private Long id;
    private Long accountId;
    private LocalDateTime date;
}

当前问题 #

尝试将包含账户、交易数据的Customer实体映射到DTO失败后，使用了如下查询并映射到Projection，但查询结果会为每笔交易返回一条重复的客户记录，无法直接得到聚合后的结果：

@Query(value = "SELECT c.id AS id, c.name AS name, c.surname as surname, a.balance AS balance, " +
        "t.id AS transactionId, t.description AS description, t.date AS date " +
        "FROM Customer c " +
        "LEFT JOIN Account a ON a.customer.id = c.id " +
        "LEFT JOIN Transaction t ON a.id = t.account.id " +
        "ORDER BY c.id")
List<CustomerDetailsResponse> findAllWithDetails();

返回的原始结果示例：

|id |name       |surname    |balance    |t_id   |date                   |
|---|-----------|-----------|-----------|-------|------------------------
|1  |Thorsten   |Canto      |100        |1      |2023-02-19 20:47:18.212|
|1  |Thorsten   |Canto      |200        |2      |2023-02-19 20:47:21.425|
|1  |Thorsten   |Canto      |300        |3      |2023-02-19 20:47:23.296|
|2  |Tailor     |Bingham    |300        |4      |2023-02-19 20:47:25.350|
|2  |Tailor     |Bingham    |500        |5      |2023-02-19 20:47:38.589|
|2  |Tailor     |Bingham    |600        |6      |2023-02-19 20:47:40.916|
|3  |Benson     |Wilkowski  |600        |7      |2023-02-19 20:47:42.675|
|3  |Benson     |Wilkowski  |800        |8      |2023-02-19 20:47:44.635|
|3  |Benson     |Wilkowski  |900        |9      |2023-02-19 20:47:47.436|
|3  |Benson     |Wilkowski  |1000       |10     |2023-02-19 20:47:51.328|
|4  |Pryce      |Gorriessen |           |       |                       |
|5  |Melita     |Griffoen   |           |       |                       |

解决方案 #

方法一：嵌套Projection + 聚合查询 #

通过JPQL/HQL的聚合函数和嵌套Projection，直接从数据库获取聚合后的结果：

1. 定义Transaction投影接口：

public interface TransactionProjection {
    Long getTransactionId();
    LocalDateTime getDate();
}

2. 定义Customer聚合数据投影接口：

public interface CustomerDetailsProjection {
    Long getId();
    String getName();
    String getSurname();
    BigDecimal getTotalBalance();
    List<TransactionProjection> getTransactions();
}

3. 修改@Query语句，使用聚合函数计算总余额，并收集交易列表（依赖Hibernate的COLLECT函数）：

@Query(value = "SELECT c.id AS id, c.name AS name, c.surname AS surname, " +
        "SUM(a.balance) AS totalBalance, " +
        "COLLECT(t) AS transactions " +
        "FROM Customer c " +
        "LEFT JOIN Account a ON a.customer.id = c.id " +
        "LEFT JOIN Transaction t ON a.id = t.accountId " +
        "GROUP BY c.id, c.name, c.surname " +
        "ORDER BY c.id")
List<CustomerDetailsProjection> findAllWithDetails();

注：如果使用标准JPQL而非HQL，可替换COLLECT(t)为FUNCTION('COLLECT', t)，或根据数据库类型调整为对应聚合函数（如PostgreSQL的array_agg、MySQL的json_arrayagg）。

方法二：Java Stream处理原始查询结果 #

直接基于现有查询的原始结果，用Stream进行分组聚合：

1. 定义目标DTO类：

// 客户聚合DTO
public class CustomerAggregateDTO {
    private Long id;
    private String name;
    private String surname;
    private BigDecimal totalBalance;
    private List<TransactionDTO> transactions;
    // 省略getter、setter
}

// 交易DTO
public class TransactionDTO {
    private Long id;
    private LocalDateTime date;
    // 省略getter、setter
}

2. 用Stream分组并聚合数据：

List<CustomerDetailsResponse> rawResults = customerRepository.findAllWithDetails();

List<CustomerAggregateDTO> finalResult = rawResults.stream()
    .collect(Collectors.groupingBy(CustomerDetailsResponse::getId))
    .values()
    .stream()
    .map(group -> {
        CustomerAggregateDTO dto = new CustomerAggregateDTO();
        // 提取客户基础信息（取分组内第一条即可）
        CustomerDetailsResponse first = group.get(0);
        dto.setId(first.getId());
        dto.setName(first.getName());
        dto.setSurname(first.getSurname());
        
        // 计算总余额，过滤null值
        BigDecimal totalBalance = group.stream()
            .map(CustomerDetailsResponse::getBalance)
            .filter(Objects::nonNull)
            .reduce(BigDecimal.ZERO, BigDecimal::add);
        dto.setTotalBalance(totalBalance);
        
        // 收集交易列表，去重并过滤空交易
        List<TransactionDTO> transactions = group.stream()
            .filter(item -> item.getTransactionId() != null)
            .map(item -> {
                TransactionDTO t = new TransactionDTO();
                t.setId(item.getTransactionId());
                t.setDate(item.getDate());
                return t;
            })
            .distinct()
            .collect(Collectors.toList());
        dto.setTransactions(transactions);
        
        return dto;
    })
    .collect(Collectors.toList());

方法三：ModelMapper配合Stream聚合 #

用ModelMapper简化对象映射，结合Stream分组：

1. 先配置ModelMapper（Spring环境下可注册为Bean）：

@Bean
public ModelMapper modelMapper() {
    return new ModelMapper();
}

2. 映射并聚合数据：

@Autowired
private ModelMapper modelMapper;

// ...

List<CustomerDetailsResponse> rawResults = customerRepository.findAllWithDetails();

List<CustomerAggregateDTO> finalResult = rawResults.stream()
    .collect(Collectors.groupingBy(CustomerDetailsResponse::getId))
    .values()
    .stream()
    .map(group -> {
        // 映射客户基础信息
        CustomerAggregateDTO dto = modelMapper.map(group.get(0), CustomerAggregateDTO.class);
        
        // 计算总余额
        BigDecimal totalBalance = group.stream()
            .map(CustomerDetailsResponse::getBalance)
            .filter(Objects::nonNull)
            .reduce(BigDecimal.ZERO, BigDecimal::add);
        dto.setTotalBalance(totalBalance);
        
        // 映射交易列表
        List<TransactionDTO> transactions = group.stream()
            .filter(item -> item.getTransactionId() != null)
            .map(item -> modelMapper.map(item, TransactionDTO.class))
            .distinct()
            .collect(Collectors.toList());
        dto.setTransactions(transactions);
        
        return dto;
    })
    .collect(Collectors.toList());

内容的提问来源于stack exchange，提问作者user21099360