需要场景:
不论业务如许简单，零碎架构如许简单，最终都是对各类数据库的增删改查，现阶段研发的协同研发平台采纳微服务架构，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)@GetMappingpublic 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")@Builderpublic 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:
@Slf4jpublic 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);