Spring JPA中如何动态处理多可选参数的WHERE子句？

针对多可选参数组合查询、避免SQL硬编码和方法冗余的问题，推荐两种实用的设计模式，结合Spring生态的实现方式：

1. 建造者模式（Builder Pattern） #

核心思路 #

通过建造者类动态拼接SQL的WHERE条件，仅针对非空参数添加查询条件，彻底摆脱固定AND连接的硬编码逻辑，同时将SQL构建逻辑与业务代码解耦。

代码实现 #

首先封装SQL构建的建造者类：

public class ClassroomQueryBuilder {
    private final StringBuilder sql;
    private final List<Object> params;

    public ClassroomQueryBuilder() {
        sql = new StringBuilder("SELECT * FROM classroom WHERE 1=1");
        params = new ArrayList<>();
    }

    public ClassroomQueryBuilder withStudentId(String studentId) {
        if (studentId != null && !studentId.isEmpty()) {
            sql.append(" AND studentid = ?");
            params.add(studentId);
        }
        return this;
    }

    public ClassroomQueryBuilder withTeacherId(String teacherId) {
        if (teacherId != null && !teacherId.isEmpty()) {
            sql.append(" AND teacherid = ?");
            params.add(teacherId);
        }
        return this;
    }

    public ClassroomQueryBuilder withClassroomNo(String classroomNo) {
        if (classroomNo != null && !classroomNo.isEmpty()) {
            sql.append(" AND classroomno = ?");
            params.add(classroomNo);
        }
        return this;
    }

    public String buildSql() {
        return sql.toString();
    }

    public Object[] buildParams() {
        return params.toArray();
    }
}

在Repository层调用建造者生成动态SQL：

@Repository
public class ClassroomRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Classroom> queryClassrooms(String studentId, String teacherId, String classroomNo) {
        ClassroomQueryBuilder builder = new ClassroomQueryBuilder()
                .withStudentId(studentId)
                .withTeacherId(teacherId)
                .withClassroomNo(classroomNo);
        return jdbcTemplate.query(builder.buildSql(), builder.buildParams(), new BeanPropertyRowMapper<>(Classroom.class));
    }
}

2. 规范模式（Specification Pattern） #

核心思路 #

将每个参数的查询条件封装为独立的Specification对象，根据参数是否存在动态组合这些条件。Spring Data JPA原生支持该模式，无需手写原生SQL，完全基于ORM的API构建查询逻辑。

代码实现 #

首先让Repository继承JpaSpecificationExecutor：

public interface ClassroomJpaRepository extends JpaRepository<Classroom, Long>, JpaSpecificationExecutor<Classroom> {
}

然后定义各个参数对应的Specification：

public class ClassroomSpecifications {
    public static Specification<Classroom> hasStudentId(String studentId) {
        return (root, query, criteriaBuilder) -> {
            if (studentId == null || studentId.isEmpty()) {
                return criteriaBuilder.conjunction(); // 空条件，不影响最终查询
            }
            return criteriaBuilder.equal(root.get("studentId"), studentId);
        };
    }

    public static Specification<Classroom> hasTeacherId(String teacherId) {
        return (root, query, criteriaBuilder) -> {
            if (teacherId == null || teacherId.isEmpty()) {
                return criteriaBuilder.conjunction();
            }
            return criteriaBuilder.equal(root.get("teacherId"), teacherId);
        };
    }

    public static Specification<Classroom> hasClassroomNo(String classroomNo) {
        return (root, query, criteriaBuilder) -> {
            if (classroomNo == null || classroomNo.isEmpty()) {
                return criteriaBuilder.conjunction();
            }
            return criteriaBuilder.equal(root.get("classroomNo"), classroomNo);
        };
    }
}

在Service层组合Specification并执行查询：

@Service
public class ClassroomService {
    @Autowired
    private ClassroomJpaRepository classroomJpaRepository;

    public List<Classroom> getClassrooms(String studentId, String teacherId, String classroomNo) {
        Specification<Classroom> spec = Specification.where(ClassroomSpecifications.hasStudentId(studentId))
                .and(ClassroomSpecifications.hasTeacherId(teacherId))
                .and(ClassroomSpecifications.hasClassroomNo(classroomNo));
        return classroomJpaRepository.findAll(spec);
    }
}

简化方案：Spring Data JPA动态@Query #

如果场景简单，也可以直接用@Query配合SpEL表达式实现动态条件，无需额外设计模式：

@Query("SELECT c FROM Classroom c WHERE " +
        "(:#{#studentId == null} OR c.studentId = :studentId) AND " +
        "(:#{#teacherId == null} OR c.teacherId = :teacherId) AND " +
        "(:#{#classroomNo == null} OR c.classroomNo = :classroomNo)")
List<Classroom> findByDynamicParams(@Param("studentId") String studentId,
                                    @Param("teacherId") String teacherId,
                                    @Param("classroomNo") String classroomNo);

这种方式代码量少，但参数过多时可读性会下降，适合参数数量少的场景。

内容的提问来源于stack exchange，提问作者Dnyaneshwar Jadhav