JPA dynamic criteria-api query

Question

I wonder if there is a generic way to use the criteria api in combination with a little more complex model?

I have an entity class that has one-to-one relationships to other entities. My service wrapper that does the database query via the criteria api gets the parameters from front end to figure out pagination, sorting and filtering.

Entities

@Entity
public class Person implements Serializable {
    @Id
    private Long id;
    private String name;
    private String givenName;

    @Temporal(TemporalType.DATE)
    private Date birthdate;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "INFORMATION_ID")
    private Information information;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "ADDRESS_ID")
    private Address address;
    ...
}

@Entity
public class Information implements Serializable {
    @Id
    private Long id;
    private String detail;
    ...
}

@Entity
public class Address implements Serializable {
    @Id
    private Long id;
    private String street;
    private String city;
    ...
}

Service

@Stateless
public class PersonService {
    @PersistenceContext(unitName = "ProblemGenericDatatableFilterPU")
    private EntityManager em;
    public List<Person> findAllPersons222(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<Person> criteriaQuery = builder.createQuery(Person.class);
        Root<Person> rootPerson = criteriaQuery.from(Person.class);
        Join<Person, Information> joinPersonInformation = rootPerson.join(Person_.information);
        Join<Person, Address> joinPersonAddress = rootPerson.join(Person_.address);

        // select
        criteriaQuery.select(rootPerson);

        // filter
        List<Predicate> allPredicates = new ArrayList<>();
        for(Entry<String, Object> currentEntry : filters.entrySet()) {
            Predicate currentPredicate;

            if(currentEntry.getKey().startsWith("information_")) {
                currentPredicate = builder.like(
                        builder.lower(joinPersonInformation.<String>get(currentEntry.getKey())),
                    builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                );
            }
            else if(currentEntry.getKey().startsWith("address_")) {
                currentPredicate = builder.like(
                        builder.lower(joinPersonAddress.<String>get(currentEntry.getKey())),
                    builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                );
            }
            else {
                currentPredicate = builder.like(
                        builder.lower(rootPerson.<String>get(currentEntry.getKey())),
                    builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                );
            }
            allPredicates.add(currentPredicate);
        }
        criteriaQuery.where(builder.and(allPredicates.toArray(new Predicate[0])));

        // order
        if(sortField != null && !sortField.isEmpty()) {
            Order orderBy;
            if(sortField.startsWith("information_")) {
                orderBy = (sortOrder == SortOrder.DESCENDING
                        ? builder.desc(joinPersonInformation.get(sortField))
                        : builder.asc(joinPersonInformation.get(sortField)));
            }
            else if(sortField.startsWith("address_")) {
                orderBy = (sortOrder == SortOrder.DESCENDING
                        ? builder.desc(joinPersonAddress.get(sortField))
                        : builder.asc(joinPersonAddress.get(sortField)));
            }
            else {
                orderBy = (sortOrder == SortOrder.DESCENDING
                        ? builder.desc(rootPerson.get(sortField))
                        : builder.asc(rootPerson.get(sortField)));
            }
            criteriaQuery.orderBy(orderBy);
        }

        Query query = em.createQuery(criteriaQuery);
        // pagination
        query.setFirstResult(first);
        query.setMaxResults(pageSize);
        return query.getResultList();
    }
}

I need to do a distinction of cases for filtering and sorting depending on the root/join on which I am accessing the property. Plus I need to use a naming convention in the facelet. The same goes for the count-query except for sorting.

Now I ask myself whether there is some "dot-notation" or anything which makes the case dispensable. In e. g. native SQL I would do something like create a subquery and select all alias values from the inner projection (select * from (select person.name as name, address.street as street, ...) where name = ... and street like ...).

I would be grateful for any advice.

Answer 1

Finally I got the time to deal with my problem. I found a solution thats not perfect, but works for me.

