优化JpaRepository的Specification查询

组装自己的 MySpecification

import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.StrUtil;
import lombok.Getter;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;
import java.util.Collection;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

@Getter
public class MySpecification<T> {
    private final Map<Class<?>, MySpecificationJoin> mySpecificationJoinMap;
    private final Map<Class<?>, Join> joineMap;
    private Specification<T> specification;

    public MySpecification() {
        this.specification = Specification.where(null);
        this.mySpecificationJoinMap = new ConcurrentHashMap<>();
        this.joineMap = new ConcurrentHashMap<>();
    }

    /**
     * 连表
     *
     * @param joinClass 联接类
     * @param joinType  联接类型
     * @return {@link MySpecification}<{@link T}>
     */
    public MySpecification<T> join(Class<?> joinClass, JoinType joinType) {
        return this.join(joinClass, null, joinType);
    }

    /**
     * 连表
     *
     * @param joinClass     联接类
     * @param joinFieldName 联接字段名称
     * @param joinType      联接类型
     * @return {@link MySpecification}<{@link T}>
     */
    public MySpecification<T> join(Class<?> joinClass, String joinFieldName, JoinType joinType) {
        String joinClassName = StrUtil.lowerFirst(joinClass.getSimpleName());
        MySpecificationJoin join = MySpecificationJoin.builder().joinClass(joinClass).joinClassName(joinClassName).joinType(joinType).build();
        if (EmptyUtil.isNotEmpty(joinFieldName)) {
            join.setJoinFieldName(joinFieldName);
            join.setJoinColumnName(StrUtil.toUnderlineCase(joinFieldName));
        }
        this.mySpecificationJoinMap.put(joinClass, join);
        return this;
    }

    /**
     * 获取联接类型
     *
     * @param joinClass 联接类
     * @return {@link JoinType}
     */
    private <K> Join<T, K> getJoin(Root<T> root, Class<K> joinClass) {
        if (EmptyUtil.isEmpty(joinClass)) {
            return null;
        }

        Join<T, K> join = joineMap.get(joinClass);
        if (EmptyUtil.isNotEmpty(join)) {
            return join;
        }
        MySpecificationJoin mySpecificationJoin = mySpecificationJoinMap.get(joinClass);
        KeduAssert.isNotEmpty(mySpecificationJoin, ErrorCode.MY_SPECIFICATION_JOIN_IS_NULL, joinClass.getSimpleName());

        if (EmptyUtil.isNotEmpty(mySpecificationJoin.getJoinFieldName())) {
            join = root.join(mySpecificationJoin.getJoinFieldName(), mySpecificationJoin.getJoinType());
        } else {
            join = root.join(mySpecificationJoin.getJoinClassName(), mySpecificationJoin.getJoinType());
        }
        joineMap.put(joinClass, join);
        return join;
    }

    /**
     * 模糊查询
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public MySpecification<T> like(String fieldName, String value) {
        return this.like(null, fieldName, value, EmptyUtil.isBizAllNotEmpty(fieldName, value));
    }

    /**
     * 模糊查询
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @param condition 条件
     * @return {@link MySpecification}<{@link T}>
     */
    public MySpecification<T> like(String fieldName, String value, Boolean condition) {
        return this.like(null, fieldName, value, condition);
    }

    /**
     * 模糊查询
     *
     * @param joinClass 联接类
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public MySpecification<T> like(Class<?> joinClass, String fieldName, String value) {
        return this.like(joinClass, fieldName, value, EmptyUtil.isBizAllNotEmpty(fieldName, value));
    }

    /**
     * 模糊查询
     *
     * @param joinClass 联接类
     * @param fieldName 字段名称
     * @param value     价值
     * @param condition 条件
     * @return {@link MySpecification}<{@link T}>
     */
    public <K> MySpecification<T> like(Class<K> joinClass, String fieldName, String value, Boolean condition) {
        if (BooleanUtil.isTrue(condition)) {
            Specification<T> spec = (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
                Join<T, K> join = this.getJoin(root, joinClass);
                Expression<String> fieldExpression;
                if (EmptyUtil.isBizEmpty(join)) {
                    fieldExpression = root.get(fieldName);
                } else {
                    fieldExpression = join.get(fieldName);
                }
                return cb.like(fieldExpression, "%" + value + "%");
            };
            this.specification = this.specification.and(spec);
        }
        return this;
    }

