将SQL转为JPA Criteria Query时，子查询返回多值的问题

原需求与SQL语句 #

需要获取每个请求的最新状态信息，原SQL如下：

select r.id, s.CODE, rs.CREATE_DATE
from request r
    join request_status rs on r.id = rs.request_id
    join status s on rs.STATUS_ID = s.ID
    join (SELECT rss.REQUEST_ID, max(rss.CREATE_DATE) lastDate
            FROM REQUEST_STATUS rss GROUP BY rss.REQUEST_ID) lastStatus ON r.ID = lastStatus.REQUEST_ID AND rs.CREATE_DATE = lastStatus.lastDate;

实体类定义 #

@Entity
public class Request {
    @Id
    private UUID id;
    @OneToMany(mappedBy = "request", fetch = LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    private List<RequestStatus> requestStatuses = new ArrayList<>();
}

@Entity
public class RequestStatus {
    @Id
    private UUID id;
    @ManyToOne(fetch = LAZY, optional = false)
    private Request request;
    @ManyToOne(fetch = LAZY, optional = false)
    private Status status;
    private OffsetDateTime createDate;
}

@Data
@AllArgsConstructor
public class LastRow {
    private UUID id;
    private OffsetDateTime date;
}

遇到的问题 #

尝试编写的Criteria Query代码中，子查询使用builder.construct(LastRow.class)返回CompoundSelection类型，但subQuery.select()要求传入Expression类型，类型不匹配，无法运行。

解决方法 #

核心问题修正 #

1. 子查询不需要返回自定义LastRow类，改用Tuple承载分组后的requestId和最新日期，可直接用于关联条件。
2. 子查询分组条件错误，应按request.id分组，而非RequestStatus.id。
3. 主查询直接从RequestStatus出发，关联Request和Status，再通过子查询过滤出每个请求的最新状态记录。

修正后的代码 #

public List<RequestStatusDto> getLatestRequestStatuses() {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    // 定义返回的DTO类型
    CriteriaQuery<RequestStatusDto> query = builder.createQuery(RequestStatusDto.class);

    // 主查询根：RequestStatus
    Root<RequestStatus> rsRoot = query.from(RequestStatus.class);
    Join<RequestStatus, Request> requestJoin = rsRoot.join(RequestStatus_.request);
    Join<RequestStatus, Status> statusJoin = rsRoot.join(RequestStatus_.status);

    // 子查询：获取每个请求的最新状态创建日期
    Subquery<Tuple> subQuery = query.subquery(Tuple.class);
    Root<RequestStatus> subRoot = subQuery.from(RequestStatus.class);
    subQuery.select(builder.tuple(
            subRoot.get(RequestStatus_.request).get(Request_.id).alias("requestId"),
            builder.max(subRoot.get(RequestStatus_.createDate)).alias("lastDate")
    ));
    // 按请求ID分组，取每个请求的最大日期
    subQuery.groupBy(subRoot.get(RequestStatus_.request).get(Request_.id));

    // 添加关联条件：匹配请求ID，且当前状态的创建日期等于子查询的最新日期
    query.where(
            builder.equal(requestJoin.get(Request_.id), subQuery.get("requestId")),
            builder.equal(rsRoot.get(RequestStatus_.createDate), subQuery.get("lastDate"))
    );

    // 构造返回的DTO对象
    query.select(builder.construct(RequestStatusDto.class,
            requestJoin.get(Request_.id),
            statusJoin.get(Status_.code),
            rsRoot.get(RequestStatus_.createDate)
    ));

    return entityManager.createQuery(query).getResultList();
}

// 对应的结果DTO类
public class RequestStatusDto {
    private UUID requestId;
    private String statusCode;
    private OffsetDateTime createDate;

    public RequestStatusDto(UUID requestId, String statusCode, OffsetDateTime createDate) {
        this.requestId = requestId;
        this.statusCode = statusCode;
        this.createDate = createDate;
    }

    // 可根据需求添加getter方法
}

代码说明 #

• 子查询通过builder.tuple()返回多个字段，并用alias()命名方便后续引用。
• 主查询通过where条件关联子查询结果，确保只获取每个请求的最新状态记录。
• 使用DTORequestStatusDto接收最终结果，与原SQL的返回字段一一对应。

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