目录
4.choose,when,otherwise标签和sql标签
前言
在SQL中,一个SQL语句就对应了一个查询情况,要考虑用户的所有输入情况的话,那就要写很多个SQL语句以及对应的方法,那样是很麻烦的。所以MyBatis给我们提供了动态拼装SQL语句的功能,非常方便。
Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了 解决 拼接SQL语句字符串时的痛点问题。
0. 准备
在数据库中创建一个Student表,并创建对应的学生类。
一、动态SQL
1. if标签
if标签可通过test属性的表达式进行判断,若表达式的结果为true,则标签中的内容会拼接到SQL语句中执行;反之标签中的内容不会拼接到SQL语句中。
使用:
<!--List<Student> getStudentByMutilCondiction1(Student stu);-->
<select id="getStudentByMutilCondiction1" resultType="Student">
select * from t_student where
<if test="stuId != '' and stuId != null">
stu_id = #{stuId}
</if>
<if test="stuName != '' and stuName != null">
and stu_name = #{stuName}
</if>
<if test="age != '' and age != null">
and age = #{age}
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
</select>
当用户输入了对应的信息之后,if标签就会对test属性中的表达式进行判断,为真就拼接到上面的SQL语句中。stuId是第一个写的条件,所以前面不用加and,stuName、age、sex条件是拼接在前面的条件的后面,在最前面要加上一个and,否则SQL语句就会出错。
但是上面的代码有一点错误,当用户输入的信息中没有stuId,而是输入后面的条件,那么后面三个条件前面的and就会多余。还有一个问题就是当所有的条件都不成立的时候,即用户没有输入任何信息,或者输出的是空,那么where关键字就会多余。
解决办法就是在where后面加一个恒成立的条件,stuId、stuName、age、sex这几个条件都拼接在这个恒成立的条件后面,这样第一个条件不成立的时候,后面成立的条件中的and就不会多余了,所有条件都不成立的时候,where关键字也不会多余了。
总结where 1=1的作用:
- 不影响查询结果。
- 更好的拼接后面的查询条件。
- 若第一个条件不成立,第二个条件前面的“and”也不会多余。
- 若条件都不成立,where关键字也不会多出来。
改进代码:
mapper接口中:
List<Student> getStudentByMutilCondiction1(Student stu);
mapper.xml文件中:
<!--List<Student> getStudentByMutilCondiction1(Student stu);-->
<select id="getStudentByMutilCondiction1" resultType="Student">
select * from t_student where 1=1
<if test="stuId != '' and stuId != null">
and stu_id = #{stuId}
</if>
<if test="stuName != '' and stuName != null">
and stu_name = #{stuName}
</if>
<if test="age != '' and age != null">
and age = #{age}
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
</select>
测试:
@Test
public void testGetStudentByMutilCondiction1(){
SqlSession sqlSession = SqlSessionUtil.getSqlsession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Student> list = mapper.getStudentByMutilCondiction1(new Student(5,"",null,""));
System.out.println(list);
}
恒成立条件也可以修改,比如用户不输入任何信息时,默认查询学号为1的学生信息等等。
2.where标签
上面的例子就是要在where后面加上1=1这样恒成立的条件,防止where或者and/or这些关键字的多余。但是一般写SQL语句的时候,我们是直接判断条件是否成立,有时候就会忘记加上1=1这样恒成立的条件,导致SQL语句出错。这里我们就可以使用where标签来解决这问题。
where和if一般结合使用。
总结where标签的作用:
- 当where标签中有条件成立(有内容)时,会自动生成where关键字,并且将内容前多余的and或or去掉。
- 当where标签中没有条件成立(没有内容)时,where标签无效,不会自动生成where关键字。
- where标签不能将内容后面多余的and或or去掉。所以在写if标签中的内容的时候,要将and、or这样的连接条件的关键字写在条件的前面。
使用:
mapper接口中:
List<Student> getStudentByMutilCondiction2(Student stu);
mapper.xml文件中:
<!--List<Student> getStudentByMutilCondiction2(Student stu);-->
<select id="getStudentByMutilCondiction2" resultType="Student">
select * from t_student
<where>
<if test="stuId != '' and stuId != null">
stu_id = #{stuId}
</if>
<if test="stuName != '' and stuName != null">
and stu_name = #{stuName}
</if>
<if test="age != '' and age != null">
and age = #{age}
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
</where>
</select>
测试:
@Test
public void testGetStudentByMutilCondiction2(){
SqlSession sqlSession = SqlSessionUtil.getSqlsession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Student> list = mapper.getStudentByMutilCondiction2(new Student(null,"ww",null,null));
System.out.println(list);
}
3.trim标签
前面说到的and/or这些连接条件的关键字都是写在前面,那我就想写在后面,而where又没有办法去掉后面的and/or,这个时候我们就可以使用trim标签。
trim用于去掉或添加标签中的内容。
作用:
- 若标签中有条件成立
- prefix/suffix:将trim标签中内容的前面/后面添加指定内容
- prefixOverrides/suffixOverrides:将trim标签中内容的前面/后面去掉指定内容
- 若标签中没有条件成立,trim标签无效,不会添加或去掉指定内容。在本例中,没有条件成立的话,就是查询所有的学生信息。
使用:
mapper接口中:
List<Student> getStudentByMutilCondiction3(Student stu);
mapper.xml文件中:
<!--List<Student> getStudentByMutilCondiction3(Student stu);-->
<select id="getStudentByMutilCondiction3" resultType="Student">
select * from t_student
<trim prefix="where" suffixOverrides="and|or">
<if test="stuId != '' and stuId != null">
stu_id = #{stuId} and
</if>
<if test="stuName != '' and stuName != null">
stu_name = #{stuName} and
</if>
<if test="age != '' and age != null">
age = #{age} and
</if>
<if test="sex != '' and sex != null">
sex = #{sex}
</if>
</trim>
</select>
测试:
@Test
public void testGetStudentByMutilCondiction3(){
SqlSession sqlSession = SqlSessionUtil.getSqlsession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Student> list = mapper.getStudentByMutilCondiction3(new Student(null,"",null,null));
System.out.println(list);
}
4.choose,when,otherwise标签和sql标签
若现在要实现当学号不为空时,根据学号查询,学号为空,姓名不为空时,根据姓名查询,以此类推,当所有条件都为空时,根据学号=1查询。这个用if标签是没办法做到的,这个时候我们就可以使用<choose>、<when>、<otherwise>标签来处理。在这些标签中,只会有一个条件成立,所以在写sql语句的时候就不用加and/or这样的连接关键字。
choose、when、otherwise标签相当于if...else if....else。when标签至少有一个,otherwise标签最多有一个。
choose标签表示一个完整的if...else if...else。
when标签相当于if...else if....。
otherwise标签相当于else...。
有时候不需要将数据库中表的所有信息都输出,例如我只要学号、姓名和年龄,而且这三个条件会用在很多个sql语句中,每次都写会很麻烦,那我们就可以将它们放在一个sql标签中,用到的时候就sql标签包含进去就可以了。
sql标签也可以用来代替经常要查询的字段、条件等等。
sql标签的使用:
设置sql片段:<sql id="标签的唯一id">经常用到的字段名</sql>
引用sql片段:select <include refid="要用到的sql标签的唯一id"></include> from 表名
使用:
mapper接口中:
List<Student> getStudentByChoose(Student stu);
mapper.xml文件中:
<sql id="stuColumns">stu_id,stu_name,age,sex</sql>
<!--List<Student> getStudentByChoose(Student stu);-->
<select id="getStudentByChoose" resultType="Student">
select <include refid="stuColumns"></include> from t_student
<!--使用where标签,有条件成立就自动拼接where关键字-->
<where>
<!--choose表示一个完整的if...else if...else-->
<!--只会有一个条件成立,所以要拼接的SQL语句不用写and/or-->
<choose>
<!--when相当于if...else if....-->
<when test="stuName != '' and stuName != null">
stu_name = #{stuName}
</when>
<when test="age != '' and age != null">
age = #{age}
</when>
<when test="sex != '' and sex != null">
sex = #{sex}
</when>
<!--都不满足就默认输出学号是1的学生信息-->
<otherwise>
stu_id = 1
</otherwise>
</choose>
</where>
</select>
测试:
@Test
public void testGetStudentByChoose(){
SqlSession sqlSession = SqlSessionUtil.getSqlsession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Student> list = mapper.getStudentByChoose(new Student(null,"",null,""));
System.out.println(list);
}
5.foreach标签
foreach标签适用于批量添加、删除、查询记录。
foreach标签的属性说明:
- collection:设置需要循环的数组或集合
- item:表示数组或集合中的每一个数据,后面#{}或者${}中的内容就是item的值
- separator:循环体之间的分割符
- open:foreach标签所循环的所有内容的开始符
- close:foreach标签所循环的所有内容的结束符
- open和close一般只在 "字段名 in" 的时候使用
- open、close是整个循环的开始符和结束符,不能随便什么sql语句都用。
批量删除:
//通过数组实现批量删除
int delectMutilByArray(@Param("stuIds") Integer[] stuIds);
<!--int delectMutilByArray(@Param("stuIds") Integer[] stuIds);-->
<delete id="delectMutilByArray">
<!--方法一:-->
delete from t_student where stu_id in
<foreach collection="stuIds" item="stuId" separator="," open="(" close=")">
#{stuId}
</foreach>
<!--方法二:-->
<!--
delete from t_student where
<foreach collection="stuIds" item="stuId" separator="or">
stu_id = #{stuId}
</foreach>
-->
</delete>
@Test
public void testDelectMutilByArray(){
SqlSession sqlSession = SqlSessionUtil.getSqlsession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
int result = mapper.delectMutilByArray(new Integer[]{2,3,4});
System.out.println(result);
}
方法一输出的sql语句:
delete from t_student where stu_id in ( ? , ? , ? )
方法二输出的sql语句:
delete from t_student where stu_id = ? or stu_id = ? or stu_id = ?
批量添加
//通过List集合实现批量添加
int insertMutilByList(@Param("students") List<Student> students);
要注意open、close的使用。
<!--int insertMutilByList(@Param("students") List<Student> students);-->
<insert id="insertMutilByList">
insert into t_student values
<!--
open close 是在整个循环的开始符号和结束符号
这里的()是每个循环体都要有的
用了open close:(null,qq,14,nan,null,oo,4,女),这个是错误的
不用:(null,qq,14,nan),(null,oo,4,女)
-->
<foreach collection="students" item="student" separator=",">
(null,#{student.stuName},#{student.age},#{student.sex},null)
</foreach>
</insert>
测试:
@Test
public void testInsertMutilByList(){
SqlSession sqlSession = SqlSessionUtil.getSqlsession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Student student1 = new Student(null,"qq",14,"男");
Student student2 = new Student(null,"oo",4,"女");
List<Student> students = Arrays.asList(student1,student2);
int result = mapper.insertMutilByList(students);
System.out.println(result);
}
二、MyBatis缓存
1.一级缓存:
一级缓存是SqlSession级别的,通过同一个SqlSession查询的数据会被缓存,下次查询相同的数据,就会从缓存中直接获取,不会从数据库重新访问
使一级缓存失效的四种情况:
1) 不同的SqlSession对应不同的一级缓存
2) 同一个SqlSession但是查询条件不同
3) 同一个SqlSession两次查询期间执行了任何一次增删改操作(一定会将一级缓存清空)
4) 同一个SqlSession两次查询期间手动清空了缓存
2.二级缓存:
比一级的范围大,且要手动开启
二级缓存是SqlSessionFactory级别,通过同一个SqlSessionFactory创建的SqlSession查询的结果会被缓存;此后若再次执行相同的查询语句,结果就会从缓存中获取
二级缓存开启的条件:缺一不可
a>在核心配置文件中,设置全局配置属性cacheEnabled="true",默认为true,不需要设置
b>在映射文件中设置标签<cache/>
c>二级缓存必须在SqlSession关闭或提交之后有效
d>查询的数据所转换的实体类类型必须实现序列化的接口 实现Serializable 使二级缓存失效的情况: 两次查询之间执行了任意的增删改,会使一级和二级缓存同时失效
3.MyBatis缓存查询的顺序
先查询二级缓存,因为二级缓存中可能会有其他程序已经查出来的数据,可以拿来直接使用。
如果二级缓存没有命中,再查询一级缓存
如果一级缓存也没有命中,则查询数据库
SqlSession关闭之后,一级缓存中的数据会写入二级缓存