MySpecification Jpa复杂查询封装

 该类用于JpaSpecificationExecutor 进行jpa的复杂查询

使用示例

该示例为一个分页controller，传入相关分页参数 page ，size 后根据相应查询条件进行复杂查询分页，DAO层继承了JpaSpecificationExecutor，示例中在findAll中实例化MySpecification类，后续加上QueryParams查询参数和条件。

 

代码片段地址：https://gitee.com/mx1014/codes/m1hvcwyjqxs8u9rlt3a0672

    @RequestMapping(value = {"memo/list"})
    @ResponseBody
    //分页查询
    public Page<Memo> list(int page, int size) {
        PageRequest pr = new PageRequest(page - 1, size);
        Page<Memo> page1 = memoService.findAll(new MySpecification<>().and(
                MySpecification.QueryParams.eq("userId", Long.parseLong(request.getSession().getAttribute("userId").toString())),
                MySpecification.QueryParams.ne("status", "5")), pr);
        return page1;
    }

package 

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.CollectionUtils;

import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;

/**
 * Created By mx1014 On 17-11-23
 *
 * @author Mx1014
 * 使用示例 
 * PageRequest pr = new PageRequest(0, 10);
 * Page pageData = userDao.findAll(new MySpecification<User>().and(
 * QueryParams.like("id", id),
 * QueryParams.like("Name",Name)).asc("id"), pr);
 */
public class MySpecification<T> implements Specification<T> {

    //属性分隔
    private static final String PROPERTY_SEPARATOR = ".";

    //AND
    List<QueryParams> andConditions = new ArrayList<>();

    //OR
    List<QueryParams> orConditions = new ArrayList<>();

