本文接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项目地址