使用disruptor 将5百多万数据从mysql导入到oracle

最新推荐文章于 2023-04-14 23:11:29 发布

weixin_30607659

最新推荐文章于 2023-04-14 23:11:29 发布

阅读量282

点赞数

文章标签：数据库 java

原文链接：http://www.cnblogs.com/suyuji/p/3640666.html

版权

知道disruptor快1年多了，一直没有用武之地。这次正好要迁移数据。表结构由于完全不一样，导数据的时候还需要进行一些计算。果断用disruptor试试

  1 public class TransferProcessor implements Runnable,InitializingBean {
  2 
  3     private JdbcTemplate mysqlJdbcTemplate;
  4 
  5     private PlatformTransactionManager txManager;
  6 
  7     private JdbcTemplate oracleJdbcTemplate;
  8 
  9     public PlatformTransactionManager getTxManager() {
 10         return txManager;
 11     }
 12 
 13     public void setTxManager(PlatformTransactionManager txManager) {
 14         this.txManager = txManager;
 15     }
 16 
 17     public JdbcTemplate getMysqlJdbcTemplate() {
 18         return mysqlJdbcTemplate;
 19     }
 20 
 21     public void setMysqlJdbcTemplate(JdbcTemplate mysqlJdbcTemplate) {
 22         this.mysqlJdbcTemplate = mysqlJdbcTemplate;
 23     }
 24 
 25     public JdbcTemplate getOracleJdbcTemplate() {
 26         return oracleJdbcTemplate;
 27     }
 28 
 29     public void setOracleJdbcTemplate(JdbcTemplate oracleJdbcTemplate) {
 30         this.oracleJdbcTemplate = oracleJdbcTemplate;
 31     }
 32 
 33     private final ExecutorService EXECUTOR = Executors.newFixedThreadPool(2);
 34 
 35     private Integer offset = 0;
 36 
 37     private Integer lastId = 0;
 38 
 39     private Integer limit = 10000;
 40 
 41     private final WorkerValueEventHandler[] handlers = new WorkerValueEventHandler[2];
 42 
 43     private RingBuffer<ValueEvent> ringBuffer = RingBuffer.createSingleProducer(ValueEvent.EVENT_FACTORY, 32);
 44     private WorkerPool<ValueEvent> workerPool = null;
 45 
 46     private String sql = "select t.uid as uid,t.tel,t.email,t.password,t.username,t3.extcredits2 as credits,1 as status,t.regdate,t2.realname,t2.gender,t2.birthyear,t2.birthmonth,t2.birthday,t2.constellation,t2.birthcity ,t2.residecity,t2.bloodtype,t2.qq,t2.msn,t2.taobao,t2.bio,t2.occupation,t.salt from sz_ucenter_members t left join sz_common_member_profile t2 on t.uid=t2.uid left join sz_common_member_count t3 on t.uid=t3.uid";
 47 
 48     public void run() {
 49         RingBuffer<ValueEvent> ringBuffer = workerPool.start(EXECUTOR);
 50         ringBuffer.addGatingSequences(workerPool.getWorkerSequences());
 51         long t1 = System.currentTimeMillis();
 52         List<Result> list = query(sql + "  order by uid limit ?,?", offset, limit);
 53         long t2 = System.currentTimeMillis();
 54         Result result = list.get(list.size() - 1);
 55         lastId = result.getUid();
 56         Integer id = 1;
 57         Integer total = list.size();
 58         System.out.println("查询第" + id + "批数据,耗时" + (t2 - t1) + "ms");
 59         while (list != null && list.size() > 0) {
 60             Result r = list.get(list.size() - 1);
 61             lastId = r.getUid();
 62             long sequence = ringBuffer.next();
 63             ValueEvent event = ringBuffer.claimAndGetPreallocated(sequence);
 64             event.setValue(list);
 65             event.setId(id);
 66             ringBuffer.publish(sequence);
 67             id++;
 68             long t3 = System.currentTimeMillis();
 69             list = query(sql + " where t.uid>?  order by uid limit ?", lastId, limit);
 70             long t4 = System.currentTimeMillis();
 71             total += list.size();
 72             System.out.println("查询第" + id + "批数据,耗时" + (t4 - t3) + "ms,RingBuffer剩余空间:" + ringBuffer.remainingCapacity());
 73         }
 74         while (true) {
 75             try {
 76                 Thread.sleep(500);
 77             } catch (InterruptedException e) {
 78                 e.printStackTrace();
 79             }
 80             System.out.println("RingBuffer中剩余数据量:" + (32 - ringBuffer.remainingCapacity()));
 81             if (ringBuffer.remainingCapacity() == 32) {
 82                 System.out.println("数据迁移结束！总共" + total + "条记录");
 83             System.exit(0);
 84             }
 85         }
 86     }
 87 
 88 
 89     private List<Result> query(String sql, Object p1, Object p2) {
 90         List<Result> list = mysqlJdbcTemplate.query(sql, new Object[] { p1, p2 }, new RowMapper<Result>() {
 91             @Override
 92             public Result mapRow(ResultSet rs, int rowNum) throws SQLException {
 93                 Result r = new Result();
 94                 r.setUid(rs.getInt("uid"));
 95                 String tel = rs.getString("tel");
 96                 r.setTel(StringUtils.isEmpty(tel) ? null : tel.length() > 11 ? tel.substring(0, 11) : tel);
 97                 r.setEmail(rs.getString("email"));
 98                 r.setPassword(rs.getString("password"));
 99                 r.setUsername(StringUtils.isEmpty(rs.getString("username")) ? " " : rs.getString("username"));
100                 r.setCredits(rs.getInt("credits"));
101                 r.setStatus(rs.getInt("status"));
102                 r.setRegdate(rs.getString("regdate"));
103                 r.setRealname(rs.getString("realname"));
104                 r.setGender(rs.getInt("gender"));
105                 r.setBirthyear(rs.getInt("birthyear"));
106                 r.setBirthmonth(rs.getInt("birthmonth"));
107                 r.setBirthday(rs.getInt("birthday"));
108                 r.setConstellation(rs.getString("constellation"));
109                 r.setBirthcity(rs.getString("birthcity"));
110                 r.setResidecity(rs.getString("residecity"));
111                 r.setBloodtype(rs.getString("bloodtype"));
112                 r.setQq(rs.getString("qq"));
113                 r.setMsn(rs.getString("msn"));
114                 r.setTaobao(rs.getString("taobao"));
115                 r.setBio(rs.getString("bio"));
116                 r.setOccupation(rs.getString("occupation"));
117                 r.setSalt(rs.getString("salt"));
118                 return r;
119             }
120         });
121         return list;
122     }
123 
124     private List<Address> queryAddress() {
125         List<Address> addresss = oracleJdbcTemplate.query("SELECT t.NAME,t.ID from ADDRESS t where t.LVL=2", new RowMapper<Address>() {
126             @Override
127             public Address mapRow(ResultSet rs, int rowNum) throws SQLException {
128                 Address address = new Address();
129                 address.setId(rs.getInt("ID"));
130                 address.setName(rs.getString("NAME"));
131                 return address;
132             }
133 
134         });
135         return addresss;
136     }
137 
138     @Override
139     public void afterPropertiesSet() throws Exception {
140         List<Address> addresss = queryAddress();
141         for (int i = 0; i < 2; i++) {
142             handlers[i] = new WorkerValueEventHandler(oracleJdbcTemplate, addresss);
143             handlers[i].setTxManager(txManager);
144         }
145         workerPool = new WorkerPool<ValueEvent>(ringBuffer, ringBuffer.newBarrier(), new FatalExceptionHandler(), handlers);
146     }
147 
148 }

  1 public class WorkerValueEventHandler implements WorkHandler<ValueEvent> {
  2 
  3     private JdbcTemplate jdbcTemplate;
  4 
  5     private List<Address> address;
  6 
  7     private PlatformTransactionManager txManager;
  8 
  9     public PlatformTransactionManager getTxManager() {
 10         return txManager;
 11     }
 12 
 13     public void setTxManager(PlatformTransactionManager txManager) {
 14         this.txManager = txManager;
 15     }
 16 
 17     private final String SQL = "insert into ACCOUNT(ID,PHONE,NICKNAME,EMAIL,PASSWORD,CREATETIME,UPDATETIME,CREDITS,CP_ID,BIRTHDAY,SEX,BLOOD,WORK,INTRODUCTION,CONSTELLATION,QQ,MSN,ALWW,STATE,ADDRESS_ID,HOMETOWN_ID,VERSION,REALNAME,SALT) values(?,?,?,?,?,?,?,?,1,?,?,?,?,?,?,?,?,?,?,?,?,0,?,?)";
 18 
 19     public WorkerValueEventHandler(JdbcTemplate jdbcTemplate, List<Address> address) {
 20         this.jdbcTemplate = jdbcTemplate;
 21         this.address = address;
 22     }
 23 
 24     @Override
 25     public void onEvent(ValueEvent event) throws InterruptedException {
 26         final List<Result> value = event.getValue();
 27         DefaultTransactionDefinition def = new DefaultTransactionDefinition();
 28         def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
 29         def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
 30         TransactionStatus status = txManager.getTransaction(def);
 31         try {
 32             int[] num = jdbcTemplate.batchUpdate(SQL, new BatchPreparedStatementSetter() {
 33 
 34                 @Override
 35                 public void setValues(PreparedStatement ps, int i) throws SQLException {
 36                     Result result = value.get(i);
 37                     ps.setLong(1, result.getUid());
 38                     String tel = result.getTel();
 39                     ps.setString(2, tel);
 40                     ps.setString(3, result.getUsername());
 41                     ps.setString(4, result.getEmail());
 42                     if (tel != null && tel.startsWith("111")) {//马甲帐号
 43                         ps.setString(5, "4b213b65fa4f12d5416354d3df6e5089");
 44                         ps.setString(22, "654cba");
 45                     } else {
 46                         ps.setString(5, result.getPassword());
 47                         ps.setString(22, result.getSalt());
 48                     }
 49                     Timestamp time = getCreateTime(result.getRegdate());
 50                     ps.setTimestamp(6, time);
 51                     ps.setTimestamp(7, time);
 52                     ps.setInt(8, result.getCredits());
 53                     Date birthDate = getBirthDate(result.getBirthyear(), result.getBirthmonth(), result.getBirthday());
 54                     ps.setDate(9, birthDate == null ? null : new java.sql.Date(birthDate.getTime()));
 55                     Integer sex = result.getGender();
 56                     ps.setInt(10, sex == 0 ? -1 : sex == 2 ? 0 : 1);
 57                     ps.setString(11, result.getBloodtype());
 58                     ps.setString(12, result.getOccupation());
 59                     ps.setString(13, result.getBio());
 60                     ps.setString(14, result.getConstellation());
 61                     ps.setString(15, result.getQq());
 62                     ps.setString(16, result.getMsn());
 63                     ps.setString(17, result.getTaobao());
 64                     ps.setInt(18, 1);
 65                     ps.setInt(19, getAddressId(result.getResidecity()));
 66                     ps.setInt(20, getAddressId(result.getBirthcity()));
 67                     ps.setString(21, result.getRealname());
 68                     if (i % 2000 == 0) {
 69                         ps.executeBatch();
 70                     }
 71 
 72                 }
 73 
 74                 @Override
 75                 public int getBatchSize() {
 76                     return value.size();
 77                 }
 78 
 79             });
 80             txManager.commit(status);
 81             System.out.println("线程:" + Thread.currentThread().getName() + ">>>结束处理序列号为" + event.getId() + "的数据");
 82         } catch (Throwable e) {
 83             txManager.rollback(status);
 84             System.out.println(e);
 85             System.exit(0);
 86         }
 87 
 88 
 89     }
 90 
 91     private Date getBirthDate(Integer year, Integer month, Integer day) {
 92         if (year == 0 || month == 0 || day == 0) {
 93             return null;
 94         }
 95         Calendar c = Calendar.getInstance();
 96         c.set(Calendar.YEAR, year);
 97         c.set(Calendar.MONTH, month - 1);
 98         c.set(Calendar.DATE, day);
 99         return c.getTime();
100     }
101 
102     private Timestamp getCreateTime(String regDate) {
103         Long time = Long.parseLong(regDate + "000");
104         return new Timestamp(time);
105     }
106 
107     private Integer getAddressId(String name) {
108         if (StringUtils.isEmpty(name)) {
109             return -1;
110         }
111         Iterator<Address> it = address.iterator();
112         Integer id = -1;
113         while (it.hasNext()) {
114             Address address = it.next();
115             String n = address.getName();
116             if (n.equals(name)) {
117                 id = address.getId();
118                 break;
119             }
120         }
121         return id;
122     }

 1 public class ValueEvent {
 2 
 3     private Integer id;
 4 
 5     public Integer getId() {
 6         return id;
 7     }
 8 
 9     public void setId(Integer id) {
10         this.id = id;
11     }
12 
13     private List<Result> value;
14 
15     public List<Result> getValue() {
16         return value;
17     }
18 
19     public void setValue(List<Result> value) {
20         this.value = value;
21     }
22 
23     public final static EventFactory<ValueEvent> EVENT_FACTORY = new EventFactory<ValueEvent>() {
24         public ValueEvent newInstance() {
25             return new ValueEvent();
26         }
27     };
28 }

最终速度还是比较快的，5min完成。1个线程读，2个线程写。双核cpu。

接下来又从7百万条数据的csv文件中导入到库中，3min就完成。

  1 public class TransferProcessor implements Runnable, InitializingBean {
  2 
  3 
  4     private PlatformTransactionManager txManager;
  5 
  6     private JdbcTemplate oracleJdbcTemplate;
  7 
  8     public PlatformTransactionManager getTxManager() {
  9         return txManager;
 10     }
 11 
 12     public void setTxManager(PlatformTransactionManager txManager) {
 13         this.txManager = txManager;
 14     }
 15 
 16     public JdbcTemplate getOracleJdbcTemplate() {
 17         return oracleJdbcTemplate;
 18     }
 19 
 20     public void setOracleJdbcTemplate(JdbcTemplate oracleJdbcTemplate) {
 21         this.oracleJdbcTemplate = oracleJdbcTemplate;
 22     }
 23 
 24     private final ExecutorService EXECUTOR = Executors.newFixedThreadPool(2);
 25 
 26     private final WorkerValueEventHandler[] handlers = new WorkerValueEventHandler[2];
 27 
 28     private RingBuffer<ValueEvent> ringBuffer = RingBuffer.createSingleProducer(ValueEvent.EVENT_FACTORY, 32);
 29     private WorkerPool<ValueEvent> workerPool = null;
 30 
 31     public void run() {
 32         Integer total = 0;
 33         RingBuffer<ValueEvent> ringBuffer = workerPool.start(EXECUTOR);
 34         ringBuffer.addGatingSequences(workerPool.getWorkerSequences());
 35         Integer id = 1;
 36         BufferedReader reader = null;
 37         try {
 38             List<String> values = new ArrayList<String>();
 39             reader = new BufferedReader(new FileReader("d:\\black.csv"));
 40             reader.readLine();
 41             String line = null;
 42             while ((line = reader.readLine()) != null) {
 43                 total += 1;
 44                 line = line.replace("\"", "");
 45                 if (line.length() > 11) {
 46                     line = line.substring(line.length() - 11);
 47                 }
 48                     values.add(line);
 49 
 50                 if (values.size() % 10000 == 0) {
 51                     publishEvent(ringBuffer, id, new ArrayList<String>(values));
 52                     values = new ArrayList<String>();
 53                     id++;
 54                 }
 55             }
 56             if (values.size() != 0) {
 57                 publishEvent(ringBuffer, id, new ArrayList<String>(values));
 58             }
 59         } catch (IOException e) {
 60             e.printStackTrace();
 61             System.exit(0);
 62         } finally {
 63             try {
 64                 if (reader != null)
 65                     reader.close();
 66             } catch (IOException e) {
 67                 e.printStackTrace();
 68             }
 69         }
 70         while (true) {
 71             try {
 72                 Thread.sleep(500);
 73             } catch (InterruptedException e) {
 74                 e.printStackTrace();
 75             }
 76             System.out.println("RingBuffer中剩余数据量:" + (32 - ringBuffer.remainingCapacity()));
 77             if (ringBuffer.remainingCapacity() == 32) {
 78                 System.out.println("数据迁移结束！总共" + total + "条记录");
 79                 System.exit(0);
 80             }
 81         }
 82 
 83     }
 84 
 85     protected void publishEvent(RingBuffer<ValueEvent> ringBuffer, Integer id, List<String> values) {
 86         long sequence = ringBuffer.next();
 87         ValueEvent event = ringBuffer.claimAndGetPreallocated(sequence);
 88         event.setId(id);
 89         event.setValues(values);
 90         ringBuffer.publish(sequence);
 91     }
 92 
 93     @Override
 94     public void afterPropertiesSet() throws Exception {
 95         for (int i = 0; i < 2; i++) {
 96             handlers[i] = new WorkerValueEventHandler(oracleJdbcTemplate);
 97             handlers[i].setTxManager(txManager);
 98         }
 99         workerPool = new WorkerPool<ValueEvent>(ringBuffer, ringBuffer.newBarrier(), new FatalExceptionHandler(), handlers);
100     }
101 
102 }

转载于:https://www.cnblogs.com/suyuji/p/3640666.html

weixin_30607659

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
使用disruptor 将5百多万数据从mysql导入到oracle

知道disruptor快1年多了，一直没有用武之地。这次正好要迁移数据。表结构由于完全不一样，导数据的时候还需要进行一些计算。果断用disruptor试试 1 public class TransferProcessor implements Runnable,InitializingBean { 2 3 private JdbcTemplate m...
复制链接

扫一扫