views:

135

answers:

2

I'm trying to write a JPQL Query with an ORDER BY clause:

query = "SELECT c FROM item ORDER BY c.name ASC"

I would like to set an "order" parameter, whose value would be either "ASC" or "DESC":

query = "SELECT c FROM item ORDER BY c.name :order"

And then in my implementation:

query.setParameter("order", "ASC");

This is when I get an Hibernate error:

org.hibernate.HibernateException: Errors in named queries

Any idea on what I'm doing wrong? Thanks!

+1  A: 

The "ASC" or "DESC" can't be a query parameter. You could use string concatenation instead.

query = "SELECT c FROM item ORDER BY c.name " + sortOrder;

You should validate that the contents of sortOrder can only be ASC or DESC and does not come directly from the user.

Mark Byers
OK that explains a lot. Is there anyway to use a string concatenation in the declaration of a @NamedQuery ?
Pedro
@Pedro: I don't know, sorry.
Mark Byers
alright, thanks anyway!
Pedro
+2  A: 

If you want to use named queries here, you'd need two of them (named queries are static and you can't use ASC and DESC as parameter as pointed out by @Mark).

Pascal Thivent
Or I guess I could just use a dynamic query?
Pedro
@Pedro Sure. But that's won't be a @NamedQuery (I was actually answering one of your comment).
Pascal Thivent
You're right. But the truth is, I have quite a few named queries that need to be sorted, so creating 2 queries for each will result in twice as much named queries. Is that a problem? Or should I use dynamic queries instead?
Pedro
From a maintenance point of view, using dynamic queries will be definitely better. That's what I would do.
Pascal Thivent
Alright, thanks for your feedback!
Pedro