JAVA实现跨数据库服务器数据导出导入功能

package db.connect; import java.beans.Statement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Date; import java.text.SimpleDateFormat; import java.util.Calendar; public class DBManager { public static String htmEncode(String target) { StringBuffer stringbuffer = new StringBuffer(); int j = target.length(); for (int i = 0; i < j; i++) { char c = target.charAt(i); switch (c) { case 60: stringbuffer.append("&lt;"); break; case 62: stringbuffer.append("&gt;"); break; case 38: stringbuffer.append("&amp;"); break; case 34: stringbuffer.append("&quot;"); break; case 169: stringbuffer.append("&copy;"); break; case 174: stringbuffer.append("&reg;"); break; case 165: stringbuffer.append("&yen;"); break; case 8364: stringbuffer.append("&euro;"); break; case 8482: stringbuffer.append("&#153;"); break; case 13: if (i < j - 1 && target.charAt(i + 1) == 10) { stringbuffer.append("<br>"); i++; } break; case 32: if (i < j - 1 && target.charAt(i + 1) == ' ') { stringbuffer.append(" &nbsp;"); i++; break; } default: stringbuffer.append(c); break; } } return new String(stringbuffer.toString()); } public static String getNormalMysqlStr(String content) { if (content == null) return ""; String html = content; html = content.replace("\"", "\\\""); html = content.replace("\'", "\\\'"); // html=content.replace("\\%", "\\\\%"); return html; } private static String Time() { Calendar c = Calendar.getInstance(); c.setTimeInMillis(System.currentTimeMillis()); java.util.Date d = c.getTime(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss "); return sdf.format(d); } public static void main(String args[]) throws Exception { /** ResultSet rs = null; java.sql.Statement s1 = null; java.sql.Statement s2 = null; java.sql.Statement s3 = null; Connection cn1 = null; Connection cn2 = null; long startTime = 0;// 开始时间 long endTime = 0; // 结束时间 int count = 1; // 计数 int onerun = 0; // 执行的最大数 int datanum = 0; // 总条数 int num = 0; // 取整最大数据 try { startTime = System.currentTimeMillis(); cn1 = DBConnection.getCon1(); //mysql 连接 目标数据 cn2 = DBConnection.getCon2(); //sqlserver 连接 源数据 cn1.setAutoCommit(false);// 插入MYSQL数据库时使用事务批量提交 if (cn2 != null) { s1 = cn1.createStatement(); s3= cn2.createStatement(); s2 = cn2.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = s2.executeQuery("select count(*) from crawlerdatacontent where GatherFlag='1'"); if (rs.next()) {// 先查询sqlserver数据源表，获取总数据条数 System.out.print("开始执行1 ！！！"); datanum = rs.getInt(1); } System.out.println("总记录数：" + datanum + "条"); onerun = 1;// 执行的最大数 num = datanum / onerun * onerun;// 计算出总条数符合每批10000的数量是多少 s2.setMaxRows(datanum); s2.setFetchSize(onerun);// 每批执行的数据条数 rs = s2.executeQuery("select * from crawlerdatacontent where GatherFlag='1'"); while (rs.next()) { String id = CId.getUniqId();//获取自动代码生成主键 //插入第一张表maes_original String sql = "insert into maes_original_back(id,datatitle,dataurl,sourceid,createtime) values('" + id + "','" + rs.getString("AnnoTitle") + "','" + rs.getString("URL") + "','" + rs.getString("ID") + "'," + "now()" + ")"; //System.out.println(sql); s1.addBatch(sql); //插入第二张表 String sql1="insert into maes_oritext_back(id,sourceid) values('" + id + "','" + rs.getString("ID") + "')"; s1.addBatch(sql1); // +rs.getString("HTML")+"')"); if (rs.getString("HTML") != null) { String ID=rs.getString("ID"); String sql2 = "update maes_oritext_back set datahtml = '" + getNormalMysqlStr(rs.getString("HTML")) + "' where id = '" + id + "';"; String sql3 = "update CrawlerDataContent set GatherFlag='3' where ID = '" + rs.getString("ID") + "';"; String sql4 = "update CrawlerDataContent set GatherFlag='9' where ID = '" + rs.getString("ID") + "';"; System.out.println(id); s1.addBatch(sql2); s3.addBatch(sql3); System.out.println(sql3); } if (count < num) {// 10000取整后剩余的小数据量就顺序插入 int [] num1=((java.sql.Statement) s1).executeBatch(); s3.executeBatch(); System.out.println(num1.length); ((java.sql.Statement) s1).clearBatch(); cn1.commit(); //continue; } else {// 数据够批次的就按批量插入 if (count % onerun != 0) {// 10000条一批插入 ((java.sql.Statement) s1).executeBatch(); cn1.commit(); ((java.sql.Statement) s1).clearBatch(); } } count++; } } } catch (Exception e) { //count--; cn1.rollback(); // ConnectMysql.rollback(cn1); String id = CId.getUniqId(); String sql4 = "update CrawlerDataContent set GatherFlag='9' where ID = '" + rs.getString("ID") + "';"; s3.addBatch(sql4); s3.executeBatch(); e.printStackTrace(); } finally { rs.close(); s2.close(); s1.close(); s3.close(); cn2.close(); cn1.close(); } endTime = System.currentTimeMillis(); System.out.println("成功移植数据：" + (count - 1) + "条，耗时" + (endTime - startTime) / 1000 + "秒");*/ } public static void DataExceuteOuput() throws Exception{ ResultSet rs = null; java.sql.Statement s1 = null; java.sql.Statement s2 = null; java.sql.Statement s3 = null; Connection cn1 = null; Connection cn2 = null; long startTime = 0;// 开始时间 long endTime = 0; // 结束时间 int count = 1; // 计数 int onerun = 0; // 执行的最大数 int datanum = 0; // 总条数 int num = 0; // 取整最大数据 try { startTime = System.currentTimeMillis(); cn1 = DBConnection.getCon1(); //mysql 连接 目标数据 cn2 = DBConnection.getCon2(); //sqlserver 连接 源数据 cn1.setAutoCommit(false);// 插入MYSQL数据库时使用事务批量提交 if (cn2 != null) { s1 = cn1.createStatement(); s3= cn2.createStatement(); s2 = cn2.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = s2.executeQuery("select count(*) from crawlerdatacontent where GatherFlag='1'"); if (rs.next()) {// 先查询sqlserver数据源表，获取总数据条数 System.out.print("开始执行1 ！！！"); datanum = rs.getInt(1); } System.out.println("总记录数：" + datanum + "条"); onerun = 1;// 执行的最大数 num = datanum / onerun * onerun;// 计算出总条数符合每批10000的数量是多少 s2.setMaxRows(datanum); s2.setFetchSize(onerun);// 每批执行的数据条数 rs = s2.executeQuery("select * from crawlerdatacontent where GatherFlag='1'"); while (rs.next()) { String id = CId.getUniqId();//获取自动代码生成主键 //插入第一张表maes_original if (rs.getString("AnnoTitle") != null) { String sql = "insert into txadm_original(id,datatitle,dataurl,sourceid,createtime) values('" + id + "','" + rs.getString("AnnoTitle") + "','" + rs.getString("URL") + "','" + rs.getString("ID")