public List<Homework> getHomeworks(Integer schId, Integer gradeId, Integer classId, Integer subjectId, String beginTime, String endTime) {
QueryWrapper<Homework> hw = new QueryWrapper<>();
hw.eq("sch_id", schId).eq("grade_id", gradeId)
.eq("subject_id", subjectId)
.eq("node", 4).between("time", beginTime, endTime)
.and(qr -> qr.eq("type", 0)
.or(qrw -> qrw.eq("type", 1)
.apply("JSON_CONTAINS(clazz_ids, '" + classId + "')")));
return homeworkMapper.selectList(hw);
}
底层执行的sql为:
SELECT * FROM homework
WHERE sch_id = #{schId}
AND grade_id = #{gradeId}
AND subject_id = #{subjectId}
AND node = 4
AND time BETWEEN #{beginTime} AND #{endTime}
AND (type = 0 OR (type = 1 AND JSON_CONTAINS(clazz_ids, #{classId})))
其中clazz_id在MySQL 8.0版本中存储类型为json。(5.7存不了json)