【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=?
【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项目地址
