动态sql: sql的内容是变化的,可以根据条件获取到不同的sql语句。主要是where部分发生变化。
一、foreach
<foreach> 循环java中的数组, list集合的。主要用在sql的in语句中。
collection="集合类型(list、array)"
item="集合中的成员"
open="开始字符"
close="结束字符"
separator="集合成员之间的分隔符"
1.1 例子
接口:
1 2
| List<Student> selectStudentForeach(List<Integer> list);
|
xml:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
|
<select id="selectStudentForeach" resultType="Student"> /* 引入sql */ <include refid="studentSql" /> where id in <foreach collection="list" item="myId" open="(" close=")" separator=","> #{myId} </foreach> </select>
|
测试类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| @Test public void testSelectStudentForeach() {
SqlSession sqlSession = MyBatisUtils.getSqlSession(); StudentDao studentDao = sqlSession.getMapper(StudentDao.class); List<Integer> list = new ArrayList<>(); list.add(1001); list.add(1002); List<Student> students = studentDao.selectStudentForeach(list); students.forEach(stu1 -> System.out.println(stu1)); sqlSession.close(); }
|
1.2 例子
接口:
1 2
| List<Student> selectStudentForeach2Student(List<Student> list);
|
xml:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
|
<select id="selectStudentForeach2Student" resultType="Student"> /* 引入sql */ <include refid="studentSql" /> where id in ( <foreach collection="list" item="myStu"> #{myStu.id} , </foreach> -1 ) </select>
|
测试类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| @Test public void testSelectStudentForeach2Student() {
SqlSession sqlSession = MyBatisUtils.getSqlSession(); StudentDao studentDao = sqlSession.getMapper(StudentDao.class); List<Student> list = new ArrayList<>(); Student s1 = new Student(); s1.setId(1001); Student s2 = new Student(); s2.setId(1002); list.add(s1); list.add(s2); List<Student> students = studentDao.selectStudentForeach2Student(list); students.forEach(stu1 -> System.out.println(stu1)); sqlSession.close(); }
|