Dynamic Named Query in Entity class using JPQL exa

Question

I have a named query as below;

@NamedQuery(name = "MyEntityClass.findSomething", query = "SELECT item FROM MyTable mytbl")

Now I want to append dynamic sort clause to this query (based on UI parameters)

Can I get an example using JPQL for doing the same (like how to set a dynamic ORDER BY in the Entity class)

I have already tried using CriteriaQuery, but was looking for a JPQL implementation now.

Answer 1

@NameQuery

• Persistence Provider convert the named queries from JPQL to SQL at deployment time.
• Until now, there is no feature to create/update the query with @NameQuery annotation at runtime.
• On the other hand, you can use Reflection API, to change the annotation value at runtime. I think It is not solution, also it is not you wanted .

em.createQuery()

• Persistence Provider convert the dynamic queries from JPQL to SQL every time it is invoked.
• The main advantage of using dynamic queries is the query can create dependent on the user inputs.

Answer 2

NamedQueries are by definition NOT dynamic, it is not correct to change them programmatically.

So the way to go is to create a JPQL query (but not a named query) like this:

TypedQuery<MyEntity> query = em.createdQuery("SELECT item FROM MyEntity item ORDER BY "+sortingCol, MyEntity.class);

On the other hand, if you REALLY want to use the named query, you could do that the following way:

@NamedQuery(name = "MyEntityClass.findSomething", query = MyEntity.NAMED_QUERY)
@Entity
public class MyEntity {
    public static final NAMED_QUERY= "SELECT item FROM MyTable mytbl";
    //+your persistent fields/properties...
}
//and later in your code
TypedQuery<MyEntity> query = entityManager.createQuery(MyEntity.NAMED_QUERY + " ORDER BY " + sortingCol, MyEntity.class);

Answer 3

Complementing for JPA 2.1

As of JPA 2.1 it is possible to define named queries programmatically.
This can be achieved using entityManagerFactory.addNamedQuery(String name, Query).

Example:

Query q = this.em.createQuery("SELECT a FROM Book b JOIN b.authors a WHERE b.title LIKE :title GROUP BY a");
this.em.getEntityManagerFactory().addNamedQuery("selectAuthorOfBook", q);
// then use like any namedQuery

Reference here

This can be useful, for instance, if you have the orderby field defined as a application parameter. So, when the application starts up or on the first run of the query, you could define the NamedQuery with the defined OrderBy field.

On the other side, if your OrderBy can be changed anytime (or changes a lot), then you need dynamic queries instead of NamedQuery (static). It would not worth to (re)create a NamedQuery every time (by performance).