背景

    新增一个模块我们就要写一大堆CRUD代码，一般新增更新我们需要自己写（牵扯其它模块调用，业务逻辑），但是查询我们可以利用jpa来实现一个baseController，跟前端约定好查询方式，省去各种字段的查询加快项目进度。主要参考《Spring Data JPA从入门到精通》这本书，百度可以搜到pdf。ide是InteliJava 2020。

    先把git地址贴出来：https://github.com/fantasticqiang/jpatest.git

注意事项

1. 代码下载下来之后，在applicaton.yml文件中更改数据库地址，数据库名称。
2. 项目启动之后会自动建一张user表（确保你连接的库中没有user表），表中 会自动插入测试数据
3. 修改mysql的用户名，密码

有了baseController之后，我们的UserController什么都不用写，就可以CRUD了

先展示一张查询图片：

查询条件说明：

    我们只需要新建一个UserController继承BaseController，对于User表的CRUD就都已经做好了。到底是怎么实现的呢？ 先熟悉一下以下知识点（简单的介绍一下）。

关键知识点

自定义标签

//声明自定义标签用在参数上
@Target({ElementType.PARAMETER})
//声明标签保留到vm运行期
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Filter {
    /**
     * 说明：@interface 自定义标签
     * @return
     */
    String value() default "filter";
}

自定义查询条件 MySpecification，根据url中的一个(k,v)生成一个查询条件，比如：username= 张三

    MySpecification实现Specification接口，里面只定义了一个接口

public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {  }

这个接口的目的就是封装查询条件，就是 sql语句where后面的查询条件。下面表格介绍toPredicate中的三个参数

1. CriteriaQuery<T> select(Selection<? extends T> selection);
2.CriteriaQuery<T> where(Predicate... restrictions);
3.CriteriaQuery<T> groupBy(Expression<?>... grouping);
4.CriteriaQuery<T> having(Expression<Boolean> restriction);
5.CriteriaQuery<T> orderBy(Order... o);

下面贴下最关键的代码：

package org.example.parameter;

import org.example.common.Common;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;

public class MySpecification<T> implements Specification<T> {

    private ParameterCriteria parameterCriteria;

    public MySpecification(String parameterCriteriaStr) {
        this.parameterCriteria = new ParameterCriteria(parameterCriteriaStr);
    }

    /**
     * 获取查询的字段名称
     * @param root
     * @param path
     * @return
     */
    private Expression get(Root<T> root, String path) {
        String[] paths = path.split("\\.");
        Path finalPath = root.get(paths[0]);
        if (paths.length > 1) {
            for (int i = 1; i < paths.length; i++) {
                finalPath = finalPath.get(paths[i]);
            }
        }
        return finalPath;
    }

    /**
     * 封装一个查询条件
     * @param root
     * @param criteriaQuery
     * @param criteriaBuilder
     * @return
     */
    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
        Expression expression = get(root, parameterCriteria.getKey());
        Class javaType = expression.getJavaType();

        switch (parameterCriteria.getOperator()) {
            case greaterEqualThan:
                return criteriaBuilder.greaterThanOrEqualTo(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
            case greaterThan:
                return criteriaBuilder.greaterThan(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
            case lessEqualThan:
                return criteriaBuilder.lessThanOrEqualTo(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
            case lessThan:
                return criteriaBuilder.lessThan(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
            case equal:
                return criteriaBuilder.equal(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
            case notEqual:
                return criteriaBuilder.notEqual(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
            case in:
                return expression.in(Arrays.asList(parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER)));
            case notIn:
                return criteriaBuilder.not(expression.in(Arrays.asList(parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER))));
            case like:
                return criteriaBuilder.like(expression,"%" + parameterCriteria.getStringValue() + "%");
            case unlike:
                return criteriaBuilder.notLike(expression,"%" + parameterCriteria.getStringValue() + "%");
            case isnull:
                return criteriaBuilder.isNull(expression);
            case notnull:
                return criteriaBuilder.isNotNull(expression);
            case jspe:
                String[] jspeParamValues = parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER);
                String jspePath = jspeParamValues[0], jspeValue = jspeParamValues[1];
                return criteriaBuilder.equal(criteriaBuilder.function("JSON_EXTRACT", String.class, root.get(parameterCriteria.getKey()), criteriaBuilder.literal(jspePath)),
                        jspeValue);
            case jos:
                String[] josParamValues = parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER);
                String josPath = josParamValues[0], josValue = josParamValues[1];
                return criteriaBuilder.isNotNull(criteriaBuilder.function("JSON_SEARCH", String.class, root.get(parameterCriteria.getKey()),
                        criteriaBuilder.literal("one"),
                        criteriaBuilder.literal(josValue),
                        criteriaBuilder.nullLiteral(String.class),
                        criteriaBuilder.literal(josPath)));
            case jas:
                String[] jasParamValues = parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER);
                String jasPath = jasParamValues[0], jasValue = jasParamValues[1];
                return criteriaBuilder.isNotNull(criteriaBuilder.function("JSON_SEARCH", String.class, root.get(parameterCriteria.getKey()),
                        criteriaBuilder.literal("all"),
                        criteriaBuilder.literal(jasValue),
                        criteriaBuilder.nullLiteral(String.class),
                        criteriaBuilder.literal(jasPath)));
        }
        return null;
    }


