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

Answer 1

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.