1.foreach 迭代类型：属性
新建实体类Grade
com.bb.entity.Grade.java
package com.bb.entity;
import java.util.List;
public class Grade {
private List<Integer> ids;
public Grade() {
}
public Grade(List<Integer> ids) {
this.ids = ids;
}
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
studentMapper.xml
<!-- foreach 迭代查询：属性-->
<select id="selectForeachAttr" parameterType="Grade" resultType="Student">
select * from student
<where>
-- 集合判断大小使用ids.size
<if test="ids!=null and ids.size>0">
-- collection 指定要遍历的对象，此处为grade对象的属性ids
-- item 指定遍历的项目名，可以随意定义，为便于理解 此处定义为id
-- separator为分隔符，如 id in （id1，id2，id3）的逗号
<foreach collection="ids" open=" and id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
StudentMapper.java接口
public interface StudentMapper {
// foreach 属性
List<Student> selectForeachAttr(Grade grade);
}
TestMybatis.java
//foreach 属性
public static void queryForeachAttr() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Grade grade = new Grade();
//建立一个数组集合，并将集合放入grade对象中，ids即为对象的属性
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
grade.setIds(ids);
List<Student> students = mapper.selectForeachAttr(grade);
System.out.println(students);
}
输入结果：
[Student{id=1, name='bb', age=11, address=null}, Student{id=2, name='ss', age=22, address=null}]
2.foreach 迭代类型：数组
studentMapper.xml
<!-- foreach 迭代查询：数组 int[] -->
<select id="selectForeachArray" parameterType="int[]" resultType="Student">
select * from student
<where>
-- 数组判断大小使用array.length
-- 数组的test、collection 固定使用字段为array
<if test="array!=null and array.length>0">
<foreach collection="array" item="id" separator="," open=" and id in (" close=")">
#{id}
</foreach>
</if>
</where>
</select>
上述代码中使用open=" and id in (" 报错，使用open=" id in (" 去掉and后不报错，不知道是什么原因
找到原因：<where> <if>标签中写了两段注释，将该注释删掉或转移到其他地方，保证<where> <if>之间没有东西即可，也有可能是idea的bug，暂时如此解决。
StudentMapper.java
public interface StudentMapper {
// foreach 数组
List<Student> selectForeachArray(int[] ids);
}
TestMybatis.java
//foreach 数组
public static void queryForeahArray() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
int[] ids = {1,2};
List<Student> students = mapper.selectForeachArray(ids);
System.out.println(students);
}
输入结果：
[Student{id=1, name='bb', age=11, address=null}, Student{id=2, name='ss', age=22, address=null}]
3.foreach 迭代类型：集合
studentMapper.xml
<!-- foreach 迭代查询：集合 list -->
<select id="selectForeachList" parameterType="list" resultType="Student">
select * from student
-- 集合判断大小使用list.size
-- 集合的test、collection 固定使用字段为list
<where>
<if test="list!=null and list.size>0">
<foreach collection="list" open=" and id in (" close=")" separator="," item="id">
#{id}
</foreach>
</if>
</where>
</select>
StudentMapper.java
public interface StudentMapper {
// foreach 集合list
List<Student> selectForeachList(List<Integer> ids);
}
TestMybatis.java
//foreach 集合 list
public static void queryForeahList() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
List<Student> students = mapper.selectForeachList(ids);
System.out.println(students);
}
输入结果：
[Student{id=1, name='bb', age=11, address=null}, Student{id=2, name='ss', age=22, address=null}]
4.foreach 迭代类型：对象数组
studentMapper.xml
<!-- foreach 迭代查询：对象数组 Object[] -->
<select id="selectForeachObjectArray" parameterType="Object[]" resultType="Student">
select * from student
<where>
-- 对象数组判断大小使用array.length
-- 对象数组的test、collection 固定使用字段为array
-- 对象数组的item为对象Student，遍历时为对象的属性 student.id
<if test="array!=null and array.length>0">
<foreach collection="array" open="id in (" close=" ) " item="Student" separator=",">
#{Student.id}
</foreach>
</if>
</where>
</select>
StudentMapper.java
public interface StudentMapper {
// foreach 数组array
List<Student> selectForeachObjectArray(Student[] students);
}
TestMybatis.java
//foreach 对象数组 ObjectArray
public static void queryForeahObjectArray() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student1 = new Student();
student1.setId(1);
Student student2 = new Student();
student2.setId(2);
Student[] students = {student1, student2};
List<Student> studentList = mapper.selectForeachObjectArray(students);
System.out.println(studentList);
}
输入结果：
[Student{id=1, name='bb', age=11, address=null}, Student{id=2, name='ss', age=22, address=null}]
5.sql片段
studentMapper.xml
<!-- sql片段-->
<sql id="sqlSelect">
select * from student
</sql>
<select id="selectSql" resultType="Student">
<include refid="sqlSelect"></include>
</select>
不再赘述
