mysql数据库迁移工具类

import org.apache.commons.lang.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DbHelper {
    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    // 建立连接,00000
    public Connection getConnection(String DRIVER, String url, String username, String password) {
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

// 建立一个叫做testdatabase的数据库,00000

    // 查询专用方法
    public ResultSet executeQuery(String DRIVER, String sql, String url, String username, String password,
                                  Object... params) {
        this.conn = getConnection(DRIVER, url, username, password);
        try {
            ps = conn.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }

    // 获得所有表的列表
    public List<String> getAllTableName(String DRIVER, String url, String username, String password) {
        DatabaseMetaData metaData;
        List<String> colNamesList = new ArrayList<String>();
        try {
            metaData = getConnection(DRIVER, url, username, password).getMetaData();
            ResultSet tables = metaData.getTables(null, null, "%", new String[] { "TABLE" });
            // 表名称列表
            while (tables.next()) {
                colNamesList.add(tables.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // 获取数据库下面所有表
        closeConnection();
        return colNamesList;
    }

    // 获得ddl语句
    public List<String> Aachievddl(String DRIVER, String url, String username, String password, String table) {
        String tableName = table;
        Connection conn = getConnection(DRIVER, url, username, password);
        String sql = String.format("SHOW CREATE TABLE %s", tableName);// 查询sql
        // String sql = "SHOW CREATE TABLE ?";
        List<String> list = new ArrayList<>();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            // ps.setString(1, tableName);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                /// 第一个参数获取的是tableName
                list.add(resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    // 执行ddl语句
    public int copyddltable(String DRIVER, String url, String username, String password, String url2, String username2,
                            String password2) {
        Statement stsm = null;
        List<String> tablename = getAllTableName(DRIVER, url, username, password);
        for (String table : tablename) {
            List<String> list = Aachievddl(DRIVER, url, username, password, table);
            try {
                Class.forName(DRIVER);
                Connection conns = getConnection(DRIVER, url2, username2, password2);
                stsm = conns.createStatement();
                int result = stsm.executeUpdate(StringUtils.strip(list.toString(), "[]"));
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        return 0;

    }

    // 执行数据库复制操作
// 查询表的字段信息
    public List<String> getTablefields(String DRIVER, String url, String username, String password, String table) {
        List<String> tablelist = new ArrayList<String>();
        try {
            getConnection(DRIVER, url, username, password);
            ps = conn.prepareStatement("select * from " + table + " where 1=2");
            ResultSetMetaData rsd = ps.executeQuery().getMetaData();
            for (int i = 0; i < rsd.getColumnCount(); i++) {
                tablelist.add((rsd.getColumnName(i + 1) + " " + rsd.getColumnTypeName(i + 1) + "("
                        + rsd.getColumnDisplaySize(i + 1) + ")"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tablelist;
    }

    // 进行字段对比
    public int ComparisonField(String DRIVER, String url, String username, String password, String UrlSecond,
                               String userSecond, String passSecond, String Tablename) {
        // 查询所有的表名称
        List<String> tableNameList = getTablefields(DRIVER, UrlSecond, userSecond, passSecond, Tablename);
        // 查询表的字段
        List<String> ComparisonList = getTablefields(DRIVER, url, username, password, Tablename);
        // 查询表的字段
        List<String> list = new ArrayList<>(tableNameList);
        // 取出不同的值
        for (int i = 0; i < ComparisonList.size(); i++) {
            // 如果第三个List已经存在，则不添加，如果不存在就添加
            if (!list.contains(ComparisonList.get(i))) {
                // contains是包含的意思，这个if加了！,意思是不包含
                list.add(ComparisonList.get(i));
            }
        }
        // 取出不同的值
        for (int i = 0; i < list.size(); i++) {
            // 将第一，第二个List和第三个比较，如果第一第二个都有的，则从第三个删除
            if (tableNameList.contains(list.get(i)) && ComparisonList.contains(list.get(i))) {
                list.remove(i);
                i--;// 如果删除了一个元素，就继续从这个数组下标开始比较
            }
        }
        // 把值传入字段添加里面
        int num = UpdateComparison(DRIVER, UrlSecond, userSecond, passSecond, list, Tablename);
        // 执行修改字段的方法
        closeConnection();
        return num;
    }

    // 执行修改字段的方法
    public int UpdateComparison(String DRIVER, String UrlSecond, String userSecond, String passSecond,
                                List<String> list, String Tablename) {
        getConnection(DRIVER, UrlSecond, userSecond, passSecond);
        for (String nameL : list) {
            String sql = "alter table " + Tablename + " add " + nameL + "";
            System.out.println(sql);
            try {
                conn.createStatement().executeUpdate(sql);

            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        closeConnection();
        return 1;
    }

    // 执行表数据复制
    public int Copytabledata(String DRIVER, String url, String username, String password, String UrlSecond,
                             String userSecond, String passSecond) {
        List<String> list = getAllTableName(DRIVER, url, username, password);
        for (String tablu : list) {
            List<String> emplist = new ArrayList<String>();
            String sql = "select * from " + tablu + "";
            try {
                Connection iteconn = getConnection(DRIVER, url, username, password);
                Statement itestmt = iteconn.createStatement();
                ResultSet iters = itestmt.executeQuery(sql);
                int size = iters.getMetaData().getColumnCount();
                StringBuffer sbf = new StringBuffer();
                sbf.append("insert into " + tablu + " values (");
                String link = "";
                for (int i = 0; i < size; i++) {
                    sbf.append(link).append("?");
                    link = ",";
                }
                sbf.append(")");
                Connection mysqlconn = getConnection(DRIVER, UrlSecond, userSecond, passSecond);
                PreparedStatement mysqlpstmt = mysqlconn.prepareStatement(sbf.toString());

                // 取出结果集并向MySQL数据库插入数据 ( 使用批处理 )
                // 完成条数
                int count = 0;
                int num = 0;
                // 取消事务(不写入日志)
                mysqlconn.setAutoCommit(false);
                long start = System.currentTimeMillis();
                while (iters.next()) {
                    ++count;
                    for (int i = 1; i <= size; i++) {
                        mysqlpstmt.setObject(i, iters.getObject(i));
                    }

                    // 将预先语句存储起来，这里还没有向数据库插入
                    mysqlpstmt.addBatch();
                    // 当count 到达 20000条时 向数据库提交
                    if (count % 20000 == 0) {
                        ++num;
                        mysqlpstmt.executeBatch();
                        System.out.println("第" + num + "次提交,耗时:" + (System.currentTimeMillis() - start) / 1000.0 + "s");
                    }
                }
                // 防止有数据未提交
                mysqlpstmt.executeBatch();
                // 提交
                mysqlconn.commit();
                System.out.println("完成 " + count + " 条数据,耗时:" + (System.currentTimeMillis() - start) / 1000.0 + "s");
                // 恢复事务
                // mysqlconn.setAutoCommit(true);

                // 关闭资源

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        closeConnection();
        return 1;
    }

    // 关闭连接
    public void closeConnection() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        DbHelper dbHelper = new DbHelper();
        String DRIVER = "com.mysql.jdbc.Driver";
        // 取值的数据库
        String Url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";
        String username = "xxxx";
        String password = "xxxxx";
        // 复制到数据库

        String UrlSecond = "jdbc:mysql://127.0.0.1:3306/test_copy?useUnicode=true&characterEncoding=UTF-8";
        String userSecond = "xxxx";
        String passSecond = "xxxx";
        // 复制数据库表结构
        //int num = dbHelper.copyddltable(DRIVER, Url, username, password, UrlSecond, userSecond, passSecond);
        //System.out.println(num);
        /* // 字段进行对比
        //传入需要修改的表
        String Tablename = "test_table1";
        int nums =dbHelper.ComparisonField(DRIVER, Url, username, password, UrlSecond,
                userSecond, passSecond, Tablename);
        System.out.println(num);*/
       int emp=dbHelper.Copytabledata(DRIVER,Url, username, password, UrlSecond, userSecond, passSecond);
        System.out.println(emp);
    }

}