介绍:
一款jpa的动态查询Specification构建思路,利用自定义注解在查询参数上设置查询规则,已实现表在一对一关联的查询,SpecBuilder与MySpec分离,可单独使用MySpec,MySpec中已实现排序规则。
数据表:
package com.ms.entity;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import javax.persistence.*;
import java.io.Serializable;
/**
* @author xiezc
* @since 2022/3/31
*/
@Entity
@DynamicInsert
@DynamicUpdate
@EntityListeners(AuditingEntityListener.class)
@Table(name = "t_admin")
@Data
public class TAdmin implements Serializable {
@Id
@Basic
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Basic
@Column(name = "username")
private String username;
@Basic
@Column(name = "email")
private String email;
@Basic
@Column(name = "mobile")
private String mobile;
}
Specification构建类 :
package com.ms.utils;
import org.apache.commons.lang.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import javax.persistence.criteria.*;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @author xiezc
* @since 2022/3/29
*/
public class MySpec<T> {
private Specification<T> mSpec;
private Map<String, Object> mParams = new HashMap<>();
private List<SortParams> mSortList = new ArrayList<>();
private List<QueryParams> mQueryList = new ArrayList<>();
private Class<T> clazz = null;
public MySpec() {
}
public MySpec(Class<T> clazz) {
this.clazz = clazz;
}
public Class<T> getTClass() {
if (this.clazz != null) {
return this.clazz;
}
return (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
}
public MySpec<T> build(Map<String, Object> params) {
this.build(params, false);
return this;
}
public MySpec<T> build(Map<String, Object> params, boolean isOr) {
if (params == null) {
params = new HashMap<>();
}
this.mParams = params;
// 默认查询参数设置
if (mParams.get("deleted") == null && !isOr) {
mParams.put("deleted", 0);
}
mSortList = new ArrayList<>();
mQueryList = new ArrayList<>();
// 解析参数和比较关系
for (Map.Entry<String, Object> entry : mParams.entrySet()) {
String key = entry.getKey();
Object val = entry.getValue();
// MsLog.info("key = " + key);
// 空值均不处理
if (val == null) {
continue;
}
if (val instanceof String) {
String s = (String) val;
if (s.isEmpty() || s.trim().isEmpty()) {
continue;
}
}
String[] arr = key.split("_");
int len = arr.length;
if (len == 1) {
mQueryList.add(new QueryParams(key, "EQ", entry.getValue()));
continue;
}
if (len != 2) {
continue;
}
// MsLog.info("arr = " + JSON.toJSONString(arr));
// 排序列表
if (isInteger(arr[1])) {
SortParams param = new SortParams(arr[0], Integer.parseInt(arr[1]), (String) entry.getValue());
int i = 0;
boolean added = false;
for (SortParams sp : mSortList) {
if (sp.getIndex() > param.getIndex()) {
mSortList.add(i, param);
added = true;
break;
}
i++;
}
if (!added) {
mSortList.add(param);
}
continue;
}
// MsLog.info("sort = " + JSON.toJSONString(mSortList));
// 保存
mQueryList.add(new QueryParams(arr[0], arr[1], entry.getValue()));
}
// 删除不必要的排序字段
final Class<T> clazz = getTClass();
final Field[] fds = clazz.getDeclaredFields();
boolean hasSortField = false;
boolean hasSortReq = false;
for (SortParams sp : mSortList) {
if (!hasSortReq) {
if (sp.getName().equals("sort")) {
hasSortReq = true;
}
}
boolean isValid = false;
for (Field fd : fds) {
// 判断是否有sort字段
if (!hasSortField) {
if (fd.getName().equals("sort")) {
hasSortField = true;
}
}
// 字段比较
if (fd.getName().equals(sp.getName())) {
isValid = true;
break;
}
}
if (!isValid) {
mSortList.remove(sp);
}
}
// 设置默认排序
if (hasSortField && !hasSortReq) {
mSortList.add(0, new SortParams("sort", -1, "DESC"));
}
// MsLog.info("query: " + JSON.toJSONString(mQueryList));
// MsLog.info("sort: " + JSON.toJSONString(mSortList));
// 解析查询规则
this.mSpec = new Specification<T>() {
private final List<QueryParams> mSearchList = mQueryList;
@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
// 应用查询规则
List<Predicate> ps = new ArrayList<>();
// 逐个处理查询参数
for (QueryParams qp : mSearchList) {
String key = qp.getName();
for (Field fd : fds) {
if (!fd.getName().equals(key) && !key.contains(".")) {
continue;
}
String exp = qp.getExp();
Object val = qp.getVal();
// 等于
if (exp.equalsIgnoreCase("EQ")) {
ps.add(criteriaBuilder.equal(getPath(root, key), val));
break;
}
// 不等于
if (exp.equalsIgnoreCase("NEQ")) {
ps.add(criteriaBuilder.notEqual(getPath(root, key), val));
break;
}
// 模糊匹配字符串,相似则命中
if (exp.equalsIgnoreCase("LIKE")) {
ps.add(criteriaBuilder.like((Expression<String>) getPath(root, key), "%" + val + "%"));
break;
}
// 模糊匹配字符串,不相似则命中
if (exp.equalsIgnoreCase("NLIKE")) {
ps.add(criteriaBuilder.notLike((Expression<String>) getPath(root, key), "%" + val + "%"));
break;
}
// 向左模糊匹配字符串,相似则命中
if (exp.equalsIgnoreCase("LLIKE")) {
ps.add(criteriaBuilder.like((Expression<String>) getPath(root, key), val + "%"));
break;
}
// 向左模糊匹配字符串,不相似则命中
if (exp.equalsIgnoreCase("NLLIKE")) {
ps.add(criteriaBuilder.notLike((Expression<String>) getPath(root, key), val + "%"));
break;
}
// 向右模糊匹配字符串,相似则命中
if (exp.equalsIgnoreCase("RLIKE")) {
ps.add(criteriaBuilder.like((Expression<String>) getPath(root, key), "%" + val));
break;
}
// 向右模糊匹配字符串,不相似则命中
if (exp.equalsIgnoreCase("NRLIKE")) {
ps.add(criteriaBuilder.notLike((Expression<String>) getPath(root, key), "%" + val));
break;
}
// 小于
if (exp.equalsIgnoreCase("LT")) {
ps.add(criteriaBuilder.lessThan((Expression<Comparable>) getPath(root, key), (Comparable) val));
break;
}
// 小于等于
if (exp.equalsIgnoreCase("ELT")) {
ps.add(criteriaBuilder.lessThanOrEqualTo((Expression<Comparable>) getPath(root, key), (Comparable) val));
break;
}
// 大于
if (exp.equalsIgnoreCase("GT")) {
ps.add(criteriaBuilder.greaterThan((Expression<Comparable>) getPath(root, key), (Comparable) val));
break;
}
// 大于等于
if (exp.equalsIgnoreCase("EGT")) {
ps.add(criteriaBuilder.greaterThanOrEqualTo((Expression<Comparable>) getPath(root, key), (Comparable) val));
break;
}
// 日期相等
if (exp.equalsIgnoreCase("DATEEQ")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
Expression<Date> dateFunc = criteriaBuilder.function("FORMAT", Date.class, getPath(root, key), criteriaBuilder.literal("yyyy-MM-dd"));
ps.add(criteriaBuilder.equal(dateFunc, dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期大于
if (exp.equalsIgnoreCase("DATEGT")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
Expression<Date> dateFunc = criteriaBuilder.function("FORMAT", Date.class, getPath(root, key), criteriaBuilder.literal("yyyy-MM-dd"));
ps.add(criteriaBuilder.greaterThan(dateFunc, dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期大于等于
if (exp.equalsIgnoreCase("DATEEGT")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
Expression<Date> dateFunc = criteriaBuilder.function("FORMAT", Date.class, getPath(root, key), criteriaBuilder.literal("yyyy-MM-dd"));
ps.add(criteriaBuilder.greaterThanOrEqualTo(dateFunc, dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期小于
if (exp.equalsIgnoreCase("DATELT")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
Expression<Date> dateFunc = criteriaBuilder.function("FORMAT", Date.class, getPath(root, key), criteriaBuilder.literal("yyyy-MM-dd"));
ps.add(criteriaBuilder.lessThan(dateFunc, dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期小于等于
if (exp.equalsIgnoreCase("DATEELT")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
Expression<Date> dateFunc = criteriaBuilder.function("FORMAT", Date.class, getPath(root, key), criteriaBuilder.literal("yyyy-MM-dd"));
ps.add(criteriaBuilder.lessThanOrEqualTo(dateFunc, dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期时间相等
if (exp.equalsIgnoreCase("DATETIMEEQ")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
ps.add(criteriaBuilder.equal((Expression<Comparable>) getPath(root, key), (Comparable) dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期时间大于
if (exp.equalsIgnoreCase("DATETIMEGT")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
ps.add(criteriaBuilder.greaterThan((Expression<Comparable>) getPath(root, key), (Comparable) dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期时间大于等于
if (exp.equalsIgnoreCase("DATETIMEEGT")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
ps.add(criteriaBuilder.greaterThanOrEqualTo((Expression<Comparable>) getPath(root, key), (Comparable) dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期时间小于
if (exp.equalsIgnoreCase("DATETIMELT")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
ps.add(criteriaBuilder.lessThan((Expression<Comparable>) getPath(root, key), (Comparable) dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// 日期时间小于等于
if (exp.equalsIgnoreCase("DATETIMEELT")) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date dt = fmt.parse((String) val);
if (dt != null) {
// Expression<Date> dateFunc = criteriaBuilder.function("DATE", Date.class, getPath(root, key));
ps.add(criteriaBuilder.lessThanOrEqualTo((Expression<Comparable>) getPath(root, key), (Comparable) dt));
}
} catch (Exception e) {
MsLog.error(e);
}
break;
}
// find_in_set
if (exp.equalsIgnoreCase("INSET")) {
Expression<Integer> findInSetFunc = criteriaBuilder.function("FIND_IN_SET", Integer.class, criteriaBuilder.literal(val), getPath(root, key));
ps.add(criteriaBuilder.greaterThan(findInSetFunc, 0));
break;
}
// 在给定集合中
if (exp.equalsIgnoreCase("IN")) {
Collection c = (Collection) val;
if (c.size() > 0) {
ps.add(getPath(root, key).in((Collection) val));
}
break;
}
// 不在给定集合中
if (exp.equalsIgnoreCase("NOTIN")) {
Collection c = (Collection) val;
if (c.size() > 0) {
ps.add(criteriaBuilder.not(getPath(root, key).in((Collection) val)));
}
break;
}
// 空值判断
if (exp.equalsIgnoreCase("EMPTY")) {
String type = (String) val;
if (type.equalsIgnoreCase("NULL")) {
ps.add(criteriaBuilder.isNull(getPath(root, key)));
} else if (type.equalsIgnoreCase("NOTNULL")) {
ps.add(criteriaBuilder.isNotNull(getPath(root, key)));
}
break;
}
break;
}
}
if (ps.size() == 0) {
return null;
}
Predicate[] pre = new Predicate[ps.size()];
if (isOr) {
criteriaQuery.where(criteriaBuilder.or(ps.toArray(pre)));
} else {
criteriaQuery.where(criteriaBuilder.and(ps.toArray(pre)));
}
return criteriaQuery.getRestriction();
}
};
return this;
}
private Path<?> getPath(Root<T> root, String key) {
if (!key.contains(".")) {
return root.get(key);
}
String[] arr = key.split("\\.");
Path<?> ret = root.get(arr[0]);
for (int i = 1; i < arr.length; i++) {
ret = ret.get(arr[i]);
}
return ret;
}
public long count(JpaSpecificationExecutor<T> dao, Map<String, Object> params) {
this.build(params);
return dao.count(mSpec);
}
public Specification<T> getSpecification() {
return this.mSpec;
}
public Pageable getPageable() {
return PageRequest.of(this.getPageNum(), this.getPageSize());
}
public Pageable getSortPageable() {
return PageRequest.of(this.getPageNum(), this.getPageSize(), this.getSort());
}
public Sort getSort() {
List<Sort.Order> ls = new ArrayList<>();
for (SortParams sp : mSortList) {
String key = sp.getName();
String ord = sp.getDirection();
if (ord.toUpperCase().equals("DESC")) {
ls.add(new Sort.Order(Sort.Direction.DESC, key));
} else {
ls.add(new Sort.Order(Sort.Direction.ASC, key));
}
}
return Sort.by(ls);
}
public List<T> findAll(JpaSpecificationExecutor<T> dao, Map<String, Object> params) {
this.build(params);
return dao.findAll(mSpec);
}
public Page<T> findPage(JpaSpecificationExecutor<T> dao, Map<String, Object> params) {
this.build(params);
return dao.findAll(mSpec, this.getSortPageable());
}
public T findOne(JpaSpecificationExecutor<T> dao, Map<String, Object> params) {
if (params == null) {
params = new HashMap<>();
}
params.put("pageNum", 1);
params.put("pageSize", 1);
this.build(params);
if (this.mQueryList.size() == 0) {
return null;
}
Page<T> res = dao.findAll(mSpec, this.getSortPageable());
if (res == null) {
return null;
}
List<T> data = res.getContent();
if (data.size() == 0) {
return null;
}
return data.get(0);
}
private int getPageNum() {
Object val = mParams.get("pageNum");
if (val == null) return 0;
Integer pageNum = Integer.parseInt(val.toString());
int num = 0;
if (pageNum != null) {
num = pageNum.intValue() - 1;
if (num < 0) {
num = 0;
}
}
return num;
}
private int getPageSize() {
Object val = mParams.get("pageSize");
if (val == null) return 10;
Integer pageSize = Integer.parseInt(val.toString());
int size = 10;
if (pageSize != null) {
size = pageSize.intValue();
if (size <= 0) {
size = 10;
}
}
return size;
}
private boolean isInteger(String s) {
if (StringUtils.isBlank(s)) return false;
int len = s.length();
for (int i = 0; i < len; i++) {
char ch = s.charAt(i);
if (ch == '.') {
return false;
}
if (!Character.isDigit(ch)) {
return false;
}
}
return true;
}
private class QueryParams implements Serializable {
private String name;
private String exp;
private Object val;
public QueryParams(String name, String exp, Object val) {
this.name = name;
this.exp = exp;
this.val = val;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getExp() {
return exp;
}
public void setExp(String exp) {
this.exp = exp;
}
public Object getVal() {
return val;
}
public void setVal(Object val) {
this.val = val;
}
}
private class SortParams implements Serializable {
private int index;
private String name;
private String direction;
public SortParams(String name, int index, String direction) {
this.name = name;
this.index = index;
this.direction = direction;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
public String getDirection() {
return direction;
}
public void setDirection(String direction) {
this.direction = direction;
}
}
}
自定义注解:
package com.ms.annotations;
import java.lang.annotation.*;
/**
* @author xiezc
* @since 2022/5/15
*/
@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface QueryName {
String value();
}
package com.ms.annotations;
import com.ms.enums.QueryType;
import java.lang.annotation.*;
/**
* @author xiezc
* @since 2022/5/15
*/
@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface QueryColumn {
QueryType type() default QueryType.EQ;
}
枚举类:
package com.ms.enums;
/**
* @author xiezc
* @since 2022/5/15
*/
public enum QueryType {
EQ("EQ"),
NEQ("NEQ"),
LIKE("LIKE"),
NLIKE("NLIKE"),
LLIKE("LLIKE"),
NLLIKE("NLLIKE"),
RLIKE("RLIKE"),
NRLIKE("NRLIKE"),
LT("LT"),
ELT("ELT"),
GT("GT"),
EGT("EGT"),
DATEEQ("DATEEQ"),
DATEGT("DATEGT"),
DATEEGT("DATEEGT"),
DATELT("DATELT"),
DATEELT("DATEELT"),
DATETIMEEQ("DATETIMEEQ"),
DATETIMEGT("DATETIMEGT"),
DATETIMEEGT("DATETIMEEGT"),
DATETIMELT("DATETIMELT"),
DATETIMEELT("DATETIMEELT"),
INSET("INSET"),
IN("IN"),
NOTIN("NOTIN"),
EMPTY("EMPTY"),
ASC("ASC"),
DESC("DESC");
private final String name;
private QueryType(String name) {
this.name = name;
}
private String GetName() {
return this.name;
}
}
dto:
package com.ms.dto;
import com.ms.annotations.QueryColumn;
import com.ms.annotations.QueryName;
import com.ms.annotations.QueryPojo;
import com.ms.entity.TAdmin;
import com.ms.enums.QueryType;
import lombok.Data;
/**
* @author xiezc
* @since 2022/5/14
*/
@Data
@QueryPojo(queryFor = TAdmin.class)
public class PageAdminDto {
@QueryColumn(type = QueryType.LIKE)
@QueryName("username")
private String username;
private Integer pageNum;
private Integer pageSize;
}
查询:
public Page<PageAdminVo> pageAdmin(PageAdminDto dto) throws Exception {
if (dto == null) dto = new PageAdminDto();
MySpec<TAdmin> spec = new SpecBuilder<>(TAdmin.class).build(dto).getParams();
return _dao.findAll(spec.getSpecification(), spec.getSortPageable());
}