java - CriteriaBuilder 使用子查询和复合主键

Question

我希望能够使用条件 api 执行以下查询，因为它允许我动态调整我的外部选择并检索每个传感器的最新读数：

SELECT r.sensor_id, timestamp, r.value
FROM reading r
         INNER JOIN (
    SELECT sensor_id, MAX(ingestion) as max_ingestion
    FROM readings
    GROUP BY sensor_id
) last_by_sensor_id ON r.sensor_id = last_by_sensor_id.sensor_id AND r.ingestion = last_by_sensor_id.max_ingestion;

我有以下（简化的）实体：

@Entity
public class Reading {

    @EmbeddedId
    private ReadingPK pk;

    @Basic(optional = false)
    @Column(name = "ingestion", nullable = false)
    private Timestamp ingestion;

    @Column(name = "value")
    private Integer value;

    // ... quite a few more int values
}

组合PK如下：

@Entity
public class ReadingPK {

    @Basic(optional = false)
    @Column(name = "sensor_id", nullable = false)
    private int sensorId;

    @Basic(optional = false)
    @Column(name = "timestamp", nullable = false)
    private Timestamp timestamp;
}

我已经有的是使用Tuple接口动态检索我需要的列，所以这部分不需要回答。我挣扎的是这个设置：

public List<Tuple> getLatestReadingForEachSensor() {
    final CriteriaBuilder cb = em.getCriteriaBuilder();
    final CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    final Root<Reading> root = cq.from(Reading.class);
    Path<ReadingPK> rootPk = root.get(Reading_.pk);

    // Building selections (omitted) ...
    cq.multiselect(selections);  

    //Subquery
    final Subquery<ReadingPK> subquery = cq.subquery(ReadingPK.class);
    final Root<Reading> latestReadingSQ = subquery.from(Reading.class);
    final Path<ReadingPK> latestReadingPkSQ = latestReadingSQ.get(Reading_.pk);     
    subquery.select( /* how to do the selection as in the above sql query? */);
    subquery.groupBy(latestReadingPkSQ.get(ReadingPK_.sensorId));

    // How to make it an inner join?

    TypedQuery<Tuple> q = em.createQuery(cq);
    return q.getResultList();
}

标签： javasubquerycriteria-apicomposite-primary-keycriteriaquery