如何在Quarkus中使用Panache的Active Record模式执行自定义JPQL查询

Panache Active Record 原生支持自定义JPQL查询，完全适配你的开发需求，不需要编写原生SQL，全程使用Java实体字段名，和现有GraphQL接口的字段映射逻辑完全一致，无需额外做字段名适配。

核心实现步骤 #

1. 基础自定义JPQL查询 #

所有继承了PanacheEntity或PanacheEntityBase的实体类，都可以直接调用静态find方法传入自定义JPQL语句，支持通过JPQL标准语法FUNCTION调用TimescaleDB的数据库函数，示例如下：

// 示例时序数据实体类
@Entity
public class TimeSeriesData extends PanacheEntity {
    public String sensorId;
    public Double metricValue;
    public Instant collectTime;
    // 其他业务字段
}

调用示例：

// 调用TimescaleDB time_bucket函数做1小时粒度聚合查询
List<AggResult> resultList = TimeSeriesData.find(
    "SELECT FUNCTION('time_bucket', '1 hour', t.collectTime) as timeBucket, " +
    "avg(t.metricValue) as avgValue, t.sensorId " +
    "FROM TimeSeriesData t " +
    "WHERE t.sensorId = ?1 AND t.collectTime >= ?2 " +
    "GROUP BY timeBucket, t.sensorId " +
    "ORDER BY timeBucket DESC",
    sensorId, startTime
).project(AggResult.class)
.list();

2. 动态筛选条件适配 #

针对GraphQL接口的动态筛选需求，可以通过参数化拼接JPQL避免SQL注入风险，同时兼容动态条件拼接：

String jpqlBase = "SELECT FUNCTION('time_bucket', '1 hour', t.collectTime) as timeBucket, avg(t.metricValue) as avgValue, t.sensorId FROM TimeSeriesData t WHERE 1=1 ";
Map<String, Object> params = new HashMap<>();
if (sensorId != null) {
    jpqlBase += "AND t.sensorId = :sensorId ";
    params.put("sensorId", sensorId);
}
if (startTime != null) {
    jpqlBase += "AND t.collectTime >= :startTime ";
    params.put("startTime", startTime);
}
jpqlBase += "GROUP BY timeBucket, t.sensorId ORDER BY timeBucket DESC";
List<AggResult> resultList = TimeSeriesData.find(jpqlBase, params)
    .project(AggResult.class)
    .list();

3. 聚合结果映射 #

你只需要定义一个普通Java类作为投影结果类，字段名和JPQL查询返回的别名完全对应即可，Panache会自动完成结果映射：

// 聚合结果投影类
public class AggResult {
    public Instant timeBucket;
    public Double avgValue;
    public String sensorId;
    // 构造方法、getter/setter按需添加
}

4. 附加能力支持 #

你可以直接复用Panache原生的分页、单值聚合查询能力，无需手动修改JPQL语句：

// 分页查询，每页20条，取第1页
List<AggResult> pagedResult = TimeSeriesData.find(jpqlBase, params)
    .project(AggResult.class)
    .page(Page.of(0, 20))
    .list();
// 单值聚合查询
Double maxValue = TimeSeriesData.find("SELECT max(t.metricValue) FROM TimeSeriesData t WHERE t.sensorId = ?1", sensorId)
    .singleResult();

内容的提问来源于stack exchange，提问作者Marko Skočibušić