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).

stackoverflow.com/questions/17510034/…

Mason T.
– Mason T.

2015-11-10 17:20:57 +00:00
Commented Nov 10, 2015 at 17:20 — Mason T.
– Mason T., Commented Nov 10, 2015 at 17:20

Mason T. · Accepted Answer · 2015-11-10 17:30:15Z

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, 2015 at 17:30

answered Nov 10, 2015 at 17:05

Mason T.

1,5771 gold badge15 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Default Over a year ago

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.

Mason T. Over a year ago

Ah I thought orderDirection was a column, let me revise my answer then.

Default Over a year ago

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!

Community · Accepted Answer · 2017-05-23 12:23:40Z

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!

Collectives™ on Stack Overflow

Why does Hibernate throw a QuerySyntaxException for this HQL?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related