缺点

1. 需要另外同步表结构到目标数据库

代码

import java.sql.*;

public class EsqTableController {

    public static void main(String[] args) throws SQLException {
        migrateData();
    }

    public static Connection getSourceConnection() throws SQLException {
        String url = "";
        String user = "";
        String pwd = "";
        return DriverManager.getConnection(url, user, pwd);
    }

    public static Connection getTargetConnection() throws SQLException {
        String url = "";
        String user = "";
        String pwd = "";
        return DriverManager.getConnection(url, user, pwd);
    }

    public static String[] getTables() {
        String[] tables = {
                ""
        };
        return tables;
    }

    /**
     * 迁移表数据, 但是如果表结构不一致需要手动同步结构
     * @throws SQLException
     */
    public static void migrateData() throws SQLException {
        Connection sourceConnection = null;
        Connection targetConnection= null;
        Statement sourceStatement= null;
        Statement targetStatement= null;
        PreparedStatement ps= null;
        try {
            System.out.println("迁移数据开始!");
            sourceConnection = getSourceConnection();
            targetConnection = getTargetConnection();
            sourceStatement = sourceConnection.createStatement();
            targetStatement = targetConnection.createStatement();
            String[] tables = getTables();
            String searchSql = "select * from ";
            String truncateSql = "truncate table ";
            for (String table : tables) {
                ResultSet rs = sourceStatement.executeQuery(searchSql + table);
                int size = rs.getMetaData().getColumnCount();

                StringBuffer sbf = new StringBuffer();
                sbf.append("insert into ").append(table).append(" values( ");
                for (int i = 0; i < size; i++) {
                    sbf.append("?");
                    if (i != size - 1) {
                        sbf.append(", ");
                    }
                }
                sbf.append(")");

                // 清空表, 视情况情况, 如果其他数据都一致, 只是添加一些的可以不用清空
                targetStatement.execute(truncateSql + table);

                ps = targetConnection.prepareStatement(sbf.toString());

                int count = 0;
                int num = 0;
                long start = System.currentTimeMillis();
                while(rs.next()) {
                    count++;
                    for (int i = 1; i <= size; i++) {
                        ps.setObject(i, rs.getObject(i));
                    }

                    ps.addBatch();

                    if (count % 1000 == 0) {
                        num++;
                        ps.executeBatch();
                        System.out.println("第" + num + "次提交");
                    }
                }
                ps.executeBatch();

                System.out.println("完成" + table + "一共" + count + "条数据, 耗时: " + ((System.currentTimeMillis() - start)/1000.0) + "秒");

                ps.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(sourceStatement != null){
                sourceStatement.close();
            }
            if(targetStatement != null){
                targetStatement.close();
            }
            if(sourceConnection != null){
                sourceConnection.close();
            }
            if(targetConnection != null){
                targetConnection.close();
            }

            System.out.println("迁移数据结束!");
        }
    }
}
``