动态sql多用于解决查询条件不确定的情况。
在程序运行期间,根据用户提交的多种可能的查询条件进行查询,提交的查询条件不同,动态生成和执行的sql语句也不同。
常用的动态标签有if,where,choose,foreach等。
1.if标签
对于该标签的执行,当test的值为true时,会将其包含的sql片段拼接到其所在的sql语句中(有条件的选择拼接)
关键代码(在dao层的接口和实现类添加searchStudentsIf(Student student)方法,在映射文件StudentMapepr.xml添加以下代码)
<mapper namespace="com.lifeng.dao.IStudentDao"
<!-- 配置结果映射 -->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="sname" column="studentname"/>
<result property="sex" column="gender"/>
<result property="age" column="age"/>
</resultMap>
<!-- 使用if标签实现动态查询 -->
<select id="searchStudentsIf" resultMap="studentResultMap" parameterType="Student">
<!-- 在if标签中,当test的值为true时,会将其包含的sql片段拼接到此sql语句后面 -->
select * from student where 1=1
<!-- 按姓氏模糊查询学生信息,可按回车跳过 -->
<if test="sname!=null and sname!=''" >
and studentname like '%' #{sname} '%'
</if>
<!-- 按性别查询学生信息,可按回车跳过 -->
<if test="sex!=null and sex!=''">
and gender=#{sex}
</if>
<!-- 按年龄查询学生信息,可输入0跳过
<if test="age>0">
and age=#{age}
</if>
</select>
</mapper>
2.where标签
if标签存在一个比较麻烦的地方,需要在wehere后手工添加"1=1"的子句。
因为,若where后的所有if条件均为false时,where后没有"1=1"的子句的话,则sql就会只剩下一个空的where,sql将出错。
解决方法:将if标签嵌套在where标签中来省略"1=1"的子句
关键代码(在dao层的接口和实现类添加searchStudentsWhere(Student student)方法,在映射文件StudentMapepr.xml添加以下代码)
<mapper namespace="com.lifeng.dao.IStudentDao">
<!-- 配置结果映射 -->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="sname" column="studentname"/>
<result property="sex" column="gender"/>
<result property="age" column="age"/>
</resultMap>
<!-- 使用where标签来实现动态查询 -->
<select id="searchStudentsWhere" resultMap="studentResultMap" parameterType="Student">
select * from student
<!-- 将if标签嵌套在where标签中来省略"1=1"的子句 -->
<where>
<!-- 在if标签中,当test的值为true时,会将其包含的sql片段拼接到此sql语句后面 -->
<if test="...">
and...
</if>
</where>
</select>
</mapper>
3.使用map封装查询条件
在if和where标签中,多个查询条件都封装到一个Student对象的属性中去了。
但在实际开发中,可能有些条件无法封装到通一个对象中去,这时就要采用map类型进行封装和参数传递。
关键代码(在dao层的接口和实现类添加searchStudentsMap(Map map)方法,在映射文件StudentMapepr.xml添加以下代码)
<mapper namespace="com.lifeng.dao.IStudentDao">
<!-- 配置结果映射 -->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="sname" column="studentname"/>
<result property="sex" column="gender"/>
<result property="age" column="age"/>
</resultMap>
<!-- 使用map封装查询条件,将参数类型指定为java.util.Map -->
<select id="searchStudentsMap" resultMap="studentResultMap" parameterType="java.util.Map">
select * from student
<!-- 将if标签嵌套在where标签中来省略"1=1"的子句 -->
<where>
<!-- 在if标签中,当test的值为true时,会将其包含的sql片段拼接到此sql语句后面 -->
<if test="ageStart>0 and ageEnd >0">
<!-- 这里有起始年龄和结束年龄,两个年龄无法封装到同一个Student对象中 -->
<!-- 解决方案就是将其封装到一个Map集合中 -->
and age between #{ageStart} and #{ageEnd}
</if>
</where>
</select>
</mapper>
4.choose标签
类似于java的开关语句switch…case…。的功能
关键代码(在dao层的接口和实现类添加searchStudentsChoose(Student student)方法,在映射文件StudentMapepr.xml添加以下代码)
<mapper namespace="com.lifeng.dao.IStudentDao">
<!-- 配置结果映射 -->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="sname" column="studentname"/>
<result property="sex" column="gender"/>
<result property="age" column="age"/>
</resultMap>
<!-- 使用choose标签实现动态查询 -->
<select id="searchStudentsChoose" resultMap="studentResultMap" parameterType="Student">
select * from student
<!-- 将if标签嵌套在where标签中来省略"1=1"的子句 -->
<where>
<choose>
<!-- 若姓名不为空,则按姓名查询 -->
<when test="sname!=null and sname!=''">
and studentname like '%' #{sname} '%'
</when>
<!-- 若姓名为空,则按年龄查询 -->
<when test="age>0">
and age=#{age}
</when>
<!-- 若没有查询条件,则没有查询结果 -->
<otherwise>
and 1!=1
</otherwise>
</choose>
</where>
</select>
</mapper>
5.foreach标签
用于实现数组和集合类型的输入参数的遍历
i.遍历普通数组
collection属性表示要遍历的集合类型。
若为数组,则使用open,close,sparator这些属性用于对遍历内容进行sql拼接
关键代码(在dao层的接口和实现类添加searchStudentsEachArray(Object[] ids)方法,在映射文件StudentMapepr.xml添加以下代码)
<mapper namespace="com.lifeng.dao.IStudentDao">
<!-- 配置结果映射 -->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="sname" column="studentname"/>
<result property="sex" column="gender"/>
<result property="age" column="age"/>
</resultMap>
<!-- 使用foreach标签实现动态查询 -->
<select id="searchStudentsEachArray" resultMap="studentResultMap" >
select * from student
<if test="array!=null and array.length>0">
where id in
<!--
collection="array":表示要遍历的集合类型为数组类型
open="(":表示以符号"("作为拼接字符串的开始
close=")":表示以符号")"作为拼接字符串的结束
item="myid":表示遍历的每一个元素可以自定义,但要和foreach内部占位符#{myid}一致
sparator=",":表示分隔符号
-->
<!-- 输入要查询的学生id,多个id之间用逗号隔开 -->
<foreach collection="array" open="(" close=")" item="myid" sparator=",">
#{myid}
</foreach>
</if>
</select>
</mapper>
ii.遍历泛型为基本类型的List
collection属性表示要遍历的集合类型。
若为数组,则使用open,close,sparator这些属性用于对遍历内容进行sql拼接
关键代码(在dao层的接口和实现类添加searchStudentsEachList(List list)方法,在映射文件StudentMapepr.xml添加以下代码)
<mapper namespace="com.lifeng.dao.IStudentDao">
<!-- 配置结果映射 -->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="sname" column="studentname"/>
<result property="sex" column="gender"/>
<result property="age" column="age"/>
</resultMap>
<!-- 使用foreach标签实现动态查询 -->
<select id="searchStudentsEachList" resultMap="studentResultMap" >
select * from student
<if test="list!=null and list.size>0">
where id in
<!--
collection="array":表示要遍历的集合类型为数组类型
open="(":表示以符号"("作为拼接字符串的开始
close=")":表示以符号")"作为拼接字符串的结束
item="myid":表示遍历的每一个元素可以自定义,但要和foreach内部占位符#{myid}一致
sparator=",":表示分隔符号
-->
<!-- 输入要查询的学生id,多个id之间用逗号隔开 -->
<foreach collection="list" open="(" close=")" item="myid" sparator=",">
#{myid}
</foreach>
</if>
</select>
</mapper>
iii.遍历泛型为自定义类型的List
collection属性表示要遍历的集合类型。
若为数组,则使用open,close,sparator这些属性用于对遍历内容进行sql拼接
关键代码(在dao层的接口和实现类添加searchStudentsEachListStu(List list)方法,在映射文件StudentMapepr.xml添加以下代码)
<mapper namespace="com.lifeng.dao.IStudentDao">
<!-- 配置结果映射 -->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="sname" column="studentname"/>
<result property="sex" column="gender"/>
<result property="age" column="age"/>
</resultMap>
<!-- 使用foreach标签实现动态查询 -->
<select id="searchStudentsEachListStu" resultMap="studentResultMap" >
select * from student
<if test="list!=null and list.size>0">
where id in
<!--
collection="array":表示要遍历的集合类型为数组类型
open="(":表示以符号"("作为拼接字符串的开始
close=")":表示以符号")"作为拼接字符串的结束
item="myid":表示遍历的每一个元素可以自定义,但要和foreach内部占位符#{myid}一致
sparator=",":表示分隔符号
-->
<!-- 输入要查询的学生id,多个id之间用逗号隔开 -->
<foreach collection="list" open="(" close=")" item="stu" sparator=",">
#{stu.sid}
</foreach>
</if>
</select>
</mapper>
6.sql标签
用于定义sql片段供其他sql标签复用。
若其他标签要使用该sql片段,则需要使用include子标签。
sql标签可以定义sql语句的任何部分,所以include子标签可以放在动态sql的任何位置,最终拼接出需要的sql语句。
sql标签的使用代码如下
<mapper namespace="com.lifeng.dao.IStudentDao">
<!-- 配置结果映射 -->
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="sname" column="studentname"/>
<result property="sex" column="gender"/>
<result property="age" column="age"/>
</resultMap>
<!-- 定义sql片段 -->
<sql id="selectStu">
select * from student
</sql>
<!-- 定义select语句,在其内可以调用sql片段 -->
<select id="..." resultMap="studentResultMap">
<!-- 调用sql片段 -->
<include refid="selectStu"/>
<if test="...">
...
</if>
</select>
</mapper>