需要场景:
不论业务如许简单，零碎架构如许简单，最终都是对各类数据库的增删改查，现阶段研发的协同研发平台采纳微服务架构，ORM层采纳 spring-boot-starter-data-jpa, 比方， 系统管理服务 中的 用户治理 须要反对对用户的依照多个不同字段组合查问，前端的页面往往是提供一个查问表单，如 username,gender,birthdate，age 等，遵循 restful 约定，后端提供的接口通常是 Get 申请类型的，为了适配前端的多参数组合查问申请，最先想到的是这样的：
@RestController("/user")
public class UserController{@ApiOperation(value = "查问用户列表")
@GetMapping
public PageQueryResponseData<UserVO> list(@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "25") Integer limit,
@RequestParam(defaultValue = "")
String username,
@RequestParam(defaultValue = "")
String gender,
@RequestParam(defaultValue = "")
String birthdate,
@RequestParam(defaultValue = "")
String age, ) {PageQueryResponseData<UserVO> result = userService.listToVO(page, limit, params);
return result;
}
}这样带来的局限性：
- 没有扩展性；
service层充斥着大量的if/else代码，目标仅仅是为了聚合查问条件- 各个参数的查问运算符没有形容，比方
username对应的是数据库中的like还是equal查问
一种解决方案：
- 定义一个
bean组合所有的可能的查问条件，接口中间接通过bean接管前端的参数，这样接口简化成这样：
@ApiOperation(value = "查问用户列表")
@ApiImplicitParams({@ApiImplicitParam(name = "page", value = "第几页", required = true, dataType = "Integer"),
@ApiImplicitParam(name = "limit", value = "每一页显示条数", required = true, dataType = "Integer")
})
@LogAnnotation(isSaveRequestData = true, isSaveResponseData = true)
@GetMapping
public PageQueryResponseData<UserVO> list(@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "25") Integer limit,
@ModelAttribute DefaultUserQryWrapper params) {PageQueryResponseData<UserVO> result = userService.listToVO(page, limit, params);
return result;
}- 封装的查问
bean:DefaultUserQryWrapper.java
@Data
@ApiModel(value="用户查问封装，默认分页，page,limit")
@Builder
public final class DefaultUserQryWrapper implements BaseQryWrapperInterface {
private static final long serialVersionUID = 1543383274516194973L;
@Tolerate
public DefaultUserQryWrapper(){}
@QueryCriteria(sqlOperator = SqlOperator.ge)
@OrderBy(direction = DirectionEnum.DESCENDING)
private Long id;
@QueryCriteria(sqlOperator = SqlOperator.in)
private Set<String> grade;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@ApiModelProperty(value="出生日期（完结）")
@QueryCriteria(sqlOperator = SqlOperator.betweenAndTo,column = "birthday")
private Date birthdayTo;
@ApiModelProperty(value="部门 Id")
@QueryCriteria(sqlOperator = SqlOperator.in)
private Set<Long> deptId;
@ApiModelProperty(value="用户名查问")
@QueryCriteria(sqlOperator = SqlOperator.like)
private String username;
@ApiModelProperty(value="姓名")
@QueryCriteria(sqlOperator = SqlOperator.like)
private String fullName;
}- 独自抽取一个工具类，对
DefaultUserQryWrapper解决，解决成jpa的查问条件specification:
@Slf4j
public class JPAQueryUtil {
/**
* 获取一个类及其父类的所有字段（属性，field）*
* @param clazz invoked class
* @return fields include self and all parent */ private static List<Field> getAllFieldsWithRoot(Class<?> clazz) {List<Field> fieldList = new ArrayList<>();
//get from this class self
Field[] dFields = clazz.getDeclaredFields();
if (dFields.length > 0)
fieldList.addAll(Arrays.asList(dFields));
Class<?> superClass = clazz.getSuperclass();
// if parent class is Object.class ,return ..
if (superClass == Object.class) return Arrays.asList(dFields);
// recursive to get from parent class
List<Field> superFields = getAllFieldsWithRoot(superClass);
if (!superFields.isEmpty()) {superFields.stream().
filter(field -> !fieldList.contains(field)).
forEach(fieldList::add);
}
return fieldList;
}
// 动静查问 or 连贯
public static <Q extends BaseQryWrapperInterface, T extends BasePO> Specification<T> toJPASpecificationWithOr(Q queryWrapper, Class<T> entityCls) {return toJPASpecificationWithLogic("or", queryWrapper, entityCls);
}
// 动静查问 and 连贯
public static <Q extends BaseQryWrapperInterface, T> Specification<T> toJPASpecificationWithAnd(Q queryWrapper, Class<T> entityCls) {return toJPASpecificationWithLogic("and", queryWrapper, entityCls);
}
//logicType or/and
@SuppressWarnings({"rawtypes", "unchecked"})
public static <Q extends BaseQryWrapperInterface, T> Specification<T> toJPASpecificationWithLogic(String logicType, Q queryWrapper, Class<T> entityCls) {return (root, criteriaQuery, criteriaBuilder) -> {Class clazz = queryWrapper.getClass();
// 获取查问类 Query 的所有字段, 包含父类字段
List<Field> fields = getAllFieldsWithRoot(clazz);
List<Predicate> predicates = new ArrayList<>(fields.size());
Predicate predicate = null;
//wrapper 中的 column 必须在实体类中存在，否则疏忽！List<Field> supportedFields = getAllFieldsWithRoot(entityCls);
Set<String> supportFieldNames = supportedFields.stream().map(Field::getName).collect(Collectors.toSet());
if (CollUtil.isEmpty(supportFieldNames)) {return predicate;}
//init betweenAnd 用 map 寄存 between and 的字段对，from 和 to 必须指向同一个 column!
Map<String, Map<String, Object>> betweenAndMap = new HashMap<>();
Map<String, Object> targetField;
for (Field field : fields) {QueryCriteria queryCriteria = field.getAnnotation(QueryCriteria.class);
if (queryCriteria == null) {continue;}
String column = queryCriteria.column();
if (column.equals("")) {column = field.getName();
}
if (!supportFieldNames.contains(column)) {continue;}
Path path = root.get(column);
if (queryCriteria.sqlOperator() == SqlOperator.isNull) {predicates.add(criteriaBuilder.isNull(path));
continue;
}
if (queryCriteria.sqlOperator() == SqlOperator.isNotNull) {predicates.add(criteriaBuilder.isNotNull(path));
continue;
}
field.setAccessible(true);
try {
// nullable
Object value = field.get(queryWrapper);
// 如果值为 null, 注解未标注 nullable, 跳过
if (value == null && !queryCriteria.nullAble())
continue;
// can be empty
if (value != null && String.class.isAssignableFrom(value.getClass())) {String s = (String) value;
if (s.equals("") && !queryCriteria.emptyAble()) {continue;}
}
switch (queryCriteria.sqlOperator()) {
case equal:
predicates.add(criteriaBuilder.equal(path, value));
break;
case like:
predicates.add(criteriaBuilder.like(path, "%" + value + "%"));
break;
case gt:
predicates.add(criteriaBuilder.gt(path, (Number) value));
break;
case lt:
predicates.add(criteriaBuilder.lt(path, (Number) value));
break;
case ge:
predicates.add(criteriaBuilder.ge(path, (Number) value));
break;
case le:
predicates.add(criteriaBuilder.le(path, (Number) value));
break;
case notEqual:
predicates.add(criteriaBuilder.notEqual(path, value));
break;
case notLike:
predicates.add(criteriaBuilder.notLike(path, "%" + value + "%"));
break;
case greaterThan:
predicates.add(criteriaBuilder.greaterThan(path, (Comparable) value));
break;
case greaterThanOrEqualTo:
predicates.add(criteriaBuilder.greaterThanOrEqualTo(path, (Comparable) value));
break;
case lessThan:
predicates.add(criteriaBuilder.lessThan(path, (Comparable) value));
break;
case lessThanOrEqualTo:
predicates.add(criteriaBuilder.lessThanOrEqualTo(path, (Comparable) value));
break;
case in://in 查问 wrpper 中的字段值必须为 Collection 模式
if (!Collection.class.isAssignableFrom(field.getType())) {log.warn("a search attribute named {} was ignored ! because for in Query,the value of search attribute must extend Collection", column);
break;
}
if (value instanceof Collection<?>) {Collection<?> inValues = (Collection<?>) value;
CriteriaBuilder.In in = criteriaBuilder.in(path);
for (Object inValue : inValues) {in.value(inValue);
}
predicates.add(in);
break;
}
if (null != value && value.getClass().isArray()) {Object[] array = (Object[]) value;
CriteriaBuilder.In in = criteriaBuilder.in(path);
for (Object t : array) {in.value(t);
}
predicates.add(in);
}
break;
case betweenAndFrom:
targetField = betweenAndMap.get(column);
if (ObjectUtil.isEmpty(targetField)) {targetField = new HashMap<>();
betweenAndMap.put(column, targetField);
}
targetField.put("from", value);
break;
case betweenAndTo:
targetField = betweenAndMap.get(column);
if (ObjectUtil.isEmpty(targetField)) {targetField = new HashMap<>();
betweenAndMap.put(column, targetField);
}
targetField.put("to", value);
break;
default:
}
} catch (Exception e) {log.error(e.getMessage());
}
}
// 解决 between and
List<Predicate> betweenAndPredicates = handleBetweenAndPredicates(betweenAndMap, root, criteriaBuilder);
predicates.addAll(betweenAndPredicates);
if (logicType == null || logicType.equals("") || logicType.equals("and")) {predicate = criteriaBuilder.and(predicates.toArray(new Predicate[0]));//and 连贯
} else if (logicType.equals("or")) {predicate = criteriaBuilder.or(predicates.toArray(new Predicate[0]));//or 连贯
}
return predicate;
};
}
@SuppressWarnings("unchecked")
private static List<Predicate> handleBetweenAndPredicates(Map<String, Map<String, Object>> betweenAndMap, Root root, CriteriaBuilder criteriaBuilder) {List<Predicate> betweenPredicates = new ArrayList<>();
if (!MapUtil.isEmpty(betweenAndMap)) {for (Map.Entry<String, Map<String, Object>> entry : betweenAndMap.entrySet()) {String column = entry.getKey();
Map<String, Object> criteriaMap = entry.getValue();
Object from = criteriaMap.get("from");
Object to = criteriaMap.get("to");
if (ObjectUtil.isEmpty(from) && ObjectUtil.isEmpty(to)) {continue;}
// 这里只查看日期或者数值类型
Class<?> valueType = ObjectUtil.isEmpty(from) ? to.getClass() : from.getClass();
if (Date.class.isAssignableFrom(valueType)) {
// 如果日期查问中有 from 和 to 有空的，用以后工夫代替
Path path = root.get(column);
from = Optional.ofNullable(from).orElse(new Date());
to = Optional.ofNullable(to).orElse(new Date());
betweenPredicates.add(criteriaBuilder.between(path, (Date) from, (Date) to));
continue;
}
// 数值类型, 如果有空的，则 from = to
if (Integer.class.isAssignableFrom(valueType)) {Path path = root.<Date>get(column);
betweenPredicates.add(criteriaBuilder.between(path, Convert.toInt(from), Convert.toInt(to)));
continue;
}
log.warn("BetweenAnd Search Attribute was ignored,caused by unsupported field type {}", valueType);
}
}
return betweenPredicates;
}
}这样应用：
Specification<UserPO> rawSpecif = JPAQueryUtil.toJPASpecificationWithAnd(useQry, UserPO.class);
Optional<UserPO> existedOne = userDORepository.findOne(rawSpecif);