什么是动态SQL?
动态SQL即为SQL语句的拼接,形成较为复杂的SQL语句,以此达到用户的查询目的。动态SQL标签有<if> <where> <choose> <foreach> 等
<if>标签
当test的值为true时,if标签内包含的SQL片段会拼接到SQL语句中
实例:下列程序完成按照姓名和年龄查询
dao接口
List<Student> selectStudentsIf(Student student); //使用if标签
映射文件
<!-- if-->
<select id="selectStudentsIf" resultType="Student">
select * from student
<!--加入where 防止下面的两个if标签都失效-->
where 1=1
<!-- 此处的name为参数name而不是数据库中的name -->
<if test="name!=null and name!=''">
and name like concat('%',#{name},'%')
</if>
<if test="age >0">
and age>#{age}
</if>
</select>
测试方法
@Test
public void Test01()
{
//Student student =new Student("李",22,124);测试结果:未找到
Student student =new Student("",13,124); //找到所有年龄大于13的学生信息
//Student student =new Student("李",12,124);找到李四的信息
List <Student> students=dao.selectStudentsIf(student);//查询名字近似且大于此年龄的学生信息
System.out.println(students);
}
<where>标签
在<if>标签的实例中,为了防止if标签都失效,加上了1=1的条件保证SQL语句的完整,实际上可以直接使用<where>标签解决此类问题
实例:下列程序的功能与上面的一致
dao接口
List<Student> selectStudentsWhere(Student student); //使用where标签
映射文件
<select id="selectStudentsWhere" resultType="Student">
select * from student
<where>
<if test="name!=null and name!=''">
and name like concat('%',#{name},'%')
<!-- 第一个条件的and 可以省略 -->
</if>
<if test="age >0">
and age>#{age}
</if>
</where>
</select>
测试类
@Test
public void Test02()
{
//Student student =new Student("李",22,124);
//Student student =new Student("",13,124);
Student student =new Student("李",12,124);
List <Student> students=dao.selectStudentsWhere(student);//查询名字近似且大于此年龄的学生信息
System.out.println(students);
}
<choose>标签
<choose> 标签内可以使用<when>和<otherwise> 用来完成多分支的选择功能
实例:下列程序,若姓名不为空则按照姓名查找,否则按照年龄查找,若两个值都不合法,则无结果
dao接口
List<Student> selectStudentsChoose(Student student); //使用choose标签
映射文件
<select id="selectStudentsChoose" resultType="Student">
select * from student
<where>
<choose>
<when test="name!=null and name!=''">
and name like concat('%',#{name},'%')
<!-- 第一个条件的and 可以省略 -->
</when>
<when test="age >0">
and age>#{age}
</when>
<otherwise>
and 1!=1
</otherwise>
</choose>
</where>
</select>
测试方法
@Test
public void Test03()
{
Student student2 =new Student("",12,124);
Student student =new Student("李",14,124);
List <Student> students=dao.selectStudentsChoose(student2); //按照年龄查找
System.out.println(students);
students=dao.selectStudentsChoose(student); //按照姓名查找
System.out.println(students);
}
使用<foreach>标签遍历数组
将要查询的多个条件封装入数组中,如多个id,使用foreach将信息插入SQL标签中,完成查询
实例:查询id为1和3的学生信息
dao接口
List<Student> selectStudentsForeachArray(Object[] studentInfo);
//使用foreach标签遍历数组
映射文件
<!--遍历数组-->
<select id="selectStudentsForeachArray" resultType="Student">
select * from student
<!-- 必须为 array -->
<if test="array!=null and array.length>0">
where id in
<foreach collection="array" open="(" close=")" item="myid" separator=",">
<!--标记集合类型为数组,以及sql语句开头和结束的标志,元素的名称,分隔符 -->
#{myid}
</foreach>
</if>
</select>
测试方法
@Test
public void Test04()
{
Object []studentInfo =new Object[] {1,3};
List<Student> students=dao.selectStudentsForeachArray(studentInfo);
System.out.println(students);
}
使用<foreach>标签遍历List
dao接口
List<Student> selectStudentsForeachList(List<Integer> StudentId);
//使用foreach标签遍历泛型为基本类型的List
List<Student> selectStudentsForeachList2(List<Student> StudentInfo);
//使用foreach标签遍历泛型为自定义类型的List
映射文件
<!-- 遍历List 基本类型 -->
<select id="selectStudentsForeachList" resultType="Student">
select * from student
<!-- 必须为 list -->
<if test="list!=null and list.size>0">
where id in
<foreach collection="list" open="(" close=")" item="myid" separator=",">
<!--标记集合类型为数组,以及sql语句开头和结束的标志,元素的名称,分隔符 -->
#{myid}
</foreach>
</if>
</select>
<!-- 遍历List 自定义类型 -->
<select id="selectStudentsForeachList2" resultType="Student">
select * from student
<if test="list!=null and list.size>0">
where id in
<foreach collection="list" open="(" close=")" item="student" separator=",">
#{student.id}
</foreach>
</if>
</select>
测试方法
@Test
public void Test05()
{
List<Integer> ids=new ArrayList<Integer>();
ids.add(1);
ids.add(4);
List<Student> students=dao.selectStudentsForeachList(ids);
System.out.println(students);
}
@Test
public void Test06()
{
List<Student> students=new ArrayList<Student>();
Student stu1=new Student();
stu1.setId(1);
Student stu2=new Student();
stu2.setId(2);
students.add(stu1);
students.add(stu2);
List<Student> studentList=dao.selectStudentsForeachList2(students);
System.out.println(studentList);
}
<sql>标签
将常用的sql片段使用sql标签标注,可以在映射文件中直接引用,不需要重复写明
实例:将原本的selectStudentsForeachList2可以改成如下形式
<sql id="selectHead">
select * from student
</sql>
<select id="selectStudentsForeachList2" resultType="Student">
<!-- select * from student -->
<include refid="selectHead"></include>
<if test="list!=null and list.size>0">
where id in
<foreach collection="list" open="(" close=")" item="student" separator=",">
#{student.id}
</foreach>
</if>
</select>