动态Sql就是在拼接sql语句,我们在拼接的时候要保证sql的正确性,按照sql的格式,去排列组合就行
<if>标签用来实现根据条件拼接sql语句,下面示例用来判断参数如果不为null,则拼接sql
<!-- 测试<where> <if>-->
<select id="selectStudent" parameterType="HashMap" resultType="org.student.entity.Student" >
select * from student where
<if test=" id != null and id != '' ">
id = #{id}
</if>
<if test=" age != null and age != '' ">
or age = #{age}
</if>
</select>
当第一个参数id的值不是null跟空时 则正常拼接sql语句 select * from student where id = #{id} or age = #{id}
但是当第一个参数id的值为null时 sql语句为select * from student where or age =#{} 这明显是错的sql语句 解决这个问题 需要用到<where>标签
当 where 中的条件使用的 if 标签较多时,这样的组合可能会导致错误, “where”标签会自动判断如果它包含的标签中有返回值的话,就在sql中插入一个‘where’,如果where标签最后返回的内容是以 and 或者or 开头的,也会被自动移除掉。
上面的示例用where标签改写后示例如下:
<!-- 测试<where> <if>-->
<select id="selectStudent" parameterType="HashMap" resultType="org.student.entity.Student" >
select * from student where
<where>
<if test=" id != null and id != '' ">
id = #{id}
</if>
<if test=" age != null and age != '' ">
or age = #{age}
</if>
</where>
</select>
<foreach>标签迭代的类型 数组、集合、对象数组
select * from student where age in(18,19,20)
数组例子:
<!-- 将多个元素值放入数组中int[] 然后根据数组信息查询 -->
<select id="queryArray" resultType="org.student.entity.Student" parameterType="int[]" >
select * from student
<where>
<if test= " array != null and array.length > 0 ">
<foreach collection="array" item="age" open="and age in(" close=")" separator=",">
#{age}
</foreach>
</if>
</where>
</select>
//将多个元素值放入数组中int[] 然后根据数组信息查询
public static void queryArray() throws IOException{
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sqlSession = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sqlSession.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
int[] array = {2 , 18};
List<Student> students = mapper.queryArray(array);
System.out.println(students);
}
集合例子:
<!-- 将多个元素放入集合List中 然后根据集合元素查询 -->
<select id="queryList" resultType="org.student.entity.Student" parameterType="List">
select * from student
<where>
<if test= " list != null and list.size > 0 ">
<foreach collection="list" item="grade" open="and grade in(" close=")" separator=",">
#{grade}
</foreach>
</if>
</where>
</select>
//将多个元素放入集合List中 然后根据集合元素查询
public static void queryList() throws IOException{
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sqlSession = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sqlSession.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Integer> list = new ArrayList<Integer>();
list.add(2);
list.add(10);
list.add(98);
List<Student> students = mapper.queryList(list);
System.out.println(students);
}
对象数组例子:
<!-- 对象数组 将多个元素放入对象数组中 Student[] students = {student0 , student1 , student2} -->
<select id="querySs" resultType="org.student.entity.Student" parameterType="Object[]">
select * from student
<where>
<if test="array != null and array.length > 0">
<foreach collection="array" item="student" open="and grade in (" close=")" separator=",">
#{student.grade}
</foreach>
</if>
</where>
</select>
//对象数组 将多个元素放入对象数组中 Student[] students = {student0 , student1 , student2}
public static void querySs() throws IOException{
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sqlSession = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sqlSession.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
Student stu1 = new Student("1","1",2,2);
Student stu2 = new Student("1","1",2,98);
Student stu3 = new Student("1","1",2,92);
Student[] students = {stu1 , stu2 , stu3};
List<Student> stus = mapper.querySs(students);
System.out.println(stus);
}
foreach标签有以下几个属性collection, item,index,open,separator,close。
1. collection表示需要遍历的集合
2. item 表示每次遍历时生成的对象名
3. index表示在迭代过程中,每次迭代到的位置)
4. open表示开始遍历时要拼接的字符串
5. separator表示在每次遍历时两个对象直接的连接字符串
6. close表示结束遍历时要拼接的字符串
当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
在使用foreach的时候针对不同的参数类型, collection属性值要分为以下3种情况:
1.如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
2.如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
3.如果传入的参数是多个的时候,我们就需要把它们封装成一个Map或者Object。