Custom order by string parameter in HQL

Question

I had an HQL query like this:

Query query = session.createQuery("from User as user where user.joined!=null order by user.joined desc");

How do I set a variable User property as the sort order for my query? My solution:

String order = "user.joined";
Query query = session.createQuery("from User as user where user.joined!=null order by :order desc").setString("order", order);

does not give an ordered query result.

Answer 1

Use a criteria query.

List<User> users = session.createCriteria(User.class)
    .add(Restrictions.isNotNull("joined"))
    .addOrder(Order.desc(order))
    .list();

---

Or, using HQL:

Query query = session.createQuery("from User as user where user.joined!=null order by user." + order + " desc");

Answer 2

In your second query, when you use this method call:

[Hibernate Query object].setString("order", order)

You are trying to bind a column name as a parameter, which is not possible.

What is a parameter (for this concern) follows the SQL parameters definition, wich is also the parameter definition used in JDBC API PreparedStatement.

To dynamically build a query (besides it's parameters) there are other solutions like the ones pointed by Matt Ball. You could also see Using a prepared statement and variable bind Order By in Java with JDBC driver.