mybatis动态sql

动态SQL

1. 基于OGNL表达式

2. 完成多条件查询等逻辑实现

3. 用于实现动态SQL的元素主要有

   • if

   • trim

   • where

   • set

   • choose（when、otherwise）

   • foreach

if

• 改造查询用户信息列表的演示示例，增加查询条件 用户角色（根据角色id查询） 用户名称（模糊查询）

    <!-- 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->
    <resultMap type="User" id="userList">
        <result property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="phone" column="phone"/>
        <result property="birthday" column="birthday"/>
        <result property="gender" column="gender"/>
        <result property="userRole" column="userRole"/>
        <result property="userRoleName" column="roleName"/>
    </resultMap>
    
    <!-- 查询用户列表 -->
    <select id="getUserList" resultMap="userList">
        select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole = r.id
            <if test="userRole != null">
                and u.userRole = #{userRole}
            </if>
            <if test="userName != null and userName != ''">
                and u.userName like CONCAT ('%',#{userName},'%') 
            </if>
    </select>

where

1. 简化SQL语句中where条件判断

2. 智能处理and和or

<select id="getUserList" resultType="User">
        select * from smbms_user 
            <where>
                <if test="userName != null and userName != ''">
                    and userName like CONCAT ('%',#{userName},'%') 
                </if>
                <if test="userRole != null">
                    and userRole = #{userRole}
                </if>
            </where>
</select>

where:自动加where,自动去掉第一个and

set

• 若某个参数为null，则不需要更新，保持数据库原值

    <!-- 修改用户信息 -->
    <update id="modify" parameterType="User">
         update smbms_user 
             <set>
                <if test="userCode != null">userCode=#{userCode},</if>
                <if test="userName != null">userName=#{userName},</if>
                <if test="userPassword != null">userPassword=#{userPassword},</if>
                <if test="gender != null">gender=#{gender},</if>
                <if test="birthday != null">birthday=#{birthday},</if>
                <if test="phone != null">phone=#{phone},</if>
                <if test="address != null">address=#{address},</if>
                <if test="userRole != null">userRole=#{userRole},</if>
                <if test="modifyBy != null">modifyBy=#{modifyBy},</if>
                <if test="modifyDate != null">modifyDate=#{modifyDate}</if>
             </set>
             where id = #{id}
    </update>

trim

1. 属性 prefix suffix prefixOverrides suffixOverrides

2. 更灵活地去除多余关键字

3. 替代where和set

    <select id="getUserList" resultType="User">
        select * from smbms_user 
            <trim prefix="where" prefixOverrides="and | or">
                <if test="userName != null and userName != ''">
                    and userName like CONCAT ('%',#{userName},'%') 
                </if>
                <if test="userRole != null">
                    and userRole = #{userRole}
                </if>
            </trim>
    </select>

使用if+trim替代if+set进行更新用户表数据

<update id ="modify" parameterType="User">
update smbms_user
    <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">   
        <if test="userCode != null">userCode = #{userCode},</if>
        <if test="userName!= null">userCode = #{userName },</if>
        <if test="userPassword!= null">userPassword=#{userPassword },</if>
    </trim>
    
</update>

foreach

1. 迭代一个集合，通常用于in条件

2. 属性

   • item

   • index

   • collection:必须指定

     • list

     • array

     • map-key

     • open

     • separator

     • close

    public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);
    
    <!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_array -->
    <select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
        select * from smbms_user where userRole in 
            <foreach collection="array" item="roleIds" open="(" separator="," close=")">
                #{roleIds}
            </foreach>
    </select>

• 改造上一个演示示例，使用foreach实现，参数类型改为List

    public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);
    
    <!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list -->
    <select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">
        select * from smbms_user where userRole in 
            <foreach collection="list" item="roleList" open="(" separator="," close=")">
                #{roleList}
            </foreach>
    </select>

• 在上一个演示示例中，增加一个参数：gender，要求查询出指定性别和用户角色列表下的用户列表

public List<User> getUserByRoleId_foreach_map(Map<String,Object> roleMap);
​
    <!-- 根据用户角色列表(单参数)，获取该角色列表下用户列表信息-foreach_map -->
    <select id="getUserByRoleId_foreach_map" resultMap="userMapByRole">
        select * from smbms_user where userRole in 
            <foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
                #{roleMap}
            </foreach>
    </select>

choose

1. 相当于Java中switch语句

2. 当when有条件满足的时候，就跳出choose

    <!-- 查询用户列表(choose) -->
    <select id="getUserList_choose" resultType="User">
        select * from smbms_user where 1=1
            <choose>
                <when test="userName != null and userName != ''">
                    and userName like CONCAT ('%',#{userName},'%')
                </when>
                <when test="userCode != null and userCode != ''">
                    and userCode like CONCAT ('%',#{userCode},'%')
                </when>
                <when test="userRole != null">
                    and userRole=#{userRole}
                </when>
                <otherwise>
                    <!-- and YEAR(creationDate) = YEAR(NOW()) -->
                    and YEAR(creationDate) = YEAR(#{creationDate})
                </otherwise>
            </choose>
    </select>