目录
动态sql是我们Mybatis里面较重要的一部分,下面我们一起来学习一下Mybatis的动态sql
1.情景分析
问:如果让你实现上图功能,你应该怎么做?
答:写sql语句呗,就写:select * from 表 where 折扣与服务 = ?and ......
这样真的可以吗?
当然是不可以的。图片上你可以选择1个,也可以选择2个,可以有选择的,也可以有不选的,而你的那条sql语句完全做不到这一点,所以这时,我们就引入了动态sql。
2.动态sql的简介:
我们之前写的sql语句叫做静态sql,完成不了上面的功能,现在,我们要引入动态sql。
首先,什么是动态sql?动态sql就是在不同的条件下拼接出不同的sql语句。
Mybatis框架的动态sql技术是一种根据特定条件动态拼接SQl语句的功能,他存在的意义是为了解决拼接SQL语句字符串时的痛点问题。比如我们在用淘宝之类的软件在进行商品属性选择的时候,我们会发现我们可以选择的商品的属性有很多条件,其中一些条件可以选择也可以不选择,那么如果使用传统的方式进行查询,反而在拼接sql的时候会造成一些列的问题。
动态sql也是通过标签来完成的。
3.动态sql的实例及标签的讲解
下面,我们将通过具体实例来讲解一下动态sql的标签使用
3.1<if>标签
if标签的作用:做判断,进行选择 (选择性拼接)
if标签的作用很简单,就是在if后面跟个条件,满足条件,拼接下面的sql语句,不满足,就不拼接。下面我们就通过具体实例来看一下。
例:根据 username 和 sex 来查询数据。如果username为空,那么将只根据sex来查询;反之只根据username来查询
下面,让我们先用静态sql来写,截图如下:
能达到所要求的功能吗?肯定达不到啊。你要么都传参,要么都不传,都不传的情况下肯定报错啊。
下面,就让我们看一下用动态sql如何写,代码如下:
<select id="selectUserByUsernameAndSex" parameterType="com.qcby.entity.User"
resultType="com.qcby.entity.User">
select * from user where
<if test="username != null and username = ''">
username=#{username}
</if>
<if test="sex != null and sex !=''">
and sex=#{sex}
</if>
</select>
截图如下:
说明:
第45行:select语句,没啥好说的。
第46行:if标签开头,表示是进行判断,test 后面接的是判断条件“username != null and username != ' ' ”,如果username不为null且不为空,我们拼接下面的语句,如果满足一个条件,就不用下面的语句,从而达到选择判断的结果。
第47行:/if 与前面的if 对应,是一种语法格式。
然后,我们来看一下接口的书写:
然后,我们再看一下测试的代码:
然后,我们来看一下运行结果及日志:
然后, 我们注释掉测试代码的第111行,再来看一下输出日志和结果:
说明:
在测试方法里面,我们只给了username的值,然后我们再看输出的日志,利用占位赋值的方法,最终的sql的条件部分也只有“ username= ?”,然后传参,也是只有“老一”,最终查询出相要的结果,完成上述的要求。从这里,我们可以分析出 if 的选择功能。
上面所写的就是动态sql,在不同条件下写出不同的sql语句。
但是,我上面所写的就真的对吗?Of course not !
为什么呢?在测试方法中,当我们注释掉第110行,保留第111行,那么sql语句就会变成:select ...where and ...,where和and连接在一起了,sql语句就出错了,所以上面的肯定是不行的。
怎么办呢?用where if 标签
3.2<where><if>标签
<where><if>标签的作用:依然是做判断,并且进行选择,但是,由于加了<where>,所以能保证在sql语句连接时,where后面跟的部分是正确的,这是where if 标签对if 标签的一种规范。(选择性拼接并确保where部分正确)
下面,让我们依照例子来看是怎么写的,还是上面的那个例子,sql语句如下:
<select id="selectUserByUsernameAndSex" parameterType="com.qcby.entity.User"
resultType="com.qcby.entity.User">
select * from user
<where>
<if test="username != null">
username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</where>
</select>
截图如下:
说明:很简单,就是把if 标签的语句放到<where></where>里面而已。
然后,我们来写测试方法:
最后,我们运行看一下:
通过日志,我们可以看出,where标签已经成功的解决了and的问题
加上<where>的作用就是确保where后面的内容是正确的,不会出现低级的错误(个人理解)
3.3<set><if>标签
<set><if>标签作用:选择性赋值,一般常用语update语句
我们依然先来看一下例子,根据例子进行分析。
例:根据id对 username 和 sex 进行选择性赋值。
首先,我们来写UserDao.xml文件,代码如下:
<update id="updateOnNameAndSex" parameterType="com.qcby.entity.User">
update user
<set>
<if test="username != null and username != '' ">
username = #{username},
</if>
<if test="address != null and address != '' ">
address = #{address},
</if>
<if test="sex != null and sex != '' ">
sex = #{sex}
</if>
</set>
where id =#{id}
</update>
截图如下:
然后,我们来写接口:
然后,我们来写测试方法,代码如下:
@Test
public void updateOnNameAndSex(){
User user = new User();
user.setId(2);
user.setUsername("老二");
int code = mapper.updateOnNameAndSex(user);
session.commit();
System.out.println(code);
}
截图如下:
最后,我们来看一下测试结果:
说明:
1.语法规则,作用就和前面的类似,没什么好说的
2.当我们只传一个值时,就修改了一个值,数据库中该条字段的其他值没有变,不会被置空。
3.要牢记修改的sql语句是怎么写的,在每个修改的字段后面要加“ , ”,最后一个不要加。
上面都是一些容易忽略的细节性问题,要注意
3.4<choose><when><otherwise>标签
<choose><when><otherwise>标签作用:选择判断作用,这个标签相当于java中的 if...else...语句。
<choose> :父标签,没太大作用
<when>:相当于if()或elseif()
<otherwise>:相当于else...
下面,我们还是通过具体例子来看一下。
首先,我们来写UserDao.xml文件,代码如下:
<select id="selectUserByChoose" parameterType="com.qcby.entity.User"
resultType="com.qcby.entity.User">
select * from user
<where>
<choose>
<when test="id != null and id != '' ">
id =#{id}
</when>
<when test="username != null and username != '' ">
and username =#{username}
</when>
<when test="sex != null and sex != '' ">
and sex =#{sex}
</when>
</choose>
</where>
</select>
然后,我们来写接口:
然后,我们来写测试方法:
最后,让我们点击测试一下,看下结果:
说明:
1. <choose><when><otherwise>标签与前面的<where><if>标签在这个例子中是及其相似的,但是二者是有很大区别的。
2. <choose><when><otherwise>标签的应用范围要比<where><if>标签的应用范围大的多,只要涉及到选择判断都可以用前者,而后者只涉及到查询
3.二者在查询上也有区别,仔细观察测试方法和日志,我们发现, <choose><when><otherwise>标签只会采用第一个条件,即使后面的满足也不用,而<where><if>标签是满足就用,没有顺序和数量的限制(我想,这是二者在查询上面最大的区别)
3.4.1趣味小实验(与正文无关)
仔细观察下面的代码:
挺有意思的,不是吗?
3.5<trim>标签
<trim>标签作用:<trim>标签本质上是一个格式化标签,可以完成set或者where的一些功能,比如标记功能,这个标签本身没有什么实际的意义。
下面,我们依然是根据具体例子来看一下它的作用。此次我们在原先的代码上进行修改,我们来看一下具体效果。
首先,在UserDao.xml里面找到我们的selectUserByUsernameAndSex方法,然后,我们将其修改成下面的模样:
<select id="selectUserByUsernameAndSex" parameterType="com.qcby.entity.User"
resultType="com.qcby.entity.User">
select * from user
<!-- <where>-->
<!-- <if test="username != null and username != ''">-->
<!-- username =#{username}-->
<!-- </if>-->
<!-- <if test="sex != null and sex != ''">-->
<!-- and sex =#{sex}-->
<!-- </if>-->
<!-- </where>-->
<trim prefix="where" prefixOverrides="and | or">
<if test="username != null and username != ''">
username =#{username}
</if>
<if test="sex != null and sex != ''">
and sex =#{sex}
</if>
</trim>
</select>
我们来看一下截图:
说明:
第54行:trim 就是一个标签,没啥实际作用;prefix 后面接你所要代替的标签,比如这里,你要代替的是where,所以prefix后面接的是where;prefixOverrides 后面接的是你sql语句拼接时一些比较杂的东西,比如这里就是and,所以prefixOverrides 在这里的作用就是取代and(这只是我自己的理解,下面看一下比较正式的叙述:prefixoverride:去掉第一个and或者是or)
后面就是简单的<if>标签语句了,没啥好说的了。
下面,我们来运行测试函数看一下:
和之前的没啥两样,没啥好讲的。
下面,我们再看一下用trim来代替set标签,在updateOnNameAndSex方法里面进行修改,代码如下:
<update id="updateOnNameAndSex" parameterType="com.qcby.entity.User">
update user
<!-- <set>-->
<!-- <if test="username != null and username != '' ">-->
<!-- username = #{username},-->
<!-- </if>-->
<!-- <if test="address != null and address != '' ">-->
<!-- address = #{address},-->
<!-- </if>-->
<!-- <if test="sex != null and sex != '' ">-->
<!-- sex = #{sex}-->
<!-- </if>-->
<!-- </set>-->
<trim prefix="set" suffixOverrides=",">
<if test="username != null and username != '' ">
username = #{username},
</if>
<if test="address != null and address != '' ">
address = #{address},
</if>
<if test="sex != null and sex != '' ">
sex = #{sex}
</if>
</trim>
where id =#{id}
</update>
然后,我们来看一下截图:
说明:
第77行:这里就有点不一样的了,注意这里的suffixOverrides,它应该是取消拼接语句后面的东西,前面讲的prefixOverrides 是取消拼接的语句的前面的东西,所以这点需要注意。
然后,我们来运行一下,看下结果:
很简单,没啥大问题,也没什么好说的。
<trim>标签就是一种格式化替代标签。
3.6<foreach>标签
有些时候我们的数据是以数组的形式出现的,比如我们进行批量删除和批量添加的时候,这个时候我们就需要用到<foreach>标签。
下面,我们来具体讲解一下。
3.6.1批量删除
首先,我们在Navicat上写一条批量删除语句:
观察我们可以发现,后面的(1,2,3,4,5)就是以数组的形式出现的,那么我在Mybatis中应该怎么写呢?我们继续往下看。
下面,我们来写sql语句,代码如下:
<delete id="deleteMore" >
delete from user where id in
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
我们再来看下截图:
说明:
第106行:关于入参与出参我们要好好思考一下,具体可以看我前一篇博客,里面讲了如何思考的。
第107行:普通的语句,没啥好说的
第108行:foreach是标签关键字;
collection 我们要循环的数组或集合的名称
item 你是用哪个字段进行循环的,我们这里用的是id字段,所以写id
open 你这个集合或数组是以什么为开始标志的,我们数组的格式是(1,...,5),是以"("为开始标志的,所以填入(
separator 集合或数组里面的元素是以什么符合进行分隔的,很显然,我们用的是","
close 你这个集合或数组是以什么为结束标志的,我们这里是“ )”
第109行:是一个占位赋值的应用,循环遍历数组并给前面的id进行占位,赋值。
下面,我们看一下接口怎么写:
这里的@param注解我们下一篇博客再说,这里先这样写。
然后,我们来写测试方法,代码如下:
@Test
public void deleteMore(){
Integer[] arr = new Integer[]{37,38,39};
int code = mapper.deleteMore(arr);
session.commit();
System.out.println(code);
}
截图如下:
然后,我们运行一下看下结果:
最后,我们来看下数据库:
可见,我们的代码没什么问题,执行成功。
3.6.2批量插入
首先,我们来看一下在Navicat上批量插入的语句有关怎么写:
然后,我们来写我们的UserDao.xml文件,内容如下:
<insert id="insertMoreByList" >
insert into user(id,username,birthday,sex,address) values
<foreach collection="users" item="user" separator=",">
(null,#{user.username},#{user.birthday},#{user.sex},#{user.address})
</foreach>
</insert>
说明:
批量插入的本质还是一条一条的插,只不过多了一个循环遍历而已
然后,我们来写接口:
在写测试方法之前,我们先在我们的User类中写一下构造方法,如图所示:
当然,我这里写的有点简单,添加构造方法是为了赋值方便。
然后,我们来写测试方法:
@Test
public void insertMoreByList(){
User user1 = new User("a1","男");
User user2 = new User("a2","男");
User user3 = new User("a3","男");
List<User> users = Arrays.asList(user1,user2,user3);
int result = mapper.insertMoreByList(users);
session.commit();
System.out.println(result);
}
截图如下:
最后,我们测试看下日志与结果:
在日志中我们可以看到,我们没有赋值的地方是用null填充的。
然后,我们刷新数据库来看一下:
至此,我们的批量添加操作完成了。
4.小总结
1.动态sql是通过标签来完成的
2.在动态sql中,我们学习了以下的标签:<if>;<where><if>;<set><if>;<choose><when><otherwise>;<trim>;<foreach>。
3.然后我们可以回忆一下每个标签的作用,写法,参数,注意事项,本质是什么。
到这里,我们的动态sql就写完啦!如果有错误,敬请各位大佬指出!