JPA Criteria Predicate Conditions

Question

I have the following code snippet for building criteria builder where condition.

Would like to know are there any ways of making this better as I would have more where conditions and same conditions will be used for getting count of records.

Any insight is highly appreciable

private List <Product> getProducts(MultivaluedMap params) throws JSONException {

    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    CriteriaQuery <Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);

    Root <Product> root = criteriaQuery.from(Product.class);

    List <Predicate> p = new ArrayList <Predicate> ();
    Predicate prodIdPredicate, prodNamePredicate;

    JSONObject inputJSON = new JSONObject(params);

    if (inputJSON.isNull("filter") == false) {
        JSONObject filter = inputJSON.getJSONObject("filter");
        JSONArray filters = filter.getJSONArray("filters");
        for (int i = 0; i < filters.length(); i++) {

            JSONObject j = (JSONObject) filters.get(i);
            if (j.getString("field").equals("prodId")) {
                prodIdPredicate = criteriaBuilder.like(root.get(Product_.prodId), j.getString("value"));
                p.add(prodIdPredicate);
            }

            if (j.getString("field").equals("prodName")) {
                prodNamePredicate = criteriaBuilder.like(root.get(Product_.prodName), j.getString("value"));
                p.add(prodNamePredicate);
            }

        }
    }
    Predicate[] pr = new Predicate[p.size()];
    p.toArray(pr);
    criteriaQuery.where(pr);    

Answer 1

First of all you must consider to restructure your application in a layered manner. You at least need 3 layer, DAO, Service and WebService.

All the things about database and JPA must be in your DAO layer. And all the json related things must be in your WebService layer. Your service layer must manage the transaction and the communication between web service and dao layer.

First lets talk about your Web Service layer. Your JSON objects probably come from a Restful web service. Since almost all the frameworks supports json marshalling/unmarshalling it is not wise to parse your data transfer objects manually. By this I mean, you may prefer to declare a FieldDto class and pass its instances around instead of JSONObject. Here is an example of FieldDto. It is a POJO.

public class FieldDto {
    private String prodId;
    private String prodName;
    // Getters & Setters etc.
}

You can easily marshall/unmarshall to json using GSON or Jackson. Probably your framework has one of these on default to handle json conversion.

Next layer is Service layer. In service layer you manage your transactions and convert your DTO objects to something that your DAO layer can easily understand. In this case your service layer pass fieldDto.getProdId() and fielDto.getProdName() to DAO layer.

Your last layer is DAO layer. First lets change your method signature.

public List <Product> getProducts(String prodId, String prodName) {

    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    CriteriaQuery <Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);

    Root <Product> root = criteriaQuery.from(Product.class);

    List <Predicate> p = new ArrayList <Predicate> ();

    if(prodId != null){
         p.add(criteriaBuilder.like(root.get(Product_.prodId),prodId));
    }

    if(prodName != null){
         p.add(criteriaBuilder.like(root.get(Product_.prodName), prodName));
    }

    if(!p.isEmpty()){
        Predicate[] pr = new Predicate[p.size()];
        p.toArray(pr);
        criteriaQuery.where(pr);    
    }
    return getEntityManager().createQuery(criteriaQuery).getResultList();
}

This is not it. This code still needs improvement. In one of my projects, I create a fluent api to manage all the boilerplate parts. When you start to write other DAO classes you will realise that some of the code blocks repeats over and over again.

Here is an example of a fluent api. You may want to construct your version of it.

import javax.persistence.EntityManager;
import javax.persistence.LockModeType;
import javax.persistence.PersistenceException;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import javax.persistence.metamodel.Attribute;
import javax.persistence.metamodel.CollectionAttribute;
import javax.persistence.metamodel.PluralAttribute;
import javax.persistence.metamodel.SingularAttribute;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Vector;

public final class SimpleSelectBuilder<E extends Entity> {

    private final EntityManager entityManager;
    private final CriteriaBuilder criteriaBuilder;
    private final CriteriaQuery<E> criteriaQuery;
    private final Root<E> root;
    private final Collection<Predicate> predicates;

    private Integer first = null;
    private Integer max = null;
    private LockModeType lockModeType = null;

    public SimpleSelectBuilder(final EntityManager entityManager, final Class<E> entityClazz) {
        this.entityManager = entityManager;
        this.criteriaBuilder = entityManager.getCriteriaBuilder();
        this.criteriaQuery = this.criteriaBuilder.createQuery(entityClazz);
        this.root = criteriaQuery.from(entityClazz);
        this.predicates = new Vector<>();
    }

    public SimpleSelectBuilder<E> and(final Attribute attribute, final Object value) {
        final Expression expression = this.getExpression(attribute, root);
        this.predicates.add(criteriaBuilder.equal(expression, value));
        return this;
    }

    public SimpleSelectBuilder<E> andNotIn(final Attribute attribute, final Collection<Object> values) {
        final Expression expression = this.getExpression(attribute, root);
        this.predicates.add(criteriaBuilder.not(expression.in(values)));
        return this;
    }

    public SimpleSelectBuilder<E> andIn(final Attribute attribute, final Collection<Object> values) {
        final Expression expression = this.getExpression(attribute, root);
        this.predicates.add(expression.in(values));
        return this;
    }


    public SimpleSelectBuilder<E> andContains(final Attribute attribute, final Object value) {

        final Expression expression = this.getExpression(attribute, root);
        this.predicates.add(criteriaBuilder.isMember(value, expression));
        return this;
    }

