?最近工作中要用到批量插入家坎，由于插入的數(shù)據(jù)量很大，采用foreach直接拼接SQL時擎浴，由于數(shù)據(jù)量大，拼接的SQL超過mybatis的限制后毒涧，造成異常導致插入失敗贮预，經(jīng)過網(wǎng)上搜索一番，發(fā)現(xiàn)零零散散的不完整链嘀，都無法使用，后來經(jīng)過自己整理后档玻，大功告成怀泊，在此記錄以備查詢。

mapper代碼片段：

  <insert id="insertBatch" parameterType="java.util.List">
      insert into ics.DAY_REPORT_PROJECT (PID, REPORTDATE, PROJECTID, PROJECTAMT, BIDTIME, BIDURL, TRANSFERSTATUS,LIMITTYPE, PROJECTLIMIT, BIDRATE, VALUEDATE, REPAYTYPE, ASSURETYPE, CREATETIME)
         select ics.SEQ_report_project_PID.Nextval, a.* from (
           <foreach collection="list" item="item" index="index" separator="union all" >  
              select #{item.reportDate}, #{item.projectId}, #{item.projectAmt}, #{item.bidTime}, #{item.bidUrl}, #{item.transferStatus},#{item.limitType}, #{item.projectLimit}, #{item.bidRate}, #{item.valueDate}, #{item.repayType}, #{item.assureType}, #{item.createTime}
              from dual
           </foreach>
          ) a
   </insert>

dao代碼片段：

    /**
     * 批量插入項目信息
     * 
     * @param reportProjects
     * @throws Exception
     */
    public void insert(List<DayReportProject> reportProjects) throws Exception {
        int result = 1;
        SqlSession batchSqlSession = null;
        try {
            batchSqlSession = this.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            // 獲取批量方式的sqlsession
            int batchCount = 1000; // 每批commit的個數(shù)
            int batchLastIndex = batchCount; // 每批最后一個的下標
            for (int index = 0; index < reportProjects.size();) {
                if (batchLastIndex >= reportProjects.size()) {
                    batchLastIndex = reportProjects.size();
                    result = result * batchSqlSession.insert("cn.hepai.model.DayReportProject.insertBatch", reportProjects.subList(index, batchLastIndex));
                    batchSqlSession.commit();
                    batchSqlSession.clearCache(); // 清理緩存误趴，防止溢出
                    log.info("index : " + index + ", batchLastIndex : " + batchLastIndex + ", 共插入項目信息 : " + (batchLastIndex + 1) + "條");
                    break; // 數(shù)據(jù)插入完畢霹琼，退出循環(huán)
                } else {
                    result = result * batchSqlSession.insert("cn.hepai.model.DayReportProject.insertBatch", reportProjects.subList(index, batchLastIndex));
                    batchSqlSession.commit();
                    batchSqlSession.clearCache();
                    log.info("index : " + index + ", batchLastIndex : " + batchLastIndex + ", 共插入項目信息 : " + (batchLastIndex + 1) + "條");
                    index = batchLastIndex; // 設置下一批下標
                    batchLastIndex = index + (batchCount - 1);
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            batchSqlSession.rollback();
        } finally {
            batchSqlSession.close();
        }
    }

測試通過，執(zhí)行速度很快凉当，上線后運行平穩(wěn)枣申。