无聊写了个JPA动态查询的工具方法,注意!仅支持普通查询。不说废话,以下3个类即可实现:
1. JPA操作类型枚举类:
/**
* @Description: JPA操作类型枚举
* @Author: caijun
*/
public enum OperationType {
EQUAL, // 等于
NOTEQUAL, // 不等于
LIKE, // 包含字符串(全like查询value不拼接%,前后like拼接%)
NOTLIKE, // 不包含字符串(全like查询value不拼接%,前后like拼接%)
IN, // 集合中
NOTIN, // 不在集合中
NULL, // 为空
NOTNULL, // 不为空
LESSTHANOREQUALTO, // 小于等于(支持Number、Date、String)
LESSTHAN, // 小于(支持Number、Date、String)
GREATERTHANOREQUALTO, // 大于等于(支持Number、Date、String)
GREATERTHAN, // 大于(支持Number、Date、String)
TRUE, // 为真(boolean类型)
FALSE // 不为真(boolean类型)
}
2. 动态查询参数封装类:
/**
* @Description: 动态查询参数封装类
* @Author: caijun
*/
public class QueryParam {
// 实体类字段名称
private String fieldName;
// 操作类型枚举
private OperationType operationType;
// 查询值(包装数据类型)
private Object value;
// 查询连接符(and或or 默认and)
private String connector = "and";
/**
* 构造函数
*/
public QueryParam() {
}
/**
* 构造函数
*
* @param: [fieldName, operationType, value, connector]
*/
public QueryParam(String fieldName, OperationType operationType, Object value, String connector) {
super();
this.fieldName = fieldName;
this.operationType = operationType;
this.value = value;
this.connector = connector;
}
/**
* 构造函数
*
* @param: [fieldName, operationType, value]
*/
public QueryParam(String fieldName, OperationType operationType, Object value) {
super();
this.fieldName = fieldName;
this.operationType = operationType;
this.value = value;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public OperationType getOperationType() {
return operationType;
}
public void setOperationType(OperationType operationType) {
this.operationType = operationType;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
public String getConnector() {
return connector;
}
public void setConnector(String connector) {
this.connector = connector;
}
}
3. 普通JPA动态查询工具类:
/**
* @Description: 普通JPA动态查询工具方法
* @Author: caijun
*/
public class QueryUtils {
/**
* 构建分页查询对象(统一排序方向)
*
* @param pageNum 当前页码
* @param pageSize 每页条数
* @param direction 排序规则
* @param properties 排序属性
* @return
*/
public static PageRequest buildPageRequest(int pageNum, int pageSize, String direction, String[] properties) {
Sort sort;
if (properties == null || properties.length == 0) {
return new PageRequest(pageNum - 1, pageSize);
} else if (direction != null) {
if (Direction.ASC.equals(direction)) {
sort = new Sort(Direction.ASC, properties);
} else {
sort = new Sort(Direction.DESC, properties);
}
return new PageRequest(pageNum - 1, pageSize, sort);
} else {
sort = new Sort(Direction.ASC, properties);
return new PageRequest(pageNum - 1, pageSize, sort);
}
}
/**
* 构建分页查询对象(默认排序规则)
*
* @param pageNum 当前页码
* @param pageSize 每页条数
* @param properties 排序属性
* @return
*/
public static PageRequest buildPageRequest(int pageNum, int pageSize, String[] properties) {
return buildPageRequest(pageNum, pageSize, null, properties);
}
/**
* 构建分页查询对象(独立排序方向)
*
* @param pageNum 当前页码
* @param pageSize 每页条数
* @param directions 排序规则和属性(多个)(其中map的key为排序规则,value为多个属性)
* @return
*/
public static PageRequest buildPageRequest(int pageNum, int pageSize, Map<Direction, String[]> directions) {
Sort sort = null;
if (directions == null || directions.size() == 0) {
return new PageRequest(pageNum - 1, pageSize);
} else {
Set<Direction> keySet = directions.keySet();
for (Direction direction : keySet) {
String[] strings = directions.get(direction);
Direction directionType = Direction.ASC;
if (Direction.DESC.equals(direction)) {
directionType = Direction.DESC;
}
if (sort == null) {
sort = new Sort(directionType, strings);
} else {
sort.and(new Sort(directionType, strings));
}
}
return new PageRequest(pageNum - 1, pageSize, sort);
}
}
/**
* 构建动态查询条件
*
* @param params 查询条件参数封装列表
* @param <T>
* @return
*/
public static <T> Specification<T> buildPredicate(final List<QueryParam> params) {
return new Specification<T>() {
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> andPredicates = new ArrayList<>();
List<Predicate> orPredicates = new ArrayList<>();
for (QueryParam queryParam : params) {
OperationType operationType = queryParam.getOperationType();
Object value = queryParam.getValue();
Predicate predicateRecord = null;
switch (operationType) {
case EQUAL:
predicateRecord = cb.equal(root.get(queryParam.getFieldName()), value);
break;
case NOTEQUAL:
predicateRecord = cb.notEqual(root.get(queryParam.getFieldName()), value);
break;
case LIKE:
String searchWord = (String) value;
if (searchWord.indexOf("%") == -1) {
searchWord = "%" + searchWord + "%";
}
predicateRecord = cb.like(root.get(queryParam.getFieldName()).as(String.class), searchWord);
break;
case NOTLIKE:
String searchWord1 = (String) value;
if (searchWord1.indexOf("%") == -1) {
searchWord1 = "%" + searchWord1 + "%";
}
predicateRecord = cb.notLike(root.get(queryParam.getFieldName()).as(String.class), searchWord1);
break;
case IN:
In<Object> in = cb.in(root.get(queryParam.getFieldName()));
if (value instanceof List) {
for (Object val : (List) value) {
in.value(val);
}
} else if (value instanceof Object[]) {
for (Object val : (Object[]) value) {
in.value(val);
}
}
predicateRecord = in;
break;
case NOTIN:
In<Object> notIn = cb.in(root.get(queryParam.getFieldName()));
if (value instanceof List) {
for (Object val : (List) value) {
notIn.value(val);
}
} else if (value instanceof Object[]) {
for (Object val : (Object[]) value) {
notIn.value(val);
}
}
predicateRecord = cb.not(notIn);
break;
case NULL:
predicateRecord = cb.isNull(root.get(queryParam.getFieldName()));
break;
case NOTNULL:
predicateRecord = cb.isNotNull(root.get(queryParam.getFieldName()));
break;
case LESSTHANOREQUALTO:
if (value instanceof Number) {
predicateRecord = cb.le(root.get(queryParam.getFieldName()).as(Number.class), (Number) value);
} else if (value instanceof String) {
predicateRecord = cb.lessThanOrEqualTo(root.get(queryParam.getFieldName()).as(String.class), (String) value);
} else if (value instanceof Date) {
predicateRecord = cb.lessThanOrEqualTo(root.get(queryParam.getFieldName()).as(Date.class), (Date) value);
}
break;
case LESSTHAN:
if (value instanceof Number) {
predicateRecord = cb.lt(root.get(queryParam.getFieldName()).as(Number.class), (Number) value);
} else if (value instanceof String) {
predicateRecord = cb.lessThan(root.get(queryParam.getFieldName()).as(String.class), (String) value);
} else if (value instanceof Date) {
predicateRecord = cb.lessThan(root.get(queryParam.getFieldName()).as(Date.class), (Date) value);
}
break;
case GREATERTHANOREQUALTO:
if (value instanceof Number) {
predicateRecord = cb.ge(root.get(queryParam.getFieldName()).as(Number.class), (Number) value);
} else if (value instanceof String) {
predicateRecord = cb.greaterThanOrEqualTo(root.get(queryParam.getFieldName()).as(String.class), (String) value);
} else if (value instanceof Date) {
predicateRecord = cb.greaterThanOrEqualTo(root.get(queryParam.getFieldName()).as(Date.class), (Date) value);
}
break;
case GREATERTHAN:
if (value instanceof Number) {
predicateRecord = cb.gt(root.get(queryParam.getFieldName()).as(Number.class), (Number) value);
} else if (value instanceof String) {
predicateRecord = cb.greaterThan(root.get(queryParam.getFieldName()).as(String.class), (String) value);
} else if (value instanceof Date) {
predicateRecord = cb.greaterThan(root.get(queryParam.getFieldName()).as(Date.class), (Date) value);
}
break;
case TRUE:
predicateRecord = cb.isTrue(root.get(queryParam.getFieldName()));
break;
case FALSE:
predicateRecord = cb.isFalse(root.get(queryParam.getFieldName()));
break;
default:
break;
}
if ("or".equalsIgnoreCase(queryParam.getConnector())) {
predicateRecord = cb.or(predicateRecord);
orPredicates.add(predicateRecord);
} else {
andPredicates.add(predicateRecord);
}
}
Predicate and = cb.and(andPredicates.toArray(new Predicate[andPredicates.size()]));
Predicate or = cb.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
if (and.getExpressions().size() > 0 && or.getExpressions().size() > 0) {
return cb.and(or, and);
} else if (and.getExpressions().size() > 0 && or.getExpressions().size() <= 0) {
return and;
} else if (and.getExpressions().size() <= 0 && or.getExpressions().size() > 0) {
return or;
} else {
List<Predicate> empty = new ArrayList<>();
return cb.and(empty.toArray(new Predicate[]{}));
}
}
};
}
}
4. 示例:
String name = "name";
Integer id = 111;
int pageNum = 1;
int pageSize = 20;
// 分页参数以及排序规则
String[] sortProperties = {"createTime"};
Pageable pageable = QueryUtils.buildPageRequest(pageNum, pageSize, sortProperties);
// 字段查询条件
List<QueryParam> params = new ArrayList<>();
QueryParam param = new QueryParam("name", OperationType.LIKE, name);
QueryParam param2 = new QueryParam("id", OperationType.EQUAL, id);
params.add(param);
params.add(param2);
// 调用JpaSpecificationExecutor<Entity>接口查询方法查询
Page<Entity> entities = entityRepository.findAll(QueryUtils.buildPredicate(params), pageable);
有人问我连表复杂查询怎么办?我的建议还是写纯SQL吧。。。