MyBatis框架多态SQL

1.1 MyBatis框架多态SQL的常用标签

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

1.1.1 if标签

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

SysUserMapper类的selectList()方法:

/**

* 查询用户列表(分页查询)
* @param realName
* @param roleId
* @return
*/
List<SysUser> selectList(@Param("realName") String realName,@Param("roleId")Integer roleId);

SysUserMapper.xml中的查询代码:

   <resultMap id="SysUserResult" type="SysUser">
        <id property="id" column="id"/>
        <result property="userRoleName" column="roleName"/>
    </resultMap>

    <select id="selectList" resultMap="SysUserResult" parameterType="map">
     select u.* r.roleName from t_sys_user u ,t_sys_role r
     where u.roleId=r.id
     and u.roleId=#{roleId}
     and u.realName like CONCAT('%',#{realName},'%')
</select>

1.1.2 where标签

where 标签的主要作用是对 SQL语句中的 where 关键字进行简化处理,并可以智能地处理其内部 and、or 等关键字，避免多余字符带来的语法错误。下面通过代码来演示使用 where 标签的优势。

SysUserMapper类的selectList()方法:

/**

* 查询用户列表(分页查询)
* @param realName
* @param roleId
* @return
*/
List<SysUser> selectList(@Param("realName") String realName,@Param("roleId")Integer roleId);

 SysUserMapper.xml中的查询代码:

 <resultMap id="SysUserResult" type="SysUser">
        <id property="id" column="id"/>
        <result property="userRoleName" column="roleName"/>
    </resultMap>

    <select id="selectList" resultMap="SysUserResult" parameterType="map">
SELECT u.id, u.realName, r.roleName
        FROM t_sys_user u
        LEFT JOIN t_sys_role r ON u.roleId = r.id
        <where>
            <if test="roleId != null">
                AND u.roleId = #{roleId}
            </if>
            <if test="realName != null and realName != ''">
                AND u.realName LIKE CONCAT('%', #{realName}, '%')
            </if>
        </where>
   </select>

where标签可以自动剔除标签内部多余的and关键字,并可以在没有过滤条件忽略where关键字,从而简化SQL语句并智能处理where、and、or关键字。

1.1.3 choose(when、otherwise)标签

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

SysUserMapper类的selectListByChoose()方法:

   List<SysUser> selectListByChoose(@Param("realName") String realName, @Param("roleId") Integer roleId, @Param("account") String account, @Param("createdTime")Date createdTime);

 SysUserMapper.xml中的查询代码:

  <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 UEAR(createdTime)=YEAR(#{createdTime})
        </otherwise>
            </choose>
        </where>
    </select>

提示:

1.第一个when标签符合条件后,其他的when标签就不会被执行了。

2.当所有条件都不满足时,才会执行otherwise标签内的代码。

2.1MyBatis框架动态SQL处理集合参数

MyBats 框架通过 foreach 标签对这类参数进行循环处理,最终拼接出一个符合MSQL语法的in 语句来处理这类参数。foreach 标签处理数组、List集合、Map 对象类型参数的语法如下所示。

语法

<foreach collection="参数名称" item="元素别名" open"(" separator"," close=")"
        index="当前元素位置下标">
            #{元素别名}
</foreach>

标签属性	说明
item	遍历数组时,为数组或List集合中的元素起的别名。
open	起始位置的拼接字符，表示in语句以"("左括号开始。
close	结束位置的拼接字符，表示in 语句以")"右括号结束。
Separator	元素之间的连接符，表示in语句中的元素之间以“，逗号连接。
Collection	参数名称。当参数为数组类型时，默认参数名为array。当参数类型为List集合时，默认参数名为list。当参数类型为Map 时，参数名为Map 中集合元素所在键值对的 key。

2.1.1 foreach标签处理数组类型参数

SysUserMapper类的getUserByRoleIdArray()方法:

    /**
     * 根据角色id数组查询用户列表信息
     * @param roleIds
     * @return
     */
    List<SysUser> getUserByRoleIdArray(Integer[]roleIds);

 SysUserMapper.xml中的查询代码:

  <select id="getUserByRoleIdArray" resultType="SysUser">
        select * from t_sys_user where roleId in
        <foreach collection="array" item="item" open="(" separator="," close= ")">
            #{item}
        </foreach>
    </select>

在测试类中编写测试方法testGetUserByRoleIdArray():

 @Test
    public void testGetUserByRoleIdArray() {
        List<SysUser> userList = new ArrayList<SysUser>();
        Integer[] roleIds = {1, 2};
        try {
            sqlSession = MyBatisUtil.createSqlSession();
            userList = sqlSession.getMapper(SysUserMapper.class).getUserByRoleIdArray(roleIds);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        logger.info("查询到用户数量:" + userList.size());
        for (SysUser user : userList) {
            logger.info("查询到用户数量:" + user);
        }
    }

2.1.2 foreach标签处理List类型参数

SysUserMapper类的getUserByRoleIdArray()方法:

 /**
     * 根据角色id集合 查询用户列表信息
     * @param roleList
     * @return
     */
    List<SysUser>getUserByRoleIdList(List<Integer> roleList);

 SysUserMapper.xml中的查询代码:

 <select id="getUserByRoleIdList" resultType="SysUser">
        select * from t_sys_user where roleId in
        <foreach collection="list" item="item" open="(" separator="," close= ")">
            #{item}
        </foreach>
    </select>

2.1.3  foreach标签处理Map类型参数

SysUserMapper类的getUserByRoleIdMap()方法:

  /**
     * 根据角色id集合(集合存在Map中) 查询用户列表信息
     * @param releMap
     * @return
     */
    List<SysUser>getUserByRoleIdMap(Map<String,Object>releMap);

SysUserMapper.xml中的查询代码:

 <select id="getUserByRoleIdMap" resultType="SYsUser">
        select * from t_sys_user where roleId in
        <foreach collection="roleIdList" item="item" open="(" separator="," close= ")">
            #{item}
        </foreach>
    </select>

实际上,MyBatis 框架在进行参数传递时都会把它封装成一个Map 对象,而 Map 对象的 key就是参数名称，参数的值就是 key 对应的value。

小结:

(1)当参数为数据基本类型或数组、List 集合类型时，MyBatis框架会将参数封装在个 Map 对象中。

(2)当参数为数组类型时，collection对应值默认为 array。

(3)当参数为List集合类型时，collection 对应值默认为 list。

(4)如果使用@Param 注解为参数设置了名称，collection 对应值为参数名。

(5)当参数为Map对象时,collection对应值为该Map 对象中数组或集合元素对应的key。

3.1 MyBatis框架动态SQL处理更新能力

MyBatis框架动态更新数据的功能主要通过set+ 让标签实现。

3.1.1 set标签

SysUserMapper类的update()方法:

  /**
     * 修改角色id
     * @param sysUser
     * @return
     */
    int update(SysUser sysUser);

SysUserMapper.xml中的更新代码:

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

如果嫌太复杂,可以使用MyBatis框架提供的set标签和if标签进行处理。其中set标签主要是用于数据的更新操作,if标签则用于控制更新的字段

语法:

<set>
 <if test="条件判断,放回true或false"
    SQL语句...
</if>
</set>

set标签的用法和功能都与 where 标签非常相似,可以在其包衷的语句前拼接一个set关键字，并能忽略更新语句尾部多余出来的逗号。配合if标签灵活使用set 标签,就可以在拼接 SOl时忽略不需要修改的字段，从而实现数据部分更新的功能。

SysUserMapper.xml中的更新代码:

    <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="password != null">password = #{password},</if>
        <if test="sex!=null">sex=#{sex},</if>
        <if test="birthday!=null">birthday=#{birthday},</if>
        <if test="phone!=null">phone=#{phone},</if>
        <if test="address!=null">address=#{address},</if>
        <if test="roleId!=null">roleId=#{roleId},</if>
        <if test="updatedUserId!=null">updatedUserId=#{updatedUserId},</if>
        <if test="updatedTime!=null">updatedTime=#{updatedTime}</if>
    </set>
    where id=#{id}
    </update>

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

4.1 MyBatis框架动态SQL知识扩展

4.1.1 trim 标签

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

语法

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

trim标签的属性介绍: 

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

SysUserMapper.xml中的查询代码:

 resultMap id="SysUserResult" type="SysUser">
        <id property="id" column="id"/>
        <result property="userRoleName" column="roleName"/>
    </resultMap>

    <select id="selectList" resultMap="SysUserResult" parameterType="map">
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>

 SysUserMapper.xml中的更新代码:

 <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="password != null">password = #{password},</if>
            <if test="sex!=null">sex=#{sex},</if>
            <if test="birthday!=null">birthday=#{birthday},</if>
            <if test="phone!=null">phone=#{phone},</if>
            <if test="address!=null">address=#{address},</if>
            <if test="roleId!=null">roleId=#{roleId},</if>
            <if test="updatedUserId!=null">updatedUserId=#{updatedUserId},</if>
            <if test="updatedTime!=null">updatedTime=#{updatedTime}</if>
        </trim>
    </update>

4.1.2 MyBatis框架分页查询

MySQL数据库的分页关键字为 limit，使用 MyBatis 框架对其进行操作时，用法与在客户端的操作基本相同。

SysUserMapper类的selectPageList()方法:

/**
     * 分页查询用户列表
     * @param realName
     * @param roleId
     * @param pageBegin
     * @param pageSize
     * @return
     */
    List<SysUser>selectPageList(@Param("realName")String realName,@Param("roleId")Integer roleId, @Param("pageBegin") Integer pageBegin,@Param("pageSize") Integer pageSize);
}

 SysUserMapper.xml中的分页查询代码:

<select id="selectPageList" resultType="SysUser">
        select * from t_sys_user
        <trim prefix="where" prefixOverrides="and | or">
            <if test="realName !=null and realName !=''">
                and realName like concat('%',#{realName},'%')
            </if>
        <if test="roleId !=null">
            and roleId=#{roleId}
        </if>
        </trim>
        order by createdTime desc
        limit #{pageBegin},#{pageSize}
    </select>

测试方法testSelectPageList的代码

 @Test
    public void testSelectPageList() {
        List<SysUser> userList = new ArrayList<SysUser>();
        try {
            String realName = "";
            Integer roleId = 2;
            Integer pageindex = 2;
            Integer pageSize = 2;
            Integer pageBegin = (pageindex - 1) * pageSize; //计算查询原始位置
            userList = sqlSession.getMapper(SysUserMapper.class).selectPageList(realName, roleId, pageBegin, pageSize); //查询分页数据
        } catch (Exception e) {
            e.printStackTrace();
        }
        logger.info("查询到用户数量:" + userList.size());
        for (SysUser user : userList) {
            logger.info("查询到用户数量:" + user);
        }
    }

在上述代码中，limit#{pageBegin},#{pageSize}语句中的 pageBegin 表示查询开始位置pageSize 表示要查询的数据量(每页数据量)。
pageBegin的计算方法为IntegerpageBegin=(pageIndex-1)*pageSize。