jpa 动态参数查询 高级查询

@GetMapping("/find")
@ApiOperation("通用查询")
@ScSecurityPermission(name = "通用查询")
public ScResult find(FileInfo fileInfo,
                                    @RequestParam(name="page", defaultValue="1") int page,
                                    @RequestParam(name="limit", defaultValue="10") int limit,
                                    HttpServletRequest req) {
    Specification<FileInfo> cation = BaseQuery.initQuery(fileInfo, req.getParameterMap());
    Page<FileInfo> pageList = fileInfoService.findAll(cation,PageableUtil.simplePageable(page, limit));
    return ScPageResult.set(pageList.getContent(), pageList.getTotalPages(), pageList.getTotalElements());
}

通用查询类：BaseQuery.java  

import com.alibaba.fastjson.JSON;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLDecoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 查询类
 */
@Slf4j
@Data
public  class BaseQuery {

    public static final String SQL_RULES_COLUMN = "SQL_RULES_COLUMN";

    private static final String BEGIN = "_begin";
    private static final String END = "_end";
    /**
     * 数字类型字段，拼接此后缀 接受多值参数
     */
    private static final String MULTI = "_MultiString";
    private static final String STAR = "*";
    private static final String COMMA = ",";
    private static final String NOT_EQUAL = "!";
    /**页面带有规则值查询，空格作为分隔符*/
    private static final String QUERY_SEPARATE_KEYWORD = " ";
    /**高级查询前端传来的参数名*/
    private static final String SUPER_QUERY_PARAMS = "superQueryParams";
    /** 高级查询前端传来的拼接方式参数名 */
    private static final String SUPER_QUERY_MATCH_TYPE = "superQueryMatchType";
    /** 单引号 */
    public static final String SQL_SQ = "'";
    /**排序列*/
    private static final String ORDER_COLUMN = "column";
    /**排序方式*/
    private static final String ORDER_TYPE = "order";
    private static final String ORDER_TYPE_ASC = "ASC";
    /**
     * 获取查询条件构造器
     * @param searchObj 查询实体
     * @param parameterMap request.getParameterMap()
     * @return Specification实例
     */
    public static <T> Specification<T>  initQuery(T searchObj, Map<String, String[]> parameterMap){
        return toSpecification(searchObj,parameterMap);
    }

    /**
     *  根据逻辑类型生成Specification实体
     * @return Specification实体
     */
    private static <T> Specification<T> toSpecification(T searchObj, Map<String, String[]> parameterMap) {

        return (Specification<T>) (root, criteriaQuery, criteriaBuilder) -> {
            //区间条件组装 模糊查询 高级查询组装 
            // 获取查询类的所有字段, 包括父类
            List<Field> fields = listAllFieldWithRoot(searchObj.getClass());
            List<Predicate> predicates = new ArrayList<>(fields.size());
            String name, type;
            for (Field field : fields ) {
                name = field.getName();
                type = field.getType().getTypeName();
                try {
                    if (judgedIsUselessField(name)) {
                        continue;
                    }
                    // 排序逻辑 处理
                    doMultiFieldsOrder(root,predicates, criteriaBuilder, parameterMap);

                    //高级查询
                    doSuperQuery(root,predicates, criteriaBuilder, parameterMap);
                    // 添加 判断是否有区间值
                    String endValue = null,beginValue = null;
                    if (parameterMap != null && parameterMap.containsKey(name + BEGIN)) {
                        beginValue = parameterMap.get(name + BEGIN)[0].trim();
                        addQueryByRule(root,predicates, criteriaBuilder,name, type, beginValue, QueryRuleEnum.GE);
                    }
                    if (parameterMap != null && parameterMap.containsKey(name + END)) {
                        endValue = parameterMap.get(name + END)[0].trim();
                        addQueryByRule(root,predicates, criteriaBuilder,name, type, endValue, QueryRuleEnum.LE);
                    }
                    //多值查询
                    if (parameterMap != null && parameterMap.containsKey(name + MULTI)) {
                        endValue = parameterMap.get(name + MULTI)[0].trim();
                        addQueryByRule(root,predicates, criteriaBuilder, name.replace(MULTI,""), type, endValue, QueryRuleEnum.IN);
                    }
                    //判断单值  参数带不同标识字符串 走不同的查询
                    //TODO 这种前后带逗号的支持分割后模糊查询需要否 使多选字段的查询生效  in查询
                    Object value = PropertyUtils.getSimpleProperty(searchObj, name);
                    if (null != value && value.toString().startsWith(COMMA) && value.toString().endsWith(COMMA)) {
                        String multiLikeval = value.toString().replace(",,", COMMA);
                        String[] vals = multiLikeval.substring(1, multiLikeval.length()).split(COMMA);
                        //in查询
                        if(StrUtils.isNotEmpty(vals) && vals.length > 0) {
                            Expression<String> exp = root.get(name);
                            predicates.add(exp.in(vals));
                        }
                    }else {
                        //根据参数值带什么关键字符串判断走什么类型的查询
                        QueryRuleEnum rule = convert2Rule(value);
                        value = replaceValue(rule,value);
                        // add -begin 添加判断为字符串时设为全模糊查询
                        if( (rule==null || QueryRuleEnum.EQ.equals(rule)) && "class java.lang.String".equals(type)) {
                            // 可以设置左右模糊或全模糊，因人而异
                            rule = QueryRuleEnum.LIKE;
                        }
                        // add -end 添加判断为字符串时设为全模糊查询
                        addEasyQuery(root,predicates, criteriaBuilder, name, rule, value);
                    }

                }catch (Exception e) {
                   e.printStackTrace();
                }
            }
            if (predicates.size() > 1) {
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            } else if (predicates.size() == 1) {
                return predicates.get(0);
            } else {
                return null;
            }

        };
    }

