数据库的批量操作

 <update id="updateBatch" parameterType="java.util.List">
        update am_dept
        set `status` = case dept_id
        <foreach collection="list" item="item">
            when #{item.deptId,jdbcType=BIGINT} then #{item.status,jdbcType=TINYINT}
        </foreach>
        end
        where dept_id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.deptId,jdbcType=BIGINT}
        </foreach>
    </update>
    dept_id是主键
    

 <update id="batchUpdate" parameterType="java.util.List">
        update im_safe_stock
        set
        material_inventory_id --（列名字）= case safe_stock_id  ---主键
        <foreach collection="list" item="po">
            when #{po.safeStockId,jdbcType=BIGINT} then #{po.materialInventoryId,jdbcType=BIGINT}
        </foreach>
        end,
        lower_limit --（列名字）= case safe_stock_id  ---主键
        <foreach collection="list" item="po">
            when #{po.safeStockId,jdbcType=BIGINT} then #{po.lowerLimit,jdbcType=DECIMAL}
        </foreach>
        end,
        way --（列名字）= case safe_stock_id  ---主键
        <foreach collection="list" item="po">
            when #{po.safeStockId,jdbcType=BIGINT} then #{po.way,jdbcType=TINYINT}
        </foreach>
        end
        where safe_stock_id in  ---主键
        <foreach collection="list" item="po" open="(" separator="," close=")">
            #{po.safeStockId,jdbcType=BIGINT}
        </foreach>
    </update>

查询自创

UNION ALL 不允许重复
UNION 消去重复
forreach 后面一定是 ","而不是 ";"
<foreach collection="list" item="dayTime" separator="union all">
    (select #{dayTime} as dayTime, IFNULL(sum(stroage_num),0.0) as num
    from kb_product_rank
    where dates like #{dayTime} '%')
</foreach>

      <!--  根据userId和权限id批量更新用户权限中间表-->
      Integer insertBatch(@Param("uids") List<Long> uids,@Param("perms") List<Long> perms);
  <insert id="insertBatch">
    insert into am_m_user_authority(user_id,authority_id)values
    <foreach collection="uids" item="uid" separator=",">
       <foreach collection="perms" item="perm" separator=",">
         (
           #{uid,jdbcType=BIGINT},
           #{perm,jdbcType=BIGINT}
         )
       </foreach>
    </foreach>
  </insert>
  --双重list
   
    <choose>
    <when test="parentId != null and parentId !=0">
        where au.parent_id = #{parentId,jdbcType=BIGINT} and r.role_id = #{roleId,jdbcType=BIGINT} and au.type =
        #{type,jdbcType=TINYINT}
    </when>
    <otherwise>
        where au.parent_id = 0 and au.type = #{type,jdbcType=TINYINT} and r.role_id = #{roleId,jdbcType=BIGINT}
    </otherwise>
</choose>
--相当于if  条件