java多线程向数据库写入数据

任务: 从sqlserver中将一个表A（约16W条数据）导到mysql中对应的一个表B中。

思路：分段获取A表中的数据后，用多个线程同时向B表中写入。

关键代码

//将数据库中的数据条数分段public void division(){//获取要导入的总的数据条数String sql3="SELECT  count(*)  FROM [CMD].[dbo].[mycopy1]";try {pss=cons.prepareStatement(sql3);rss=pss.executeQuery();while(rss.next()){System.out.println("总记录条数:"+rss.getInt(1));sum=rss.getInt(1);}//每30000条记录作为一个分割点if(sum>=30000){n=sum/30000;residue=sum%30000;}else{residue=sum;}System.out.println(n+"  "+residue);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}

线程类

public MyThread(int start,int end) {this.end=end;     this.start=start;System.out.println("处理掉余数");  try {        System.out.println("--------"+Thread.currentThread().getName()+"------------");Class.forName(SQLSERVERDRIVER);System.out.println("加载sqlserver驱动...");cons = DriverManager.getConnection(CONTENTS,UNS,UPS);stas = cons.createStatement();System.out.println("连接SQLServer数据库成功！！");System.out.println("加载mysql驱动.....");Class.forName(MYSQLDRIVER);con = DriverManager.getConnection(CONTENT,UN,UP);sta = con.createStatement();// 关闭事务自动提交con.setAutoCommit(false);System.out.println("连接mysql数据库成功！！");} catch (Exception e) {e.printStackTrace();}// TODO Auto-generated constructor stub}public ArrayList<Member> getAll(){Member member;String sql1="select * from (select row_number() over (order by pmcode) as rowNum,*" +" from [CMD].[dbo].[mycopy1]) as t where rowNum between "+start+" and "+end;try {System.out.println("正在获取数据...");allmembers=new ArrayList();rss=stas.executeQuery(sql1);while(rss.next()){member=new Member();member.setAddress1(rss.getString("address1"));member.setBnpoints(rss.getString("bnpoints"));member.setDbno(rss.getString("dbno"));member.setExpiry(rss.getString("expiry"));member.setHispoints(rss.getString("hispoints"));member.setKypoints(rss.getString("kypoints"));member.setLevels(rss.getString("levels"));member.setNames(rss.getString("names"));member.setPmcode(rss.getString("pmcode"));member.setRemark(rss.getString("remark"));member.setSex(rss.getString("sex"));member.setTelephone(rss.getString("telephone"));member.setWxno(rss.getString("wxno"));member.setPmdate(rss.getString("pmdate"));allmembers.add(member);//System.out.println(member.getNames());}System.out.println("成功获取sqlserver数据库数据！");return allmembers;} catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.println("获取sqlserver数据库数据发送异常！");e.printStackTrace();}try {rss.close();stas.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return null;}public void inputAll(ArrayList<Member> allmembers){System.out.println("开始向mysql中写入");String sql2="insert into test.mycopy2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";try {ps=con.prepareStatement(sql2);System.out.println("-------------------------等待写入数据条数: "+allmembers.size());for(int i=0;i<allmembers.size();i++){ps.setString(1, allmembers.get(i).getPmcode());ps.setString(2, allmembers.get(i).getNames());//System.out.println(allmembers.get(i).getNames());ps.setString(3, allmembers.get(i).getSex());ps.setString(4, allmembers.get(i).getTelephone());ps.setString(5, allmembers.get(i).getAddress1());ps.setString(6, allmembers.get(i).getPmdate());ps.setString(7, allmembers.get(i).getExpiry());ps.setString(8, allmembers.get(i).getLevels());ps.setString(9, allmembers.get(i).getDbno());ps.setString(10, allmembers.get(i).getHispoints());ps.setString(11, allmembers.get(i).getBnpoints());ps.setString(12, allmembers.get(i).getKypoints());ps.setString(13, allmembers.get(i).getWxno());ps.setString(14, allmembers.get(i).getRemark());//插入命令列表//ps.addBatch();ps.executeUpdate();}//ps.executeBatch();con.commit();ps.close();con.close();this.flag=false;System.out.println(Thread.currentThread().getName()+"--->OK");} catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.println("向mysql中更新数据时发生异常！");e.printStackTrace();}}@Overridepublic void run() {// TODO Auto-generated method stubwhile(true&&flag){this.inputAll(getAll());}}

测试类：

public class Test1 {DbManager dm=null;MyThread my1=null;public Test1(){dm=new DbManager();System.out.println(dm.n+"----"+dm.residue); if(dm.n<1){//数据条数小于30000单线程处理my1=new MyThread(1,dm.sum);my1.start=1;my1.end=dm.residue;Thread t1=new Thread(my1);    t1.start();}else{//大于30000时    //起n个线程 每个处理30000条数据for (int i = 1; i <=dm.n; i++) {new Thread(new MyThread(i)).start();try {Thread.sleep(1);} catch (InterruptedException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//处理掉余数my1=new MyThread(dm.n*30000+1,dm.sum);Thread t1=new Thread(my1);    t1.start();} }public static void main(String[] args) {//new Test1();//迁移完数据，自动关机 try {Runtime.getRuntime().exec("cmd  /c Shutdown -t 10");} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}  }

经过多次测试：从sqlserver中读取16w条数据并写入mysql，耗时15min左右。

开始会报错： java heap space 

解决方案：（myeclipse）window->Preferences->Java->Installed JREs，选择当前的JRE，然后edit它；在新窗口里设置Default VM Arguments为 -Xms512M -Xmx512M即可
：