JPA 1 (Hibernate) and creating NamedQuery with dynamically order

Submitted by Jochus on Wed, 06/01/2010 - 23:09 | Posted in: Java

Today, I really noticed something strange with the JPA framework. I wanted to create a NamedQuery with dynamical order. So this means, you have 1 SQL statement, but you can reuse it to sort on different fields and you can also use to sort DESC or ASC.







So my entity was looking like this:

@NamedQueries({
    @NamedQuery(name = MyEntity.Queries.FOO_SORTED, query="SELECT e FROM MyEntity e ORDER BY :" + MyEntity.QParams.SORT_FIELD + " :" + MyEntity.QParams.SORT_MODE),
    ...
})
@Entity
public class MyEntity {
   ...
}

And in my DAO layer, I was doing something like this:

Query q = entityManager.createNamedQuery(MyEntity.Queries.FOO_SORTED);
q.setParameter(MyEntity.QParams.SORT_FIELD, "name");
q.setParameter(MyEntity.QParams.SORT_MODE, "DESC");

Unfortuneatly, this code doesn't run. I was getting this exception:


Reason: javax.persistence.PersistenceException: org.hibernate.HibernateException: Errors in named queries: MyEntity.Queries.FOO_SORTED

So it seems that I was not able to use the dynamical sorting.
A solution is to use the createQuery() method on the EntityManager

Query q = entityManager.createQuery("SELECT e FROM MyEntity e ORDER BY name DESC");

... but I don't fully agree with this solution as you are moving your SQL statements from your model to your DAO layer.
This problem could have been solved by using the Hibernate criteria API, but then you are moving away from the JPA. So I'm really looking forward to use JPA 2, as this framework also has a criteria API:

crit.addOrder(Order.desc("name"));

Add new comment

The content of this field is kept private and will not be shown publicly.

Full HTML

  • Lines and paragraphs break automatically.
  • You can caption images (data-caption="Text"), but also videos, blockquotes, and so on.
  • Web page addresses and email addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <cpp>, <css>, <html5>, <java>, <javascript>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.