正文
JPA使用Specification构建动态查询
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
封装Specification查询条件，在Spring Data JPA 2.0以前使用 Specifications 这个辅助类来操作where、not、and和or连接，在2.0版本以后这个类会被剔除，可以直接使用 Specification 自身对象来操作where多条件连接。（以下展示单表多条件查询）
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.domain.Specifications;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
* SQL拼接工具类
*
* @author yanhu
* @date 2018/8/9
*/
public class SpecificationFactory {
private Specifications specs;
private SpecificationFactory(Specification specs) {
this.specs = Specifications.where(specs);
}
public static SpecificationFactory wheres(Specification spec) {
return new SpecificationFactory(spec);
}
public SpecificationFactory and(Specification other) {
this.specs.and(other);
return this;
}
public SpecificationFactory or(Specification other) {
this.specs.or(other);
return this;
}
public Specifications build() {
return this.specs;
}
/**
* 单where条件
*
* @param p
* @return
*/
public static Specification where(Predication p) {
List<Predication> ps = new ArrayList<>();
ps.add(p);
return where(ps);
}
/**
* 多where条件and连接
*
* @param ps
* @param <T>
* @return
*/
public static <T> Specification<T> where(List<Predication> ps) {
return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) ->
builder.and(getPredicateList(root, builder, ps));
}
/**
* 多where条件or连接
*
* @param ps
* @param <T>
* @return
*/
public static <T> Specification<T> or(List<Predication> ps) {
return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) ->
builder.or(getPredicateList(root, builder, ps));
}
/**
* 获取查询条件数组
*
* @param root
* @param builder
* @param ps
* @return
*/
private static Predicate[] getPredicateList(Root<?> root, CriteriaBuilder builder, List<Predication> ps) {
List<Predicate> predicateList = new ArrayList<>();
ps.forEach(p -> {
Predicate predicate = buildPredicate(builder, root.get(p.getName()), p);
predicateList.add(predicate);
});
return predicateList.toArray(new Predicate[predicateList.size()]);
}
/**
* 选取查询方式
*
* @param cb
* @param path
* @param p
* @return
*/
private static Predicate buildPredicate(CriteriaBuilder cb, Path path, Predication p) {
Predicate predicate;
switch (p.getOperator()) {
case LIKE:
predicate = cb.like(path, p.getValue().toString());
break;
case EQ:
predicate = cb.equal(path, p.getValue());
break;
case NOTEQ:
predicate = cb.notEqual(path, p.getValue());
break;
case GT:
predicate = cb.greaterThan(path, (Comparable) p.getValue());
break;
case GTEQ:
predicate = cb.greaterThanOrEqualTo(path, (Comparable) p.getValue());
break;
case LT:
predicate = cb.lessThan(path, (Comparable) p.getValue());
break;
case LTEQ:
predicate = cb.lessThanOrEqualTo(path, (Comparable) p.getValue());
break;
case NULL:
predicate = cb.isNull(path);
break;
case NOTNULL:
predicate = cb.isNotNull(path);
break;
case IN:
predicate = getIn(path, p.getValue());
break;
case NOTIN:
predicate = getIn(path, p.getValue()).not();
break;
default:
throw new IllegalArgumentException("非法的操作符");
}
return predicate;
}
/**
* 创建in操作
*
* @param path
* @param value
* @param <T>
* @return
*/
private static <T> Predicate getIn(Path path, T value) {
if (value instanceof Object[]) {
return path.in((Object[]) value);
} else if (value instanceof Collection) {
return path.in((Collection) value);
} else {
throw new IllegalArgumentException("非法的IN操作");
}
}
/***********************************************单where条件查询********************************************************/
// like
public static Specification like(String name, String value) {
return (root, query, cb) ->
cb.like(root.get(name), value);
}
// =
public static Specification equal(String name, Object value) {
return (root, query, cb) ->
cb.equal(root.get(name), value);
}
// !=
public static Specification notEqual(String name, Object value) {
return (root, query, cb) ->
cb.notEqual(root.get(name), value);
}
// >
public static Specification gt(String name, Object value) {
return (root, query, cb) ->
cb.greaterThan(root.get(name), (Comparable) value);
}
// >=
public static Specification gtEqual(String name, Object value) {
return (root, query, cb) ->
cb.greaterThanOrEqualTo(root.get(name), (Comparable) value);
}
// <
public static Specification lt(String name, Object value) {
return (root, query, cb) ->
cb.lessThan(root.get(name), (Comparable) value);
}
// <=
public static Specification ltEqual(String name, Object value) {
return (root, query, cb) ->
cb.lessThanOrEqualTo(root.get(name), (Comparable) value);
}
// is null
public static Specification isNull(String name) {
return (root, query, cb) ->
cb.isNull(root.get(name));
}
// is not null
public static Specification notNull(String name) {
return (root, query, cb) ->
cb.isNotNull(root.get(name));
}
// in
public static Specification in(String name, Object value) {
return (root, query, cb) ->
root.get(name).in(value);
}
// not in
public static Specification notIn(String name, Object value) {
return (root, query, cb) ->
root.get(name).in(value).not();
}
}
import lombok.Data;
@Data
public class Predication<T> {
private OP operator;
private String name;
private T value;
private Predication() {
}
public static <T> Predication<T> get(OP operator, String name, T value) {
return new Builder().operator(operator)
.name(name).value(value).build();
}
public static class Builder<T> {
private Predication p;
public Builder() {
this.p = new Predication();
}
public Builder operator(OP op) {
this.p.operator = op;
return this;
}
public Builder name(String name) {
this.p.name = name;
return this;
}
public Builder value(T value) {
this.p.value = value;
return this;
}
public <T> Predication<T> build() {
return this.p;
}
}
}public enum OP {
// like
LIKE,
// =
EQ,
// !=
NOTEQ,
// >
GT,
// >=
GTEQ,
// <
LT,
// <=
LTEQ,
// is null
NULL,
// is not null
NOTNULL,
// in
IN,
// not in
NOTIN,
AND,
OR,
NOT
}具体使用
Sort sort = new Sort(Sort.Direction.DESC, "id");
Pageable pageable = new PageRequest(number, size, sort);
Specification spec;
/***********************单条件查询*************************/
// 方式1
Predication p = Predication.get(OP.EQ, "name", name);
spec = SpecificationFactory.where(p);
// 方式2
spec = SpecificationFactory.equal("name", name);
/***********************多条件查询*************************/
List<Predication> ps = new ArrayList<>();
ps.add(Predication.get(OP.LIKE, "name", name));
ps.add(Predication.get(OP.EQ, "age", age));
// 全and连接
spec = SpecificationFactory.where(ps);
// 全or连接
spec = SpecificationFactory.or(ps);
// and和or混合连接
// where name like ?1 and age = ?2
// and name like ?3 and age = ?4
// or name like ?5 or age = ?6
// 工具类实现
spec = SpecificationFactory.wheres(SpecificationFactory.where(ps))
.and(SpecificationFactory.where(ps))
.or(SpecificationFactory.or(ps))
.build();
// JPA API辅助类实现
spec = Specifications.where(SpecificationFactory.where(ps))
.and(SpecificationFactory.where(ps))
.or(SpecificationFactory.where(ps));
// where name like ?1 and age = ?2
// and ( name like ?3 or age = ?4 )
// 工具类实现
spec = SpecificationFactory.wheres(SpecificationFactory.where(ps))
.and(SpecificationFactory.or(ps))
.build();
// JPA API辅助类实现
spec = Specifications.where(SpecificationFactory.where(ps))
.and(SpecificationFactory.or(ps));
Page<ConsultChat> chatPage = consultChatDao.findAll(spec, pageable);
