Thanks for the suggestion; that looks like a reasonable alternative.
Out of curiosity, is there a technical reason that the ORDER BY clause
cannot accept a parameter? I understand that the spec may not require
it, but it seems like it would be a very useful capability (feature
enhancement?) in glassfish.
Tim
________________________________
From: Michael.Bouschen_at_Sun.COM [mailto:Michael.Bouschen_at_Sun.COM]
Sent: Tuesday, January 09, 2007 9:17 AM
To: persistence_at_glassfish.dev.java.net
Subject: Re: Can you pass parameters to an Order By clause in
NamedQuery?
Hi Tim,
no, the JPA query language does not support input parameters in the
ORDER BY clause. The expression in the ORDER BY clause must be a state
field path expression.
I'm afraid, if you want to use named queries, you have to define
multiple named queries having a different ORDER BY clause. An
alternative may be using dynamic queries, e.g.
String queryText = "SELECT t FROM Task t ORDER BY " + sortField + " "
+ sortOrder;
Query q = em.createQuery(queryText);
List<Task> result = q.getResultList();
The above code assumes that sortField holds the name of the field you
want to use for ordering. Variable sortOrder is either "ASC" or "DESC"
or the empty string.
I hope this helps.
Regards Michael
Is it possible to create a named query that uses parameters in the
"order by" clause? When I include the following named query in an
entity, it throws the exception below. The exception occurs as soon as
I deploy my webapp; I only declare the namedquery, I do not use it
anywhere, so this is the point of failure. The reason I want to do this
is so that I can have sortable columns in my JSF app's datatables...if
there is a more efficient way to do this, I'd be interested in
suggestions!
@NamedQuery(name = "Task.findAllOrderByField", query = "SELECT t FROM
Task t ORDER BY :sortField :sortOrder")
Exception [TOPLINK-8024] (Oracle TopLink Essentials - 9.1 (Build b23)):
oracle.toplink.essentials.exceptions.EJBQLException Exception
Description: Syntax error parsing the query [SELECT t FROM Task t ORDER
BY :sortField :sortOrder] at [:sortField]. Internal Exception: line
1:31: expecting IDENT, found ':sortField'
Tim