    public SimpleSelectBuilder<E> orderByAsc(final Attribute attribute) {
        final List<Order> orders = new ArrayList<>();
        if (this.criteriaQuery.getOrderList() != null) {
            orders.addAll(this.criteriaQuery.getOrderList());
        }
        orders.add(criteriaBuilder.asc(this.getExpression(attribute, root)));
        this.criteriaQuery.orderBy(orders.toArray(new Order[orders.size()]));
        return this;
    }

    public SimpleSelectBuilder<E> orderByDesc(final Attribute attribute) {
        List<Order> orders = this.criteriaQuery.getOrderList();
        if (orders == null) {
            orders = new ArrayList<>();
        }
        orders.add(criteriaBuilder.desc(this.getExpression(attribute, root)));
        this.criteriaQuery.orderBy(orders.toArray(new Order[orders.size()]));
        return this;
    }

    public SimpleSelectBuilder<E> setFirst(Integer first) {
        this.first = first;
        return this;
    }

    public SimpleSelectBuilder<E> setMax(Integer max) {
        this.max = max;
        return this;
    }

    public SimpleSelectBuilder<E> setLockModeType(LockModeType lockModeType) {
        this.lockModeType = lockModeType;
        return this;
    }

    public List<E> getResultList() {
        final TypedQuery<E> query = this.prepareQuery();

        if (lockModeType != null) {
            query.setLockMode(lockModeType);
        }

        if (first != null) {
            query.setFirstResult(first);
        }

        if (max != null) {
            query.setMaxResults(max);
        }

        return query.getResultList();
    }

    public List<E> getCacheableResultList() {
        final TypedQuery<E> query = this.prepareQuery();

        if (lockModeType != null) {
            query.setLockMode(lockModeType);
        }

        if (first != null) {
            query.setFirstResult(first);
        }

        if (max != null) {
            query.setMaxResults(max);
        }

        query.setHint("org.hibernate.cacheable", true);
        query.setHint("org.hibernate.cacheMode", "NORMAL");
        return query.getResultList();
    }

    public E getSingleResult() {
        final TypedQuery<E> query = this.prepareQuery();

        if (lockModeType != null) {
            query.setLockMode(lockModeType);
        }

        return query.getSingleResult();
    }

    public E getCacheableSingleResult() {
        final TypedQuery<E> query = this.prepareQuery();

        if (lockModeType != null) {
            query.setLockMode(lockModeType);
        }

        query.setHint("org.hibernate.cacheable", true);
        query.setHint("org.hibernate.cacheMode", "NORMAL");
        return query.getSingleResult();
    }

    private TypedQuery<E> prepareQuery() {
        this.criteriaQuery.where(this.predicates.toArray(new Predicate[this.predicates.size()]));
        return this.entityManager.createQuery(criteriaQuery);
    }

    private <T> Expression<T> getExpression(final Attribute attribute, final From<E, T> from) {
        if (attribute instanceof SingularAttribute) {
            SingularAttribute singularAttribute = (SingularAttribute) attribute;
            return from.get(singularAttribute);
        } else if (attribute instanceof PluralAttribute) {
            PluralAttribute pluralAttribute = (PluralAttribute) attribute;
            return from.get(pluralAttribute);
        } else {
            throw new PersistenceException("Attribute type of '" + attribute
                    + "' must be one of [SingularAttribute, PluralAttribute].");
        }
    }

    private <T> Join<E, T> getJoinExpression(final Attribute attribute, final From<E, T> from) {
        if (attribute instanceof SingularAttribute) {
            final SingularAttribute singularAttribute = (SingularAttribute) attribute;
            return from.join(singularAttribute);
        } else if (attribute instanceof CollectionAttribute) {
            final CollectionAttribute collectionAttribute = (CollectionAttribute) attribute;
            return from.join(collectionAttribute);
        } else {
            throw new PersistenceException("Attribute type of '" + attribute
                    + "' must be one of [SingularAttribute, PluralAttribute].");
        }
    }

    public SimpleSelectBuilder<E> joinAnd(final Attribute attribute, final Object value, final Attribute... joinOn) {
        Join tableJoin = null;
        for (final Attribute join : joinOn) {
            if (tableJoin == null) {
                tableJoin = this.getJoinExpression(join, root);
            } else {
                tableJoin = this.getJoinExpression(join, tableJoin);
            }

        }

        if (tableJoin == null) {
            throw new PersistenceException("SelectBuilder cannot construct your join statement");
        }

        final Expression expression = this.getExpression(attribute, tableJoin);
        this.predicates.add(criteriaBuilder.equal(expression, value));
        return this;
    }
}

If you use this. Than your method become this.

public List <Product> getProducts(String prodId, String prodName) {

    // TODO add like statement to SimpleSelectBuilder
    return new SimpleSelectBuilder<Product>(this.getEntityManager(), Product.class)
           .and(Product_.prodId, prodId))
           .and(Product_.prodName, prodName))
           .getResultList();

}

It will be better if you write your own SimpleSelectBuilder to handle boilerplate code blocks and increase reusability. For example you need to add like statement to code above.

Managing all the layers, transactions, connection-pools etc will take your time a lot. Instead you may want to consider a middleware to manage all of this for your. In my projects I prefer Spring.