Why does Hibernate throw a QuerySyntaxException for this HQL?

Question

While building a query using Hibernate, I noticed something rather odd. If I use sequential named parameters for the ORDER BY clause, Hibernate throws a QuerySyntaxException (the colon prefix being an unexpected token):

createQuery("FROM MyEntity ORDER BY :orderProperty :orderDirection");

However, when this is done with a plain SQL query the query is created without a problem:

createSQLQuery("SELECT * FROM my_entity_table ORDER BY :orderProperty :orderDirection");

I know Hibernate is doing more String evaluation for the HQL query, which is probably why the SQL query is created without an error. I am just wondering why Hibernate would care that there are two sequential named parameters.

This isn't a huge issue since it is simple to work around (can just append the asc or desc String value to the HQL instead of using a named paramater for it), but it struck my curiosity why Hibernate is preventing it (perhaps simply because 99% of the time sequential named parameters like this result in invalid SQL/HQL).

http://stackoverflow.com/questions/17510034/hibernate-column-name-parameter-binding — Mason T., Nov 10 '15 at 17:20

Mason T. · Accepted Answer · 2015-11-10T17:30:15.143

3

I've been testing this in my local, and I can't get your desired outcome to work with HQL.

Here is quote from the post I linked:

You can't bind a column name as a parameter. Only a column value. This name has to be known when the execution plan is computed, before binding parameter values and executing the query. If you really want to have such a dynamic query, use the Criteria API, or some other way of dynamically creating a query.

Criteria API looks to be the more useful tool for your purposes.

Here is an example:

    Criteria criteria = session.createCriteria(MyEntity.class);

    if (orderDirection.equals("desc")) {
        criteria.addOrder(Order.desc(orderProperty));
    }
    else {
        criteria.addOrder(Order.asc(orderProperty));
    }

edited Nov 10 '15 at 17:30

answered Nov 10 '15 at 17:05

Mason T.

1,567
1
14
33

I don't think that is going to work since `:orderDirection` is not a column, it is the ascending/descending string value to modify a column's sort direction. Your suggestion would result in `ORDER BY columnName, asc` which would cause an error since ASC (and likewise DESC) is not a column name. – Default Nov 10 '15 at 17:21
Ah I thought orderDirection was a column, let me revise my answer then. – Mason T. Nov 10 '15 at 17:23
Yeah, Criteria will probably work best. It's an easy switch to make I was just a bit thrown off by what it didn't like that. But I think you're right about it being an issue with using a column name as a named parameter. Even without the `:orderDirection` param it fails (Query builds, but the actual SQL generated is incorrect). Thanks for the input! – Default Nov 10 '15 at 17:32

score 0 · Answer 2 · edited May 23 '17 at 12:23

According to the answer accepted in this question, you can only define parameters in WHERE and HAVING clauses.

The same answer also gives you some ways to have a workaround for your problem, however I will add one more way to do this:

Use the CASE - WHEN clause in your ORDER BY, this would work by the following way:

SELECT u FROM User u
ORDER BY 
CASE WHEN '**someinputhere**' = :orderProperty 
AND '**someotherinput**' = :orderDirection 
THEN yourColumn asc 
ELSE yourColumn desc END

Please, note that in this approach would required you to write all the possible inputs for ordering. Not really beautiful but really useful, especially because you would not need to write multiple queries with different orderings, plus with this approach you can use NamedQueries, which would be possible by writing the query dinamically using string concats.

Hope this can solve your problem, good luck!

Why does Hibernate throw a QuerySyntaxException for this HQL?

2 Answers2