您现在的位置是:首页 > 后台技术 > MyBatisMyBatis

MyBatis动态SQL(第四章)(图文)

第十三双眼睛2020-05-05【MyBatis】人已围观

简介MyBatis的强大特性之一便是它的动态SQL,使用过JDBC或者其他类似框架的人都知道,根据不同条件拼接SQL语句时不仅不能忘了比要的空格,还要注意省略掉列名列表最后的逗号,处理方式麻烦且混乱,MyBatis的动态SQL则可以让我们摆脱这种痛苦。以下是MyBatis支持的几种标签,if,choose,trim,foreach,bind.除了会介绍这几个标签的用法之外,还会介绍如何在一个xml种针对不同的数据库编写不同的SQL语句,另外对这5种标签中必须用到的OGNL表达式进行一个简单的介绍。

if用法
if标签通常用于where 语句中,通过判断参数值来决定是否使用某个查询条件,它也经常用于update语句中用于判断是否更新某一字段,还可以用于insert语句中用于判断是否插入某一字段。

在where条件中使用if
假设现在有一个需求,实现一个用户查询的功能,根据输入的条件去检索用户的信息,这个功能需要支持以下3中情况,当只输入用户名时,需要根据用户名进行模糊查询,当只输入邮箱时,根据邮箱查询用户,当同时输入用户名和邮箱时,根据两者去查询用户。则mapper中的写法如下:
<select id="getUser">
    select * from sys_user
    where 1=1 
    <if test="userName != null and userName != ''">
    and user_name = like concat("%",#{userName},"%")
    </if>
    <if test="userEmail != null and userEmail != ''">
    and user_email = #{userEmail}
    </if>
</select>
if标签有一个属性test,test的属性值是一个符合ognl要求的判断表达式,表达式的结果可以是true或者false,除此之外,所有非0值都为true,只有0为false.
判断条件property!=null 或者property==null适用于任何类型的字段,用于判断属性值是否为null
判断条件property!=''或者property==''仅适用于String类型的字段,用于判断是否为空字符串。
and 和or:当有多个条件时,使用and 或or进行连接,嵌套的判断可以使用括号分组。and相当于java中的&&,or相当于java中的||,字符串的判断要先判断是否为null,再判断是否为空,否则会报空指针。

在update语句中使用if
现在有这样一个需求,只更新有变化的字段,需要注意的是,更新的时候不能将原来有值但没有发生变化的字段更新为空或者null,通过if标签可以动态更新。mapper中的写法如下:
<update id="updateById">
    update sys_user 
    set 
    <if test="userName != null and userName != ''">
        user_name =#{userName},
    </if>
        id =#{id}
        where id =#{id}
</update>
这里要结合业务层进行判断,确保最终产生的SQL语句没有有法错误,需要注意的有两点,第一点是每个if元素里面SQL语句后面的逗号,第二点就是where关键字前面
id =#{id}这个条件,不管上面的条件成立与否,都能保证SQL语句是正确的。


在insert中使用if
在数据库表中插入数据的时候,如果某一列的参数值不为空,就使用传入的值,如果传入的参数为空,就使用数据库中的默认值,而不使用传入的值,使用if就可以实现这种动态插入列的功能。mapper文件中的写法如下:
<insert id="insert1">
    insert into sys_user (
    <if test="userName != null and userName != ''">
    user_name,
    </if>
    user_password)    
    values(
    <if test="userName != null and userName != ''">
    #{userName}
    </if>
    #{userPassword}
    )
</insert>
在insert中使用是要注意,如果在列的部分使用了if条件,则在values部分也要增加相应的if条件,必须保证上下可以互相对应。

choose用法
上一节得if标签提供了基本得条件判断,但是它无法实现if...else得逻辑,要想实现这样的逻辑,就必须使用choose when otherwise标签。choose元素中包含when和otherwise两个标签,一个choose最少包含一个when,0个或者一个otherwise,在已有得sys_user表中,除了主键id外,我们人为user_name也是唯一的,所有的用户名都不能重复,现在进行如下查询,如果有id的时候,就用id进行查询,没有id的时候就用user_name进行查询:
<select id="selectByIdOrUsername" resultType="SysUser">
    select * from sys_user 
    where 1=1
    <choose>
        <when test="id != null">
            and id =#{id}
        </when>
        <when test="userName != null and userName != ''">
            and user_name =#{userName}
        </when>
        <otherwise>
            and 1=2
        </otherwise>
    </choose>
</select>  
使用choose when otherwise时逻辑要严密,避免因为某些值得问题导致SQL出现问题。
在上面得查询中,如果没有otherwise这个限制条件,所有得用户都会被查询出来,但是我们在对应得接口上得返回值却时SysUser,当查询结果是多个得时候,就会报错,添加otherwise后,由于where条件不满足,因此在这种情况下,就查询不到结果。
where set trim用法
这三个标签解决了类似得问题,并且where和set都属于trim的一种具体用法。下面分别来看这三个标签。
where用法
where标签的作用:如果该标签包含的元素中有返回值,就插入一个where,如果where后面的字符串已and 或者or开头,就将它们剔除。
<select id="selectByUser">
    select * from sys_user
    <where>
        <if test="userName != null and userName != ''">
            and user_name =#{userName}
        </if>
        <if test="userEmail != null and userEmail != ''">
            and user_email = #{userEmail}
        </if>
    </where>
</select>
当if条件都不满足的时候,where中没有内容,所以在sql中不会出现where,如果if条件满足,where元素的内容就是and开头的条件,where 会自动去除开头的and.

set用法
set标签的作用:如果该标签包含的元素中有返回值,就插入一个set,如果set后面的字符串是以逗号结尾的,就将这个逗号剔除。
<update id="updateUser">
    update sys_user
    <set>
        <if test="userName != null and userName != ''">
            user_name =#{userName}
        </if>
        <if test="password != null and password != ''">
            user_password =  #{password} 
        </if>
        id = #{id}
    </set>
    where id = #{id}
</update>
在set标签的用法中,SQL后面的逗号没有问题了,但是如果set元素中没有内容,照样会出现sql错误,类似id =#{id} 这样必然存在的赋值仍然有保留的必要,从这一点来看,set标签并美欧解决全部的问题。使用时仍然需要注意。
trim用法
where和set标签的功能都可以用trim标签来实现,并且在底层就是通过TrimSqlNode来实现的。
where标签对应的trim的实现如下:
<trim prefix="where" prefixOverrides="and |or ">
...
</trim>
这里的and和or后面的空格不能省略,为了避免匹配到andes,orders等单词。
set对应的trim实现如下:
<trim prefix="set" suffixOverrides=",">
..
</trim>
trim标签有如下属性:
prefix:当trim元素包含内容时,会给内容增加prefix指定的前缀
prefixOverrides:当trim元素包含内容时,会把内容中匹配的前缀字符串去掉
suffix:当trim元素中包含内容时,会给内容增加suffix指定的后缀
suffixOverrides:当trim元素包含内容时,会把内容中匹配的后缀字符串去掉。

foreach用法
SQL语句中有时候会用到in关键字,例如id in(1,3,2),可以使用${ids}的方式直接取值,但是这种方法不能防止SQL注入,想避免SQL注入就必须使用#{}的方式,这时就要配合使用foreach标签来满足需求。
foreach可以对数组,map,或者实现了iterable接口的对象进行遍历,数组在处理时会转换为list对象,因此foreach遍历的对象可以分为两大类,iterable类型和map类型,这两种类型在遍历时情况不一样。
foreach实现in集合
foreach实现in集合是最简单和常用的一种情况,下面介绍如何根据传入的用户id集合查询出所有符合条件的用户。
<select id="selectByIdList" resultType="SysUser">
    select * from sys_user
    where id in 
    <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
        #{id}
    </foreach>
</select>
foreach包含如下属性:
collection:必填,值为要循环的属性名,这个属性值的情况有很多。
item:变量名,值为从迭代对象中取出的每一个值。
index:索引的属性名,在集合数组情况下为当前索引值,在迭代循环的对象是map类型时,这个值为map的key.
open:整个循环内容的开头字符串。
close:整个循环内容结尾的字符串。
separator:每次循环的分隔符。
collection要符合设置呢,来看看mybatis是如何处理这种类型的参数的。
1只有一个数组参数或者集合参数
以下代码是DefaultSqlSession中的方法,也是默认情况下的处理逻辑。
private Object wrapCollection(final Object object){
        if(object instanceof Collection){
            StrictMap<Object> map = new StrictMap<Object>();
            map.put("collection", object);
            if(object instanceof List){
                map.put("list", object);
            }
            return map;
        }else if(object != null&&object.getClass().isArray()){
            StrictMap<Object> map = new StrictMap<Object>();
            map.put("array", object);
            return map;
        }
        return object;
    }
dang当参数类型为集合的时候,默认会转为map类型,并添加一个key为collection的值,如果参数是list类型,那么就继续添加一个key为list的值,这样,当collection="list"时,就能获取到这个集合,并对齐进行操作。
当参数类型为数组的时候,也会转换为map类型,默认的key为array.
s上面提到的是数组或者集合类型的参数默认的名字,如果使用@Param注解给参数指定名字,这时,collection就设置为自己指定的名字。
2有多个参数
第二章中讲过,当有多个参数的时候,要使用@Param注解给每一个参数指定一个名,否则在SQL中使用参数将会很不方便,因此,将collection设置为@Param注解指定的名字即可。
3参数是map类型
使用map和是使用@param方式一样,将collection指定为对应map中的key即可,如果要循环所传入的Map,推荐使用@param注解指定名字,此时将collection设置为指定的名字,如果不想指定名字,就使用默认值_parameter.
4参数是一个对象
这种情况下指定为对象的属性名即可,当使用对象内队曾嵌套时,使用属性.属性的方式可以指定深层的属性值。

foreach实现批量插入
如果数据库支持批量插入,就可以通过foreach来实现,批量插入是SQL-92新增的新特性,批量插入的语法如下:
INSERT INTO TABLENAME (column a,column b,...) values(valuea,valueb,...),(valuec,valued,...)...
从语法来看,后面是一个循环,因此可以通过foreach实现循环插入。
<insert id="insertList">
insert into sys_user(user_name,user_password)
values 
<foreach collection="list" item="user" separator=",">
    (#{user.userName},#{user.password})
</foreach>
</insert>
通过item指定了循环变量名,在引用值的时候,使用的是属性.属性的方式。如user.userName.
从mybatis3.1开始,支持批量新增回写主键的功能,这个功能首先要求数据库主键类型为自增,同时还要求该数据库提供的JDBC驱动可以支持返回批量插入的主键值(jdbc提供了该接口,但是并不是所有数据库的驱动都实现了该接口),到目前为止,可以完美支持该功能的仅有mysql数据库,由于sqlserver数据库提供的JDBC驱动只能返回最后一个插入的主键值,所以不支持该功能。
如果要在mysql中实现批量插入返回自增主键,只需要在原来的代码中进行如下修改。
<insert id="insertList" useGeneratedKeys="true" keyProperty="id">
和单表一样,此处增加了useGeneratedKeys="true"和keyProperty="id"两个属性。
对于不支持该功能的数据库,许多人会通过select ... union all select 的方式去实现,这种方式在不同的数据库中实现也不同,并且这种实现也不安全。

foreach实现动态update
这一节主要介绍当参数类型是Map时,foreach如何实现动态的UPDATE.
当参数是Map类型的时候,foreach标签的index属性值对应的不是索引值,而是Map中的key,利用这个key可以实现动态update.
现在需要通过指定的列名和对应的值去更新数据,实现代码如下:
<update id="updateByMap">
    update sys_user set 
    <foreach collection="_parameter" item="val" index="key" separator=",">
        ${key} = #{val}
    </foreach>
    where id = #{id}
</update>
这里的key作为列名,对应的值作为该列的值,通过foreach将需要更新的字段拼接在SQL语句中,这里没有通过@Param注解指定参数名,因而使用了默认值_parameter作为该参数的key.

bind用法
bind标签可以作为ognl表达式创建一个变量并将其绑定到上下文中,在前面的例子中,有的方法用到了like查询条件。部分代码如下:
<select id="getUser">
    select * from sys_user
    where 1=1 
    <if test="userName != null and userName != ''">
    and user_name = like concat("%",#{userName},"%")
    </if>
    <if test="userEmail != null and userEmail != ''">
    and user_email = #{userEmail}
    </if>
</select>
使用concat连接函数,在MySql中,这个函数支持多个参数,但在oracle中,只支持两个参数,由于不同数据库之间的差异,如果更换数据库,有些SQL语句肯能会报错,针对这种情况,可以使用bind标签避免由于数据库不同而带来的一些麻烦。将上面的语句改为bind方式后,写法如下:
<select id="getUser">
    select * from sys_user
    where 1=1 
    <if test="userName != null and userName != ''">
        <bind name="userName" value="'%'+userName+'%'"/>
    and user_name = like #{userName}
    </if>
    <if test="userEmail != null and userEmail != ''">
    and user_email = #{userEmail}
    </if>
</select>
bind标签的这两个属性都是必选属性,name为绑定到上下文的变量名,value为ognl表达式,创建一个bind标签的变量后,就可以在下面直接使用,使用bind拼接字符串不仅可以避免因更换数据库而修改sql,也能预防sql注入。

多数据库支持
bind标签并不能解决更换数据库带来的所有问题,那么通过什么方式支持不同的数据库呢,这需要用到if标签以及由mybatis提供的databaseIdProvider数据库厂商表示配置,mybatis可以根据不同的数据库厂商执行不同的sql语句,这种多厂商的支持是基于映射语句中的databaseId属性的,mybatis会加载不带databaseId属性和带有匹配当前数据库databaseId属性的所有语句,如果同时找到带有databaseId和不带databaseId的相同语句,则后者会被丢弃。为支持多厂商特性,只要像下面这样在配置文件中加入databaseProvider配置即可。<databaseIdProvider type="DB_VENDOR"/>
这里的DB_VENDOR会通过DataBaseMetaData#getDataBaseProductName()返回的字符串进行设置,由于通常情况下这个字符串都非常长,而且相同产品的不同版本会返回不同的字符串,所以通常会通过设置属性别名来使其变短。代码如下:
<databaseIdProvider type="DB_VENDOR">
    <property name="SQL Server" value="sqlserver"/>
    <property name="DB2" value="db2"/>
    <property name="Oracle" value="oracle"/>
    <property name="MySQL" value="mysql"/>
</databaseIdProvider>
上面列举了常见的数据库产品名称,在有property配置时,databaseId将被设置为第一个能匹配到的数据库产品的属性键对应的值。如果没有匹配到则会被设置为null,数据库的产品名一般由所选择的当前数据库的JDBC驱动所决定,只要找到对应数据库DataBaseMetaData接口的实现类,一般在getDataBaseProductName方法中就可以找到该值,任何情况下都可以通过调用该方法获得具体的值。
除了增加上面的配置,映射文件也是需要变化的,关键在于以下几个映射文件的标签中含有的dataBaseId属性
select,insert,delete,update,selectKey,sql.举一个简单的例子介绍如何使用dataBaseId.针对不同的数据库,如mysql,oracle.
<select id="getUser" databaseId="mysql">
    select * from sys_user
    where 1=1 
    <if test="userName != null and userName != ''">
        and user_name = like #{userName}
    </if>
</select>
<select id="getUser" databaseId="oracle">
    select * from sys_user
    where 1=1 
    <if test="userName != null and userName != ''">
        and user_name = like '%'||#{userName}||'%'
    </if>
</select>

OGNL用法
在mybatis的动态sql中,${}形式的参数都用到了ognl表达式,所以我们由比要了解以下OGNL的简单用法,mybatis常用的ognl表达式如下:
1:e1 or e2
2:e1 and e2
3:e1==e2 或 e1 eq e2
4:e1!=e2 或 e1 neq e2
5:e1 lt e2 小于
6:e1 lte e2 小于等于
7:e1+e2 e1-e2 e1*e2 e1/e2 e1%e2
8:!e或not e取反
9:e.method(args)
10:e.property
11:e1[e2],按索引取值list,数组,map
12:@class@method(args)调用类的静态方法
13:@class@field调用类的静态字段
表达式1-4是最常用的4种情况,另外一个情况:需要判断集合是否为空,<if test="list != null and list.size()>0"></if>
表达式10,11两种情况也非常常见,而且可以多层嵌套使用,假设User类型的属性user中有一个address类型的属性名为addr,在address中还有一个属性zipcode,可以通过user.addr.zipcode直接使用zipcode的值,假设map类型的属性为map,我们可以通过map['userName'],或者map.userName来获取map中key为userName的值,这里一定要注意,不管userName的值是不是null,必须保证userName这个key存在,否则就会报错。
表达式12通常用于简化一些校验,或者用于进行更加特殊的校验,例如if中常用的判断可以写成如下这样
<if test="@tk.mybatis.util.StringUtil@isNotEmpty(userName)">
        and user_name = like '%'||#{userName}||'%'
</if>

本章通过大量的示例详细讲解了MyBatis支持的所有动态sql,通过动态sql可以避免在java代码中处理繁琐的业务逻辑,通过将大量的判断写入到MyBatis的映射层,极大的提高了我们的逻辑应变能力。

Tags:MyBatis   持久层

很赞哦! ()

文章评论

    共有条评论来说两句吧...

    用户名:

    验证码:

本站推荐

站点信息

  • 网站名称:JavaStudy
  • 建站时间:2019-1-14
  • 网站程序:帝国CMS7.5
  • 文章统计108篇文章
  • 标签管理标签云
  • 统计数据百度统计
  • 微信公众号:扫描二维码,关注我们