我正试图在后端创建一个通用的排序和过滤抽象,但web CRUD上的一些值可能非常复杂,因此在某些情况下,我们会使用子查询。我试图生成的查询是这样的格式:
SELECT f, (SELECT COUNT(r.id) FROM Bar b WHERE b.foo = e) c
FROM Foo f
ORDER BY c
创建查询时,我无法重现。由于某种原因,WHERE子句中重复了子查询。这是我得到的错误:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: query
[select generatedAlias0, (select count(generatedAlias1) from example.Bar as generatedAlias1
where generatedAlias0=generatedAlias1.foo) from example.Foo as generatedAlias0
order by (select count(generatedAlias1) from example.Bar as generatedAlias1
where generatedAlias0=generatedAlias1.foo) asc]
摘录自我的查询代码:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<T> root = query.from(Foo.class);
Subquery<Long> subquery = query.subquery(Long.class);
Root<?> subqueryRoot = subquery.from(Bar.class);
Expression<Long> count = cb.count(subqueryRoot);
subquery.select(count);
subquery.where(cb.equal(root, subqueryRoot.get("foo")));
query.multiselect(root, subquery.getSelection());
query.orderBy(cb.asc(subquery));
即使使用CriteriaAPI,也可以利用其他替代方案进行排序。我还没有设法根据子查询的结果进行排序,但您可以执行以下操作之一:
JAVA 8
return entityManager.createQuery(cq).getResultList().stream()
.sorted(Comparator.comparing(YourObject::getCount))
.collect(Collectors.toList());
所有版本
List<YourObjet> list = entityManager.createQuery(cq).getResultList();
Collections.sort(list, new Comparator<YourObjet>(){
@Override
public int compare(YourObjet o1, YourObjet o2) {
return o1.getCount().compareToIgnoreCase(o2.getCount());
}
});
当然还有更多的选择,但Java 8确实是最佳的