第三章-mybatis框架动态sql

1.动态sql处理简单的多参数查询

1.1if标签

需求：

1.查询roleId为2的并且realName里有赵这个字的用户

2.只查realName里有赵这个字的用户

也就是说roleId和realName都不是必传的参数时要封装成一个sql怎么写？

<select id="getList" resultMap="userRole" parameterType="cn.cvs.pojo.SysUser">
        SELECT
            a.*, b.id AS rid,
            b.roleName,
            b. CODE
        FROM
            t_sys_user a
        LEFT JOIN t_sys_role b ON a.roleId = b.id
        WHERE
            1 = 1
        <if test="roleId != null ">
            and a.roleId = #{roleId }
        </if>

        <if test=" realName!= null and realName!= '' ">
            and a.realName like CONCAT( '%', #{realName},'%')
        </if>
    </select>

1.2where标签

 <resultMap id="userRole" type="cn.cvs.pojo.SysUser">
        <id column="id" property="id"></id>
        <association property="sysRole" javaType="cn.cvs.pojo.SysRole">
            <id property="id" column="rid"></id>
            <result property="code" column="code"></result>
            <result property="roleName" column="roleName"></result>
        </association>
    </resultMap>

    <select id="getList" resultMap="userRole" parameterType="cn.cvs.pojo.SysUser">
        SELECT
            a.*, b.id AS rid,
            b.roleName,
            b. CODE
        FROM
            t_sys_user a
        LEFT JOIN t_sys_role b ON a.roleId = b.id

        <where>
            <if test="roleId != null ">
                 a.roleId = #{roleId }
            </if>

            <if test=" realName!= null and realName!= '' ">
                and a.realName like CONCAT( '%', #{realName},'%')
            </if>
        </where>


    </select>

1.3实战训练

使用if标签+where标签改造入库记录模块的列表查询功能

1.4choose（when,othervise）标签

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

<select id="getListByChoose" resultType="cn.part3.pojo.SysUser" parameterType="cn.part3.pojo.SysUser">

        select  * from t_sys_user
        <where>

            <choose>
                <when test=" roleId!= null  ">
                    and roleId = #{roleId}
                </when>
                <when test=" realName!= null and realName!= '' ">
                    and realName like CONCAT( '%', #{realName},'%')
                </when>

                <when test=" account!= null and account != '' ">
                    and account = #{account}
                </when>

                <otherwise>
                    and  YEAR (createdTime) = YEAR (#{createdTime})

                </otherwise>
            </choose>
        </where>
    </select>

备注：

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

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

2.动态sql处理集合参数

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

    <select id="getUserByRoleIdArray" resultType="cn.part3.pojo.SysUser">

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

• collection:参数名称，当参数为数组类型时，默认参数名为array,当参数类型为list集合的时候，默认参数名为list,当参数类型为map时，参数名为map中集合元素所在键值对的key.
• item：遍历数组的时候，为数组或list集合中的元素起的别名
• open:起始位置的拼接字符，表示in语句以“（”左括号开始
• separator：元素之间的连接符，表示in语句中的元素之间以“，”逗号连接
• close：结束位置的拼接字符，表示in语句以“）”右括号结束

2.2foreach标签处理list类型参数

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

    </select>

2.3foreach标签处理map类型参数

<select id="getUsersByRoleMap" resultType="cn.part3.pojo.SysUser">
        select  * from  t_sys_user where roleId in
        <foreach collection="roleIdList" item="item" open="(" separator="," close =")">
            #{item}
        </foreach>
    </select>

sqlSession=MyBatisUtil.createSqlSession();
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);

Map<String,Object> map = new HashMap<>();
map.put("roleIdList",ids);
List<SysUser> sysUserList = sqlSession.getMapper(SysUserMapper.class).getUsersByRoleMap(map);
sysUserList.forEach(u -> System.out.println(u));

3.动态sql处理更新功能

mybatis框架动态更新数据的功能主要是通过set+if标签实现

set标签

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

        where id = #{id}
    </update>

4.动态sql知识扩展

4.1tirm标签

语法：

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

</trim>

改造set标签

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

        where id = #{id}
    </update>

改造where标签

<select id="getList" resultMap="userRole" parameterType="cn.part3.pojo.SysUser">
            SELECT
                a.*, b.id AS rid,
                b.roleName,
                b. CODE
            FROM
                t_sys_user a
            LEFT JOIN t_sys_role b ON a.roleId = b.id

            <trim prefix="where" prefixOverrides="and|or">
            
                <if test="roleId != null ">
                     and a.roleId = #{roleId }
                </if>

                <if test=" realName!= null and realName!= '' ">
                    and a.realName like CONCAT( '%', #{realName},'%')
                </if>
            </trim>

    </select>

5.mybatis框架的分页功能

mybatis框架并没有对分页功能做过多的处理，而是使用数据库自带的分页功能。

重点：分页参数和实体参数(条件查询)同时传过来的处理方式

 //分页查询
    public List<SysUser> page(@Param("user")SysUser user, @Param("pageIndex") Integer pageIndex,@Param("pageSize") Integer pageSize);

<select id="page" resultType="cn.part3.pojo.SysUser">
        select  * from  t_sys_user
        <trim prefix="where" prefixOverrides="and|or">
            <if test="user.realName != null and  user.realName != '' ">
               and realName like  CONCAT ('%',#{user.realName},'%')
            </if>
            <if test="user.roleId != null ">
               and roleId = #{user.roleId}
            </if>
        </trim>

        order by createdTime desc
        limit #{pageIndex}, #{pageSize}
    </select>

重点注意这个@Param("user")和sql映射文件中的 <if test="user.xxx>绑定。

6.讲课视频

https://www.bilibili.com/video/BV1TR4y1Z7np?p=3