mybatis XML 中 mysql 和orcale 的批量插入，使用队列多线程进行批量插入，迁移数据

 

mysql 的批量修改 需要在连接地址上加上 allowMultiQueries=true

spring.datasource.druid.master.url=jdbc:mysql://ip:port/dev_rcs?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&verifyServerCertificate=true&useSSL=false&allowMultiQueries=true

mysql批量新增

 
 <!--批量修改 审核状态-->
    <update id="updateRcsTaskBatch" parameterType="java.util.List">
        <foreach close="" collection="list" index="index" item="item" open="" separator=";">
            update t_rcs_task
            <trim prefix="SET" suffixOverrides=",">
                <if test="item.status != null ">STATUS = #{item.status},</if>
                <if test="item.auditId != null ">AUDIT_ID = #{item.auditId},</if>
                <if test="item.auditTime != null ">AUDIT_TIME = #{item.auditTime},</if>
                <if test="item.explanation != null  and item.explanation != ''">EXPLANATION = #{item.explanation},</if>
                <if test="item.reviewer != null  and item.reviewer != ''">REVIEWER = #{item.reviewer},</if>
            </trim>
            where TASK_ID = #{item.taskId} and  STATUS=0
        </foreach>
    </update>


  <!--批量插入-->
    <insert id="insertRcsTopoLogicBatch" parameterType="java.util.List">
        insert into t_rcs_topo_logic
        (BUTTON_ID, TOPO_ID, BELONG_MSG_ID, RELATION_MSG_ID, CREATE_TIME)
        VALUES
        <foreach collection ="list" item="item" index= "index" separator =",">
            (
            #{item.buttonId},
            #{item.topoId},
            #{item.belongMsgId},
            #{item.relationMsgId},
            now()
            )
        </foreach>
    </insert>

oracle 批量新增

 

<!--批量插入-->
	<insert id="insertBatch" parameterType="java.util.List">
		insert into t_kpi_zlsj(kpi_time,kpi_id,kpi_code,kpi_area,area_code,kpi_cycle,kpi_section,kpi_value,kpi_permanent,kpi_nopermanent,kpi_nopertotal,kpi_total,kpi_operratio,kpi_source,kpi_version,create_time,kpi_gyrs)
		<foreach close=")" collection="list" item="item" index="index" open="(" separator="union all">
			select
			#{item.kpiTime,jdbcType=DATE},#{item.kpiId,jdbcType=INTEGER},#{item.kpiCode,jdbcType=VARCHAR},#{item.kpiArea,jdbcType=INTEGER},#{item.areaCode,jdbcType=VARCHAR},#{item.kpiCycle,jdbcType=INTEGER},#{item.kpiSection,jdbcType=INTEGER},#{item.kpiValue,jdbcType=VARCHAR},#{item.kpiPermanent,jdbcType=VARCHAR},#{item.kpiNopermanent,jdbcType=VARCHAR},#{item.kpiNopertotal,jdbcType=VARCHAR},#{item.kpiTotal,jdbcType=VARCHAR},#{item.kpiOperratio,jdbcType=VARCHAR},#{item.kpiSource,jdbcType=VARCHAR},#{item.kpiVersion,jdbcType=VARCHAR},sysdate,#{item.kpiGyrs,jdbcType=VARCHAR}
			from dual
		</foreach>
	</insert>

迁移数据使用队列多线程（线程池）读取插入   oracle 迁移mysql 数据  在博客中有配置自行查看

   //需要插入mysql的数据队列
    public static BlockingQueue<RcsTqybSub> insertMysqlQueue = new LinkedBlockingQueue<>(10000);

    private static ExecutorService executorService;//声明线程

   @Autowired
private SqlSessionTemplate oracleSqlSessionTemplate;


//项目启动时初始化线程
 @PostConstruct
    public void init(){
        
      
          //初始化线程池执行
        executorService= Executors.newFixedThreadPool(10);
        for(int i=0;i<10;i++){
            //处理请求
             executorService.submit(new thread());
        }

        //初始化时候取一次数据
        getUserMobile();
    }





     //查询数据放入队列
    public void getUserMobile(final String area){
        try {
           
                oracleSqlSessionTemplate.select("cn.witsky.quota.dao.oracledao.UserMobileDzMapper.selectAll",
                        area, new ResultHandler() { //回调处理逻辑，汇总批量数据写入响应流
                            long i=0;
                            @Override
                            public void handleResult(ResultContext resultContext) {
                                if (resultContext.getResultObject() != null) {
                                    UserMobileDz  ud=(UserMobileDz)resultContext.getResultObject();//数据库查询出来的记录
                                    String phone =ud.getUserMobile();
                                    RcsTqybSub sub=new RcsTqybSub(1L,area,3L,1L,1L,phone,1L,4L,area,"订购关系同步",id1);
                                    RcsTqybSub sub2=new RcsTqybSub(1L,area,3L,1L,1L,phone,1L,4L,area,"订购关系同步",id2);
                                    try {
                                        insertMysqlQueue.put(sub);
                                    } catch (Exception e) {
                                        log.error("area={},insertMysqlQueue失败={}",area,e.getMessage());
                                    }
                                }
                            }
                        });
                log.info("start finish area={},delay={}",area,(System.currentTimeMillis()-currTime));
        }catch (Exception e){
           
            log.error("处理失败area={}，error msg={}",area,e);
        }
    }





//线程类

class thread extends Thread{
            @Override
            public void run() {
                log.info("---------开启批量插入mysql数据库线程");
                while(true){
                    try{
                        if(insertMysqlQueue.size()>0){
                            int tempcount = 500;
                            if (tempcount > insertMysqlQueue.size()) {
                                tempcount = insertMysqlQueue.size();
                            }
                            List<RcsTqybSub>  rcsTqybSubs=new ArrayList<>();
                            for (int i =0; i < tempcount; i++) {
                                RcsTqybSub out = insertMysqlQueue.poll();
                                if (null != out) {
                                    insertCount =insertCount+1;
                                    rcsTqybSubs.add(out);
                                }
                            }
                            try {
                                rcsTqybSubMapper.insertRcsTqybSubBatch(rcsTqybSubs);
                            } catch (Exception e) {
                                log.error("insert queue error,msg={}",e);
                            }
                            Constants.sleep(10);
                        }else{
                            Constants.sleep(50);
                        }
                    }catch (Exception e){
                        log.error("error={}",e);
                    }

                }
            }


}