    //排序
    List<Order> orders = new ArrayList<>();

    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        Predicate restrictions = cb.and(getAndPredicates(root, cb));
        restrictions = cb.and(restrictions, getOrPredicates(root, cb));
        cq.orderBy(getOrders(root, cb));
        return restrictions;
    }

    public MySpecification and(Collection<QueryParams> conditions) {
        andConditions.addAll(conditions);
        return this;
    }

    public MySpecification and(QueryParams... conditions) {
        for (QueryParams condition : conditions) {
            andConditions.add(condition);
        }
        return this;
    }

    public MySpecification or(Collection<QueryParams> conditions) {
        orConditions.addAll(conditions);
        return this;
    }

    public MySpecification or(QueryParams... conditions) {
        for (QueryParams condition : conditions) {
            orConditions.add(condition);
        }
        return this;
    }

    public MySpecification desc(String property) {
        this.orders.add(Order.desc(property));
        return this;
    }

    public MySpecification asc(String property) {
        this.orders.add(Order.asc(property));
        return this;
    }

    public MySpecification order(String property, Sort.Direction direction) {
        this.orders.add(new Order(property, direction));
        return this;
    }

    public MySpecification orders(Order... orders) {
        this.orders.addAll(Arrays.asList(orders));
        return this;
    }

    public MySpecification orders(Collection<Order> orders) {
        this.orders.addAll(orders);
        return this;
    }

    private Predicate getAndPredicates(Root<T> root, CriteriaBuilder cb) {
        Predicate restrictions = cb.conjunction();
        for (QueryParams condition : andConditions) {
            if (condition == null) {
                continue;
            }

            Path<?> path = this.getPath(root, condition.property);
            if (path == null) {
                continue;
            }

            switch (condition.operator) {
                case eq:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String) {
                            if (!((String) condition.value).isEmpty()) {
                                restrictions = cb.and(restrictions, cb.equal(path, condition.value));
                            }
                        } else {
                            restrictions = cb.and(restrictions, cb.equal(path, condition.value));
                        }
                    }
                    break;
                case ge:
                    if (Number.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof Number) {
                        restrictions = cb.and(restrictions, cb.ge((Path<Number>) path, (Number) condition.value));
                    }
                    break;
                case gt:
                    if (Number.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof Number) {
                        restrictions = cb.and(restrictions, cb.gt((Path<Number>) path, (Number) condition.value));
                    }
                    break;
                case in:
                    restrictions = cb.and(restrictions, path.in(condition.value));
                    break;
                case le:
                    if (Number.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof Number) {
                        restrictions = cb.and(restrictions, cb.le((Path<Number>) path, (Number) condition.value));
                    }
                    break;
                case lt:
                    if (Number.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof Number) {
                        restrictions = cb.and(restrictions, cb.lt((Path<Number>) path, (Number) condition.value));
                    }
                    break;
                case ne:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.and(restrictions, cb.notEqual(path, condition.value));
                        } else {
                            restrictions = cb.and(restrictions, cb.notEqual(path, condition.value));
                        }
                    }
                    break;
                case like:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.and(restrictions, cb.like((Path<String>) path, "%" + condition.value + "%"));
                        }
                    }
                    break;
                case ilike:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.and(restrictions, cb.like((Path<String>) path, (String) condition.value));
                        }
                    }
                    break;
                case llike:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.and(restrictions, cb.like((Path<String>) path, "%" + condition.value));
                        }
                    }
                    break;
                case rlike:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.and(restrictions, cb.like((Path<String>) path, condition.value + "%"));
                        }
                    }
                    break;
                case notIn:
                    restrictions = cb.and(restrictions, path.in(condition.value).not());
                    break;
                case isNull:
                    restrictions = cb.and(restrictions, path.isNull());
                    break;
                case isNotNull:
                    restrictions = cb.and(restrictions, path.isNotNull());
                    break;
                default:
                    break;
            }
        }
        return restrictions;
    }

    private Predicate getOrPredicates(Root<T> root, CriteriaBuilder cb) {
        Predicate restrictions = cb.conjunction();
        for (QueryParams condition : orConditions) {
            if (condition == null) {
                continue;
            }
            Path<?> path = this.getPath(root, condition.property);
            if (path == null) {
                continue;
            }

            switch (condition.operator) {
                case eq:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String) {
                            if (!((String) condition.value).isEmpty()) {
                                restrictions = cb.or(restrictions, cb.equal(path, condition.value));
                            }
                        }
                    } else {
                        restrictions = cb.or(restrictions, path.isNull());
                    }
                    break;
                case ge:
                    if (Number.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof Number) {
                        restrictions = cb.or(restrictions, cb.ge((Path<Number>) path, (Number) condition.value));
                    }
                    break;
                case gt:
                    if (Number.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof Number) {
                        restrictions = cb.or(restrictions, cb.gt((Path<Number>) path, (Number) condition.value));
                    }
                    break;
                case in:
                    restrictions = cb.or(restrictions, path.in(condition.value));
                    break;
                case le:
                    if (Number.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof Number) {
                        restrictions = cb.or(restrictions, cb.le((Path<Number>) path, (Number) condition.value));
                    }
                    break;
                case lt:
                    if (Number.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof Number) {
                        restrictions = cb.or(restrictions, cb.lt((Path<Number>) path, (Number) condition.value));
                    }
                    break;
                case ne:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.or(restrictions, cb.notEqual(path, condition.value));
                        } else {
                            restrictions = cb.or(restrictions, cb.notEqual(path, condition.value));
                        }
                    }
                    break;
                case like:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.or(restrictions, cb.like((Path<String>) path, "%" + condition.value + "%"));
                        }
                    }
                    break;
                case ilike:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.or(restrictions, cb.like((Path<String>) path, (String) condition.value));
                        }
                    }
                    break;
                case llike:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.or(restrictions, cb.like((Path<String>) path, "%" + condition.value));
                        }
                    }
                    break;
                case rlike:
                    if (condition.value != null) {
                        if (String.class.isAssignableFrom(path.getJavaType()) && condition.value instanceof String && !((String) condition.value).isEmpty()) {
                            restrictions = cb.or(restrictions, cb.like((Path<String>) path, condition.value + "%"));
                        }
                    }
                    break;
                case notIn:
                    restrictions = cb.or(restrictions, path.in(condition.value).not());
                    break;
                case isNull:
                    restrictions = cb.or(restrictions, path.isNull());
                    break;
                case isNotNull:
                    restrictions = cb.or(restrictions, path.isNotNull());
                    break;
                default:
                    break;
            }
        }
        return restrictions;
    }

    private List<javax.persistence.criteria.Order> getOrders(Root<T> root, CriteriaBuilder cb) {
        List<javax.persistence.criteria.Order> orderList = new ArrayList<>();
        if (root == null || CollectionUtils.isEmpty(orders)) {
            return orderList;
        }
        for (Order order : orders) {
            if (order == null) {
                continue;
            }
            String property = order.getProperty();
            Sort.Direction direction = order.getDirection();
            Path<?> path = this.getPath(root, property);
            if (path == null || direction == null) {
                continue;
            }
            switch (direction) {
                case ASC:
                    orderList.add(cb.asc(path));
                    break;
                case DESC:
                    orderList.add(cb.desc(path));
                    break;
                default:
                    break;
            }
        }
        return orderList;
    }

    /**
     * 获取Path
     *
     * @param path         Path
     * @param propertyPath 属性路径
     * @return Path
     */
    private <X> Path<X> getPath(Path<?> path, String propertyPath) {
        if (path == null || StringUtils.isEmpty(propertyPath)) {
            return (Path<X>) path;
        }
        String property = StringUtils.substringBefore(propertyPath, PROPERTY_SEPARATOR);
        return getPath(path.get(property), StringUtils.substringAfter(propertyPath, PROPERTY_SEPARATOR));
    }

    /**
     * 运算符
     */
    public enum Operator {

        /**
         * 等于
         */
        eq(" = "),

        /**
         * 不等于
         */
        ne(" != "),

        /**
         * 大于
         */
        gt(" > "),

        /**
         * 小于
         */
        lt(" < "),

        /**
         * 大于等于
         */
        ge(" >= "),

        /**
         * 小于等于
         */
        le(" <= "),

        /**
         * 类似
         */
        like(" like "),

        /**
         * 类似
         */
        rlike("like "),

        /**
         * 类似
         */
        llike(" like "),

        /**
         * 类似
         */
        ilike(" like "),

        /**
         * 包含
         */
        in(" in "),

        /**
         * 包含
         */
        notIn(" not in "),

        /**
         * 为Null
         */
        isNull(" is NULL "),

        /**
         * 不为Null
         */
        isNotNull(" is not NULL ");

        private String operator;

        Operator(String operator) {
            this.operator = operator;
        }

        public String getOperator() {
            return operator;
        }

        public void setOperator(String operator) {
            this.operator = operator;
        }
    }

    /**
     * 条件
     */
    public static class QueryParams {

        Operator operator;
        String property;
        Object value;

        public QueryParams(String property, Operator operator, Object value) {
            this.operator = operator;
            this.property = property;
            this.value = value;
        }

        public QueryParams(String property, Operator operator) {
            this.operator = operator;
            this.property = property;
        }

        /**
         * 相等
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams eq(String property, Object value) {
            return new QueryParams(property, Operator.eq, value);
        }

        /**
         * 不相等
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams ne(String property, Object value) {
            return new QueryParams(property, Operator.ne, value);
        }

        /**
         * 大于
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams gt(String property, Object value) {
            return new QueryParams(property, Operator.gt, value);
        }

        /**
         * 小于
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams lt(String property, Object value) {
            return new QueryParams(property, Operator.lt, value);
        }

        /**
         * 大于等于
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams ge(String property, Object value) {
            return new QueryParams(property, Operator.ge, value);
        }

        /**
         * 小于等于
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams le(String property, Object value) {
            return new QueryParams(property, Operator.le, value);
        }

        /**
         * like %%
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams like(String property, Object value) {
            return new QueryParams(property, Operator.like, value);
        }

        /**
         * 右like xxx%
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams rlike(String property, Object value) {
            return new QueryParams(property, Operator.rlike, value);
        }

        /**
         *左like %xxx
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams llike(String property, Object value) {
            return new QueryParams(property, Operator.llike, value);
        }

        /**
         * 自定义模糊
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams ilike(String property, Object value) {
            return new QueryParams(property, Operator.ilike, value);
        }

        /**
         * 集合中
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams in(String property, Object value) {
            return new QueryParams(property, Operator.in, value);
        }

        /**
         * 不在集合中
         *
         * @param property
         * @param value
         * @return
         */
        public static QueryParams notIn(String property, Object value) {
            return new QueryParams(property, Operator.notIn, value);
        }

        /**
         * 是空
         *
         * @param property
         * @return
         */
        public static QueryParams isNull(String property) {
            return new QueryParams(property, Operator.isNull);
        }

        /**
         * 不是空
         *
         * @param property
         * @return
         */
        public static QueryParams isNotNull(String property) {
            return new QueryParams(property, Operator.isNotNull);
        }

    }

    /**
     * 排序
     */
    public static class Order {
        private String property;
        private Sort.Direction direction = Sort.Direction.ASC;

        /**
         * 构造方法
         */
        public Order() {
        }

        /**
         * 构造方法
         *
         * @param property  属性
         * @param direction 方向
         */
        public Order(String property, Sort.Direction direction) {
            this.property = property;
            this.direction = direction;
        }

        /**
         * 返回递增排序
         *
         * @param property 属性
         * @return 递增排序
         */
        public static Order asc(String property) {
            return new Order(property, Sort.Direction.ASC);
        }

        /**
         * 返回递减排序
         *
         * @param property 属性
         * @return 递减排序
         */
        public static Order desc(String property) {
            return new Order(property, Sort.Direction.DESC);
        }


        @Override
        public String toString() {
            return property + " " + direction.name();
        }

        public Sort.Direction getDirection() {
            return direction;
        }

        public String getProperty() {
            return property;
        }
    }
}

 

转载于:https://my.oschina.net/mx1014/blog/1577930