java JPA 标准谓词条件

JPA Criteria Predicate Conditions

提问by J?cob

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

我有以下代码片段用于构建条件构建器 where 条件。

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

回答by bhdrkn

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

首先，您必须考虑以分层方式重构您的应用程序。你至少需要 3 层，DAO、Service 和 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.

所有关于数据库和 JPA 的东西都必须在你的 DAO 层中。并且所有与 json 相关的东西都必须在您的 WebService 层中。您的服务层必须管理 Web 服务和 dao 层之间的事务和通信。

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.

首先让我们谈谈您的 Web 服务层。您的 JSON 对象可能来自 Restful Web 服务。由于几乎所有框架都支持 json 编组/解组，因此手动解析数据传输对象是不明智的。我的意思是，您可能更喜欢声明一个 FieldDto 类并传递它的实例而不是 JSONObject。下面是一个例子FieldDto。这是一个POJO。

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

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

您可以使用 GSON 或 Hymanson 轻松编组/解组到 json。可能您的框架默认使用其中之一来处理 json 转换。

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.

下一层是服务层。在服务层中，您管理您的事务并将您的 DTO 对象转换为您的 DAO 层可以轻松理解的内容。在这种情况下，你的服务层通过fieldDto.getProdId()并fielDto.getProdName()于DAO层。

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

你的最后一层是 DAO 层。首先让我们更改您的方法签名。

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.

这不是它。这段代码还需要改进。在我的一个项目中，我创建了一个 fluent api 来管理所有样板部分。当您开始编写其他 DAO 类时，您会发现某些代码块一遍又一遍地重复。

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

这是一个流畅的 api 示例。您可能想要构建它的版本。

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 likestatement to code above.

如果您编写自己的 SimpleSelectBuilder 来处理样板代码块并提高可重用性会更好。例如，您需要在like上面的代码中添加语句。

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.

管理所有层、事务、连接池等将花费您很多时间。相反，您可能需要考虑一个中间件来为您管理所有这些。在我的项目中，我更喜欢 Spring。