JDBC实现Mysql数据库表同步

用于Mysql数据库间数据迁移

package com.lingyi.sync.util;


import org.apache.log4j.Logger;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class JdbcUtil {

    //日志
    private static Logger logger = Logger.getLogger(JdbcUtil.class.getClass());
    //开始时间
    private   static  Long startTime = 0L;
    //结束时间
    private static Long endTime =0L;




    public static enum DatabaseType {
        MySQL_LOCAL("mysql_local"),
        MySQL_REMOTE("mysql_remote");

        private String name;
        private DatabaseType(String name) {
            this.name = name;
        }
        public String getName() {
            return name;
        }
    };


    //配置文件
    private static Properties props;
    //项目运行路径
    private static String filePath =null;


    //获取项目运行路径
     static  {
        URL url = JdbcUtil.class.getProtectionDomain().getCodeSource().getLocation();
        try {
            filePath = URLDecoder.decode(url.getPath(), "utf-8");// 转化为utf-8编码，支持中文
        } catch (Exception e) {
            e.printStackTrace();
        }

        filePath = filePath.replace("target/classes/", "");

        if (filePath.endsWith(".jar")) {// 可执行jar包运行的结果里包含".jar"
            // 获取jar包所在目录
            filePath = filePath.substring(0, filePath.lastIndexOf("/") + 1);
        }
        if (System.getProperty("os.name").contains("dows")){
            File file = new File(filePath);
            filePath = file.getAbsolutePath();//得到windows下的正确路径
        }

}


    //加载配置文件safe_exam_db.properties
    static {
        try {
            props = new Properties();
            InputStream in = new FileInputStream(new File(filePath + "/config/safe_exam_db.properties"));
            props.load(in);
        } catch (IOException e) {
            throw  new RuntimeException("没有找到外部配置文件");
        }
    }






    /**
     * 获取数据库连接
     * @param type 要操作的数据库
     * @return
     */
    public static Connection getConnection(DatabaseType type){
        //加载驱动
        try {
            Class.forName(props.getProperty(type.getName() + ".driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String jdbcUrl = props.getProperty(type.getName() + ".jdbcUrl");
        String user = props.getProperty(type.getName() + ".user");
        String password = props.getProperty(type.getName() + ".password");
        //链接数据库
        try {
            return DriverManager.getConnection(jdbcUrl, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
            throw  new RuntimeException("连接数据库失败");
        }
    }





    /**
     * 将查询的数据复制到另一个数据库的表中，要求两张表的字段名，字段类型完全相同。
     * @param src 要查询的数据库
     * @param srcTableName 要查询的表
     * @param dest 目标数据库
     * @param destTableName 目标表名称
     * @return
     */
    public  int[] copy(DatabaseType src,String srcTableName, DatabaseType dest, String destTableName ) {
        startTime = System.currentTimeMillis();
        int count[] = new int[0];
        //创建表
        createTable(src,srcTableName,dest,destTableName);
        //查询数据
        String sql = String.format("select * from  %s ", srcTableName);
        List<Map<String,Object>> query = query(src, sql);
        //插入数据
        String insertSql = "insert into %s(%s) values(%s)";
        StringBuilder key = new StringBuilder();
        StringBuilder value = new StringBuilder();
        List<String> columns = new ArrayList<>();
        List<List<Object>> params = new ArrayList<>();
        if(query.size() > 0) {
            for(String column : query.get(0).keySet()) {
                key.append(column).append(",");
                value.append("?,");
                columns.add(column);
            }
            insertSql = String.format(insertSql,
                    destTableName,
                    key.substring(0, key.length()-1).toString(),
                    value.substring(0, value.length()-1).toString());

            for (Map<String, Object> map : query) {
                List<Object> param = new ArrayList<>();
                for (String column : columns) {
                    param.add(map.get(column));
                }
                params.add(param);
            }
            count = executeBatch(dest, insertSql, params);
            endTime = System.currentTimeMillis();
            logger.debug("复制表"+destTableName+"成功"+"用时"+(endTime-startTime)+"ms");
        }
        return count;
    }




    /**
     * 查询数据并封装到 List 集合中
     * @param type 要查询的数据库
     * @param sql SQL 语句
     * @param args 参数列表
     * @return List<Map<字段名, 值>>
     */
    public static List<Map<String, Object>> query(DatabaseType type, String sql, Object...args) {
        List<Map<String, Object>> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = getConnection(type);
            pst = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
            rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            //获取字段数量
            int columnCount = rsmd.getColumnCount();

            List<String> columns = new ArrayList<>(columnCount);
            for(int i = 1; i <= columnCount; i++) {
                columns.add(rsmd.getColumnName(i)); // 字段名
            }
            //封装数据
            while(rs.next()) {
                Map<String, Object> map = new HashMap<>();
                for (String column : columns) {
                    map.put(column, rs.getObject(column));
                }
                result.add(map);
            }
        } catch (Exception e) {
            throw new RuntimeException("查询异常"+e.getMessage());
        } finally {
            closeStatement(pst);
            closeConnection(conn);
            closeResultSet(rs);
        }
        return result;
    }


    /**
     * 从源数据库查询建表语句，在目标数据库执行
     * @param src 要查询的数据库
     * @param srcTableName 要查询的表名
     * @param dest 目标数据库
     * @param destTableName 目标表名称
     */
    public  void createTable(DatabaseType src,String srcTableName,DatabaseType dest, String destTableName){
        String sql1 = String.format("DROP TABLE IF EXISTS %s ", destTableName);//如果目标数据库存在就删除
        String sql2 = getDDL(src, srcTableName,destTableName);
        ArrayList<String> list = new ArrayList<>();
        list.add(sql1);
        list.add(sql2);
        executeBatch(dest,list);
    }




    /**
     * 查询建表语句(不同数据库复制表，需要重写此方法获得建表语句)
     * @param src 要查询的数据库
     * @param srcTableName 目标表名称
     * @return
     */
    public  String getDDL(DatabaseType src,String srcTableName,String destTableName){
        return null;
    }




    /**
     * 批量执行多条 SQL 语句
     * @param type 要执行的数据库
     * @param sqls SQL 语句
     * @return
     */
    public static int[] executeBatch(DatabaseType type, List<String> sqls) {
        int count[] = new int[0];
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection(type);
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            for (String sql : sqls) {
                stmt.addBatch(sql);
            }
            count = stmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(autoCommit);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeStatement(stmt);
            closeConnection(conn);
        }
        return count;
    }




    /**
     * 批量执行一个 SQL 语句，可以传不同的参数
     * @param type 要执行的数据库
     * @param sql SQL 语句
     * @param params 参数列表
     * @return
     */
    public static int[] executeBatch(DatabaseType type, String sql, List<List<Object>> params) {
        int count[] = new int[0];
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getConnection(type);
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);

            for (List<Object> list : params) {
                for(int i = 0; i < list.size(); i++) {
                    pst.setObject(i + 1, list.get(i));
                }
                pst.addBatch();
            }
            count = pst.executeBatch();
            conn.commit();
            conn.setAutoCommit(autoCommit);
        } catch (Exception e) {
            logger.error("插入数据失败"+e.getMessage());
            throw new RuntimeException("插入数据失败"+e.getMessage());
        } finally {
            closeStatement(pst);
            closeConnection(conn);
        }
        return count;
    }



    /**
     * 执行单条 SQL 语句
     * @param type 要执行的数据库
     * @param sql SQL 语句
     * @param args 参数列表
     * @return
     */
    public static int execute(DatabaseType type, String sql, Object...args) {
        int count = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getConnection(type);
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
            count = pst.executeUpdate();
            conn.commit();
            conn.setAutoCommit(autoCommit);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeStatement(pst);
            closeConnection(conn);
        }
        return count;
    }





    /**
     * 关闭 Statement
     * @param stmt
     * @return
     */
    public static boolean closeStatement(Statement stmt) {
        boolean flag = true;
        if(stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }



    /**
     * 关闭 ResultSet
     * @param rs
     * @return
     */
    public static boolean closeResultSet(ResultSet rs) {
        boolean flag = true;
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }



    /**
     * 关闭 Connection
     * @param conn
     * @return
     */
    public static boolean closeConnection(Connection conn) {
        boolean flag = true;
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }


}

