在我的spring数据存储库中有以下命名查询:
@Query("FROM Pedido p JOIN FETCH p.status ps WHERE ps.status IN (?1) AND ps.id IN (SELECT MAX(ps2.id) FROM PedidoStatus ps2 GROUP BY ps2.pedido)")
我正试图使用标准API和spring数据规范实现相同的结果,这就是我到目前为止所拥有的:
public static Specification<Pedido> byUltimoStatus(final List<PedidoStatus.StatusPedido> ultimoStatus) {
return new Specification<Pedido>() {
public Predicate toPredicate(Root<Pedido> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
Expression<PedidoStatus.StatusPedido> status = root.join("status").get("status");
Predicate predicateByStatus = status.in(ultimoStatus);
final Subquery<Long> subQuery = query.subquery(Long.class);
final Root<PedidoStatus> ps = subQuery.from(PedidoStatus.class);
Expression<Long> psId= ps.get("id");
Expression<Long> maxId = builder.max(psId);
subQuery.select(maxId);
subQuery.groupBy(ps.get("pedido").get("id"));
Predicate predicateByUltimoStatus = builder.in(root.join("status").get("id")).value(subQuery);
return builder.and(predicateByStatus, predicateByUltimoStatus);
}
};}它仍然不工作,看起来结果查询中有一个额外的INNERJOIN PedidoStatus。
这是@查询的结果:
select ... from Pedido pedido0_ inner join PedidoStatus status1_ on pedido0_.id=status1_.pedido where (status1_.status in (? , ?)) and (status1_.id in (select max(pedidostat2_.id) from PedidoStatus pedidostat2_ group by pedidostat2_.pedido)) 这是标准API的结果:
select ... from Pedido pedido0_ inner join PedidoStatus status1_ on pedido0_.id=status1_.pedido inner join PedidoStatus status2_ on pedido0_.id=status2_.pedido where (pedido0_.id is not null) and status1_.status IN (?, ?) and (status2_.id in (select max(pedidostat3_.id) from PedidoStatus pedidostat3_ group by pedidostat3_.pedido)) 发布于 2017-11-01 11:06:13
知道这是一个很老的问题,在我看来,CriteriaQuery生成的查询中重复的INNERJOIN的原因是,构建查询的代码实际上调用了root.join("status")两次。第一次调用的结果应该保存到局部变量中,这样就可以重用它,而不是两次连接。
首先要做的是:
Expression<PedidoStatus.StatusPedido> status = root.join("status").get("status");
后来你做了:
Predicate predicateByUltimoStatus = builder.in(root.join("status").get("id")).value(subQuery);
https://stackoverflow.com/questions/22892114
复制相似问题