006mybatis动态sql

bind （模糊查询，前后缀补充）

bind 标签是通过 OGNL 表达式去定义一个上下文的变量

SysUser sysUser1 = new SysUser();
sysUser1.setUserName("Kong");
List<SysUser> sysUsers = sysUserMapper.selectByUserLike(sysUser1);

select *
    from sys_user
    <bind name="userNameLike" value="'%'+userName+'%'"/>
    where user_name like #{userNameLike}

<where>

        where 1=1 的条件拼装

<if>

<if test="userCode != null and userCode != ''">
    user_code = #{userCode,jdbcType=VARCHAR}
</if>
<if test="userName != null  and userName.contains('Kon')">
    and user_name = #{userName}
</if>

set

        会自动去掉语句后面多余的逗号

<update id="updateInterface" parameterType="IntFTvForm">
  
  UPDATE tab_omin_cm_cc_intf_tv
    <set>
            C_URL_PARAM = #{cUrlParam},
            C_TABLE_NAME = #{cTableName},
    </set>
   WHERE  C_INTF_TV_ID = #{cIntfTvId}
</update>

<foreach>

collection: 必填， 集合/数组/Map的名称

item: 变量名。 即从迭代的对象中取出的每一个值

index: 索引的属性名。 当迭代的对象为 Map 时， 该值为 Map 中的 Key

open: 循环开头的字符串

close: 循环结束的字符串

separator: 每次循环的分隔符

批量新增

Integer  batchInsert(List<SysUser> sysUsers);
<insert id="batchInsert" >
    insert into sys_user ( user_code, user_name, user_password, user_state)
            values
    <foreach collection="collection" item="user" separator=",">
        (
         #{user.userCode},
         #{user.userName},
         #{user.userPassword},
         #{user.userState})
    </foreach>
</insert>

IN

List<SysUser>  selectByIdsAndUserName( List<Integer> ids, String userName);
<select id="selectByIdsAndUserName" resultType="com.du.bean.manytomany.SysUser">
    select *
    from sys_user
    <where>
        <foreach collection="ids" open="user_id in (" close=")" item="id" separator=",">
            #{id}
        </foreach>
        and user_name=#{userName}
    </where>
</select>

choose when otherwise

一个 choose 标签至少有一个 when, 最多一个otherwise

SELECT
    <choose>
        <when test="sqlColumns.size() > 0">
            <foreach collection="sqlColumns" item="column" separator=",">
                ${column}
            </foreach>
        </when>
        <otherwise>
            *
        </otherwise>
    </choose>
FROM
    ${tableName}
<where>
    <if test="sqlParams.size() > 0">
        <foreach collection="sqlParams" item="map" separator=" ">
            ${map.c_logic} ${map.c_param_key} ${map.c_param_operator}
            <choose>
                <when test="map.c_value_prefix != null and map.c_value_suffix == null">
                    <bind name="pattern" value="map.c_value_prefix + map.value" />
                    #{pattern}
                </when>
                <when test="map.c_value_prefix == null and map.c_value_suffix != null">
                    <bind name="pattern" value="map.value + map.c_value_suffix" />
                    #{pattern}
                </when>
                <when test="map.c_value_prefix != null and map.c_value_suffix != null">
                    <bind name="pattern" value="map.c_value_prefix + map.value + map.c_value_suffix" />
                    #{pattern}
                </when>
                <otherwise>
                    #{map.value}
                </otherwise>
            </choose>
        </foreach>
    </if>
</where>
ORDER BY version DESC, c_updated_date DESC

Trim

prefix: 当 trim 元素包含有内容时， 增加 prefix 所指定的前缀

prefixOverrides: 当 trim 元素包含有内容时， 去除 prefixOverrides 指定的 前缀

suffix: 当 trim 元素包含有内容时， 增加 suffix 所指定的后缀

suffixOverrides： 当 trim 元素包含有内容时， 去除 suffixOverrides 指定的后缀

 <update id="upadteBlogByConditionTrim" parameterType="Blog">
        UPDATE 
          `blog` 
        <trim prefix="set" suffixOverrides=",">
         author_id = #{authorId},
        </trim>
        WHERE `id` = #{id}
    </update> 
  <select id="selectBlogByConditionTrim" parameterType="Blog" resultMap="blogResultMap">
        select * from blog 
        <trim prefix="where" prefixOverrides="and | or">    
               lower(title) like lower(#{title})
        </trim>
</select>