Hi Tim,
I would like to second what Craig described. When using the parameter as
value in the ORDER BY clause the ordering expression would evaluate to
the same value for all instances to be returned and then ordering does
not make sense. So we would need a different syntax in case a parameter
holds the name of the field that substitutes the parameter. Another
issue is that you cannot check the correctness of the ORDER BY clause at
query compile time, because the parameter values are not yet substituted.
Regards Michael
> Hi Tim,
>
> On Jan 9, 2007, at 7:05 AM, Romanowski, Tim wrote:
>
>> 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?
>>
> Generally, parameters are used as values not as names of fields. For
> example, in the query "SELECT t FROM Task t WHERE :fieldname = :value"
> how would you distinguish between :fieldname which is supposed to
> contain the field you want to compare and :value which is supposed to
> contain the value against which to compare the field?
>
> You would need a different parameter marker to distinguish between
> parameters used as substitutions in the query versus parameters used
> as values. This would be an interesting feature. Perhaps use a double
> "::" for a name substitution, as "SELECT t FROM Task t WHERE
> ::fieldname = :value".
>
> Craig
>>
>> 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
>> <mailto: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
>>
>>
>>
>>
>
> Craig Russell
>
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>
> 408 276-5638 mailto:Craig.Russell_at_sun.com
>
> P.S. A good JDO? O, Gasp!
>
>