Spring Data JPA之JpaSpecificationExecutor复杂动态查询实例

本文接SpringDataJPA查询方法那些事继续学习JpaSpecificationExecutor复杂动态查询。

【1】回顾简单查询

简单动态查询实例如下：

 public Page<User> test(){
        Sort sort = new Sort(Sort.Direction.DESC,"id");
        int page = 1;
        int pageSize = 5;
        Pageable pageable = PageRequest.of(page,pageSize,sort);

        //通常使用 Specification 的匿名内部类
        Specification<User> specification = new Specification<User>() {
            /**
             * @param *root: 代表查询的实体类.
             * @param query: 可以从中可到 Root 对象, 即告知 JPA Criteria 查询要查询哪一个实体类. 还可以
             * 来添加查询条件, 还可以结合 EntityManager 对象得到最终查询的 TypedQuery 对象.
             * @param *cb: CriteriaBuilder 对象. 用于创建 Criteria 相关对象的工厂. 当然可以从中获取到 Predicate 对象
             * @return: *Predicate 类型, 代表一个查询条件.
             */
            @Override
            public Predicate toPredicate(Root<User> root,
                                         CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path id = root.get("id");
                Predicate predicateId = cb.gt(id,5);
                return predicateId ;
            }
        };
        Page<User> userPage = userRepository.findAll(specification,  pageable);
        return userPage;
    }

只有一个属性，一个查询条件。

后台打印SQL如下所示：

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_,
 user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
 where user0_.id>5 order by user0_.id desc limit ?, ?
 
Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ where user0_.id>5

---

【2】添加多个查询条件

比如这里再添加查询条件：id=5 and email="aa@qq.com"。

核心方法如下所示：

Specification<User> specification = new Specification<User>() {
     @Override
     public Predicate toPredicate(Root<User> root,
                                  CriteriaQuery<?> query, CriteriaBuilder cb) {
         Path id = root.get("id");
         Predicate predicateId = cb.gt(id,5);
         Path<User> email = root.get("email");
         Predicate predicateEmail = cb.equal(email, "aa@qq.com");
         Predicate endPredicate = cb.and(predicateId, predicateEmail);
         return endPredicate;
     }
 };

这里需要用到CriteriaBuilder提供的几个方法：

/**
     * Create a conjunction of the given boolean expressions.
     * @param x  boolean expression
     * @param y  boolean expression
     * @return and predicate
     */
    Predicate and(Expression<Boolean> x, Expression<Boolean> y);
    
    /**
     * Create a conjunction of the given restriction predicates.
     * A conjunction of zero predicates is true.
     * @param restrictions  zero or more restriction predicates
     * @return and predicate
     */
    Predicate and(Predicate... restrictions);

    /**
     * Create a disjunction of the given boolean expressions.
     * @param x  boolean expression
     * @param y  boolean expression
     * @return or predicate
     */
    Predicate or(Expression<Boolean> x, Expression<Boolean> y);

    /**
     * Create a disjunction of the given restriction predicates.
     * A disjunction of zero predicates is false.
     * @param restrictions  zero or more restriction predicates
     * @return or predicate
     */
    Predicate or(Predicate... restrictions);

连词And和OR，其中每种连词又有两种参数。 Predicate and(Predicate... restrictions);表示不定数参数Predicate使用And连接起来，通常你可以传入多个Predicate参数，但是建议传入一个数组。

修改上面核心方法如下：

Specification<User> specification = new Specification<User>() {
    @Override
    public Predicate toPredicate(Root<User> root,
                                 CriteriaQuery<?> query, CriteriaBuilder cb) {
        Path id = root.get("id");
        List<Predicate> predicates=new ArrayList<Predicate>();
        Predicate predicateId = cb.gt(id,5);
        predicates.add(predicateId);
        Path<User> email = root.get("email");
        Predicate predicateEmail = cb.equal(email, "aa@qq.com");
        predicates.add(predicateEmail);
        Predicate endPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
//                Predicate endPredicate = cb.and((Predicate[]) predicates.toArray());
        return endPredicate;
    }
};

这里toArray有两种方式：

 Object[] toArray();
 <T> T[] toArray(T[] a);
 
  * 相同点：都是作为数组和collection之间的桥梁；
  * 不同点：toArray()每次都会分配一个新的数组空间，toArray(T[] a);则在某些情况下节省分配成本。

即完整方法如下：

