MyBatis动态SQL

作者: lihongyan | 来源:发表于2018-05-14 10:49 被阅读0次

MyBatis 动态SQL

内容

Mybatis动态SQL在XML中支持的几种标签：

if
chose
trim、set、where
foreach
bind
多数据支持
OGNL

使用动态SQL还可以实现多数据的支持。由于动态SQL中大量使用OGNL，所以最后介绍了OGNL的用法

if用法

if标签用于where语句中，通过判断参数值来决定是否使用某个查询条件；

if标签用于update语句中，通过判断参数值来决定是否更新某个字段；

if标签用于insert语句中，判断是否插入某个字段的值。

在where条件中使用if

select id, 
    user_name userName, 
    user_password userPassword,
    user_email userEmail,
    user_info userInfo,
    head_img headImg,
    create_time createTime
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>

需要注意的内容：

test的属性值是一个符合OGNL要求的判断表达式，表达式的结果可以是true或false。除此之外，所有的非0值都为true，只有0为false。为了方便理解，在表达式中，建议只用true和false作为结果
判断条件properties != null或properties == null 适用于任何类型的字段，用于判断属性值是否为空
判断条件properties != ''或properties == ''只适用与string类型的字段，用于判断是否为空字符串
当有多个判断时，使用and或or进行连接，嵌套的判断可以使用小括号分组
在OGNL表达式中，判断的顺序不会影响判断的结果，也不会有空指针异常。但建议判断顺序与Java一致
注意SQL中where关键字后面的查询条件1 = 1
由于查询条件都是动态的，当两个if判断都不满足时，最后生成的SQL就会以where结束，因此会报错。加上1 = 1这个条件就可以避免
注意条件中的and或or
当if判断通过时，这部分拼接到where 1 = 1之后时仍然是合法的SQL

在update更新列中使用if

需求：只更新有变化的字段，更新时不能将原来有值但没有变化的字段更新为空或null

update sys_user 
<set>
    <if test="userName != null and userName != ''">
    user_name = #{userName},
    </if>
    <if test="userPassword != null and userPassword != ''">
    user_password = #{userPassword},
    </if>
    <if test="userEmail != null and userEmail != ''">
    user_email = #{userEmail},
    </if>
    <if test="userInfo != null and userInfo != ''">
    user_info = #{userInfo},
    </if>
    <if test="headImg != null">
    head_img = #{headImg, jdbcType=BLOB},
    </if>
    <if test="createTime != null">
    create_time = #{createTime, jdbcType=TIMESTAMP},
    </if>
    id = #{id},
</set>
where id = #{id}

需要注意的内容：

每个if元素里面SQL语句后面的逗号
where关键字前面的id = #{id}

注意以上两点确保生成的SQL的正确性

在insert动态插入列中使用if

需求：在数据库表插入数据的时候，如果某一列的参数值不为空，就用传入的值；如果传入的参数为空，就用数据库中的默认值（或空值）。

