JPA type safe order Query

Question

I have

Entity: Users

and

@NamedQuery(name = "Users.findAll", query = "SELECT u FROM Users u")

Use it like this:

Query query = em.createNamedQuery("Technologies.findByIsActive", Technologies.class);
query.setFirstResult(technologiesTableModel.getLowItemIndex());
query.setMaxResults(technologiesTableModel.getPageRange().intValue());
List<Technologies> technologies = query.setParameter("isActive", 'Y').getResultList();

But I want to sort this result dynamic. I have one String that contains column name that I want to sort(field content come from request). How can I do that without new query for each field and direction and to type safe (without direct append order by clause with field name).

My research show suggestion to use CriteriaQuery(I can't found example with using a NamedQuery defined above entity), TypedQuery(I can't found example with ordering, only with setFirstResult and setMaxResults. But I need both sorting and pagination) or JpaRepository (I can't find full implemented example with good description).

That I want is to sort my data by field name (that I have like String) and after that to paging it (with setFirstResult and setMaxResults). I want to do that with NamedQuery that I was define(in Entity class declaration) and not define query on fly or append order clause to query end with String concatenations.

EDIT:

All I need is a good explanation how this have to be implemented (with all good practices). I didn't have idea how to do that. All I try is:

My queries to be at one place(most of them)
String concatenation is not good practice to create queries.

errm, since a NamedQuery is defined statically then you define the query string statically (not dynamically). Criteria or JPQL both support you defining the query at runtime. Any JPA docs would show that. http://www.datanucleus.org/products/accessplatform_5_1/jpa/query.html#_criteria_api_ordering — Neil Stockton, Jun 06 '17 at 12:33
Named queries aren't dynamic. have a look on this topic https://stackoverflow.com/questions/22167730/dynamic-named-query-in-entity-class-using-jpql-example I would go with criteria or sql. As the JPA criteria API isn't the nicest, QueryDSL could be a option or the Hibernate API itself. — matthias, Jun 06 '17 at 12:35
and you wouldn't go the Hibernate API if not using that JPA provider, or of you are wanting portability (i.e sane) — Neil Stockton, Jun 06 '17 at 12:48
Ok, NamedQuery is defined statically! I understand it! But ask for solution like my pagination(with methods *setFirstResult* and *setMaxResults*) who don't use complex queries with **rownum** and **case then** clause. — Morticia A. Addams, Jun 06 '17 at 12:50

score 0 · Accepted Answer · answered Jun 09 '17 at 07:22

I resolve my problem with case clause. In my entity I added orderClause field like:

public static final String orderClause = " order by "
            + "case when :orderField = 'id' and :orderDirection = 'asc' then s.id else 0 end ASC, "
            + "case when :orderField = 'id' and :orderDirection = 'desc' then s.id else 0 end DESC, "
            + "case when :orderField = 'username' and :orderDirection = 'asc' then s.username else '' end ASC, "
            + "case when :orderField = 'username' and :orderDirection = 'desc' then s.username else '' end DESC, "
            + "s.id ASC";

And add this field to end of named queries where is needed like:

@NamedQueries(
{
    @NamedQuery(name = "SysRoles.findAll", query = "SELECT s FROM SysRoles s" + SysRoles.orderClause),
    @NamedQuery(name = "SysRoles.findByIsActive", query = "SELECT s FROM SysRoles s WHERE s.isActive = :isActive " + SysRoles.orderClause)
}) 
public class SysRoles implements Serializable {
    public static final String orderClause = " order by "
        + "case when :orderField = 'id' and :orderDirection = 'asc' then s.id else 0 end ASC, "
        + "case when :orderField = 'id' and :orderDirection = 'desc' then s.id else 0 end DESC, "
        + "case when :orderField = 'username' and :orderDirection = 'asc' then s.username else '' end ASC, "
        + "case when :orderField = 'username' and :orderDirection = 'desc' then s.username else '' end DESC, "
        + "case when :orderField = 'isActive' and :orderDirection = 'asc' then s.isActive else '' end ASC, "
        + "case when :orderField = 'isActive' and :orderDirection = 'desc' then s.isActive else '' end DESC, "
        + "s.id ASC";
 ...
}

And I call my named query like:

Query query = em.createNamedQuery("SysRoles .findAll", SysRoles.class);
        //Pagination
        query.setFirstResult(lowItemIndex);
        query.setMaxResults(maxResults);
List<SysRoles> sysUsers= query.setParameter("isActive", "Y")
                 //ditection of sorting
                .setParameter("orderDirection", direction)
                 //name of sorting columns
                .setParameter("orderField", orderByColumnName).getResultList();

More about ordering by column name and good explantation can be find here in blog asktom.oracle.com.

With this solution we have a little bit ugly queries, but they are in one place and ordering is also there.

JPA type safe order Query

1 Answers1