As I searched for another problem I came to this article by Leonardo Shikida and found a very handy Path<?> getPath(...) method (I also had a deeper look into the brilliant inheritance relationships in the CriteriaAPI: Path, Root, Join, From, etc). With that in mind I remermbered my former problem and thought for a more gerneric way of this method. So here is what I made of this:

At first I create all the joins I need (i. e. Root<?> and Join<? ?>) and put them in a Map<String, From<?, ?>> where the String is an element on which an attribute is queried in a dotted notation (naming convention and the downside on the complete solution) and the From is the corresponding source.

With the Map I can do filtering and sorting in a more or less generic way.

To make it work the front end needs to use the very same naming convention and pass the filters-Map accordingly (i. e. JSF using primefaces field attribute in p:column).

public List<Person> newFindAllPersons(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters)
{
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Person> criteriaQuery = builder.createQuery(Person.class);

    // setting up the required joins
    Root<Person> rootPerson = criteriaQuery.from(Person.class);
    Join<Person, Information> joinPersonInformation = rootPerson.join(Person_.information);
    Join<Person, Address> joinPersonAddress = rootPerson.join(Person_.address);
    Join<Address, Information> joinAddressInformation = joinPersonAddress.join(Address_.information);

    // putting all joins into a map with a dot`ted name
    Map<String, From<?, ?>> mapFieldToFrom = new HashMap<>();
    mapFieldToFrom.put("person", rootPerson);
    mapFieldToFrom.put("person.address", joinPersonAddress);
    mapFieldToFrom.put("person.information", joinPersonInformation);
    mapFieldToFrom.put("person.address.information", joinAddressInformation);

    // select
    criteriaQuery.select(rootPerson);

    // filter
    List<Predicate> allPredicates = new ArrayList<>();
    for(Entry<String, Object> currentEntry : filters.entrySet())
    {
        Predicate currentPredicate = builder.like(
                builder.lower(getStringPath(currentEntry.getKey(), mapFieldToFrom)),
                builder.lower(builder.literal("%" + String.valueOf(currentEntry.getValue()) + "%"))
        );
        allPredicates.add(currentPredicate);
    }
    criteriaQuery.where(builder.and(allPredicates.toArray(new Predicate[0])));

    // order
    if(sortField != null && !sortField.isEmpty())
    {
        Path<?> actualPath = getStringPath(sortField, mapFieldToFrom);
        Order orderBy = (sortOrder == SortOrder.DESCENDING
                ? builder.desc(actualPath)
                : builder.asc(actualPath));

        criteriaQuery.orderBy(orderBy);
    }

    Query query = em.createQuery(criteriaQuery);
    // pagination
    query.setFirstResult(first);
    query.setMaxResults(pageSize);
    return query.getResultList();
}

/**
 * divides the given field at the last dot and takes <br>
 * -   the first part as the key in the map to retrieve the From<?, ?> <br>
 * -   the last part as the name of the column in the entity
 */
private Path<String> getStringPath(String field, Map<String, From<?, ?>> mapFieldToFrom)
{
    if(!field.matches(".+\\..+"))
    {
        throw new IllegalArgumentException("field '" + field + "' needs to be a dotted path (i. e. customer.address.city.zipcode)");
    }
    String fromPart = field.substring(0, field.lastIndexOf('.'));
    String fieldPart = field.substring(field.lastIndexOf('.') + 1);

    From<?, ?> actualFrom = mapFieldToFrom.get(fromPart);
    if(actualFrom == null)
    {
        throw new IllegalStateException("the given map does not contain a from or for the value '" + fromPart + "' or is null");
    }
    return actualFrom.get(fieldPart);
}

Example front end

<p:dataTable>
    <!-- mapFieldToFrom.put("person", rootPerson); -->
    <p:column field="person.name"> 
    </p:column>

    <!-- mapFieldToFrom.put("person.address", joinPersonAddress); -->
    <p:column field="person.address.street">
    </p:column>
</p:dataTable>