I have the below code snippet for dynamic sorting using JPA Criteria API Root<Employee> root = criteriaQuery.from(Employee); Join<Employee, Project> joinProject = root.join(Employee_.projectList, JoinType.LEFT); if (sortDirection.equals("asc")) { criteriaQuery.orderBy(cb.asc(root.get(sortField))); If I am passing an attribute of Employee entity to order by statement, it works without any hitch, however if an attribute of Project entity is passed to order by statement, exception is thrown stating that The attribute [projectName] is not present in the managed type because projectName is an attribute of Project entity which is joined with Employee using joinProject . In order by statement I am using root.get(sortField) . if it is joinProject.get(sortField) , it would work fine when attributes of Project are being passed to order by statement. My questions are How could I modify my Order By statement in order to cater all the attributes which being passed? Do I need to conditionally check which attribute and accordingly use if conditions or are there better ways of doing this? Appreciate insight into this.

JPA Dynamic Order By with Criteria API

Reputation: 7459

A specific approach for a simple scenario (predetermined one-level only joins) may be something like this:

    Root<Employee> root = criteriaQuery.from(Employee.class);
    Join<Employee, Project> joinProject = root.join(Employee_.projectList, JoinType.LEFT);

    Class<?> baseClass = fieldTypeMap.get(sortField);
    From<?, ?> from;
    if(baseClass == Employee.class)
    {
        from = root;
    }
    else if(baseClass == Project.class)
    {
        from = joinTeam;
    }
    else ...

    Expression<?> expr = from.get(sortField);

    if(sortDirection.equals("asc")) 
    {               
        criteriaQuery.orderBy(cb.asc(expr));
    }

    ...

where fieldTypeMap is something like:

private final static Map<String, Class<?>> fieldTypeMap = new HashMap<>();
static {
    fieldTypeMap.put("employeeName", Employee.class);
    fieldTypeMap.put("projectName", Project.class);
    ...
}

However, this is quick and dirty, ugly and unmaintainable.

If you want a generic approach, things may get a bit complex.

Personally, I'm using my own classes built on top of EntityManager, CriteriaBuilder and Metamodel, which provides dynamic filtering and multi-sorting features. But something like this should be meaningful enough:

protected static List<Order> buildOrderBy(CriteriaBuilder builder, Root<?> root, List<SortMeta> sortList)
{
    List<Order> orderList = new LinkedList<>();

    for(SortMeta sortMeta : sortList)
    {
        String sortField = sortMeta.getSortField();
        SortOrder sortOrder = sortMeta.getSortOrder();

        if(sortField == null || sortField.isEmpty() || sortOrder == null)
        {
            continue;
        }

        Expression<?> expr = getExpression(root, sortField);

        if(sortOrder == SortOrder.ASCENDING)
        {
            orderList.add(builder.asc(expr));
        }
        else if(sortOrder == SortOrder.DESCENDING)
        {
            orderList.add(builder.desc(expr));
        }
    }

    return orderList;
}

protected static Expression<?> getExpression(Root<?> root, String sortField)
{
    ManagedType<?> managedType = root.getModel();
    From<?, Object> from = (From<?, Object>) root;

    String[] elements = sortField.split("\\.");
    for(String element : elements)
    {
        Attribute<?, ?> attribute = managedType.getAttribute(element);
        if(attribute.getPersistentAttributeType() == PersistentAttributeType.BASIC)
        {
            return from.get(element);
        }

        from = from.join(element, JoinType.LEFT);
        managedType = EntityUtils.getManagedType(from.getJavaType());
    }

    return from;
}

This way the join is based on sort field, which is now a dotted-path-expr like "projectList.name" or "office.responsible.age"

public static <X> ManagedType<X> getManagedType(Class<X> clazz)
{
    try
    {
        return getMetamodel().managedType(clazz);
    }
    catch(IllegalArgumentException e)
    {
        return null;
    }
}

public static Metamodel getMetamodel()
{
    return getEntityManagerFactory().getMetamodel();
}

public static EntityManagerFactory getEntityManagerFactory()
{
    try
    {
        return InitialContext.doLookup("java:module/persistence/EntityManagerFactory");
    }
    catch(NamingException e)
    {
        throw new RuntimeException(e.getMessage(), e);
    }
}

to make it work on a webapp, you have to declare contextual references on web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
    version="3.1">

    <display-name>my_app_name</display-name>

    ...

    <persistence-context-ref>
        <persistence-context-ref-name>java:module/persistence/EntityManager</persistence-context-ref-name>
        <persistence-unit-name>my_pu_name</persistence-unit-name>
    </persistence-context-ref>

    <persistence-unit-ref>
        <persistence-unit-ref-name>java:module/persistence/EntityManagerFactory</persistence-unit-ref-name>
        <persistence-unit-name>my_pu_name</persistence-unit-name>
    </persistence-unit-ref>
</web-app>

update

I don't know about how EclipseLink handles grouping, but also Hibernate does not perform joins (actually, conditions on joins) correctly in some case.

In example, when all these conditions are met:

querying an entity (as Root/From) that is part of a class hierarchy (and not a leaf) based on SINGLE_TABLE
joining/getting a property of a subclass
joining/getting by property name (String) instead of Attribute

To workaround the issue I always resolve property name to an Attribute and reuse joins already "walked" (did I say things may get complicated?):

public class MetaDescriptor extends BusinessObject implements Serializable, MemberEx, ColumnDescriptor
{
    private static final long serialVersionUID = 1L;

    @BusinessKey
    protected final Attribute<?, ?> attribute;

    @BusinessKey
    protected final MetaDescriptor parent;

    protected List<MetaDescriptor> childList;

    protected final Type<?> elementType;

    ...

    protected MetaDescriptor(Attribute<?, ?> attribute, MetaDescriptor parent)
    {
        this.attribute = attribute;
        this.parent = parent;

        if(attribute instanceof SingularAttribute)
        {
            SingularAttribute<?, ?> singularAttribute = (SingularAttribute<?, ?>) attribute;
            elementType = singularAttribute.getType();
        }
        else if(attribute instanceof PluralAttribute)
        {
            PluralAttribute<?, ?, ?> pluralAttribute = (PluralAttribute<?, ?, ?>) attribute;
            elementType = pluralAttribute.getElementType();
        }
        else
        {
            elementType = null;
        }
    }

    public static MetaDescriptor getDescriptor(ManagedType<?> managedType, String path)
    {
        return getDescriptor(managedType, path, null);
    }

    public static MetaDescriptor getDescriptor(From<?, ?> from, String path)
    {
        if(from instanceof Root)
        {
            return getDescriptor(((Root<?>) from).getModel(), path);
        }

        return getDescriptor(from.getJavaType(), path);
    }

    public static MetaDescriptor getDescriptor(Class<?> clazz, String path)
    {
        ManagedType<?> managedType = EntityUtils.getManagedType(clazz);
        if(managedType == null)
        {
            return null;
        }

        return getDescriptor(managedType, path);
    }

    private static MetaDescriptor getDescriptor(ManagedType<?> managedType, String path, MetaDescriptor parent)
    {
        if(path == null)
        {
            return null;
        }

        Entry<String, String> slice = StringUtilsEx.sliceBefore(path, '.');
        String attributeName = slice.getKey();

        Attribute<?, ?> attribute;
        if("class".equals(attributeName))
        {
            attribute = new ClassAttribute<>(managedType);
        }
        else
        {
            try
            {
                attribute = managedType.getAttribute(attributeName);
            }
            catch(IllegalArgumentException e)
            {
                Class<?> managedClass = managedType.getJavaType();

                // take only if it is unique
                attribute = StreamEx.of(EntityUtils.getMetamodel().getManagedTypes())
                    .filter(x -> managedClass.isAssignableFrom(x.getJavaType()))
                    .flatCollection(ManagedType::getDeclaredAttributes)
                    .filterBy(Attribute::getName, attributeName)
                    .limit(2)
                    .collect(Collectors.reducing((a, b) -> null))
                    .orElse(null);

                if(attribute == null)
                {
                    return null;
                }
            }
        }

        MetaDescriptor descriptor = new MetaDescriptor(attribute, parent);

        String remainingPath = slice.getValue();
        if(remainingPath.isEmpty())
        {
            return descriptor;
        }

        Type<?> elementType = descriptor.getElementType();
        if(elementType instanceof ManagedType)
        {
            return getDescriptor((ManagedType<?>) elementType, remainingPath, descriptor);
        }

        throw new IllegalArgumentException();
    }

    @Override
    public <T> Expression<T> getExpression(CriteriaBuilder builder, From<?, ?> from)
    {
        From<?, Object> parentFrom = getParentFrom(from);

        if(attribute instanceof ClassAttribute)
        {
            return (Expression<T>) parentFrom.type();
        }

        if(isSingular())
        {
            return parentFrom.get((SingularAttribute<Object, T>) attribute);
        }

        return getJoin(parentFrom, JoinType.LEFT);
    }

    private <X, T> From<X, T> getParentFrom(From<?, ?> from)
    {
        return OptionalEx.of(parent)
            .map(x -> x.getJoin(from, JoinType.LEFT))
            .select(From.class)
            .orElse(from);
    }

    public <X, T> Join<X, T> getJoin(From<?, ?> from, JoinType joinType)
    {
        From<?, X> parentFrom = getParentFrom(from);

        Join<X, T> join = (Join<X, T>) StreamEx.of(parentFrom.getJoins())
            .findAny(x -> Objects.equals(x.getAttribute(), attribute))
            .orElseGet(() -> buildJoin(parentFrom, joinType));

        return join;
    }

    private <X, T> Join<X, T> buildJoin(From<?, X> from, JoinType joinType)
    {
        if(isSingular())
        {
            return from.join((SingularAttribute<X, T>) attribute, joinType);
        }

        if(isMap())
        {
            return from.join((MapAttribute<X, ?, T>) attribute, joinType);
        }

        if(isSet())
        {
            return from.join((SetAttribute<X, T>) attribute, joinType);
        }

        if(isList())
        {
            return from.join((ListAttribute<X, T>) attribute, joinType);
        }

        if(isCollection())
        {
            return from.join((CollectionAttribute<X, T>) attribute, joinType);
        }

        throw new ImpossibleException();
    }

    public Order buildOrder(CriteriaBuilderEx builder, From<?, ?> from, SortOrder direction)
    {
        if(direction == null)
        {
            return null;
        }

        Expression<?> expr = getExpression(builder, from);

        return direction == SortOrder.ASCENDING ? builder.asc(expr) : builder.desc(expr);
    }
}

with this construct, I can now safely:

public static List<Order> buildOrderList(CriteriaBuilderEx builder, From<?, ? extends Object> from, List<SortMeta> list)
{
    return StreamEx.of(list)
        .nonNull()
        .map(x -> buildOrder(builder, from, x.getSortField(), x.getSortOrder()))
        .nonNull()
        .toList();
}

public static Order buildOrder(CriteriaBuilderEx builder, From<?, ? extends Object> from, String path, SortOrder direction)
{
    if(path == null || path.isEmpty() || direction == null)
    {
        return null;
    }

    MetaDescriptor descriptor = MetaDescriptor.getDescriptor(from, path);
    if(descriptor == null)
    {
        return null;
    }

    return descriptor.buildOrder(builder, from, direction);
}

Upvotes: 4

JPA Dynamic Order By with Criteria API

Answers (2)

Related Questions