配置文件放在项目根目录下config/safe_exam_db.properties

#MySQL_LOCAL
mysql_local.driver=com.mysql.cj.jdbc.Driver
mysql_local.jdbcUrl=jdbc:mysql://localhost:3306/jeecg24
mysql_local.user=root
mysql_local.password=root


#MySQL_REMOTE
mysql_remote.driver=com.mysql.cj.jdbc.Driver
mysql_remote.jdbcUrl=jdbc:mysql://localhost:3306/labv2
mysql_remote.user=root
mysql_remote.password=root

当从mysql复制到mysql时，编写JdbcUtil子类重写getDDL方法获取建表语句

package com.lingyi.sync;

import com.lingyi.sync.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Mysql2Mysql extends JdbcUtil {

    /**获取建表语句
     * 不同数据库查询建表语句不同，需要重写获取建表语句
     * @param src 要查询的数据库
     * @param srcTableName 目标表名称
     * @return
     */
    @Override
    public String getDDL(DatabaseType src, String srcTableName,String destTableName) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String ddl = null;
        String sql = String.format("SHOW CREATE TABLE %s", srcTableName);//查询sql
        try {
            conn = getConnection(src);
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                ddl = rs.getString(2);
            }
            String newDDL = ddl.replace(srcTableName, destTableName);
            return newDDL;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询建表语句失败" + e.getMessage());
        } finally {
            closeStatement(pstm);
            closeConnection(conn);
            closeResultSet(rs);
        }

    }
}

执行复制操作

// 将 源mysql数据库中的表中的数据插入到目标mysql数据库中
        mysql.copy(JdbcUtil.DatabaseType.MySQL_REMOTE,"idata_exam_v_chengji", JdbcUtil.DatabaseType.MySQL_LOCAL, "idata_exam_v_chengji");