    /**
     * 相等
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public MySpecification<T> eq(String fieldName, Object value) {
        return this.eq(null, fieldName, value, EmptyUtil.isBizAllNotEmpty(fieldName, value));
    }

    /**
     * 相等
     *
     * @param fieldName 字段名称
     * @param value     值
     * @param condition 条件
     * @return {@link MySpecification}<{@link T}>
     */
    public MySpecification<T> eq(String fieldName, Object value, Boolean condition) {
        return this.eq(null, fieldName, value, condition);
    }

    /**
     * 相等
     *
     * @param joinClass 联接类
     * @param fieldName 字段名称
     * @param value     值
     * @return {@link MySpecification}<{@link T}>
     */
    public MySpecification<T> eq(Class<?> joinClass, String fieldName, Object value) {
        return this.eq(joinClass, fieldName, value, EmptyUtil.isBizAllNotEmpty(fieldName, value));
    }

    /**
     * 相等
     *
     * @param joinClass 联接类
     * @param fieldName 字段名称
     * @param value     价值
     * @param condition 条件
     * @return {@link MySpecification}<{@link T}>
     */
    public <K> MySpecification<T> eq(Class<K> joinClass, String fieldName, Object value, Boolean condition) {
        if (BooleanUtil.isTrue(condition)) {
            Specification<T> spec = (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
                Join<T, K> join = this.getJoin(root, joinClass);
                Expression<String> fieldExpression;
                if (EmptyUtil.isBizEmpty(join)) {
                    fieldExpression = root.get(fieldName);
                } else {
                    fieldExpression = join.get(fieldName);
                }
                return cb.equal(fieldExpression, value);
            };
            this.specification = this.specification.and(spec);
        }
        return this;
    }

    /**
     * IN
     *
     * @param fieldName 字段名称
     * @param values    价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <J> MySpecification<T> in(String fieldName, Collection<J> values) {
        return this.in(null, fieldName, values, EmptyUtil.isBizAllNotEmpty(fieldName, values));
    }

    /**
     * IN
     *
     * @param fieldName 字段名称
     * @param values    价值
     * @param condition 条件
     * @return {@link MySpecification}<{@link T}>
     */
    public <J> MySpecification<T> in(String fieldName, Collection<J> values, Boolean condition) {
        return this.in(null, fieldName, values, condition);
    }


    /**
     * IN
     *
     * @param joinClass 联接类
     * @param fieldName 字段名称
     * @param values    价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <J> MySpecification<T> in(Class<?> joinClass, String fieldName, Collection<J> values) {
        return this.in(joinClass, fieldName, values, EmptyUtil.isBizAllNotEmpty(fieldName, values));
    }

    /**
     * 模糊查询
     *
     * @param fieldName 字段名称
     * @param joinClass 联接类
     * @param values    价值观
     * @param condition 条件
     * @return {@link MySpecification}<{@link T}>
     */
    public <K, J> MySpecification<T> in(Class<K> joinClass, String fieldName, Collection<J> values, Boolean condition) {
        if (BooleanUtil.isTrue(condition)) {
            Specification<T> spec = (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
                Join<T, K> join = this.getJoin(root, joinClass);
                Path<J> fieldExpression;
                if (EmptyUtil.isBizEmpty(join)) {
                    fieldExpression = root.get(fieldName);
                } else {
                    fieldExpression = join.get(fieldName);
                }
                CriteriaBuilder.In<J> inClause = cb.in(fieldExpression);
                values.forEach(inClause::value);
                return inClause;
            };
            this.specification = this.specification.and(spec);
        }
        return this;
    }

