Mybatis动态SQL之foreach

一、概要

动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历
foreach可以遍历三种类型，List，array，Map

二、属性

属性	说明
item	必选。循环体中的具体对象。 在list和数组中是其中的对象，在map中是value。
collection	必选 1. 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list 2. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array 3. 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map
separator	可选。元素之间的分隔符
open	可选。循环前添加前缀
close	可选。循环后添加后缀
index	可选。在list和数组中,index是元素的索引，在map中，index是元素的key

三、栗子

遍历集合

List<User> findByIdList(@Param("ids") List<Integer> ids);

<select id="findByIdList" resultMap="BaseResultMap">
  SELECT
  <include refid="Base_Column_List" />
  from user
  WHERE uid IN
  <foreach collection="list" item="item" index="index"
           open="(" close=")" separator=",">
    #{item}
  </foreach>
</select>

遍历map

// 参数封装成Map的类型
User findKeysCol(@Param("map")Map<String, Object> map);

  <select id="findKeysCol" resultType="User">
        SELECT *
        FROM user
        WHERE
        <foreach item="item" index="key" collection="map"
                 open="" separator="AND" close="">${key} = #{item}
        </foreach>
    </select>

注意！！！一定要注意集合判断size>0,否则size为0时也可以进去，sql语法可能错误

//如果map中put一个集合时
map.put("customerContactList",customerContactList);
//则xml中collection="put进去的集合"
    <if test="customerContactList != null and customerContactList.size>0 ">
                id not in
                <foreach collection="customerContactList" open="(" close=")" item="item" separator=",">
                    #{item.contactId}
                </foreach>
          </if>

批量新增

int batchSave(List<WomcWorkOrderTemplateField> fieldList);

<insert id="batchSave" parameterType="java.util.List",keyProperty="id" useGeneratedKeys="true">
    insert into womc_work_order_template_field
    (
    `template_name`,
    `field_name`,
    `field_type`,
    `is_required`,
    `status`,
    `sort`,
    `template_id`,
    `option_content`
    )
    values
    <foreach collection="list" item="fieldList" index="index" separator=",">
      (
      #{fieldList.templateName},
      #{fieldList.fieldName},
      #{fieldList.fieldType},
      #{fieldList.isRequired},
      #{fieldList.status},
      #{fieldList.sort},
      #{fieldList.templateId},
      #{fieldList.optionContent}
      )
    </foreach>

批量更新

int batchUpdateTypeName(List<WomcCustomWorkOrder> list);

<update id="batchUpdateTypeName" parameterType="java.util.List">
        <foreach collection="list" separator=";" item="item">
            update womc_custom_work_order
            <set>
                <if test="item.typeName != null">`type_name` = #{item.typeName}</if>
            </set>
            where type_id = #{item.typeId}
        </foreach>
    </update>

其他：

 <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.example.mytest.domain.UmsAdmin" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into ums_admin
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="username != null">
        username,
      </if>
      <if test="password != null">
        `password`,
      </if>
      <if test="icon != null">
        icon,
      </if>
      <if test="email != null">
        email,
      </if>
      <if test="nickName != null">
        nick_name,
      </if>
      <if test="note != null">
        note,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="loginTime != null">
        login_time,
      </if>
      <if test="status != null">
        `status`,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="username != null">
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="icon != null">
        #{icon,jdbcType=VARCHAR},
      </if>
      <if test="email != null">
        #{email,jdbcType=VARCHAR},
      </if>
      <if test="nickName != null">
        #{nickName,jdbcType=VARCHAR},
      </if>
      <if test="note != null">
        #{note,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="loginTime != null">
        #{loginTime,jdbcType=TIMESTAMP},
      </if>
      <if test="status != null">
        #{status,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>

  <update id="updateByPrimaryKeySelective" parameterType="com.example.mytest.domain.UmsAdmin">
    <!--@mbg.generated-->
    update ums_admin
    <set>
      <if test="username != null">
        username = #{username,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        `password` = #{password,jdbcType=VARCHAR},
      </if>
      <if test="icon != null">
        icon = #{icon,jdbcType=VARCHAR},
      </if>
      <if test="email != null">
        email = #{email,jdbcType=VARCHAR},
      </if>
      <if test="nickName != null">
        nick_name = #{nickName,jdbcType=VARCHAR},
      </if>
      <if test="note != null">
        note = #{note,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="loginTime != null">
        login_time = #{loginTime,jdbcType=TIMESTAMP},
      </if>
      <if test="status != null">
        `status` = #{status,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>