    //多字段排序 TODO 需要修改前端
    public static void doMultiFieldsOrder(Root root, List<Predicate> predicates, CriteriaBuilder criteriaBuilder,Map<String, String[]> parameterMap) {
        String column=null,order=null;
        if(parameterMap!=null&& parameterMap.containsKey(ORDER_COLUMN)) {
            column = parameterMap.get(ORDER_COLUMN)[0];
        }
        if(parameterMap!=null&& parameterMap.containsKey(ORDER_TYPE)) {
            order = parameterMap.get(ORDER_TYPE)[0];
        }
        log.debug("排序规则>>列:"+column+",排序方式:"+order);
        if (StrUtils.isNotEmpty(column) && StrUtils.isNotEmpty(order)) {
            //字典字段，去掉字典翻译文本后缀
            if(column.endsWith(CommonConstant.DICT_TEXT_SUFFIX)) {
                column = column.substring(0, column.lastIndexOf(CommonConstant.DICT_TEXT_SUFFIX));
            }
            //SQL注入check
            SqlInjectionUtil.filterContent(column);

            if (order.toUpperCase().indexOf(ORDER_TYPE_ASC)>=0) {
                //predicates.add(criteriaBuilder) .orderByAsc(StrUtils.camelToUnderline(column));
            } else {
                //queryWrapper.orderByDesc(StrUtils.camelToUnderline(column));
            }
        }
    }

    /**
     * 高级查询
     * @param
     * @param parameterMap
     */
    public static void doSuperQuery(Root root, List<Predicate> predicates, CriteriaBuilder criteriaBuilder,Map<String, String[]> parameterMap) {
        if(parameterMap!=null&& parameterMap.containsKey(SUPER_QUERY_PARAMS)){
            String superQueryParams = parameterMap.get(SUPER_QUERY_PARAMS)[0];
            String superQueryMatchType = parameterMap.get(SUPER_QUERY_MATCH_TYPE) != null ? parameterMap.get(SUPER_QUERY_MATCH_TYPE)[0] : MatchTypeEnum.AND.getValue();
            MatchTypeEnum matchType = MatchTypeEnum.getByValue(superQueryMatchType);
            // update-begin--Author:sunjianlei  Date:20200325 for：高级查询的条件要用括号括起来，防止和用户的其他条件冲突 -------
            try {
                superQueryParams = URLDecoder.decode(superQueryParams, "UTF-8");
                List<QueryCondition> conditions = JSON.parseArray(superQueryParams, QueryCondition.class);
                if (conditions == null || conditions.size() == 0) {
                    return;
                }
                log.info("---高级查询参数-->" + conditions.toString());
                for (int i = 0; i < conditions.size(); i++) {
                    QueryCondition rule = conditions.get(i);
                    if (StrUtils.isNotEmpty(rule.getField())
                            && StrUtils.isNotEmpty(rule.getRule())
                            && StrUtils.isNotEmpty(rule.getVal())) {

                        log.debug("SuperQuery ==> " + rule.toString());
                        addEasyQuery(root,predicates, criteriaBuilder, rule.getField(), QueryRuleEnum.getByValue(rule.getRule()), rule.getVal());

                        // 如果拼接方式是OR，就拼接OR
                        if (MatchTypeEnum.OR == matchType && i < (conditions.size() - 1)) {
                            
                        }
                    }
                }
            } catch (UnsupportedEncodingException e) {
                log.error("--高级查询参数转码失败：" + superQueryParams, e);
            } catch (Exception e) {
                log.error("--高级查询拼接失败：" + e.getMessage());
                e.printStackTrace();
            }
            // update-end--Author:sunjianlei  Date:20200325 for：高级查询的条件要用括号括起来，防止和用户的其他条件冲突 -------
        }
        //log.info(" superQuery getCustomSqlSegment: "+ queryWrapper.getCustomSqlSegment());
    }