    /**
     * 大于
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <K extends Comparable<? super K>> MySpecification<T> gt(String fieldName, K value) {
        return this.gt(fieldName, value, EmptyUtil.isBizAllNotEmpty(fieldName, value));
    }

    /**
     * 大于
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <K extends Comparable<? super K>> MySpecification<T> gt(String fieldName, K value, Boolean condition) {
        if (BooleanUtil.isTrue(condition)) {
            Specification<T> spec = (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) ->
                    cb.greaterThan(root.get(fieldName), value);
            this.specification = this.specification.and(spec);
        }
        return this;
    }

    /**
     * 大于等于
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <K extends Comparable<? super K>> MySpecification<T> gte(String fieldName, K value) {
        return this.gte(fieldName, value, EmptyUtil.isBizAllNotEmpty(fieldName, value));
    }

    /**
     * 大于等于
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <K extends Comparable<? super K>> MySpecification<T> gte(String fieldName, K value, Boolean condition) {
        if (BooleanUtil.isTrue(condition)) {
            Specification<T> spec = (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) ->
                    cb.greaterThanOrEqualTo(root.get(fieldName), value);
            this.specification = this.specification.and(spec);
        }
        return this;
    }

    /**
     * 小于
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <K extends Comparable<? super K>> MySpecification<T> lt(String fieldName, K value) {
        return this.lt(fieldName, value, EmptyUtil.isBizAllNotEmpty(fieldName, value));
    }

    /**
     * 小于
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <K extends Comparable<? super K>> MySpecification<T> lt(String fieldName, K value, Boolean condition) {
        if (BooleanUtil.isTrue(condition)) {
            Specification<T> spec = (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) ->
                    cb.lessThan(root.get(fieldName), value);
            this.specification = this.specification.and(spec);
        }
        return this;
    }

    /**
     * 小于等于
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <K extends Comparable<? super K>> MySpecification<T> lte(String fieldName, K value) {
        return this.lte(fieldName, value, EmptyUtil.isBizAllNotEmpty(fieldName, value));
    }

    /**
     * 小于等于
     *
     * @param fieldName 字段名称
     * @param value     价值
     * @return {@link MySpecification}<{@link T}>
     */
    public <K extends Comparable<? super K>> MySpecification<T> lte(String fieldName, K value, Boolean condition) {
        if (BooleanUtil.isTrue(condition)) {
            Specification<T> spec = (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) ->
                    cb.lessThan(root.get(fieldName), value);
            this.specification = this.specification.and(spec);
        }
        return this;
    }
}

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.criteria.JoinType;

/**
 * MySpecification-连表
 *
 * @author 帅哥
 * @date 2024/05/30
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class MySpecificationJoin {
    /**
     * 联接类
     */
    private Class<?> joinClass;
    /**
     * 联接类名，首字母小写
     */
    private String joinClassName;
    /**
     * 联接字段名称，驼峰格式
     */
    private String joinFieldName;
    /**
     * 联接列名，下划线格式
     */
    private String joinColumnName;
    /**
     * 联接类型
     */
    private JoinType joinType;
}

MySpecificationJoin 支持 like、eq（等于）、in、gt（大于）、gte（大于等于）、lt（小于）、lte （小于等于）和inner 查询

举例

CostType 费用类型

import lombok.*;
import lombok.experimental.Accessors;
import org.hibernate.annotations.DynamicUpdate;

import javax.persistence.Entity;
import javax.persistence.Table;

/**
 * @author 帅哥
 * @date 2024-05-15 17:46:15
 */
