<> in "order by" clause causes createQuery to fail-CodePudding

I'm on Hibernate 5.6 and want to attach this SQL order string to criteriaBuilder:

order by rmgtnav_path, rmgtnav_collection_type<>'PROFILE', rmgtnav_denotation asc

Here's the code:

List<Order> orderStatements = new ArrayList();

orderStatements.add(cb.asc(root.get("rmgtnav_path")));
orderStatements.add(cb.asc(cb.notEqual(root.get("rmgtnav_collection_type"), "PROFILE")));
orderStatements.add(cb.asc(root.get("rmgtnav_denotation")));


criteriQuery.orderBy(orderStatements); 

Query query = session.createQuery(cq);

When I call the session.createQuery(cq) i get the following error:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: <> near line 1, column 596 [select generatedAlias0 from com.myapp.core.rmgt.rate.business.object.RmgtBVOImpl as generatedAlias0 where ( generatedAlias0.collectionType<>:param0 ) and ( ( generatedAlias0.collectionSubType<>:param1 ) or ( generatedAlias0.collectionSubType is null ) ) and ( ( lower(generatedAlias0.profileStatus) like :param2 ) and ( ( generatedAlias0.type=:param3 ) or ( generatedAlias0.type=:param4 ) ) ) and ( generatedAlias0.enabled=:param5 ) and ( ( generatedAlias0.owner=:param6 ) or ( generatedAlias0.owner=:param7 ) ) order by generatedAlias0.path asc, generatedAlias0.collectionType<>:param8 asc, generatedAlias0.denotation asc]

The generated order by clause looks no different than the native SQL statement but I get this error message. Any idea how to translate this <> comparison in Order By to an expression? Looks like Hibernate is just not able to process these kinds of Expressions in orderBy segments.

In case of the WHY: I can only speculate here. The clause <> 'PROFILE' resolves to true (1) or false (0). False for values equal to 'PROFILE'. And since 0 is smaller than 1, the listing starts with 'PROFILE' and is followed by the rest.

The statement itself is valid sql:

SELECT rmgtnav_path, rmgtnav_denotation, rmgtnav_collection_type
FROM public.rmgt_t_navigation
order by rmgtnav_path , rmgtnav_collection_type, rmgtnav_denotation asc

Whereas:

SELECT rmgtnav_path, rmgtnav_denotation, rmgtnav_collection_type
FROM public.rmgt_t_navigation
order by rmgtnav_path , rmgtnav_collection_type <> 'PROFILE', rmgtnav_denotation asc

And as a second question, is there any way of removing the asc tags from the generated code when I don't need them, like in the original sql?

CodePudding user response：

Use conventional SQL. Change:

rmgtnav_collection_type<>'PROFILE'

to:

case when rmgtnav_collection_type = 'PROFILE' then 0 else 1 end

Swap 0 and 1 (or add DESC) if you want the reverse order.

CodePudding user response：

You will have to use a case expression for this purpose:

orderStatements.add(
    cb.asc(
        cb.selectCase()
            .when(
                cb.notEqual(root.get("rmgtnav_collection_type"), "PROFILE"),
                cb.literal(0)
            )
            .otherwise(cb.literal(1))
    )
);