Select additional field for sorting via JPA Criteria API

Question

Using the JPA Criteria API, I want to sort by a column that is created as part of the select clause.

Example in SQL:

SELECT m.name as name,
       LOWER(m.name) as lowerName
FROM   MyEntityTable as m
WHERE  ...

Here, lowerName is only part of the selection.

At the moment, my intention is to use the Specification API of JPA because I want to compose complex conditions dynamically.

Thus, I have something like this (hasName is just an example of more complex condition chains):

mySearchResultRepository.findAll(
        Specification.allOf(
                MySearchResultRepository.hasName("foo"),
                // further conditions depending on context ...
                ),
        pageRequest // Wants to sort by 'lowerName'
        );

• mySearchResultRepository implements JpaSpecificationExecutor, which yields the findBy method.
• The pageRequest contains the column to be sorted by. In the example, this is lowerName.

Problem

• At the moment lowerName is not part of the entity such that Hibernate complains that it cannot sort it: No property 'lowerName' found for type 'MyEntity'.

Question

• How to express the additional selection of LOWER(obj.name) as lowerName when using the Specification API of JPA?

---

Solution Attempt

I found a lower method in criteriaBuilder, so I tried this:

static Specification<MyEntitySearchResult> augmentWithLowerName() {
        return (entity, criteriaQuery, criteriaBuilder) -> {
            // Add an alias for lowerName as side effect (does not work)
            Selection<String> lowerNameSelection = criteriaBuilder.lower(entity.get("name")).alias("lowerName");
            Selection<?> oldSelection = criteriaQuery.getSelection();
            criteriaQuery.multiselect(oldSelection, lowerNameSelection); work

            // Only executed for the side effect, so return true-predicate
            return criteriaBuilder.and();
        };
    }

mySearchResultRepository.findAll(
        Specification.allOf(
                MySearchResultRepository.hasName("foo"),
                // further conditions depending on context ...

                // Add virtual 'lowerName' column to selection for sorting
                MySearchResultRepository.augmentWithLowerName()
                ),
        pageRequest // Wants to sort by 'lowerName'
        );

However, this does not work.

What is the proper way to extend the selection when using the Specification API? Is this possible at all using the Criteria API?

Answer 1

The JpaSpecificationExecutor only provides a handful of methods, which may be too limited to express everything.

However, Specification.toPredicate allows to create a Predicate that can be used with the regular JPA Criteria API.

Example:

static Specification<MyEntity> hasName(String name) {
    return (root, cq, cb) -> cb.equal(root.get("name"), name);
}

void queryExample()
{
    EntityManager em = ...;
    CriteriaBuilder cb = em.getCriteriaBuilder()

    // Create query with projected type.
    // NOTE: It may not be possible to use an interface as projected type.
    CriteriaQuery<MyQueryResult> cq = cb.createQuery(MyQueryResult.class);

    // The table that is queried
    Root<MyEntity> root = cq.from(MyEntity.class);

    // SELECT clause
    cq.multiselect(
        root.get("name").alias("name"),
        criteriaBuilder.lower(root.get("name")).alias("lowerName"));

    // WHERE clause, with Predicate from Specification
    Predicate predicate = hasName("foo").toPredicate(root, cq, cb);
    cq.where(predicate);

    // Execute query
    List<MyQueryResult> resultList = entityManager
        .createQuery(cq)
        .getResultList();
}

// Projected query result
public record MyQueryResult(String name, String lowerName) {}