@Data
@Entity
@DynamicUpdate
@Table(name = "sc_cost_type")
public class CostType  extends BaseData {
    private static final long serialVersionUID = 1L;
    /**
     * 唯一标识ID
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long id;
    /**
     * 是否生效，1 - 生效 0 - 失效
     */
    protected Boolean isEnable;
    /**
     * 创建时间
     */
    protected Timestamp createTime;
    /**
     * 更新时间
     */
    protected Timestamp updateTime;    
    /**
     * 名称
     */
    private String name;
    /**
     * 父ID，一级的父类ID为0
     */
    private Long parentId;
}

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

/**
 * @author 帅哥
 * @date 2024-05-15 17:46:15
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("系统配置-费用类型 列表请求参数")
public class CostTypeListReq implements Serializable {
    @ApiModelProperty("名称")
    private String name;
    @ApiModelProperty("父ID，一级的父类ID为0")
    private Long parentId;
}

    public List<CostType> list(CostTypeListReq req) {
        MySpecification<CostType> mySpecification = new MySpecification<>();
        mySpecification.eq(OtherConstant.IS_ENABLE, true);
        mySpecification.eq(getFieldName(CostType::getParentId), req.getParentId(), EmptyUtil.isNotEmpty(req.getParentId()));
        mySpecification.like(getFieldName(CostType::getName), req.getName());
        return costTypeRepository.findAll(mySpecification.getSpecification());
    }

上例中 CostType 支持 name模糊查询，parentId 相等查询

连表查询

@Data
@Accessors(chain = true)
@Entity
@DynamicUpdate
@Table(name = "check_price")
public class CheckPrice {
    private static final long serialVersionUID = 1L;
    
    /**
     * 唯一标识ID
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long id;

    /**
     * 是否生效，1 - 生效 0 - 失效
     */
    protected Boolean isEnable;
    /**
     * 创建时间
     */
    protected Timestamp createTime;
    /**
     * 更新时间
     */
    protected Timestamp updateTime;
    /**
     * 报价申请（报价审核）ID
     */
    @ManyToOne
    @JoinColumn(name = "quote_apply_id")
    private QuoteApply quoteApply;
    @Column(name = "quote_apply_id", insertable = false, updatable = false)
    private Long quoteApplyId;
    /**
     * 医院名称
     */
    private String hospitalName;
    ......
}

import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.DynamicUpdate;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.Where;

import javax.persistence.*;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by ChenGong on 2020/11/25
 */
@Entity
@Table(name = "quote_apply")
@DynamicUpdate
@Getter
@Setter
public class QuoteApply  {

    /**
     * 唯一标识ID
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long id;

    /**
     * 是否生效，1 - 生效 0 - 失效
     */
    protected Boolean isEnable;
    /**
     * 创建时间
     */
    protected Timestamp createTime;
    /**
     * 更新时间
     */
    protected Timestamp updateTime;
    /**
     * 医院名称
     */
    private String hospitalName;
    /**
     * 申请状态
     */
    private String auditStatus;
    ......
}

/**
 * @author 帅哥
 * @date 2024-05-15 17:46:15
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("核价 分页请求参数")
public class CheckPricePageReq extends PageReq implements Serializable {
    @ApiModelProperty("医院名称")
    private String hospitalName;
    @ApiModelProperty("报价状态")
    private String quoteApplyStatus;
}

    public Page<CheckPrice> page(CheckPricePageReq req) {
        Pageable pageable = getPageable(req);
        MySpecification<CheckPrice> mySpecification = new MySpecification<>();
        mySpecification.join(QuoteApply.class, JoinType.INNER);
        mySpecification.eq(OtherConstant.IS_ENABLE, true);
        mySpecification.like(getFieldName(CheckPrice::getHospitalName), req.getHospitalName());
        mySpecification.eq(QuoteApply.class, getFieldName(QuoteApply::getAuditStatus), 
        return checkPriceRepository.findAll(mySpecification.getSpecification(), pageable);
    }

上列中，QuoteApply 和 CheckPrice 一对多，通过check_price.quote_apply_id = quote_apply.id 关联。
医院名称 查询表 check_price.hospital_name ，报价状态 连表查询 quote_apply.audit_status