java - 在java中将表从一个数据库复制到另一个数据库(sybase到mysql)

Question

我已经编写了代码来连接到sybase数据库和mysql数据库，并将一个表从sybase数据库复制到mysql数据库。我的程序运行良好，我正在完成我想做的事情，但时间不够。 Sybase 在我正在复制的表中总共有大约 10000 行，复制大约需要 4 分钟。你们能建议任何可以减少复制时间的改进吗？以下是我的代码:

package jdbcexmple;
import java.sql.*;
import java.util.*;




public class Jdbcexmple {

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/alarm";
static final String JDBC_DRIVER_SECOND = "net.sourceforge.jtds.jdbc.Driver";
static final String DB_URL_SECOND = "jdbc:jtds:sybase://11.158.251.19:4100/fmdb";

static final String USER = "root";
static final String PASS = "abc";
static final String USER_SECOND = "your";
static final String PASS_SECOND = "xyz";


/**
 * @param args the command line arguments
 */
public static void main(String[] args) {
    String a;
    String b;
    String c;
    String d;
    Connection conn = null;
    Connection conn_2 = null;

    PreparedStatement stmt = null;
    try{






        Class.forName(JDBC_DRIVER);
        System.out.println("connecting to database mysql");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        System.out.println("connected to database successfully");

        Class.forName(JDBC_DRIVER_SECOND);
        System.out.println("connecting to database SYBASE");
        conn_2 = DriverManager.getConnection(DB_URL_SECOND, USER_SECOND, PASS_SECOND);
        System.out.println("connected to database successfully");


        System.out.println("creating table in given database");



        String sql = "CREATE TABLE newtable (CSN VARCHAR(255), IsCleared VARCHAR(255), ID VARCHAR(255), IP VARCHAR(255), PRIMARY KEY ( ID ))";
        stmt = conn.prepareStatement(sql);
        stmt.executeUpdate(sql);

        System.out.println("created table in database");


        Statement stmt_1= conn_2.createStatement();  
        String sql_1 = "select tbl_alm_log_2000000000.Csn, tbl_alm_log_2000000000.IsCleared, tbl_alm_log_2000000000.Id From fmdb.dbo.tbl_alm_log_2000000000 Where IsCleared = 0";

        ResultSet rs =  stmt_1.executeQuery(sql_1);



        //below loop is taking 4 mins ie copying

        while (rs.next())
        {
                a = rs.getString(1);
                b = rs.getString(2);
                c = rs.getString(3);
                d = rs.getString(4);
                sql = "INSERT INTO newtable values "+"("+"\""+a+"\","+"\""+b+"\","+"\""+c+"\","+"\""+d+"\""+")"; 
                stmt = conn.prepareStatement(sql);
                stmt.executeUpdate(sql);
                System.out.println(a+"  "+b+"  "+c+"  "+d);



        }



      }catch(SQLException se){
        se.printStackTrace();
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            if(stmt!=null)
                conn.close();
                conn_2.close();

        }catch(SQLException se){

        }
        try{
            if(conn!=null)
                conn.close();
                conn_2.close();

        }catch(SQLException se){
            se.printStackTrace();
        }
    }



}

Answer 1

使用Batch execution向mysql中插入数据，无需一条一条执行。您已经使用过 PreparedStatement。没事儿。

有两种解决方法:

解决方案 1:-

String sql = "INSERT INTO newtable values (col1, col2,col3) values (?, ?, ?)";
Connection connection = new getConnection();
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(sql);

final int batchSize = 1000;
int count = 0;

while (rs.next()){

  ps.setString(1, rs.getString(1));
  ps.setString(2, rs.getString(2));
  ps.setString(3, rs.getString(3));
  ps.addBatch();

  if(++count % batchSize == 0) {
    ps.executeBatch();
  }
}
ps.executeBatch(); // insert remaining records
connection.commit();
ps.close();
connection.close();

通过事务处理，您的插入会更快。 (connection.setAutoCommit(false); 和 connection.commit();)

http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html#addBatch--

http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#executeBatch--

http://viralpatel.net/blogs/batch-insert-in-java-jdbc/

解决方案 2:-

rewriteBatchedStatements can be set with DB_URL this way.

jdbc:mysql://localhost:3306/alarm?rewriteBatchedStatements=true

所以这里重写为数据批量插入。表锁一次，索引更新一次。这是另一种最快的方法。