如何在JPQL中将列表参数用作表实现多日期聚合查询

现有数据表定义：

create table gains (
  user_id varchar,
  recorded_at datetime,
  amount integer
)

需求：传入指定用户ID及Instant类型的日期列表，通过JPQL执行聚合查询，返回每个日期对应的累计金额。参数示例：

String userId = "1";
List<Instant> aggregateAts = Arrays.asList(
    Instant.parse("2023-12-01T00:00Z"),
    Instant.parse("2023-12-02T00:00Z"),
    Instant.parse("2023-12-03T00:00Z")
);
List<GainsHistory> result = gainsHistoryRepository.generate(userId, aggregateAts);

// 仓库接口定义
public interface GainsHistoryRepository extends Repository<GainsHistory, GainsHistoryPK> {
  @Query("...")
  List<GainsHistory> generate(String userId, List<Instant> aggregateAts);
}

期望返回结果：

已知单个日期的JPQL聚合已实现，原生查询方案可行，需确认JPQL纯实现方式，且JPQL不支持CTE。

问题解答 #

1. 是否仅通过JPQL（不使用原生查询）可实现该需求？ #

可以实现。虽然JPQL不支持CTE，但可以通过动态生成UNION ALL子查询模拟日期列表行，再关联原表完成累计聚合计算。

2. 具体实现方式 #

步骤1：定义结果DTO

创建GainsHistory类，提供与JPQL查询结果匹配的构造函数：

public class GainsHistory {
    private String userId;
    private Instant aggregateAt;
    private BigDecimal amount;

    // 参数顺序和类型需与JPQL构造调用完全匹配
    public GainsHistory(String userId, Instant aggregateAt, Integer totalAmount) {
        this.userId = userId;
        this.aggregateAt = aggregateAt;
        this.amount = BigDecimal.valueOf(totalAmount != null ? totalAmount : 0).setScale(2);
    }

    // 省略getter/setter方法
}

步骤2：动态构建JPQL并执行

由于日期列表是动态的，无法用静态@Query注解直接实现，需通过EntityManager动态拼接查询语句：

@Repository
public class GainsHistoryRepositoryImpl implements GainsHistoryRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<GainsHistory> generate(String userId, List<Instant> aggregateAts) {
        if (aggregateAts.isEmpty()) {
            return Collections.emptyList();
        }

        // 构建日期子查询：用UNION ALL将每个日期转为单独一行
        StringBuilder dateSubQueryBuilder = new StringBuilder();
        for (int i = 0; i < aggregateAts.size(); i++) {
            if (i > 0) {
                dateSubQueryBuilder.append(" UNION ALL ");
            }
            dateSubQueryBuilder.append(String.format("SELECT :date%d AS aggregateAt", i));
        }

        // 完整JPQL语句：关联日期子查询与gains表，计算累计金额
        String jpql = String.format("""
            SELECT new com.yourpackage.GainsHistory(:userId, d.aggregateAt, SUM(g.amount))
            FROM (%s) d
            LEFT JOIN Gains g ON g.user_id = :userId AND g.recorded_at <= d.aggregateAt
            GROUP BY d.aggregateAt, :userId
            ORDER BY d.aggregateAt
            """, dateSubQueryBuilder.toString());

        TypedQuery<GainsHistory> query = entityManager.createQuery(jpql, GainsHistory.class);
        // 设置用户ID参数
        query.setParameter("userId", userId);
        // 设置每个日期参数
        for (int i = 0; i < aggregateAts.size(); i++) {
            query.setParameter(String.format("date%d", i), aggregateAts.get(i));
        }

        return query.getResultList();
    }
}

关键说明

• 用UNION ALL拼接多个SELECT语句，将传入的日期列表转为虚拟日期表，绕开JPQL不支持CTE的限制；
• 使用LEFT JOIN保证无记录的日期也能返回结果，通过DTO构造逻辑将SUM返回的null转为0.00；
• 采用参数绑定而非字符串拼接参数值，避免SQL注入风险；
• 增加ORDER BY保证结果顺序与传入的日期列表一致。

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