Is it possible to order by a composite key with JPA and CriteriaBuilder

Question

I would like to create a query using the JPA CriteriaBuilder and I would like to add an ORDER BY clause. This is my entity:

@Entity
@Table(name = "brands")
public class Brand implements Serializable {

    public enum OwnModeType {
        OWNER, LICENCED
    }

    @EmbeddedId
    private IdBrand id;
    private String code;
    //bunch of other properties
}

Embedded class is:

@Embeddable
public class IdBrand implements Serializable {

    @ManyToOne
    private Edition edition;
    private String name;
}

And the way I am building my query is like this:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Brand> q = cb.createQuery(Brand.class).distinct(true);
Root<Brand> root = q.from(Brand.class);
if (f != null) {
    f.addCriteria(cb, q, root);
    f.addOrder(cb, q, root, sortCol, ascending);
}
return em.createQuery(q).getResultList();

And here are the functions called:

public void addCriteria(CriteriaBuilder cb, CriteriaQuery<?> q, Root<Brand> r) {
}

public void addOrder(CriteriaBuilder cb, CriteriaQuery<?> q, Root<Brand> r, String sortCol, boolean ascending) {
    if (ascending) {
        q.orderBy(cb.asc(r.get(sortCol)));
    } else {
        q.orderBy(cb.desc(r.get(sortCol)));
    }
}

If I try to set sortCol to something like "id.name" I get the following error:

javax.ejb.EJBException: java.lang.IllegalArgumentException: Unable to resolve attribute [id.name] against path

Any idea how I could accomplish that? I tried searching online, but I couldn't find a hint about this... Also would be great if I could do a similar ORDER BY when I have a @ManyToOne relationship (for instance, "id.edition.number")

score 6 · Accepted Answer · answered Apr 16 '12 at 13:39

The problem is your understanding of JPA path usage. JPA Manual says:

Path expression whose type is a persistable user class can be extended further by reusing the dot (.) operator. For example, c.capital.name is a nested path expression that continues from the Capital entity object to its name field. A path expression can be extended further only if its type is also a user defined persistable class. The dot (.) operator cannot be applied to collections, maps and values of simple types (number, boolean, string, date).

In your case you used id.edition.number, where IdBrand (id) is not user persistable class.

To solution is building path expression this way: root.get("id").get("edition.number");

To avoid such problems, you can write a path builder, which based on javax.persistence.metamodel.Attribute.PersistentAttributeType would decide the way path is built. For basic elements - use .get(), for collection elements use .join().

For you to understand your error, you need to read more information on JPA paths in general. Here is a good manual on JPA paths.

Yes that is what it says in the other answer I linked to. I find this really annoying and don't understand why it couldn't work automagically. To your knowledge, would I have the same issue if I used IdClass instead of EmbeddedId? — Kjir, Apr 17 '12 at 15:11
Well, if we look at [JPA Type Hierarchy](http://www.objectdb.com/java/jpa/persistence/metamodel#Type_Interface_Hierarchy_), we can see, that EntityType extends IdentifiableType, so maybe @Id (@IdClass) is identifiable type, so you can do that. You should try it out. Also get PersistentType from attribute, then you can define what you can do with it. — d1e, Apr 20 '12 at 10:03
As I am saying, **maybe** it is possible, so you better check out the PersistentType, and provide it here if you can Please. — d1e, Apr 20 '12 at 14:58

score 1 · Answer 2 · edited May 23 '17 at 11:52

I actually found the answer on another similar issue: JPA - Criteria API and EmbeddedId

Here is how I fixed my code:

public void addOrder(CriteriaBuilder cb, CriteriaQuery<?> q, Root<Brand> r, String sortCol, boolean ascending) {
    Path<?> p = r;
    String []sortCols = sortCol.split("\\."); // This is a regexp, hence the escape backslash
    for(String sc: sortCols) {
        p = p.get(sc);
    }
    if (ascending) {
        q.orderBy(cb.asc(p));
    } else {
        q.orderBy(cb.desc(p));
    }
}

Is it possible to order by a composite key with JPA and CriteriaBuilder

2 Answers2