CriteriaBuilder按操作结果排序(CriteriaBuilder order by result of operations)

CriteriaBuilder按操作结果排序(CriteriaBuilder order by result of operations) java

假设以下实体：

Entity { Integer id; Boolean imported; Integer status; }

其任务是：当选择具有imported = true和status != 1实体时，应该到达搜索结果的底部。 还有必要使用CriteriaBuilder 。

我决定计算将显示它们被下拉表达式的值： imported = true AND status != 1

如果编写直接的SQL，它的工作原理如下：

SELECT .. FROM .. ORDER BY (imported = true AND status != 1) ASC

但是我无法在CriteriaBuilder上下文中对其进行转码

我试图这样做：

Predicate eqTruePredicate = cb.equal(root.get(Entity_.imported), true); Predicate neqStatusPredicate = cb.notEqual(root.get(Entity_.status)), 1); Predicate andExp = cb.and(eqTruePredicate, neqStatusPredicate); order.add(0, new OrderImpl(cb.isTrue(andExp)));

生成的查询如下所示：

order by ( generatedAlias0.imported=:param2 ) and ( generatedAlias0.status<>:param3 ) asc

但是当执行异常时会抛出：“令人意想不到的AST节点”指向令牌and

有没有一种方法可以使用CriteriaBuilder通过计算值执行排序以及需要执行什么操作？

补充： Hibernate ORM版本：4.3.5

Suppose the following entity:

Entity { Integer id; Boolean imported; Integer status; }

The task is: When selecting entities those with imported = true AND status != 1 should go to the bottom of search result. It is also necessary to use CriteriaBuilder.

I decided to compute the value which will indicate them to be pulled down by the following expression: imported = true AND status != 1

If writing direct SQL it works as intended having a look:

SELECT .. FROM .. ORDER BY (imported = true AND status != 1) ASC

However I cannot transcode it in context of CriteriaBuilder

I tried to do it like this:

Predicate eqTruePredicate = cb.equal(root.get(Entity_.imported), true); Predicate neqStatusPredicate = cb.notEqual(root.get(Entity_.status)), 1); Predicate andExp = cb.and(eqTruePredicate, neqStatusPredicate); order.add(0, new OrderImpl(cb.isTrue(andExp)));

The generated query is the following:

order by ( generatedAlias0.imported=:param2 ) and ( generatedAlias0.status<>:param3 ) asc

But when execute the exception is thrown saying: "unexpected AST node" pointing at token and

Is there a way to perform ordering by computed value using CriteriaBuilder and what would be necessary to be done?

Addition: Hibernate ORM version: 4.3.5

最满意答案

我设法使用CASE语句来做到这一点：

Expression<Integer> caseExp = cb.<Integer>selectCase() .when( cb.and( cb.equal(root.get(Entity_.imported), true), cb.notEqual(root.get(Entity_.status), 1) ), 1 ) .otherwise(0); order.add(0, cb.asc(caseExp));

这导致像这样的ORDER BY语句：

order by case when ( entity.imported=true ) and ( entity.status<>1 ) then 1 else 0 end asc

I managed to do it using CASE statement:

Expression<Integer> caseExp = cb.<Integer>selectCase() .when( cb.and( cb.equal(root.get(Entity_.imported), true), cb.notEqual(root.get(Entity_.status), 1) ), 1 ) .otherwise(0); order.add(0, cb.asc(caseExp));

This results into an ORDER BY statement like this:

order by case when ( entity.imported=true ) and ( entity.status<>1 ) then 1 else 0 end ascCriteriaBuilder按操作结果排序(CriteriaBuilder order by result of operations) java

假设以下实体：

Entity { Integer id; Boolean imported; Integer status; }

其任务是：当选择具有imported = true和status != 1实体时，应该到达搜索结果的底部。 还有必要使用CriteriaBuilder 。

我决定计算将显示它们被下拉表达式的值： imported = true AND status != 1

如果编写直接的SQL，它的工作原理如下：

SELECT .. FROM .. ORDER BY (imported = true AND status != 1) ASC

但是我无法在CriteriaBuilder上下文中对其进行转码

我试图这样做：

Predicate eqTruePredicate = cb.equal(root.get(Entity_.imported), true); Predicate neqStatusPredicate = cb.notEqual(root.get(Entity_.status)), 1); Predicate andExp = cb.and(eqTruePredicate, neqStatusPredicate); order.add(0, new OrderImpl(cb.isTrue(andExp)));

生成的查询如下所示：

order by ( generatedAlias0.imported=:param2 ) and ( generatedAlias0.status<>:param3 ) asc

但是当执行异常时会抛出：“令人意想不到的AST节点”指向令牌and

有没有一种方法可以使用CriteriaBuilder通过计算值执行排序以及需要执行什么操作？

补充： Hibernate ORM版本：4.3.5

Suppose the following entity:

Entity { Integer id; Boolean imported; Integer status; }

The task is: When selecting entities those with imported = true AND status != 1 should go to the bottom of search result. It is also necessary to use CriteriaBuilder.

I decided to compute the value which will indicate them to be pulled down by the following expression: imported = true AND status != 1

If writing direct SQL it works as intended having a look:

SELECT .. FROM .. ORDER BY (imported = true AND status != 1) ASC

However I cannot transcode it in context of CriteriaBuilder

I tried to do it like this:

Predicate eqTruePredicate = cb.equal(root.get(Entity_.imported), true); Predicate neqStatusPredicate = cb.notEqual(root.get(Entity_.status)), 1); Predicate andExp = cb.and(eqTruePredicate, neqStatusPredicate); order.add(0, new OrderImpl(cb.isTrue(andExp)));

The generated query is the following:

order by ( generatedAlias0.imported=:param2 ) and ( generatedAlias0.status<>:param3 ) asc

But when execute the exception is thrown saying: "unexpected AST node" pointing at token and

Is there a way to perform ordering by computed value using CriteriaBuilder and what would be necessary to be done?

Addition: Hibernate ORM version: 4.3.5

最满意答案

我设法使用CASE语句来做到这一点：

Expression<Integer> caseExp = cb.<Integer>selectCase() .when( cb.and( cb.equal(root.get(Entity_.imported), true), cb.notEqual(root.get(Entity_.status), 1) ), 1 ) .otherwise(0); order.add(0, cb.asc(caseExp));

这导致像这样的ORDER BY语句：

order by case when ( entity.imported=true ) and ( entity.status<>1 ) then 1 else 0 end asc

I managed to do it using CASE statement:

Expression<Integer> caseExp = cb.<Integer>selectCase() .when( cb.and( cb.equal(root.get(Entity_.imported), true), cb.notEqual(root.get(Entity_.status), 1) ), 1 ) .otherwise(0); order.add(0, cb.asc(caseExp));

This results into an ORDER BY statement like this:

order by case when ( entity.imported=true ) and ( entity.status<>1 ) then 1 else 0 end asc