CODEWITHSUNDEEP
javajpajpa-2.0jpql
copenndthagen
copenndthagen

Reputation: 50722

Dynamic Named Query in Entity class using JPQL example

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.

Upvotes: 0

Views: 14666

Answers (3)

Zaw Than oo
Zaw Than oo

Reputation: 9935

@NamedQuery

  • Persistence Provider converts the named queries from JPQL to SQL at deployment time.
  • Until now, there is no feature to create/update the query with @NamedQuery 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 converts the dynamic queries from JPQL to SQL every time it is invoked.
  • The main advantage of using dynamic queries is that the query can be created based on the user inputs.

Upvotes: 1

RicardoS
RicardoS

Reputation: 2118

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).

Upvotes: 2

V G
V G

Reputation: 19002

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);

Upvotes: 3

Related Questions