MySQL + JPA 动态条件查询

maven 依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <version>2.3.12.RELEASE</version>
</dependency>

实体类

package cn.xxx.xxx.project.dal.mysql.dataobject;

import lombok.Data;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.*;
import java.util.Date;

/**
 * 通用实验
 */
@Entity
@Table(name = "abtest_normal")
@Data
public class AbtestNormalDO {

    /**
     * 主键ID
     */
    @Id
    @GeneratedValue(generator = "custom_id")
    @GenericGenerator(name = "custom_id", strategy = "cn.yizhoucp.cupid.project.dal.mysql.strategy.CustomIdGenerator")
    private Long id;

    /**
     * 实验名字
     */
    private String name;

    /**
     * 操作-修改页面中的-备注
     */
    private String description;

    /**
     * 操作-修改页面中的-配置
     */
    private String config;

    /**
     * 数据创建时间
     */
    private Date created_at;

    /**
     * 数据更新时间
     */
    private Date updated_at;

    /**
     * 数据逻辑删除时间
     */
    private Date deleted_at;

    /**
     * 状态
     */
    private Integer status;

    /**
     * 应用
     */
    private String vest_channel;

    /**
     * 实验的id
     */
    private String experiment_id;

    /**
     * 创建者
     */
    private String creator;

    /**
     * 固化的实验组
     */
    private String solidify_abtest;
}

DAO

package cn.xxx.xxx.project.dal.mysql.dao;

import cn.xxx.xxx.project.dal.mysql.dataobject.AbtestNormalDO;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import javax.transaction.Transactional;

/**
 * 通用实验 - DAO
 */
public interface AbtestNormalDAO extends JpaSpecificationExecutor<AbtestNormalDO>, CrudRepository<AbtestNormalDO, Long> {

}

动态条件查询

	import org.springframework.data.jpa.domain.Specification;	
	import javax.persistence.criteria.CriteriaBuilder;
	import javax.persistence.criteria.CriteriaQuery;
	import javax.persistence.criteria.Predicate;
	import javax.persistence.criteria.Root;
	

	/**
     * 根据给定条件构造Abtest Normal实验的查询规范。
     *
     * @param vest_channel 应用
     * @param status       cn.yizhoucp.cupid.project.dal.mysql.dataobject.AbtestNormalDO#status
     * @return 返回一个AbtestNormalDO类型的查询规范，用于JPA查询。
     */
    private Specification<AbtestNormalDO> getAbtestNormalSpecification(String vest_channel, Integer status) {
        Specification<AbtestNormalDO> specification = new Specification<AbtestNormalDO>() {
            @Override
            public Predicate toPredicate(Root<AbtestNormalDO> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if (!StringUtils.isEmpty(vest_channel)) {
                    predicates.add(criteriaBuilder.equal(root.get("vest_channel"), vest_channel)); // 根据vest_channel进行查询
                }
                if (Objects.nonNull(status)) {
                    predicates.add(criteriaBuilder.equal(root.get("status"), status)); // 根据status进行查询
                }
                // 返回所有查询条件的逻辑与
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
        return specification;
    }

	/**
     * 根据 vest_channel、status 查询 abtest_normal 表数据
     *
     * @param vest_channel 应用
     * @param status       cn.yizhoucp.cupid.project.dal.mysql.dataobject.AbtestNormalDO#status
     * @return
     */
	public List<AbtestNormalDO> abtestNormalFind(String vest_channel, Integer status) {
        log.info("入参 vest_channel : {} status : {}", vest_channel, status);
        if (StringUtils.isEmpty(vest_channel) || Objects.isNull(status)) {
            throw new ServiceException(ErrorCode.INVALID_PARAM);
        }

        Specification<AbtestNormalDO> spec = getAbtestNormalSpecification(vest_channel, status);
        List<AbtestNormalDO> all = abtestNormalDAO.findAll(spec);

        return all;
    }