public static void moveData(String tableName){ System.out.println("----------数据迁移----------start----------"); Connection db2Conn = null; Connection orclConn = null; int total=0; //待插入记录总数 int success=0; //成功插入记录总数 int failure=0; //插入失败记录总数 Long startTime=System.currentTimeMillis(); //开始时间 //某些表主键外键都是自己的字段,insert时没有按顺序会出错,此时先跳过并保存sql语句,等其他数据插入完毕再手工插入 List<String> faildSql = new ArrayList<String>(); try{ Class.forName("com.ibm.db2.jcc.DB2Driver"); Class.forName("oracle.jdbc.driver.OracleDriver"); db2Conn = DriverManager.getConnection("jdbc:db2://192.168.0.24:50000/evoice","apadmin","apadmin"); orclConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","apadmin","apadmin"); String selectSql = "select * from "+tableName; PreparedStatement selectPs = db2Conn.prepareStatement(selectSql); ResultSet db2Data = selectPs.executeQuery(); /**获取待插入记录总数*/ String totalSql = "select count(*) from "+tableName; PreparedStatement totalPs = db2Conn.prepareStatement(totalSql); ResultSet rsTotal = totalPs.executeQuery(); rsTotal.next(); total = rsTotal.getInt(1); /** 先清空数据再插入 */ String deleteSql = "delete "+tableName; PreparedStatement orclDelPs = orclConn.prepareStatement(deleteSql); orclDelPs.executeUpdate(); ResultSetMetaData rsmd = db2Data.getMetaData(); int columnCount = rsmd.getColumnCount(); while(db2Data.next()){ String str=""; for(int i=1;i<=columnCount;i++){ String columnType = rsmd.getColumnClassName(i); if(!columnType.equals("java.lang.String")&&!columnType.equals("java.sql.Clob")){ if(columnType.equals("java.sql.Timestamp")){ str+="to_timestamp('"+db2Data.getObject(i)+"','yyyy-mm-dd hh24:mi:ss.ff'),"; }else{ str+=db2Data.getObject(i)+","; } }else{ Object data = db2Data.getObject(i); if(data==null||StringUtils.isEmpty(data.toString())){ if(rsmd.isNullable(i)==ResultSetMetaData.columnNoNulls){ str+="' ',"; //值为null且不允许为空时加空格 }else{ str+="'',"; //值为null且允许为空时直接插入 } }else{ str+="'"+data+"',"; } } } str=str.substring(0,str.length()-1); String insertSql = "insert into "+tableName+" values("+str+")"; PreparedStatement insertPs = orclConn.prepareStatement(insertSql); try{ insertPs.executeUpdate(); }catch (Exception e) { faildSql.add(insertSql); failure++; e.printStackTrace(); continue; }finally{ insertPs.close(); } success++; if(success%1000==0){ Long spendTime = (System.currentTimeMillis()-startTime)/1000; System.out.println("成功插入记录["+success+"]条,剩余["+(total-success-failure)+"]条,已用时["+spendTime+"]秒"); } } System.out.println("----------数据迁移----------end----------"); System.out.println("共插入记录["+success+"]条,失败["+failure+"]条"); }catch (Exception e) { e.printStackTrace(); }finally{ try { orclConn.close(); db2Conn.close(); for(int i=0;i<faildSql.size();i++){ System.out.println(faildSql.get(i)+";"); if(i==faildSql.size()-1){ System.out.println("commit;"); } } } catch (SQLException e) { e.printStackTrace(); } } }