<insert id="insert2" useGeneratedKeys="true" keyProperty="id">
    insert into sys_user(
        user_name, user_password, 
        <if test="userEmail != null and userEmail != ''">
            user_email,
        </if>
        user_info, head_img, create_time)
    values(
        #{userName}, #{userPassword}, 
        <if test="userEmail != null and userEmail != ''">
            #{userEmail}, 
        </if>
        #{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP})
</insert>

需要注意的内容：

若在列的部分增加if条件，则values部分也要增加相同的if条件，必须保证上下可以互相对应

choose用法

choose标签用于实现if...else if...else...的逻辑。choose标签包含when和otherwise两个标签，至多有一个otherwise标签

需求：当参数id有值时优先使用id查询，当id为没有值时，就去判断用户名是否有值，如果有值就用用户名查询，如果用户名没有值，就使SQL查询无结果。

<select id="selectByIdOrUserName" resultType="tk.mybatis.simple.model.SysUser">
select id, 
    user_name userName, 
    user_password userPassword,
    user_email userEmail,
    user_info userInfo,
    head_img headImg,
    create_time createTime
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标签的时候逻辑要严密，避免某些值出现问题导致SQL出错。
例如，如果没有otherwise这个限制条件，所有的用户都会被查询出来，如果我们在接口方法中使用对象作为返回值，当查询结果是多个的时候就会报错。添加otherwise条件后，由于where条件不满足，因此在这种情况下就查询不到结果。

where、set、trim 用法

where用法

where标签的作用：如果该标签包含的元素中有返回值，就插入一个where；如果where后面的字符串是以and或or开头的，就将它们剔除。

<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    <where>
        <if test="@tk.mybatis.util.StringUtil@isNotEmpty(userName)">
            and user_name like concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != '' and userEmail != null">
        and user_email = #{userEmail}
        </if>
    </where>
</select>

当if条件都不满足的时候，where元素中没有内容，所以在SQL中不会出现where；
当if条件满足，where元素的内容就是以and开头的查询条件，where会自动去掉开头的and。

set用法

set标签的作用：如果该标签包含的元素有返回值，就插入一个set；
如果set后面的字符串是以逗号结尾的，就想这个逗号剔除。

<update id="updateByIdSelective">
    update sys_user 
    <set>
        <if test="userName != null and userName != ''">
        user_name = #{userName},
        </if>
        <if test="userPassword != null and userPassword != ''">
        user_password = #{userPassword},
        </if>
        <if test="userEmail != null and userEmail != ''">
        user_email = #{userEmail},
        </if>
        <if test="userInfo != null and userInfo != ''">
        user_info = #{userInfo},
        </if>
        <if test="headImg != null">
        head_img = #{headImg, jdbcType=BLOB},
        </if>
        <if test="createTime != null">
        create_time = #{createTime, jdbcType=TIMESTAMP},
        </if>
        id = #{id},
    </set>
    where id = #{id}
</update>

需要注意的内容：

当set标签中没有返回值时，形成的SQL为：

update sys_user where id = #{id}

所以，仍需要添加id = #{id}，使最后的SQL满足语法

trim用法

where和set标签的功能都可以用trim标签来实现

where标签对应的trim实现：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
    
</trim>

需要注意的内容：

AND 和 OR后面的空格不能省略，这是为了避免匹配到andes、orders等单词
prefixOverriders包含的值除了AND和OR之外，还有：AND\n,OR\n,AND\r,OR\r,AND\t,OR\t

set标签对应的trim实现：

<trim prefix="SET" suffixOverrides=",">

</trim>

trim标签有如下属性：

prefix：当trim元素包含内容时，会给内容增加prefix指定的前缀
prefixOverrides：当trim元素包含内容时，会把prefixOverrides中指定的前缀字符串去掉
suffix：当trim元素包含内容时，会给内容增加suffix指定的前缀
suffixOverrides：当trim元素包含内容时，会把suffixOverrides中指定的后缀字符串去掉

foreach用法

foreach标签可以对数组、Map、实现Iterable接口的对象进行遍历。由于数组在处理时会转化为List对象，因此foreach遍历的对象可以分为两大类：Iterable类型和Map类型

foreach标签包含如下属性：

collection：必填，值为要迭代循环的属性名
item：变量名，值为从迭代对象中取出的每一个值
index：索引名，在Iterable类型下为当前索引值，当Map类型下为Map的Key
open：整个循环内容开头的字符串
close：整个循环内容结尾的字符串
separator：每次循环的分隔符

foreach标签

由于foreach遍历的对象分为：Iterable类型和Map类型，所以collection的属性值也分为不同情况：

参数是一个Iterable
参数是一个Map类型
参数是一个对象
有多个参数

实例

参数是一个Iterable

前提：没有使用@Param注解，使用默认的名称。如果使用了@Param注解，collection属性的值为@Param注解中指定的内容

当遍历的对象是一个数组时，collection属性的默认值为array
当遍历的对象是一个List时，collection属性的默认值为list

参数是一个Map类型

前提：没有使用@Param注解，使用默认的名称。如果使用了@Param注解，collection属性的值为@Param注解中指定的内容

当遍历的对象是一个Map时，collection属性的默认值为_parameter

参数是一个对象

指定为对象的属性名即可。当使用对象内多层嵌套的对象时，使用属性.属性的方式可以指定深层的属性值

有多个参数

当有多个参数的时候，要使用@Param注解给每个参数指定一个名字，否则在SQL中使用参数时就会不方便。因此将collection属性值设置为@Param直接指定的名字即可

foreach实现in集合

需求：根据传入的用户id集合查询出所有符合条件的用户

<select id="selectByIdList" resultType="tk.mybatis.simple.model.SysUser">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    where id in
    <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
        #{id}
    </foreach>
</select>

foreach实现批量插入

当参数类型是List时，实现批量插入

<insert id="insertList">
    insert into sys_user(
        user_name, user_password,user_email,
        user_info, head_img, create_time)
    values
    <foreach collection="list" item="user" separator=",">
        (
        #{user.userName}, #{user.userPassword},#{user.userEmail},
        #{user.userInfo}, #{user.headImg, jdbcType=BLOB}, 
        #{user.createTime, jdbcType=TIMESTAMP})
    </foreach>
</insert>

从MyBatis 3.3.1 版本开始，MyBatis开始支持批量新增回写主键值的功能。这个功能要求数据库主键值为自增类型，透视还要求该数据库提供的JDBC可以支持返回批量插入的主键值。目前只有Mysql数据库支持。

如果要在Mysql中实现批量插入返回自增主键值，只需要在原来代码的基础上进行如下修改即可：

<insert id="insertList" useGeneratedKeys="true" keyProperty="DTO对象的主键字段">

foreach实现动态update

当参数类型是Map时，实现批量更新

<update id="updateByMap">
    update sys_user 
    set 
    <foreach collection="_parameter" item="val" index="key" separator=",">
        ${key} = #{val}
    </foreach>
    where id = #{id}
</update>

对应的Mapper接口方法为：

void updateByMap(Map map);

当参数类型是Map时，foreach标签的index属性为Map的key，item属性为Map的value

bind用法

bind标签的作用：

bind标签使用OGNL表达式创建一个变量并将其绑定当上下文中
bind标签用于打印传入的参数

bind标签有两个属性，并且都为必选项：name、value

name为绑定到上下文的变量名
value为OGNL表达式

作用一：

在Mysql中，concat函数支持多个参数，但在Oracle中只支持两个参数。所以下面的SQL在迁移数据库时就会出现错误

<if test="userName != null and userName != ''">
    and user_name like concat('%', #{userName}, '%')
</if>

使用bind标签改写

<if test="userName != null and userName != ''">
    <bind name="userNameTemp" value="'%' + userName + '%'"/>
    and user_name like #{userNameTemp}
</if>

作用二：

打印SQL中传入的参数

<update id="updateByIdSelective">
    <bind name="print" value="@tk.mybatis.util.StringUtil@print(_parameter,'updateByIdSelective方法')"/>
        update sys_user 
    <set>
        <if test="userName != null and userName != ''">
        user_name = #{userName},
        </if>
        <if test="userPassword != null and userPassword != ''">
        user_password = #{userPassword},
        </if>
        <if test="userEmail != null and userEmail != ''">
        user_email = #{userEmail},
        </if>
        <if test="userInfo != null and userInfo != ''">
        user_info = #{userInfo},
        </if>
        <if test="headImg != null">
        head_img = #{headImg, jdbcType=BLOB},
        </if>
        <if test="createTime != null">
        create_time = #{createTime, jdbcType=TIMESTAMP},
        </if>
        id = #{id},
    </set>
    where id = #{id}
</update>

多数据支持

MyBatis多数据库的支持除了可以使用bind标签之外，还可以使用if标签配合databaseIdProvider进行配置。

MyBatis可以根据不同的数据库厂商执行不同的语句，这种多厂商的支持基于映射语句中的databaseId属性

<select id="selectRolesByUserId" resultType="tk.mybatis.simple.model.SysRole" databaseId="mysql">
    select 
        r.id, 
        r.role_name roleName, 
        r.enabled,
        r.create_by createBy,
        r.create_time createTime,
        u.user_name as "user.userName",
        u.user_email as "user.userEmail"
    from sys_user u
    inner join sys_user_role ur on u.id = ur.user_id
    inner join sys_role r on ur.role_id = r.id
    where u.id = #{userId}
</select>

Mybatis会加载不带databaseId属性和带有匹配当前数据库databaseId属性的所有语句。如果同时找到带有databaseId和不带databaseId的相同语句，则后者会被抛弃。

为支持多厂商特性，只要像下面这样在mybatis-config.xml文件中加入databaseIdProvider配置即可。

<databaseIdProvider type="DB_VENDER" />

这里的DB_VENDER值会通过DatabaseMetaData类的getDatabaseProductName()方法的返回值进行设置。由于该返回值非常长，并且包含相同产品的不同版本，所以通常设置属性别名使其变短。

<databaseIdProvider type="DB_VENDER">
    <property name="SQL Server" value="sqlserver" />
    <property name="DB2" value="db2" />
    <property name="Oracle" value="oracle" />
    <property name="MySQL" value="mysql" />
    <property name="PostgreSQL" value="postgresql" />
    <property name="Derby" value="derby" />
    <property name="HSQL" value="hsqldb" />
    <property name="H2" value="h2" />
</databaseIdProvider>

除了增加上面的配置外，映射文件也需要进行修改，包含databaseId属性的标签有：

insert
delect
update
select
selectKey
sql

示例：在Mysql中contract函数可以接受多个参数，才Oracle中contract函数只接受两个参数

mysql

<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser" databaseId="mysql">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    <where>
        <if test="userName != '' and userName != null">
            and user_name like concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != '' and userEmail != null">
        and user_email = #{userEmail}
        </if>
    </where>
</select>

oracle

<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser" databaseId="oracle">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    <where>
        <if test="userName != '' and userName != null">
            and user_name like concat(concat('%', #{userName}),'%')
        </if>
        <if test="userEmail != '' and userEmail != null">
        and user_email = #{userEmail}
        </if>
    </where>
</select>

还有更简单的写法，只是因为数据库的更换可能只会引起某个SQL语句的部分不同，所以没有必要使用上面的方式。可以使用if标签配合默认上下文中的_databaseId参数实现上面的功能，避免大量重复的SQL出现。当然也可以使用choose标签

<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    <where>
        <if test="userName != '' and userName != null">
            <if test="_databaseId == 'mysql'">
                and user_name like concat(concat('%', #{userName}),'%')
            </if>
            <if test="_databaseId == 'oracle'">
                and user_name like concat(concat('%', #{userName}),'%')
            </if>
        </if>
        <if test="userEmail != '' and userEmail != null">
        and user_email = #{userEmail}
        </if>
    </where>
</select>

OGNL用法

在Mybatis的动态SQL中大量使用了OGNL表达式，Mybatis常用的OGNL表达式有：

e1 and e2 , e1 or e2 , !e1 (not e1)
e1 == e2 (e1 eq e2) , e1 != e2 (e1 neq e2) , e1 gt e2 , e1 gte e2 , e1 lt e2 , e1 lte e2
e1 + e2 , e1 - e2 , e1 * e2 , e1/e2 , e1%e2
e.method(args) ：调用对象方法
e.property ：调用对象属性
@class@methd(args)：调用类的静态方法
@class@field：调用类的静态字段
e[key]：按索引取值（数组、List、Map）

调用对象方法

<if test="list != null and list.size() > 0">

</if>

调用类型静态方法

<if test="!@org.apache.commons.collections.CollectionUtils@isEmpty(list)">

</if>

需要注意的内容：

对于5、8中的用法，可以多层嵌套使用；不管e是不是null，必须保证属性或索引必须存在，否则会报错
对于用法6，还可以实现一些特殊功能，例如：打印传递进SQL中的参数

<bind name="print" value="@tk.mybatis.util.StringUtil@print(_parameter)"/>

其中print方法的内容大致为

public void print(Object param){
    System.out.println(param);
}

《MyBatis从入门到精通》（刘增辉）

MyBatis动态SQL

MyBatis 动态SQL

内容

if用法

在where条件中使用if

在update更新列中使用if

在insert动态插入列中使用if

choose用法

where、set、trim 用法

where用法

set用法

trim用法

foreach用法

foreach标签

foreach实现in集合

foreach实现批量插入

foreach实现动态update

bind用法

多数据支持

OGNL用法

调用对象方法

调用类型静态方法

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MyBatis