Spring Boot中如何结合教师ID列表与时间区间实现查询

你之前用case when的写法不生效，核心原因是原生SQL里对集合参数（List）的处理逻辑有误，结合Spring Data JPA的特性，给你两种可行的修改方案：

方案一：代码层控制空列表传参，简化SQL逻辑 #

1. 修改方法签名 #

添加教师ID列表参数：

List<Tuple> getAllDetailTeacherCheckOn(
    @Param("start_time") LocalDateTime startTime,
    @Param("end_time") LocalDateTime endTime,
    @Param("teacher_ids") List<Long> teacherIds // 假设ID类型为Long，可根据实际调整
);

2. 更新SQL查询条件 #

在原有时间筛选的基础上，追加教师ID的判断逻辑：

@Query(
        value = "select i.start_time, i.end_time, i.duration_time, s.subject_class_name, u.name as teacher_name," +
        " dc.name as class_room_name, ts.name as subject_name, tsc.total_time as total_time_subject_class," +
        "  i.teacher_context_type, i.teacher_id, s.subject_class_id" +
        " from tbl_attendance_sheet as s inner join tbl_attendance_instance as i on s.id = i.attendance_sheet_id " +
        " left join phong_hoc dc on i.class_id = dc.id " +
        " left join lms_user  u on u.id = i.teacher_user_id " +
        " left join tbl_subject_class as tsc on tsc.id = s.subject_class_id " +
        " left join tbl_subject as ts on ts.id = tsc.subject_id" +
        " where " +
        " ( " +
        " ((:start_time >= i.start_time and :start_time <= i.end_time) " +
        " or (i.start_time <= :end_time and :end_time <= i.end_time) or " +
        " (:start_time < i.start_time and :end_time > i.end_time)) " +
        " ) " +
        " and (:teacher_ids is null or i.teacher_id in :teacher_ids)", // 新增的教师ID筛选逻辑
        nativeQuery = true
    )

注意事项 #

调用该方法时，如果教师ID列表为空，不要传空List，直接传null，否则会生成in ()这种非法SQL语法，导致执行报错。

方案二：用SpEL表达式自动处理空列表/Null，无需代码层干预 #

这种方式更灵活，不管传入的是null还是空List，都能自动适配：

@Query(
        value = "select i.start_time, i.end_time, i.duration_time, s.subject_class_name, u.name as teacher_name," +
        " dc.name as class_room_name, ts.name as subject_name, tsc.total_time as total_time_subject_class," +
        "  i.teacher_context_type, i.teacher_id, s.subject_class_id" +
        " from tbl_attendance_sheet as s inner join tbl_attendance_instance as i on s.id = i.attendance_sheet_id " +
        " left join phong_hoc dc on i.class_id = dc.id " +
        " left join lms_user  u on u.id = i.teacher_user_id " +
        " left join tbl_subject_class as tsc on tsc.id = s.subject_class_id " +
        " left join tbl_subject as ts on ts.id = tsc.subject_id" +
        " where " +
        " ( " +
        " ((:start_time >= i.start_time and :start_time <= i.end_time) " +
        " or (i.start_time <= :end_time and :end_time <= i.end_time) or " +
        " (:start_time < i.start_time and :end_time > i.end_time)) " +
        " ) " +
        " and (:#{#teacherIds == null or #teacherIds.isEmpty()} or i.teacher_id in :teacher_ids)", // SpEL表达式判断列表状态
        nativeQuery = true
    )
List<Tuple> getAllDetailTeacherCheckOn(
        @Param("start_time") LocalDateTime startTime,
        @Param("end_time") LocalDateTime endTime,
        @Param("teacher_ids") List<Long> teacherIds
    );

这里的:#{#teacherIds == null or #teacherIds.isEmpty()}是Spring Data JPA的SpEL表达式，会在SQL执行前解析为布尔值：

• 当teacherIds为null或空列表时，该表达式返回true，直接跳过ID筛选
• 当teacherIds有值时，执行i.teacher_id in :teacher_ids进行精确匹配

内容的提问来源于stack exchange，提问作者DUONG HOA