Reactive Quarkus(Mutiny)中用Panache Repository实现Timescale DB原生查询

1. 定义结果映射POJO #

因为原生查询返回的是自定义聚合结果，需要一个简单类承接返回值，构造方法的参数顺序、类型要和查询返回的字段完全对应：

public class SensorAggregate {
    public String deviceId;
    public Double firstValue;
    public Double lastValue;

    // 参数顺序必须和SELECT语句的字段顺序一致
    public SensorAggregate(String deviceId, Double firstValue, Double lastValue) {
        this.deviceId = deviceId;
        this.firstValue = firstValue;
        this.lastValue = lastValue;
    }
}

2. 在Panache Repository中实现响应式原生查询 #

直接用Hibernate Reactive的Mutiny API执行原生SQL，返回Uni类型结果：

import io.quarkus.hibernate.reactive.panache.PanacheRepositoryBase;
import io.smallrye.mutiny.Uni;
import jakarta.enterprise.context.ApplicationScoped;
import java.util.List;

@ApplicationScoped
public class SensorDataRepository implements PanacheRepositoryBase<SensorData, Long> {

    // 查询所有设备的first/last值
    public Uni<List<SensorAggregate>> getDeviceFirstLastValues() {
        String nativeQuery = """
            SELECT device_id,
                   first(value, time) AS first_value,
                   last(value, time) AS last_value
            FROM sensor_data
            GROUP BY device_id
            """;

        return getSession()
                .flatMap(session -> session.createNativeQuery(nativeQuery, SensorAggregate.class)
                        .getResultList());
    }

    // 带时间范围参数的查询
    public Uni<List<SensorAggregate>> getDeviceFirstLastValuesInRange(String startTime, String endTime) {
        String nativeQuery = """
            SELECT device_id,
                   first(value, time) AS first_value,
                   last(value, time) AS last_value
            FROM sensor_data
            WHERE time BETWEEN ?1 AND ?2
            GROUP BY device_id
            """;

        return getSession()
                .flatMap(session -> session.createNativeQuery(nativeQuery, SensorAggregate.class)
                        .setParameter(1, startTime)
                        .setParameter(2, endTime)
                        .getResultList());
    }

    // 查询单个设备的结果（返回单个Uni对象）
    public Uni<SensorAggregate> getSingleDeviceFirstLast(String deviceId) {
        String nativeQuery = """
            SELECT device_id,
                   first(value, time) AS first_value,
                   last(value, time) AS last_value
            FROM sensor_data
            WHERE device_id = ?1
            GROUP BY device_id
            """;

        return getSession()
                .flatMap(session -> session.createNativeQuery(nativeQuery, SensorAggregate.class)
                        .setParameter(1, deviceId)
                        .getSingleResultOrNull()); // 无结果时返回null，避免抛出异常
    }
}

3. 关键注意事项 #

• 确保项目已引入所需依赖，pom.xml需包含：

<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-jdbc-postgresql</artifactId>
</dependency>
<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-hibernate-reactive</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

• 所有数据库操作必须通过Mutiny响应式API执行，禁止调用阻塞方法，避免破坏响应式特性。
• 若需复杂结果映射，可使用@SqlResultSetMapping注解，但构造方法映射是最简洁的实现方式。

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