MyBatis框架多态SQL

MyBatis框架动态SQL的常用标签

标签
if	条件判断，与Java中的语句类似
where	为SQL语句动态添加where关键字
choose	条件判断，这是一个组合标签，需要与when、otherwise标签搭配使用。可实现与Java中的switch语句类似的功能
foreach	以遍历方式处理集合类型参数
set	为SQL语句动态添加set关键字，实现动态实现数据更新功能
trim	对SQL语句进行格式化处理，添加或移除前后缀

一、if标签

if标签是MyBatis框架动态SQL技术中重要且常用的标签之一，它所实现的功能与Java中的if语句基本相同，用法也很相似

示例

<select id="selectList" resultType="sysUser">
    select *
    from t_sys_user
    where 1=1
        <if test="roleId != null">
            and roleId = #{roleId}
        </if>
        <if test="realName != null and realName != ''">
            and realName like concat('%',#{realName},'%')
        </if>
</select>

二、where标签

where标签的主要作业是对SQL语句中的where关键字进行简化处理，并可以智能地处理其内部and、or等关键字，避免多余字符带来的语法错误

示例

<select id="selectList" resultType="sysUser">
    select *
    from t_sys_user
    <where>
        <if test="roleId != null">
            and roleId = #{roleId}
        </if>
        <if test="realName != null and realName != ''">
            and realName like concat('%',#{realName},'%')
        </if>
    </where>
</select>

三、choose（when、otherwise）标签

choose标签是一个组合标签，通常与when、otherwise标签配合使用，实现类似于Java中switch语句的功能

示例

<select id="selectListByChoose" resultType="SysUser">
        select * from t_sys_user
        <where>
            <choose>
                <when test="realName != null and realName != ''">
                    and realName like concat('%',#{realName},'%')
                </when>
                <when test="roleId != null">
                    and roleId = #{roleId}
                </when>
                <when test="account != null and account != ''">
                    and account like concat('%',#{account},'%')
                </when>
                <otherwise>
                    and year(createdTime) = year(#{createdTime})
                </otherwise>
            </choose>
        </where>
    </select>

四、foreach标签

1、foreach标签处理数组类型参数

mapper接口

public List<SysUser> getUserByRoleIdArray(Integer[] roleIds);

mapper映射SQL文件

<!--foreach标签处理数组类型参数-->
    <select id="getUserByRoleIdArray" resultType="SysUser">
        select * from t_sys_user
                 where roleId in
                 <foreach collection="array" item="item" open="(" separator="," close=")">
                     #{item}
                 </foreach>
    </select>

测试

    @Test
    public void testGetUserByRoleIdArray(){
        List<SysUser> userList = new ArrayList<SysUser>();
        Integer[] roleIds = {1,2};
        userList = sqlSession.getMapper(SysUserMapper.class).getUserByRoleIdArray(roleIds);
        logger.info("查询到用户数量："+userList.size());
        for (SysUser user : userList) {
            logger.debug("查询到用户信息："+user);
        }
    }

2、foreach标签处理List参数

mapper接口

public List<SysUser> getUserByRoleIdList(List<Integer> roleList);

mapper映射SQL文件

<!--    foreach标签处理List类型参数-->
    <select id="getUserByRoleIdList" resultType="SysUser">
        select * from t_sys_user where roleId in
                                 <foreach collection="list" item="item" open="(" separator="," close=")">
                                     #{item}
                                 </foreach>
    </select>

测试

    @Test
    public void testGetUserByRoleIdList(){
        List<Integer> roleList = new ArrayList<Integer>();
        roleList.add(1);
        roleList.add(2);
        List<SysUser> userList = sqlSession.getMapper(SysUserMapper.class).getUserByRoleIdList(roleList);
        logger.info("查询到用户数量："+userList.size());
        for (SysUser user : userList) {
            logger.debug("查询到用户信息："+user);
        }
    }

3、foreach标签处理Map类型参数

mapper接口

public List<SysUser> getUserByRoleIdMap(Map<String,Object> roleMap);

mapper映射SQL文件

    <!--    foreach标签处理Map类型参数-->
    <select id="getUserByRoleIdMap" resultType="SysUser">
        select * from t_sys_user where roleId in
            <foreach collection="roleIdList" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
    </select>

测试

    @Test
    public void testGetUserByRoleIdMap(){
        List<Integer> roleIdList = new ArrayList<Integer>();
        roleIdList.add(1);
        roleIdList.add(2);
        Map<String,Object> roleMap = new HashMap<String, Object>();
        roleMap.put("roleIdList",roleIdList);
        List<SysUser> userList = sqlSession.getMapper(SysUserMapper.class).getUserByRoleIdMap(roleMap);
        logger.info("查询到用户数量："+userList.size());
        for (SysUser user : userList) {
            logger.debug("查询到用户信息："+user);
        }
    }

五、set标签

在修改操作中，可以使用MyBatis框架提供的set标签和if标签进行处理。其中set标签主要用于数据的更新操作，if标签则用于控制更新的字段

示例

    <update id="update" parameterType="sysuser">
        update t_sys_user
            <set>
                <if test="account != null">account=#{account},</if>
                <if test="realName != null">realName=#{realName},</if>
                <if test="sex != null">sex=#{sex},</if>
                <if test="phone != null">phone=#{phone},</if>
                <if test="address != null">address=#{address},</if>
                <if test="birthday != null">birthday=#{birthday},</if>
                <if test="roleId != null">roleId=#{roleId},</if>
                <if test="updatedUserId != null">updatedUserId=#{updatedUserId},</if>
                <if test="updatedTime != null">updatedTime=#{updatedTime}</if>
                <if test="password != null">password=#{password},</if>
            </set>
            where id = #{id}
    </update>

if标签起到了动态拼接SQL语句的作用，但是其所包裹的每条语句后都有一个逗号，这表示where id = #{id}语句前一定会多出一个逗号，最终会导致语法错误。要怎么解决这个问题呢？其实set标签可以智能忽略这些多余的逗号。

六、trim标签

where、set标签能够动态地为SQL语句添加前后缀，并可以智能地忽略标签前后多余的and、or或逗号等字符。除where和set标签外，MyBatis框架还提供了更加灵活的trim标签来实现类似的功能。

trim标签语句

<trim prefix = "前缀" 
      suffix = "后缀" 
      prefixOverrides = "忽略前缀" 
      suffixOverrides = "忽略后缀">
    SQL语句......
</trim>

其中trim标签的属性

• prefix：前缀，可以自动对trim标签所包含的语句是否有返回值进行判断。如果有返回值，则为SQL语句拼接相应前缀
• suffix：后缀，在trim标签包含的语句末尾拼接后缀
• prefixOverrides：忽略的前缀，忽略trim标签内部首部指定的内容
• suffixOverrides：忽略的后缀，忽略trim标签包含内容尾部指定的内容

替换where标签示例

    <select id="selectList" resultType="sysUser">
        select *
        from t_sys_user
        <trim prefix="where" prefixOverrides="and|or">
            <if test="roleId != null">
                and roleId = #{roleId}
            </if>
            <if test="realName != null and realName != ''">
                and realName like concat('%',#{realName},'%')
            </if>
        </trim>
    </select>

替换set标签示例

<update id="update" parameterType="sysuser">
        update t_sys_user
            <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
                <if test="account != null">account=#{account},</if>
                <if test="realName != null">realName=#{realName},</if>
                <if test="sex != null">sex=#{sex},</if>
                <if test="phone != null">phone=#{phone},</if>
                <if test="address != null">address=#{address},</if>
                <if test="birthday != null">birthday=#{birthday},</if>
                <if test="roleId != null">roleId=#{roleId},</if>
                <if test="updatedUserId != null">updatedUserId=#{updatedUserId},</if>
                <if test="updatedTime != null">updatedTime=#{updatedTime}</if>
                <if test="password != null">password=#{password},</if>
            </trim>
    </update>