    /**
     * 一个标准查询查询参数，url中的查询条件解析成对象
     * 比如：username=张三
     */
    public static class ParameterCriteria {
        private String key;
        private EFieldOperator operator;
        private String value;

        public ParameterCriteria(String key, EFieldOperator operator, String value) {
            this.key = key;
            this.operator = operator;
            this.value = value;
        }

        private ParameterCriteria(String parameterSpecification) {
            String[] parts = parameterSpecification.split(Common.FILTER_OPERATION_SPLITTER);
            this.key = parts[0];
            this.operator = EFieldOperator.getByName(parts[1]);
            String[] valueParts = new String[parts.length - 2];
            System.arraycopy(parts, 2, valueParts, 0, valueParts.length);
            this.value = parts.length > 2 ? String.join(Common.COMMON_SPLITTER, valueParts) : "";

        }

        public String getKey() {
            return key;
        }

        public EFieldOperator getOperator() {
            return operator;
        }

        public Expression getValue(Class clazz, CriteriaBuilder cb) {
            if (clazz.isAssignableFrom(Date.class)) {
                Date result = null;
                try {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    result = sdf.parse(value);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                return cb.literal(result);
            }
            return cb.literal(value);
        }

        public String getStringValue() {
            return value;
        }
    }
}

把多个specification合并成查询语句

关键代码：

package org.example.parameter;

import org.apache.commons.lang.StringUtils;
import org.example.common.Common;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;

import java.util.ArrayList;

/**
 * 接收传过来的url参数，封装成specification查询条件
 * @param <T>
 */
public class SimpleFilterResolver<T> {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    private String filterString;

    private String pageableString;

    private String sortString;

    public SimpleFilterResolver(String filterString, String pageableString, String sortString) {
        this.filterString = filterString;
        this.pageableString = pageableString;
        this.sortString = sortString;
    }

    //生成查询语句
    public <T> Specification<T> genSpecification() {
        Specification<T> orSpec = null;
        for (String orString : filterString.split(Common.FILTER_SPECIFICATION_OR_SPLITTER)) {
            String[] split = orString.split(Common.FILTER_SPECIFICATION_AND_SPLITTER);
            if (split.length >= 1) {
                Specification<T> mySpec = null;
                for (int i = 0; i < split.length; i++) {
                    if (mySpec == null) {
                        if (StringUtils.isNotBlank(split[i])) {
                            mySpec = new MySpecification<T>(split[i]);
                        }
                    } else {
                        if (StringUtils.isNotBlank(split[i])) {
                            mySpec = mySpec.and(new MySpecification<T>(split[i]));
                        }
                    }
                }
                if (mySpec != null) {
                    if (orSpec == null) {
                        orSpec = mySpec;
                    } else {
                        orSpec = orSpec.or(mySpec);
                    }
                }
            }
        }
        return orSpec;
    }

    //生成分页
    public PageRequest genPageRequest() {
        int start = 0;
        int size = 10;
        if (StringUtils.isNotBlank(pageableString)) {
            String[] split = pageableString.split(Common.COMMON_SPLITTER);
            if (split != null && split.length >= 2) {
                try {
                    Integer startTmp = Integer.valueOf(split[0]);
                    Integer sizeTmp = Integer.valueOf(split[1]);
                    if (startTmp >= 0) {
                        start = startTmp;
                    }
                    if (sizeTmp >= 1) size = sizeTmp;
                } catch (Exception e) {
                    logger.error("分页数据转换失败");
                }
            }
        }
        ArrayList<Sort.Order> orders = new ArrayList<>();
        if (StringUtils.isNotBlank(sortString)) {
            String[] split = sortString.split(Common.FILTER_SPECIFICATION_AND_SPLITTER);
            for (String sortStr : split) {
                String finalSortStr = null;
                boolean asc = true;
                if (sortStr.startsWith("-")) {
                    asc = false;
                    finalSortStr = sortStr.substring(1);
                } else if (sortStr.startsWith("+")) {
                    finalSortStr = sortStr.substring(1);
                } else {
                    finalSortStr = sortStr;
                }
                if (StringUtils.isNotBlank(finalSortStr)) {
                    orders.add(new Sort.Order(asc ? Sort.Direction.ASC : Sort.Direction.DESC, finalSortStr));
                }
            }
        }
        return PageRequest.of(start, size, Sort.by(orders));
    }
}