    /**
     *  找到类所有字段包括父类的集合
     * @param clazz 类Class
     * @return 类所有字段的集合
     */
    private static List<Field> listAllFieldWithRoot(Class<?> clazz) {
        List<Field> fieldList = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        if (fields.length != 0) {
            fieldList.addAll(Arrays.asList(fields));
        }
        Class<?> superclass = clazz.getSuperclass();
        // 如果父类是Object, 直接返回
        if (superclass == Object.class) {
            return fieldList;
        }
        // 递归获取所有的父级的Field
        List<Field> superClassFieldList = listAllFieldWithRoot(superclass);
        if (!superClassFieldList.isEmpty()) {
            superClassFieldList.stream()
                    // 去除重复字段
                    .filter(field -> !fieldList.contains(field))
                    .filter(field -> !field.getName().equals("created"))
                    .filter(field -> !field.getName().equals("updated"))
                    .filter(field -> !field.getName().equals("serialVersionUID"))
                    .forEach(fieldList::add);

        }
        return fieldList;
    }

    /**
     * 去掉关键字段
     * @param name
     * @return
     */
    private static boolean judgedIsUselessField(String name) {
        return "class".equals(name) || "ids".equals(name)
                || "page".equals(name) || "rows".equals(name)
                || "sort".equals(name) || "order".equals(name) ;
    }
    private static void addQueryByRule(Root root, List<Predicate> predicates, CriteriaBuilder criteriaBuilder, String name, String type, String value, QueryRuleEnum rule) throws ParseException {
        if (StrUtils.isNotEmpty(value)) {
            Object temp;
            // 针对数字类型字段，多值查询
            if (value.indexOf(COMMA) != -1) {
                temp = value;
                addEasyQuery(root,predicates, criteriaBuilder,name, rule, temp);
                return;
            }

            switch (type) {
                case "class java.lang.Integer":
                    temp = Integer.parseInt(value);
                    break;
                case "class java.math.BigDecimal":
                    temp = new BigDecimal(value);
                    break;
                case "class java.lang.Short":
                    temp = Short.parseShort(value);
                    break;
                case "class java.lang.Long":
                    temp = Long.parseLong(value);
                    break;
                case "class java.lang.Float":
                    temp = Float.parseFloat(value);
                    break;
                case "class java.lang.Double":
                    temp = Double.parseDouble(value);
                    break;
                case "class java.util.Date":
                    temp = getDateQueryByRule(value, rule);
                    break;
                default:
                    temp = value;
                    break;
            }
            addEasyQuery(root,predicates, criteriaBuilder, name, rule, temp);
        }
    }
/**
 * 根据规则走不同的查询
 * @param name         字段名字
 * @param rule         查询规则
 * @param value        查询条件值
 */
    private static void addEasyQuery(Root root,List<Predicate> predicates, CriteriaBuilder criteriaBuilder,String name, QueryRuleEnum rule, Object value){
        if (value == null || rule == null || StrUtils.isEmpty(value)) {
            return;
        }
        name = StrUtils.camelToUnderline(name);
        //log.info("--查询规则-->" + name + " " + rule.getValue() + " " + value);
        switch (rule) {
            case GT:
                //GT(">","gt","大于"),
                predicates.add(criteriaBuilder.gt(root.get(name), (Number) value));
                break;
            case GE:
                // GE(">=","ge","大于等于"),
                predicates.add(criteriaBuilder.ge(root.get(name), (Number) value));
                break;
            case LT:
                //  LT("<","lt","小于"),
                predicates.add(criteriaBuilder.lt(root.get(name), (Number) value));
                break;
            case LE:
                //  LE("<=","le","小于等于"),
                predicates.add(criteriaBuilder.le(root.get(name), (Number) value));
                break;
            case EQ:
                // EQ("=","eq","等于"),
                predicates.add(criteriaBuilder.equal(root.get(name),  value));
                break;
            case NE:
                // NE("!=","ne","不等于"),
                predicates.add(criteriaBuilder.notEqual(root.get(name), value));
                break;
            case IN:
                Expression<String> exp = root.get(name);
                 //IN("IN","in","包含"),
                if (value instanceof String) {
                    predicates.add(exp.in((Object[]) value.toString().split(",")));
                    //queryWrapper.in(name, (Object[]) value.toString().split(","));
                } else if (value instanceof String[]) {
                    predicates.add(exp.in((Object[]) value));
                } else {
                    predicates.add(exp.in(value));
                }
                break;
            case LIKE:
                //  LIKE("LIKE","like","全模糊"),
                predicates.add(criteriaBuilder.like(root.get(name), "%" + value + "%"));
                break;
            case LEFT_LIKE:
                // LEFT_LIKE("LEFT_LIKE","left_like","左模糊"),
                predicates.add(criteriaBuilder.like(root.get(name), value + "%" ));
                break;
            case RIGHT_LIKE:
                // RIGHT_LIKE("RIGHT_LIKE","right_like","右模糊"),
                predicates.add(criteriaBuilder.like(root.get(name), "%" + value));
                break;
            default:
                //  SQL_RULES("USE_SQL_RULES","ext","自定义SQL片段");
                //.info("--查询规则未匹配到---");
                break;
        }
    }
    /**时间格式化 */
    private static final ThreadLocal<SimpleDateFormat> local = new ThreadLocal<SimpleDateFormat>();
    private static SimpleDateFormat getTime(){
        SimpleDateFormat time = local.get();
        if(time == null){
            time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            local.set(time);
        }
        return time;
    }
    /**
     * 获取日期类型的值
     * @param value
     * @param rule
     * @return
     * @throws ParseException
     */
    private static Date getDateQueryByRule(String value, QueryRuleEnum rule) throws ParseException {
        Date date = null;
        if(value.length()==10) {
            if(rule==QueryRuleEnum.GE) {
                //比较大于
                date = getTime().parse(value + " 00:00:00");
            }else if(rule==QueryRuleEnum.LE) {
                //比较小于
                date = getTime().parse(value + " 23:59:59");
            }
            //TODO 日期类型比较特殊 可能oracle下不一定好使
        }
        if(date==null) {
            date = getTime().parse(value);
        }
        return date;
    }
    /**
     * 根据所传的值 转化成对应的比较方式
     * 支持><= like in !
     * @param value
     * @return
     */
    private static QueryRuleEnum convert2Rule(Object value) {
        // 避免空数据
        if (value == null) {
            return null;
        }
        String val = (value + "").toString().trim();
        if (val.length() == 0) {
            return null;
        }
        QueryRuleEnum rule =null;

        //update-begin--Author:scott  Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
        //TODO 此处规则，只适用于 le lt ge gt
        // step 2 .>= =<
        if (rule == null && val.length() >= 3) {
            if(QUERY_SEPARATE_KEYWORD.equals(val.substring(2, 3))){
                rule = QueryRuleEnum.getByValue(val.substring(0, 2));
            }
        }
        // step 1 .> <
        if (rule == null && val.length() >= 2) {
            if(QUERY_SEPARATE_KEYWORD.equals(val.substring(1, 2))){
                rule = QueryRuleEnum.getByValue(val.substring(0, 1));
            }
        }
        //update-end--Author:scott  Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284---------------------

        // step 3 like
        if (rule == null && val.contains(STAR)) {
            if (val.startsWith(STAR) && val.endsWith(STAR)) {
                rule = QueryRuleEnum.LIKE;
            } else if (val.startsWith(STAR)) {
                rule = QueryRuleEnum.LEFT_LIKE;
            } else if(val.endsWith(STAR)){
                rule = QueryRuleEnum.RIGHT_LIKE;
            }
        }
        // step 4 in
        if (rule == null && val.contains(COMMA)) {
            //TODO in 查询这里应该有个bug  如果一字段本身就是多选 此时用in查询 未必能查询出来
            rule = QueryRuleEnum.IN;
        }
        // step 5 !=
        if(rule == null && val.startsWith(NOT_EQUAL)){
            rule = QueryRuleEnum.NE;
        }
        return rule != null ? rule : QueryRuleEnum.EQ;
    }
    /**
     * 替换掉关键字字符
     *
     * @param rule
     * @param value
     * @return
     */
    private static Object replaceValue(QueryRuleEnum rule, Object value) {
        if (rule == null) {
            return null;
        }
        if (! (value instanceof String)){
            return value;
        }
        String val = (value + "").toString().trim();
        if (rule == QueryRuleEnum.LIKE) {
            value = val.substring(1, val.length() - 1);
        } else if (rule == QueryRuleEnum.LEFT_LIKE || rule == QueryRuleEnum.NE) {
            value = val.substring(1);
        } else if (rule == QueryRuleEnum.RIGHT_LIKE) {
            value = val.substring(0, val.length() - 1);
        } else if (rule == QueryRuleEnum.IN) {
            value = val.split(",");
        } else {
            //update-begin--Author:scott  Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
            if(val.startsWith(rule.getValue())){
                //TODO 此处逻辑应该注释掉-> 如果查询内容中带有查询匹配规则符号，就会被截取的（比如：>=您好）
                value = val.replaceFirst(rule.getValue(),"");
            }else if(val.startsWith(rule.getCondition()+QUERY_SEPARATE_KEYWORD)){
                value = val.replaceFirst(rule.getCondition()+QUERY_SEPARATE_KEYWORD,"").trim();
            }
            //update-end--Author:scott  Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
        }
        return value;
    }
}

完整代码：https://gitee.com/yqya/jpa-query/tree/master/