mybatis多线程，oracle，百万数据从一个表导入另一个表 |
您所在的位置：网站首页 › oracle表数据导入另一个表 › mybatis多线程，oracle，百万数据从一个表导入另一个表 |
|
要求： 从几个表读取数据，存入另几个表，这几个表一一对应，但是字段名不相同，我的情况是被读取表包含需要的所有数据，我按天取数据，每天400多万条。 思路： 先是读取数据，分页读，一次20000条，然后批量存（一次1000），然后每次导表都是一个线程 实现： serviceImpl： //存数据 public void insert1(List xxxList,String j){ List list = new ArrayList(); for(xxx x: xxxList){ x.setId(UUID.randomUUID().toString().replaceAll("-","")); Map h = new HashMap(); h.put("id",x.getId()); ... list.add(h); } //20000个分成20组，一次提交1000个 List l = Lists.partition(list,20); if (!l.isEmpty()) { for (List maps : l) { if (maps != null && !maps.isEmpty()) { xxxMapper.insertx(maps,j); } } } } /** * 读数据，分页，一次20000个 * @param j */ public void findList1(String j){ int pageSize1 = 20000; Map a = new HashMap(); a.put("sDay","2021-08-"+j+" 00:00:00"); a.put("eDay","2021-08-"+j+" 23:59:59"); int size = xxxMapper.findCount(a); int cs = size/pageSize1+1; for (int i=1; i insert1(xxxList,j); System.out.println(j+"!!!!!!!!!!共"+cs+"次，----第"+i+"次,时间"+sj); } } } service： void findList1(String j);controller: public void ttt(){ ThreadPoolService.run(new TestService(Service,"05")); ThreadPoolService.run(new TestService(Service,"06")); ThreadPoolService.run(new TestService(Service,"07")); ThreadPoolService.run(new TestService(Service,"08")); ThreadPoolService.run(new TestService(Service,"09")); ThreadPoolService.run(new TestService(Service,"10")); ThreadPoolService.run(new TestService(Service,"11")); ThreadPoolService.run(new TestService(Service,"12")); }线程池： package common.util; import java.util.concurrent.Future; import java.util.concurrent.LinkedBlockingQueue; import java.util.concurrent.ThreadPoolExecutor; import java.util.concurrent.TimeUnit; public class ThreadPoolService { private static final ThreadPoolExecutor threadpool = new ThreadPoolExecutor(5, 5, 30L, TimeUnit.SECONDS, new LinkedBlockingQueue()); public static void run(Runnable command) { threadpool.execute(command); } public static Future submit(Runnable command) { return threadpool.submit(command); } public static int getActiveCount() { return threadpool.getActiveCount(); } public static int getQueueSize() { return threadpool.getQueue().size(); } }TestService： package zd.np.service; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import java.io.UnsupportedEncodingException; public class TestService implements Runnable{ private Logger logger = LogManager.getLogger(TestService.class); private Service service; private String j; private TestService() { } public TestService(Service service,String j) { this.service=service; this.j = j; } @Override public synchronized void run() { try { System.out.println(Thread.currentThread().getName()+"正在执行...."); //每个线程都是一次读和存数据 service.findList1(j); System.out.println(Thread.currentThread().getName()+"结束执行...."); } catch (Exception e) { logger.error(e.getMessage(),e); }finally { clear(); } } private void clear() { this.logger=null; this.Service=null; this.j=null; } public static void main(String[] args) throws UnsupportedEncodingException { } }mapper: void insertx(List list,@Param("day") String day); List selectx(Map x);mapper.xml: INSERT INTO 表名${day}(id,...) select #{item.id,jdbcType=VARCHAR},... from DUAL select xx字段名 from xxx表名 where sjgxsj ;= to_date(#{sDay},'yyyy-mm-dd hh24:mi:ss') and sjgxsj ;= to_date(#{eDay},'yyyy-mm-dd hh24:mi:ss') select count(1) from xxx表名 where sj ;= to_date(#{sDay},'yyyy-mm-dd hh24:mi:ss') and sj ;= to_date(#{eDay},'yyyy-mm-dd hh24:mi:ss') com.google.guava guava 21.0附带一个存储过程的方法，作为参考，与上面的东西无关 create or replace procedure copyTo is -- 定义游标，并将sql结果集赋值到游标中 CURSOR report is select 字段 from 读取表名; CUR report%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录 xxxid varchar(40); begin -- 打开游标 open report; LOOP FETCH report INTO CUR; EXIT WHEN report%NOTFOUND; --给xxxid赋值uuid select sys_guid() into xxxid from dual; -- 执行业务逻辑 INSERT INTO 存入表名(id,...) VALUES(xxxid,...); END LOOP; -- 关闭游标 close report; end copyTo; |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |