首先基本sql和异常处理可以参考我另一篇:
SQL:对一个字段多条件查询,取交集(对A字段匹配的多个B字段求交集)_caozg0的博客-CSDN博客
接下来讲mybatis的场景:查询参数是对象,而其中还有List集合,此时我们要对集合中的元素遍历到sql中:
查询参数对象:
public class QueryDto {
private String name;
private List<String> teachs;
}
dao层:
用@Param标签标示变量名。
Object queryStudentWithSomeTeach(@Param("queryDto ") QueryDto queryDto );
XML:
<if test="queryDto .teachs!=null and queryDto .teachs.size != 0">
and student_id in (select student_id from table_xxx
<where>
<foreach collection="queryDto .teachs" open="tag in (" close=") " item="oneId" separator=",">
#{oneId}
</foreach>
</where>
group by student_id having count(student_id ) = ${queryDto .teachs.size})
</if>
注意以上只是查询sql中的一个条件,拼接起来就是:
and student_id in (select student_id from table_xxx where teach_id in (1,2,3) group by student_id having count(student_id ) = 3;)