Mybatis动态sql、Mybatis-Generator插件、Mybatis 缓存以及Spring整合Mybatis

一、Mybatis 动态SQL 配置

1. XML 标签配置

if 标签

逻辑判断标签 判断表达式 true

<if test="表达式"></if>    <!-- and | or  --> 

使用

<!-- 01 --> 
<select id="queryAccountsByParams01" parameterType="AccountQuery" resultType="Account">
    select <include refid="account_columns"/>
    from account
    where 1=1
    <if test="aname !=null and aname !=''">
        and  aname like concat('%',#{aname},'%')
    </if>
    <if test="type !=null and type !=''">
        and type=#{type}
    </if>
    <if test="userId=null">
        and user_id=#{userId}
    </if>
    <if test="time !=null and time !=''">
        and create_time &gt;=#{time}
    </if>
</select>

<!-- 02 -->
<select id="queryAccountsByParams02" parameterType="AccountQuery" resultType="Account">
    select <include refid="account_columns"/>
    from account
    where 1=1
    <if test="@Ognl@isNotEmpty(aname)">
        and  aname like concat('%',#{aname},'%')
    </if>
    <if test="@Ognl@isNotEmpty(type)">
        and type=#{type}
    </if>
    <if test="userId !=null">
        and user_id=#{userId}
    </if>
    <if test="@Ognl@isNotEmpty(time)">
        and create_time &gt;=#{time}
    </if>
</select>

where 标签

记录过滤标签

<where>
  ...
</where>

使用 记录过滤 | 条件过滤 如果紧跟where 后第一个逻辑判断条件成立时 and|or 单词被忽略

<select id="queryAccountsByParams" parameterType="AccountQuery" resultType="Account">
    select <include refid="account_columns"/>
    from account
    <where>
        <if test="@Ognl@isNotEmpty(aname)">
            and  aname like concat('%',#{aname},'%')
        </if>
        <if test="@Ognl@isNotEmpty(type)">
            and type=#{type}
        </if>
        <if test="userId !=null">
            and user_id=#{userId}
        </if>
        <if test="@Ognl@isNotEmpty(time)">
            and create_time &gt;=#{time}
        </if>
    </where>
</select>

choose when otherwise 标签

对结果 条件判断 执行二选一 类似 if-else

<choose>
    <when test=""></when>
    <otherwise></otherwise>
</choose>

<select id="queryAccountsByParams04" parameterType="AccountQuery" resultType="Account">
    select id, aname, user_id as userId, create_time as createTime, update_time as updateTime, remark,
    <choose>
        <when test="userId !=null">
            money
        </when>
        <otherwise>
            type
        </otherwise>
    </choose>
    from account
    <where>
        <if test="aname !=null and aname !=''">
            and  aname like concat('%',#{aname},'%')
        </if>
        <if test="type !=null and type !=''">
            and type=#{type}
        </if>
        <if test="userId !=null">
            and user_id=#{userId}
        </if>
        <if test="time !=null and time !=''">
            and create_time &gt;=#{time}
        </if>
    </where>
</select>

<!--
     动态sql 标签-choose when otherwise   if-else  二选一
   -->
<select id="queryAccountsByParams05" parameterType="AccountQuery" resultType="Account">
    select <include refid="account_columns"/>
    from account
    <where>
        <choose>
            <when test="userId != null">
                <if test="aname != null and aname != ''">
                    aname like concat('%',#{aname},'%')
                </if>
            </when>
            <otherwise>
                <if test="type != null and type != ''">
                    type=#{type}
                </if>
            </otherwise>
        </choose>
    </where>
</select>

trim 标签

可以替代where(update) 标签类似功能

<trim prefix=" "  prefixOverrides=" " suffixOverrides=" " suffix=" "></trim>

使用

<select id="queryAccountsByParams06" parameterType="AccountQuery" resultType="Account">
    select <include refid="account_columns"/>
    from account
    <trim prefix="where"  prefixOverrides="and |or" >
        <if test="aname !=null and aname !=''">
            and  aname like concat('%',#{aname},'%')
        </if>
        <if test="type !=null and type !=''">
            and type=#{type}
        </if>
        <if test="userId !=null">
            and user_id=#{userId}
        </if>
        <if test="time !=null and time !=''">
            and create_time &gt;=#{time}
        </if>
    </trim>
</select>

set 标签

<!--用于更新 如果最后一个字段出现, 该字符会被自动忽略-->
<set></set>

使用

<update id="updateAccountById" parameterType="Account">
     update account
     <set>
         <if test="aname !=null">
             aname=#{aname},
         </if>
         <if test="type !=null">
             type=#{type},
         </if>
         <if test="money !=null">
             money=#{money}
         </if>
     </set>
     where id=#{id}
</update>

foreach 标签

用于批量操作(批量添加 更新 删除)

批量添加

<insert id="saveUserBatch" parameterType="list">
    insert into  user(user_name, user_pwd, flag, create_time) values
    <foreach collection="list" item="item" separator=",">
        (#{item.userName},#{item.userPwd},#{item.flag},#{item.createTime})
    </foreach>
</insert>

批量更新

<update id="updateUserPwdByIdsBatch" parameterType="map">
    update user set user_pwd=#{userPwd}
    where id in
    <foreach collection="ids" item="item" open="(" separator="," close=")"