大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

【MyBatis】MyBatis动态SQL

MyBatis动态SQL

前言

在前面,我们已经学习了MyBatis的单表操作以及多表操作,在体验了MyBatis提供的对于结果集的封装之后,我们接下来来学习MyBatis中的动态SQL。

动态SQL

所谓的动态SQL,就是根据不同的情况来产生不同的SQL,比如在查询用户的时候,我们可以根据用户的ID、名称、邮箱来查询,当然,这三者也可以进行组合,如果要为每一种情况写一个查询,那工作量还是比较恐怖的,动态SQL在这个时候就充分发挥作用了。

在MyBatis中,提供了一下几个标签来实现动态SQL

  • if
  • choose
    • when
    • otherwise
  • where
  • set
  • trim
  • foreach

下面我们就来详细学习这几个标签

if标签

if标签中包含一个必须具备的属性test="",只有当test中的值为真时,if标签里面的内容才会被拼接到SQL语句中,如下代码所示。

<select id="selectByUser" resultType="domain.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 
    <!--注意这里,如果两者均为空,会出现 where之后啥都没有,SQL语法错误,不优雅,有更好地解决方案-->
    <!--如果userName非空,则使用userName来查询-->
    <if test="userName != null and userName != ''">
        and user_name like concat('%', #{userName}, '%')
    </if>
    <!--如果userEmail非空,则使用userEmail来查询-->
    <if test="userEmail != null and userEmail != ''">
        and user_email = #{userEmail}
    </if>
</select>

if语句的使用还是比较好理解的,需要注意的是,if语句可以用于select、update、insert中,后面两种情况的小例子如下所示

 <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>
        <!--注意这里,如果前面都为空,则会出现set where id = #{id},SQL语法错误 -->
        <!--如果只有一个条件,会出现 user_name = #{userName}, where id = #{id}, SQL语法错误-->
        id = #{id} 
        where id = #{id}
</update>

<!--注意insert语句中,插入的值以及字段部分均需要进行测试-->
<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 = TIMESTAMPS})
</insert>

choose标签

choose标签与if标签不同,多if标签的元素之间是没有关系的,而choose标签中的when以及otherwise则是互斥的关系,可以将choose及其子标签理解为if ... else if ... else....,其用法如下

<select id="selectByIdOrUserName" resultType="domain.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 != ''">
            and id = #{id}
        </when>
        <when test="userName != null and userName != '">
            and user_name = #{userName}
        </when>
        <otherwise>
            and 1 = 2
        </otherwise>
    </choose>
</select>

where、set、trim

在上面的if和choose标签中,我们都会根据情况加入以下辅助的操作,如1=1用于避免当所有的值都不存在的时候,拼接SQL出现错误,where以及set标签的出现,就是为了解决这样的问题

<select id="selectByUser" resultType="domain.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 != null and userName != ''">
            and user_name like concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != null and userEmail != ''">
            and user_email = #{userEmail}
        </if>
    </where>
</select>

如果没有一个if匹配,则where也不会存在,解决了出现以where结尾的情况,如果有情况匹配,会自动去除where后面的and,避免出现where and的情况,比上面添加1 = 1的情况更加优雅一点.

如果set中的内容有值,就插入一个set,如果以逗号结尾,则删除该逗号。

<update id="updateByIdSelective">
    update sys_user
    <set>
        <if test="userName != null and userName != ''">
            user_name = #{userName},
        </if>
    </set>
    id = #{id}
    where id = #{id}
</update>

但是由于if中会以,结尾,所以感觉其实set标签没有什么用处,囧

where和set可以通过trim来实现,并且底层就是通过TrimSqlNode实现的

where版本的trim

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

set版本的trim

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

foreach标签

foreach可以对数组、map、或者实现了Iterable接口的对象进行遍历,然后将对应的内容拼接起来,就是通过循环来拼接SQL

<select id="selectByIdList" resultType="domain.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>

拼接之后的SQL为

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 ( ? , ? ) 

根据生成的SQL来解释foreach的使用,应该还是比较好理解的。

foreach属性

  • collection,要迭代循环的属性名
    • 如果是数组,则是array
    • 列表,则是list
    • 其余为collection
    • 可以通过@Param("name")来指定,然后使用name
  • item:变量名,值为从迭代对象中取出的每一个值
  • index:索引,如果是map,则是key
  • open:内容开头的字符串
  • close:内容结束的字符串
  • separator:拼接时候的分隔符

可以通过下面的方式来实现批量插入

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

当参数类型是map时,可以通过以下方式来动态操作,在迭代map时,item是map的值,index是key,需要注意这一点

<update id="updateByMap">
    update sys_user
    set
        <foreach colleciton="_parameter/使用@Param()指定的name" item="val" index="key" separator=",">
            ${key} = #{val}
        </foreach>
    where id = #{id}
</update>

总结

本小节主要学习了MyBaits中的动态SQL,包括了if、choose、when、otherwise、where、set、trim、foreach等标签,通过灵活组合这些标签,可以比较容易地实现根据不同的参数来拼接不同的SQL语句,不过,由于每次都需要拼接SQL,所以,对于性能还是有一定的影响的。