springboot+mybatis+oracle 大量数据分批批量导入

配置mapper层

<insert id="insertApplyForDatas" parameterType="com.patent.patentManagementSystem.model.Patent">
        INSERT ALL
        <foreach collection="list" index="" item="patent" separator=" ">
            INTO ODS_PATENT_PATENT(SQ_ID,SQ_DATE,ZFLH,SQR,POSTCODE,ADDRESS,AJRK_DATE,ZL_CLASS,SQR_CLASS,PROVICE,CITY,SQFSMC,
            COUNTY,STATUS)
            values(
            #{patent.SQ_ID,jdbcType=CHAR},#{patent.SQ_DATE,jdbcType=DATE},#{patent.ZFLH,jdbcType=CHAR},#{patent.SQR,jdbcType=VARCHAR},
            #{patent.POSTCODE,jdbcType=VARCHAR},#{patent.ADDRESS,jdbcType=VARCHAR},#{patent.AJRK_DATE,jdbcType=DATE},#{patent.ZL_CLASS,jdbcType=CHAR},
            #{patent.SQR_CLASS,jdbcType=CHAR},#{patent.PROVICE,jdbcType=VARCHAR},#{patent.CITY,jdbcType=VARCHAR},#{patent.SQFSMC,jdbcType=VARCHAR},
            #{patent.COUNTY,jdbcType=VARCHAR},#{patent.STATUS,jdbcType=CHAR})
        </foreach>
        SELECT * from dual
    </insert>

或者还有一种写法

<insert id="insertApplyForDatas" parameterType="com.patent.patentManagementSystem.model.Patent">
        INSERT INTO ODS_PATENT_PATENT(SQ_ID,SQ_DATE,ZFLH,SQR,POSTCODE,ADDRESS,AJRK_DATE,ZL_CLASS,SQR_CLASS,PROVICE,CITY,SQFSMC,
        COUNTY,STATUS)
        (
        <foreach collection="list" index="" item="patent" separator="union all">
        select
        #{patent.SQ_ID,jdbcType=CHAR},#{patent.SQ_DATE,jdbcType=DATE},#{patent.ZFLH,jdbcType=CHAR},#{patent.SQR,jdbcType=VARCHAR},
        #{patent.POSTCODE,jdbcType=VARCHAR},#{patent.ADDRESS,jdbcType=VARCHAR},#{patent.AJRK_DATE,jdbcType=DATE},#{patent.ZL_CLASS,jdbcType=CHAR},
        #{patent.SQR_CLASS,jdbcType=CHAR},#{patent.PROVICE,jdbcType=VARCHAR},#{patent.CITY,jdbcType=VARCHAR},#{patent.SQFSMC,jdbcType=VARCHAR},
        #{patent.COUNTY,jdbcType=VARCHAR},#{patent.STATUS,jdbcType=CHAR}
            from dual
        </foreach>
        )
    </insert>

配置 Dao层 新建一个Impl类用于实现dao，

@Repository注册为bean

package com.patent.patentManagementSystem.dao.Impl;

import com.patent.patentManagementSystem.dao.PatentDao;
import com.patent.patentManagementSystem.model.AdvancedSearchDemo;
import com.patent.patentManagementSystem.model.Patent;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class PatentDaoImpl implements PatentDao {
    //从spring注入原有的sqlSessionTemplate

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;
    @Override
    public Patent getPatentBySQ_ID(String id) {
        return null;
    }

    @Override
    public List<Patent> getPatentByName(String name) {
        return null;
    }

    @Override
    public List<Patent> getPatentBySQR(String sqr) {
        return null;
    }

    @Override
    public List<Patent> getPatent(AdvancedSearchDemo advancedSearchDemo) {
        return null;
    }

    @Override
    public List<Patent> getPatentsByPatents(List<Patent> patentList) {
        return null;
    }

    @Override
    public void insertApplyForData(Patent patent) {

    }

//获取sqlsession


    @Override
    public int insertApplyForDatas(List<Patent> patentList) {
        SqlSession session = null;
        //通过新的session获取mapper
        int result=0;
        try {
            Date date1=new Date();
            session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            int a = 100;//每次提交100条
            int loop = (int) Math.ceil(patentList.size() / (double) a);

            List<Patent> tempList = new ArrayList<Patent>(a);
            int start, stop=0;
            for (int i = 0; i < loop; i++) {
                tempList.clear();
                start = i * a;
                stop = Math.min(i * a + a - 1, patentList.size() - 1);
                System.out.println("range:" + start + " - " + stop);
                for (int j = start; j <= stop; j++) {
                    tempList.add(patentList.get(j));
                }
                session.insert("insertApplyForDatas", tempList);
                session.clearCache();
                System.out.println("已经插入" + (stop + 1) + " 条");
            }
            session.commit();
            result=stop;
            Date date2=new Date();
            long time=date2.getTime()-date1.getTime();
            System.out.println("共耗时"+time+"毫秒");
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
            result=0;
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return result;
    }

    @Override
    public void updateApplyForData(Patent patent) {

    }

    @Override
    public void updateApplyForDatas(List<Patent> patentList) {

    }

    @Override
    public void insertAuthorizationData(Patent patent) {

    }

    @Override
    public void insertAvailabilityData(List<String> list) {

    }
}

在其中

session.insert("insertApplyForDatas", tempList);

insertApplyForDatas是PatentMapper.xml中的方法id

在Service层实例化Impl

必须用@Autowired实例化

@Autowired
    PatentDaoImpl patentsDao;

，否则@Autowired   private SqlSessionTemplate sqlSessionTemplate; 将不能自动注入。

使用 patentsDao 进行数据操作

 public void importApplyForDatas(List<Patent> patents){
        patentsDao.insertApplyForDatas(patents);
    }