public Page<User> test17(){
     Sort sort = new Sort(Sort.Direction.DESC,"id");
     int page = 1;
     int pageSize = 5;
     Pageable pageable = PageRequest.of(page,pageSize,sort);

     Specification<User> specification = new Specification<User>() {
         @Override
         public Predicate toPredicate(Root<User> root,
                                      CriteriaQuery<?> query, CriteriaBuilder cb) {
             Path id = root.get("id");
             List<Predicate> predicates=new ArrayList<Predicate>();
             Predicate predicateId = cb.gt(id,5);
             predicates.add(predicateId);
             Path<User> email = root.get("email");
             Predicate predicateEmail = cb.equal(email, "aa@qq.com");
             predicates.add(predicateEmail);
             Predicate endPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
//                Predicate endPredicate = cb.and((Predicate[]) predicates.toArray());
             return endPredicate;
         }
     };
     Page<User> userPage = userRepository.findAll(specification,  pageable);
     return userPage;
 }

此时后台SQL打印如下：

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
where user0_.id>5 and user0_.email=? order by user0_.id desc limit ?, ?

Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ where user0_.id>5 and user0_.email=?

---

【3】CriteriaBuilder.conjunction()和CriteriaBuilder.disjunction()

如果没有查询条件呢？即Predicate为null，CriteriaBuilder同样提供了两个方法conjunction()和disjunction()。

方法说明如下;

/**
 * Create a conjunction (with zero conjuncts).
 * A conjunction with zero conjuncts is true.
 * @return and predicate
 */
Predicate conjunction();

/**
 * Create a disjunction (with zero disjuncts).
 * A disjunction with zero disjuncts is false.
 * @return or predicate
 */
Predicate disjunction();

---

① CriteriaBuilder.conjunction()

修改方法如下所示：

