Mybatis一些比较复杂的操作

1、批量插入数据

Oracle和mysql的写法不一样，这里我只研究Oracle的：

第一种写法：这其中useGeneratedKey默认即位false，意味是否使用自增长主键，Oracle没有自增长。

<insert id="autoFullPass" parameterType="java.util.List" useGeneratedKeys="false">
INSERT ALL
<foreach collection="listAutoData" item="item" index="index">
into SATISFACTION_SURVEY 
(PROJECT_NO, EVALUATE, EMPLOYEE_NO, SUBMIT_TIME, as_side)
values
(#{item.projectNo}, #{evaluate}, #{item.employeeNo}, sysdate, 0)
</foreach>
SELECT 1 FROM DUAL
</insert>

第二种写法：

<insert id="createExamRecord" parameterType="java.util.List">
      insert into CAIM_EXAM 
      (ID, OPEN_ID, QUESTION_ID)
      <foreach collection="randomQus" item="item" index="index" separator="UNION ALL">
          SELECT
          sys_guid(),
          #{openId},
          #{item.questionId}
          FROM dual
      </foreach>
</insert>

这种写法相当于：

INSERT INTO TABLE_TEST (ID,NAME,PASSWORD,USERAGE)
     SELECT 'id1','zhangsan','123',20 FROM DUAL
      UNION ALL SELECT 'id2','lisi','321',30 FROM DUAL
      UNION ALL SELECT 'id3','wang','123456',40 FROM DUAL

嵌套循环插入：

<insert id="insertCircle" parameterType="java.util.List">
 INSERT INTO SALES_WEEKLY
        (ID, SUB_TYPE, 
        DISTRICT_CODE, REGION_CODE, SUB_AREA_CODE,
        YEAR, QUARTER, 
        MONTH, WEEK, 
        CREATE_USER, CREATE_TIME, WEEK_TYPE)
            <foreach collection="monthAry" item="item" index="index" separator="UNION ALL">
               <foreach collection="weekAry" item="val" index="seq" separator="UNION ALL">
                    SELECT
                    sys_guid() id,9 SUB_TYPE,
DISTRICT_CODE,12345 REGION_CODE,66666 SUB_AREA_CODE,
                    #{year} YEAR,#{quarter} QUARTER,
                    #{item} MONTH, 
                    #{val} WEEK,
                    'ZZ123' CREATE_USER, sysdate CREATE_TIME, 6 WEEK_TYPE
                    FROM dual
               </foreach>
            </foreach>
  </insert>

2、批量删除，通常我们删除文件都是通过主键ID删除的，主要掌握foreach中的关键字用法即可

<delete id="deleteByListSon" parameterType="java.util.List">
    delete from WECHAT_MENU where id in
    <foreach collection="listSon" item="listSon" index="index" separator="," open="("   close=")">
        #{listSon}
    </foreach>
</delete>

3、批量更新：

<update id="updateRole" parameterType="java.util.List">
    <foreach collection="updateToDB" item="record" index="index" open="begin" close=";end;" separator=";">
        update SSM_ROLE
        <set>
            <if test="record.updateUser != null">
                UPDATE_USER = #{employeeNo},
            </if>
            <if test="record.updateTime != null">
                UPDATE_TIME = sysdate,
            </if>
            <if test="record.annualHosKpi != null">
                ANNUAL_HOS_KPI = #{record.annualHosKpi,jdbcType=DECIMAL},
            </if>
            <if test="record.ytdPlanHos != null">
                YTD_PLAN_HOS = #{record.ytdPlanHos,jdbcType=DECIMAL},
            </if>
            <if test="record.status == 1">
                STATUS = 1,
                SUBMIT_USER = #{employeeNo},
                SUBMIT_TIME =
                sysdate,
            </if>
            <if test="record.manageMeeting != null">
                MANAGE_MEETING =
                #{record.manageMeeting,jdbcType=DECIMAL},
            </if>
            <if test="record.remark != null">
                REMARK = #{record.remark}
            </if>
        </set>
        where ID = #{record.id}
    </foreach>
  </update>

操作数组：

<foreach collection="listAdxSub" index="index" item="val" separator="," open="(" close=")">
             #{val}<!--也可以写成to_number(${val},999999)  这种只能用$符，外面那种#$都可以，当然  ${val} === ${listAdxSub[index]}  -->
</foreach>

批量查询：

<select id="queryList" parameterType="java.util.List" resultType="....ProductEntity">

  select T.table_filed_id as productId, T.table_field_name as productName from
    (
      <foreach collection="productList" item="product" separator="union all">
         select * from table_product where productType = #{product.productType} and ....
      </foreach>
    )T
</select>