Dynamically setting sort direction

[Eugene Williams]

I'm working on an application where I'd like to dynamically determine whether the sorting returned during an HQL query is in descending or ascending order.
When trying to do this using a named query, I get errors during deployment. Here's an example of the code from the domain object:

@NamedQueries({
    @NamedQuery(name="listcontacts_bytype",
                    query="select c, (select count(*) from TrmContacts where isdeleted = 0) as totalc from TrmContacts c " +
                            "WHERE c.isdeleted=0 AND c.contacttype_x=:ctype order by :order :sort")
})

In the DAO (using Spring), I call the query using a statement (eg.):

String sort = "ASC"
entityManager.createNamedQuery(query).setParameter("ctype", "6").setParameter("order",order).setParameter("sort", sort);

This doesn't work. I've even tried adding a case statement in the domain object:

@NamedQueries({
   @NamedQuery(name="listcontacts_bytype",
                    query="select c, (select count(*) from TrmContacts where isdeleted = 0) as totalc from TrmContacts c " +
                            "WHERE c.isdeleted=0 AND c.contacttype_x=:ctype order by :order case when :sort='ASC' then ASC else DESC end")
})

Is there a way to dynamically set whether the results are in ascending or descending order or will I have to create two separate named queries for this (i.e. listcontacts_bytype_desc & listcontacts_bytype_asc)? (Or do something much simpler that I'm not thinking of).

Thanks.

[Eugene Williams]

It appears that this may not be possible (based upon responses I've seen in other forums)
http://stackoverflow.com/questions/3616224/jpql-order-by-clause-with-parameter

I have come across something else, however, after further testing, it appears that I'm also not getting the "order" parameter set during my query either. Is it not possible to set the "order" parameter dynamically using

setParameter("order","c.fieldname")

?

Thanks.

[James Sutherland]

Correct, parameters are for executing the same query with different values.

Order by and asc/desc are not parameters they are part of the query syntax, you cannot use parameters for this.

You will need to create your query dynamically. Either by building your own JPQL based on your arguments, or using the JPA 2.0 Criteria API which is for building dynamic queries.


If you want static queries, then you would need multiple different named queries for each type of query.

[Eugene Williams]

Thanks for the guidance.