public Page<User> test(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);

    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> query, CriteriaBuilder cb) {
            return cb.conjunction();
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

后台SQL打印如下：

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
where 1=1 order by user0_.id desc limit ?, ?

//这里where子句为 where 1=1

Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ where 1=1

---

② CriteriaBuilder.disjunction()

修改方法如下所示：

public Page<User> test18(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);

    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> query, CriteriaBuilder cb) {
            return cb.disjunction();
//                return cb.conjunction();
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

此时SQL打印如下：

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
where 0=1 order by user0_.id desc limit ?, ?

//这里where子句为 where 0=1，肯定不会返回值了

Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ where 0=1

---

【4】Path应用之对象属性为对象

Path是个什么？有三个关键词：对象引用；属性；路径。

源码如下所示：

/**
 * Represents a simple or compound attribute path from a 
 * bound type or collection, and is a "primitive" expression.
 *表示来自绑定类型或集合的简单或复合属性路径，并且是“原语”表达式。

 * @param <X>  the type referenced by the path
 *
 * @since Java Persistence 2.0
 */
public interface Path<X> extends Expression<X> {

    /** 
     * Return the bindable object that corresponds to the path expression.
     */
    Bindable<X> getModel(); 
    
    /**
     *  Return the parent "node" in the path or null if no parent.
     */
    Path<?> getParentPath();
	
    /**
     *  Create a path corresponding to the referenced single-valued attribute.
     */
    <Y> Path<Y> get(SingularAttribute<? super X, Y> attribute);

    /**
     *  Create a path corresponding to the referenced collection-valued attribute.
     */
    <E, C extends java.util.Collection<E>> Expression<C> get(PluralAttribute<X, C, E> collection);

    /**
     *  Create a path corresponding to the referenced map-valued attribute.
     */
    <K, V, M extends java.util.Map<K, V>> Expression<M> get(MapAttribute<X, K, V> map);

    /**
     *  Create an expression corresponding to the type of the path.
     *  @return expression corresponding to the type of the path
     */
    Expression<Class<? extends X>> type();


    //String-based:
    /**
     *  Create a path corresponding to the referenced attribute.
     */
    <Y> Path<Y> get(String attributeName);
}

上面使用的属性都是对象的简单属性，如user.id，user.email。但是如果属性为user.address.id呢？

修改方法如下所示：

public Page<User> test(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);

    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path<Object> addressPath = root.get("address");
            //这里再次获取addressPath 中的属性
            Path<Object> id = addressPath.get("id");
            Predicate predicate = cb.equal(id, 1);
            return predicate;
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

此时后台SQL打印如下：

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_,
 user0_.email as email3_1_, user0_.last_name as last_nam4_1_ 
 from tb_user user0_ 
 where user0_.address_id=1 order by user0_.id desc limit ?, ?
 //where 子句 条件为user-address的外键列 address_id
 
Hibernate: select count(user0_.id) as col_0_0_ 
from tb_user user0_ 
where user0_.address_id=1

上面是我们手动处理–先获取addressPath，再获取其idPath，实际应用中通常处理如下：

// nested path translate, 如Task的名为"user.name"的filedName, 转换为Task.user.name属性
 String[] names = StringUtils.split("address.id", ".");
 Path expression = root.get(names[0]);
   for (int i = 1; i < names.length; i++) {
       expression = expression.get(names[i]);
   }

---

该实例有个特殊之处在于获取user.address时，外键列address_id对应address类的主键id。故而直接使用addressPath查询时后台打印SQL同上。但是如果fieldName为address.city呢？

修改方法如下所示：

public Page<User> test19(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);

    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path<Object> addressPath = root.get("address");
            Path<Object> expression = addressPath.get("city");
            Predicate predicate = cb.equal(expression, "beijing");
            return predicate;
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

后台SQL打印如下：

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_
from tb_user user0_ 
cross join tb_address address1_ 
where user0_.address_id=address1_.id and address1_.city=? order by user0_.id desc limit ?, ?
 
Hibernate: select count(user0_.id) as col_0_0_ 
from tb_user user0_ 
cross join tb_address address1_ 
where user0_.address_id=address1_.id and address1_.city=?

这里使用了cross join 参考博文：MySQL多表连接查询。

---

【5】CriteriaQuery实现多条件组合

如下图所示，常见SQL语法CriteriaQuery都提供了实现：

---

方法实例如下：

public Page<User> test(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);

    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path id = root.get("id");
            List<Predicate> predicates=new ArrayList<Predicate>();
            Predicate predicateId = cb.gt(id,5);
            predicates.add(predicateId);
            Path<User> email = root.get("email");
            Predicate predicateEmail = cb.equal(email, "aa@qq.com");
            predicates.add(predicateEmail);
            Predicate endPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
//                Predicate endPredicate = cb.and((Predicate[]) predicates.toArray());
            //添加where条件
            query.where(endPredicate);
            // //指定查询项，select后面的东西
            query.multiselect(id,email,cb.count(id));
            //分组
            query.groupBy(id);
            //排序
            query.orderBy(cb.asc(id));
            //筛选
            query.having(cb.greaterThan(id,0));
            //获取最终的Predicate
            Predicate restriction = query.getRestriction();
            return restriction;
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

后台SQL打印如下：

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_
 where user0_.id>5 and user0_.email=? 
 group by user0_.id 
 having user0_.id>0 
 order by user0_.id desc limit ?, ?
// 这里需要注意，排序根据pageable
 
Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ 
where user0_.id>5 and user0_.email=? group by user0_.id having user0_.id>0

---

【6】CriteriaQuery与entityManager整合

前面提到过CriteriaQuery可以结合 EntityManager 对象得到最终查询的 TypedQuery 对象。

实例如下：

@PersistenceContext
private EntityManager entityManager;

public List<User> test22(){
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //User指定了查询结果返回至自定义对象
        CriteriaQuery<User> query = cb.createQuery(User.class);
        Root<User> root = query.from(User.class);
        Path id = root.get("id");
        List<Predicate> predicates=new ArrayList<Predicate>();
        Predicate predicateId = cb.equal(id,1);
        predicates.add(predicateId);
        Path<User> email = root.get("email");
        Predicate predicateEmail = cb.equal(email, "aa@qq.com");
        predicates.add(predicateEmail);
        Predicate endPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
        //添加where条件
        query.where(endPredicate);
         //指定查询项，select后面的东西
//        query.multiselect(id,email);
        //分组
        query.groupBy(id);
        //排序
        query.orderBy(cb.asc(id));
        //筛选
        query.having(cb.greaterThan(id,0));
        TypedQuery<User> q = entityManager.createQuery(query);
        List<User> result = q.getResultList();
        for (User user : result) {
            //打印查询结果
            System.out.println(user.toString());
        }
        return result;
    }

后台SQL打印如下：

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_,
 user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
 where user0_.id=1 and user0_.email=? 
 group by user0_.id 
 having user0_.id>0 
 order by user0_.id asc

//关联查询Address 
Hibernate: select address0_.id as id1_0_0_, address0_.city as city2_0_0_, address0_.province as province3_0_0_
from tb_address address0_ where address0_.id=?

博文项目代码下载地址：GitHub项目地址