Java 实现两个不同服务器的数据库数据的迁移（注释含数据库建表语句备份）

现在需要将内网数据库的数据及表同步到外网中，但是连接内网需要jar包才能穿透连接到，经测试，jar包对获取建表语句进行了限制（注释的代码），所以需要内外网两边把表建好，直接同步数据。
逻辑：
获取内网中的数据，查询出来insert到外网数据库中。代码如下：

package com.merit.common.controller;

import com.merit.common.service.BakDateBaseService;
import com.merit.common.util.JdbcUtil;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.scheduling.config.ScheduledTaskRegistrar;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.Properties;

/**
 * 手动定时任务
 */
@Configuration
@EnableScheduling   //开启定时任务
public class SaticScheduleTask {

    @Scheduled(cron = "${auto.cron}")
    private void configureAutoTasks() {
        System.err.println("定时查询是否有需要更新的表，当前执行动态定时任务时间: " + LocalDateTime.now());

        //1.新建属性集对象
        Properties properties = new Properties();
        //2通过反射，新建字符输入流，读取db.properties文件
        InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("application.properties");
        //3.将输入流中读取到的属性，加载到properties属性集对象中
        try {
            properties.load(input);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //4.根据键，获取properties中对应的值
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("username");
        String password = properties.getProperty("password");

        int prosCons = Integer.parseInt(properties.getProperty("pros_cons"));

        new BakDateBaseService(driver,url, "", user, password, "",prosCons).startBakUpdate();
    }
}

package com.merit.common.service;

import com.merit.common.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


/**
 * 利用jdbc备份mysql数据库
 *
 */
public class BakDateBaseService {

    private String DRIVER = "sgcc.nds.jdbc.driver.NdsDriver";
    //private String DRIVER = "com.mysql.jdbc.Driver";
    private String URL = null;
    private String USERNAME = null;
    private String PASSWORD = null;

    //外网中：为2，反向，从rds到外网
    private Integer prosCons = 0;

    private Connection conn = null;

    private String SQL = "SELECT * FROM ";// 数据库操作

    private final static Logger log = LoggerFactory.getLogger(BakDateBaseService.class);

    /**
     *
     * <构造函数>
     *
     * @param ip
     *            数据库ip地址
     * @param database
     *            数据库名称
     * @param userName
     *            数据库用户名
     * @param password
     *            密码
     * @param bakFilePath
     *            备份的地址
     */
    public BakDateBaseService(String driver,String ip, String database, String userName, String password, String bakFilePath,int prosCons) {
        try {
            //this.DRIVER = driver;
            Class.forName(DRIVER);
            //this.URL = String.format("jdbc:mysql://%s:63336/%s?useUnicode=true&characterEncoding=utf8", ip, database);
            this.URL = ip;
            this.USERNAME = userName;
            this.PASSWORD = password;

            this.prosCons = prosCons;

            SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-ddHH时mm分ss秒");
            String datetime = tempDate.format(new java.util.Date());
            //自动加上时间戳
            datetime = datetime + "_数据库名称：" + database ;
        } catch (ClassNotFoundException e) {
            log.error("can not load jdbc driver:"+e.getMessage());
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    private Connection getConnection() {

        try {
            if (null == conn) {
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            }
        } catch (SQLException e) {
            log.error("get connection failure:"+e.getMessage());
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    private void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("close connection failure:"+e.getMessage());
            }
        }
    }

    /**
     * 获取数据库下的所有表名
     */
    /*private List<String> getTableNames() {
        List<String> tableNames = new ArrayList<String>();
        Connection conn = getConnection();
        ResultSet rs = null;
        try {
            // 获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            // 从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[] { "TABLE" });
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (Exception e) {
            log.error("getTableNames failure:"+e.getMessage());
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }

            } catch (SQLException e) {
                log.error("close ResultSet failure:"+e.getMessage());
            }
        }
        return tableNames;
    }*/

    private List<String> getTableNames() {
        List<String> tableNames = new ArrayList<String>();
        Connection conn = getConnection();
        String sql = "select table_name as tableName from table_bak where flag = 2 and is_run = 0 and pros_cons ="+ prosCons;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                tableNames.add(rs.getString(1));
            }
        } catch (Exception e) {
            log.error("getTableNames failure:"+e.getMessage());
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                closeConnection(conn);
            } catch (SQLException e) {
                log.error("close ResultSet failure:"+e.getMessage());
            }
        }
        return tableNames;
    }

    private List<String> getUpdateTableNames() {
        List<String> tableNames = new ArrayList<String>();
        Connection conn = getConnection();
        String sql = "select table_name as tableName from table_bak where is_run = 1 and flag = 2 and pros_cons = " + prosCons;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                tableNames.add(rs.getString(1));
            }
        } catch (Exception e) {
            log.error("getTableNames failure:"+e.getMessage());
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != pstmt) {
                    pstmt.close();
                }
                closeConnection(conn);
            } catch (SQLException e) {
                log.error("close ResultSet failure:"+e.getMessage());
            }
        }
        return tableNames;
    }

    /**
     * 获取表中所有字段名称
     *
     * @param tableName
     *            表名
     * @return
     */
    private List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<String>();
        // 与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            // 结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            // 表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            log.error("getColumnNames failure:"+e.getMessage());
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();

                } catch (SQLException e) {
                    log.error("getColumnNames close pstem and connection failure:"+e.getMessage());
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName
     * @return
     */
    private List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<String>();
        // 与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            // 结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            // 表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            log.error("getColumnTypes failure:"+e.getMessage());
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();

                } catch (SQLException e) {
                    log.error("getColumnTypes close pstem and connection failure:"+e.getMessage());
                }
            }
        }
        return columnTypes;
    }

    /**
     *
     * <p>
     * 生成建表语句
     * </p>
     *
     * @param tableName
     * @return
     */
    private String generateCreateTableSql(String tableName) {
        //String sql = String.format("SHOW CREATE TABLE `%s`", tableName);
        String sql = "show create table "+ "`" + tableName + "`";
        System.out.println("查询创建表sql语句：" + sql);
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            //conn = getConnection();
            System.out.println("conn-----"+conn);
            pstmt = conn.prepareStatement("show create table "+ "`" + tableName + "`");
            System.out.println("pstmt-----:"+pstmt);
            rs = pstmt.executeQuery();
            System.out.println("---rs---"+rs);
            while (rs.next()) {
                // 返回建表语句语句，查询结果的第二列是建表语句，第一列是表名
                System.out.println("rs.getString(2)---"+rs.getString(2));
                return rs.getString(2);

            }

        } catch (Exception e) {
            log.error("generateCreateTableSql建表语句生成异常:"+e.getMessage(),e);
        }finally {
            try {
                if (null != rs){
                    rs.close();
                }
                if (null != pstmt) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                log.error("generateCreateTableSql方法关闭流异常:"+e.getMessage(),e);
            }
        }
        return null;
    }

    /**
     * 获取表中字段的所有注释
     *
     * @param tableName
     * @return
     */
    private List<String> getColumnComments(String tableName) {
        // 与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        List<String> columnComments = new ArrayList<String>();// 列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {

        } finally {
            if (rs != null) {
                try {
                    rs.close();

                } catch (SQLException e) {
                   log.error("getColumnComments close ResultSet and connection failure:"+e.getMessage());
                }
            }
        }
        return columnComments;
    }

    /**
     *
     * <p>
     * 备份表数据
     * </p>
     *
     * @param tableName
     * @return
     */
    private String bakTableData(String tableName) {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet iters = null;
        //
        Connection mysqlconn = null;
        PreparedStatement mysqlpstmt = null;
        try {

            //List<Object[]> strings = getTableDatas(tableName);
            //for (Object[] string : strings) {
            //    for (int i = 0; i < string.length; i++) {
            //        System.out.print(string[i]+",");
            //    }
            //    System.out.println("----------");
            //}

            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            pstmt = conn.prepareStatement("select * from " + tableName);
            iters  = pstmt.executeQuery();
            //结果集获取到的长度
            int size = iters.getMetaData().getColumnCount();

            //拼接insert into 语句
            StringBuffer sbf =new StringBuffer();
            String isExistsTable = "truncate table "+tableName+ "";
            if(0 == deleteTable(isExistsTable)){
                System.err.println("******删除表语句:"+isExistsTable);
            };

            sbf.append("insert into "+tableName+" values (");
            String link ="";
            for (int i = 0; i <size ; i++) {
                sbf.append(link).append("?");
                link=",";
            }
            sbf.append(")");
            System.out.println(sbf);
            //MySQL数据库
            mysqlconn = JdbcUtil.getConnection();
            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);



            // 备份建表语句
            //String createTableSql = generateCreateTableSql(tableName);
            //String isExistsTable = String.format("DROP TABLE IF EXISTS `%s`;",tableName);
            //createTableSql = String.format(
            //        "\n\n\n/**\n * table name :<%s>\n *\n */\n%s\n",
            //        tableName, createTableSql);
            //
            //System.err.println("******创建表语句:"+createTableSql);
            //
            //if(0 == deleteTable(isExistsTable)){
            //    System.err.println("******删除表语句:"+isExistsTable);
            //};
            //
            将建表语句在另一个数据库中去执行
            //if (!copyTable(createTableSql)){
            //    return "";
            //};


            // 获取字段类型
            /*List<String> columnTypes = getColumnTypes(tableName);
            System.out.println("columnTypes----"+columnTypes);
            // 获取所有 字段
            List<String> columnNames = getColumnNames(tableName);
            System.out.println("columnNames----"+columnNames);
            String columnArrayStr = null;
            for (String column : columnNames) {
                if (null == columnArrayStr) {
                    columnArrayStr = "`" + column + "`";
                } else {
                    columnArrayStr = columnArrayStr + "," + "`" + column + "`";
                }
            }

            String sql = String.format("select %s from %s", columnArrayStr, tableName);
            System.out.println("select %s from %s, columnArrayStr, tableName---"+sql);

            //conn = getConnection();
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            pstmt = conn.prepareStatement(sql);
            System.out.println("pstmt+++++"+pstmt);
            ResultSet rs = pstmt.executeQuery();
            System.out.println("2222---"+rs);
            while (rs.next()) {
                String rowValues = getRowValues(rs, columnNames.size(), columnTypes);
                // 返回建表语句语句，查询结果的第二列是建表语句，第一列是表名
                String insertSql = String.format("insert into %s (%s) values(%s);", tableName, columnArrayStr,
                        rowValues);
                System.out.println(insertSql);
                insertSql = insertSql.replaceAll("\n", "<br/>");
                insertSql = insertSql + "\n";

                //复制内网数据库表中的数据
                copyTableData(insertSql);
            }*/



            //将is_run修改成0状态，记录时间
            updateTableStatus(tableName);

        } catch (Exception e) {
            log.error("***"+e.getMessage(),e);
        }finally {
            try {
                if (null != pstmt) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
                //关闭资源
                close(mysqlconn,mysqlpstmt,null);
                close(conn,pstmt,iters);
            } catch (Exception e2) {
                log.error("bakTableData方法关闭流异常:"+e2.getMessage());
            }
        }
        return null;
    }

    public  void  close(Connection conn,Statement stmt,ResultSet rs){

        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private List<Object[]> getTableDatas(String tableName) {
        List<Object[]> list = new ArrayList<>();
        String sql = "select * from " + tableName;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            System.out.println("11111---"+rs);
            //结果集获取到的长度
            int size = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                Object[] columnValue = new Object[size];// 列值。
                for (int i = 0; i < size; i++) {
                    columnValue[i] = rs.getObject(i + 1);
                    //return rs.getObject(i);
                }
                list.add(columnValue);
            }
            return list;
        } catch (Exception e) {
            log.error("getTableDatas异常:"+e.getMessage(),e);
        }finally {
            try {
                if (null != pstmt) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                log.error("getTableDatas关闭流异常:"+e.getMessage(),e);
            }
        }
        return list;
    }

    /**
     * 最后修改表的状态，记录时间
     * @param tableName
     */
    private void updateTableStatus(String tableName) {
        String sql = "update table_bak set is_run = 0,cron_time = now() where table_name ='"+tableName+"'";

        PreparedStatement pstmt = null;
        Connection conn = null;

        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            //conn=dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
            log.info(tableName+"表已更新。");
        } catch (ClassNotFoundException | SQLException e) {
            log.error("更新数据时出错："+e.getMessage());
        }finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                log.error("关闭流出错:"+e.getMessage());
            }
        }

    }

    /**
     *
     * <p>
     * 获取表数据一行的所有值
     * </p>
     *
     * @param rs
     * @param size
     */
    private String getRowValues(ResultSet rs, int size, List<String> columnTypeList) {
        try {
            String rowValues = null;
            for (int i = 1; i <= size; i++) {
                String columnValue = null;

                // 获取字段值
                columnValue = getValue(rs, i, columnTypeList.get(i - 1));
                // 如果是空值不添加单引号
                if (null != columnValue) {
                    columnValue = "'" + columnValue + "'";
                }
                // 拼接字段值
                if (null == rowValues) {
                    rowValues = columnValue;
                } else {
                    rowValues = rowValues + "," + columnValue;
                }
            }

            return rowValues;
        } catch (Exception e) {
            log.error("获取表数据一行的所有值异常:"+e.getMessage());
            return null;
        }
    }

    /**
     *
     * <p>
     * 根据类型获取字段值
     * </p>
     *
     * @param
     * @return
     */
    private String getValue(ResultSet resultSet, Integer index, String columnType) {
        try {

            if ("int".equals(columnType) || "INT".equals(columnType)) {
                // 整数
                Object intValue = resultSet.getObject(index);

                if (null == intValue) {
                    return null;
                }
                return intValue + "";
            } else if ("bigint".equals(columnType) || "BIGINT".equals(columnType)) {

                // 长整形
                Object value = resultSet.getObject(index);
                if (null == value) {
                    return null;
                }
                return value + "";
            } else if ("smallint".equals(columnType) || "SMALLINT".equals(columnType)) {
                // 整数
                Object value = resultSet.getObject(index);
                if (null == value) {
                    return null;
                }
                return value + "";
            } else if ("tinyint".equals(columnType) || "TINYINT".equals(columnType)) {
                // 整数
                Object value = resultSet.getObject(index);
                if (null == value) {
                    return null;
                }
                return value + "";
            } else if ("mediumint".equals(columnType) || "MEDIUMINT".equals(columnType)) {
                // 长整形
                Object value = resultSet.getObject(index);
                if (null == value) {
                    return null;
                }
                return value + "";
            } else if ("integer".equals(columnType) || "INTEGER".equals(columnType)) {
                // 整数
                Object value = resultSet.getObject(index);
                if (null == value) {
                    return null;
                }
                return value + "";
            } else if ("float".equals(columnType) || "FLOAT".equals(columnType)) {

                // 浮点数
                Object value = resultSet.getObject(index);
                if (null == value) {
                    return null;
                }
                return value + "";
            } else if ("double".equals(columnType) || "DOUBLE".equals(columnType)) {
                // 浮点数
                Object value = resultSet.getObject(index);
                if (null == value) {
                    return null;
                }
                return value + "";
            } else if ("decimal".equals(columnType) || "DECIMAL".equals(columnType)) {
                // 浮点数-金额类型
                BigDecimal value = resultSet.getBigDecimal(index);
                if (null == value) {
                    return null;
                }
                return value.toString();
            } else if ("char".equals(columnType) || "CHAR".equals(columnType)) {
                // 字符串类型
                String value = resultSet.getString(index);
                return value;
            } else if ("varchar".equals(columnType) || "VARCHAR".equals(columnType)) {
                // 字符串类型
                String value = resultSet.getString(index);
                return value;
            } else if ("tinytext".equals(columnType) || "TINYTEXT".equals(columnType)) {
                // 字符串类型
                String value = resultSet.getString(index);
                return value;
            } else if ("text".equals(columnType) || "TEXT".equals(columnType)) {
                // 字符串类型
                String value = resultSet.getString(index);
                return value;
            } else if ("mediumtext".equals(columnType) || "MEDIUMTEXT".equals(columnType)) {
                // 字符串类型
                String value = resultSet.getString(index);
                return value;
            } else if ("longtext".equals(columnType) || "LONGTEXT".equals(columnType)) {
                // 字符串类型
                String value = resultSet.getString(index);
                return value;
            } else if ("year".equals(columnType) || "YEAR".equals(columnType)) {
                // 时间类型：范围 1901/2155 格式 YYYY
                String year = resultSet.getString(index);
                if (null == year) {
                    return null;
                }
                // 只需要年的字符即可，
                return year.substring(0, 4);
            } else if ("date".equals(columnType) || "DATE".equals(columnType)) {
                // 时间类型：范围 '1000-01-01'--'9999-12-31' 格式 YYYY-MM-DD
                return resultSet.getString(index);
            } else if ("time".equals(columnType) || "TIME".equals(columnType)) {
                // 时间类型：范围 '-838:59:59'到'838:59:59' 格式 HH:MM:SS
                return resultSet.getString(index);
            } else if ("datetime".equals(columnType) || "DATETIME".equals(columnType)) {
                // 时间类型：范围 '1000-01-01 00:00:00'--'9999-12-31 23:59:59' 格式 YYYY-MM-DD HH:MM:SS
                return resultSet.getString(index);
            } else if ("timestamp".equals(columnType) || "TIMESTAMP".equals(columnType)) {
                // 时间类型：范围 1970-01-01 00:00:00/2037 年某时 格式 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
                return resultSet.getString(index);
            } else {
                return null;
            }

        } catch (Exception e) {
            log.error("获取数据库类型值异常:"+e.getMessage());
            return null;
        }
    }

    /**
     *
     * <开始备份>
     *
     */
    public void startBak() {
        try {
            List<String> tableNames = getTableNames();
            System.out.println("tableNames:" + tableNames);
            for (String tableName : tableNames) {
                bakTableData(tableName);
                // System.out.println(generateCreateTableSql(tableName));
                // System.out.println("ColumnNames:" + getColumnNames(tableName));
                // System.out.println("ColumnTypes:" + getColumnTypes(tableName));
                // System.out.println("ColumnComments:" + getColumnComments(tableName));
            }
            // 统一关闭连接
            closeConnection(conn);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }


    /**
     * 开始备份--针对手动修改的
     */
    public void startBakUpdate() {
        try {
            List<String> tableNames = getUpdateTableNames();
            System.out.println("tableNames:" + tableNames);
            for (String tableName : tableNames) {
                bakTableData(tableName);
                // System.out.println(generateCreateTableSql(tableName));
                // System.out.println("ColumnNames:" + getColumnNames(tableName));
                // System.out.println("ColumnTypes:" + getColumnTypes(tableName));
                // System.out.println("ColumnComments:" + getColumnComments(tableName));
            }
            // 统一关闭连接
            closeConnection(conn);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 创建表前先进行删除操作
     * @param isExistsTable
     * @return
     */
    private int deleteTable(String isExistsTable){
        Statement stmt;
        int i = 0;
        try {
            stmt = getConnIvr().createStatement();
            //stmt = JdbcUtil.getConnection().createStatement();
            i = stmt.executeUpdate(isExistsTable);
        } catch (SQLException e) {
            log.error("删除表失败！"+e.getMessage());
        }
        return i;
    }

    /**
     * 根据内网的建表语句进行外网数据库拷贝
     * @param createTableSql
     */
    private boolean copyTable(String createTableSql) {
        Statement stmt ;
        try {
            stmt = getConnIvr().createStatement();
            //stmt = JdbcUtil.getConnection().createStatement();
            if(0 == stmt.executeLargeUpdate(createTableSql)) {
               System.out.println("成功创建表！");
            }else{
               System.out.println("创建表失败！");
            }
        } catch (SQLException e) {
            log.error("创建表出错！"+e.getMessage(),e);
            return false;
        }
        return true;
    }

    /**
     * 根据内网数据库的insert语句进行外网数据拷贝
     * @param insertSql
     */
    private void copyTableData(String insertSql) {
        //DruidDataSource dataSource= new DruidDataSource();
        PreparedStatement pstmt = null;
        Connection conn = null;

        //String DRIVER="com.mysql.jdbc.Driver";
        //String DB_URL="jdbc:mysql://localhost:3306/ozone?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
        //String DB_USERNAME="root";
        //String DB_PASSWORD="123456";

        /**
         *  采用连接池也会存在如下异常：
         * com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.
         *
         * This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable.
         *
         * For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.
         *
         * For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271)
         */
        //dataSource.setUrl(DB_URL);
        //dataSource.setUsername(DB_USERNAME);
        //dataSource.setPassword(DB_PASSWORD);
        //dataSource.setDriverClassName(DRIVER);
        //dataSource.setInitialSize(5);
        //dataSource.setMaxActive(10);

        try {
            //Class.forName(DRIVER);
            //conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

            //conn=dataSource.getConnection();
            conn = JdbcUtil.getConnection();

            pstmt = conn.prepareStatement(insertSql);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            log.error("复制数据时出错："+e.getMessage());
        }finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
                /*if(dataSource != null){
                    dataSource.close();
                }*/
            } catch (SQLException e) {
               log.error("关闭流出错:"+e.getMessage());
            }
        }
    }

    /**
     * 获得连接对象
     * @return
     */
    private static synchronized Connection getConnIvr(){
        Connection CONN = null;

        //String DRIVER="com.mysql.jdbc.Driver";
        //String DB_URL="jdbc:mysql://localhost:3306/ozone?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
        //String DB_USERNAME="root";
        //String DB_PASSWORD="123456";

        //1.新建属性集对象
        Properties properties = new Properties();
        //2通过反射，新建字符输入流，读取db.properties文件
        InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("application.properties");
        //3.将输入流中读取到的属性，加载到properties属性集对象中
        try {
            properties.load(input);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //4.根据键，获取properties中对应的值
        String driver = properties.getProperty("out.driver");
        String url = properties.getProperty("out.url");
        String user = properties.getProperty("out.username");
        String password = properties.getProperty("out.password");

        try {
            Class.forName(driver);
            CONN = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException | SQLException e) {
            log.error("getConnIvr连接数据源出错"+e.getMessage());
        }
        return CONN;
    }


    public static void main(String[] args) {
        //new BakDateBaseService("123.139.156.125", "ozone", "root", "merit1q98.c0m", "f:\\bak.sql").startBak();

        //1.新建属性集对象
        Properties properties = new Properties();
        //2通过反射，新建字符输入流，读取db.properties文件
        InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("application.properties");
        //3.将输入流中读取到的属性，加载到properties属性集对象中
        try {
            properties.load(input);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //4.根据键，获取properties中对应的值
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("username");
        String password = properties.getProperty("password");

        int prosCons = Integer.parseInt(properties.getProperty("pros_cons"));

        new BakDateBaseService(driver,url, null, user, password, null,prosCons).startBak();
    }
}

package com.merit.common.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

//获取到配置文件中的数据库信息
public class JdbcUtil {
    //私有变量
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    //静态块
    static{
        try{
            //1.新建属性集对象
            Properties properties = new Properties();
            //2通过反射，新建字符输入流，读取db.properties文件
            InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("application.properties");
            //3.将输入流中读取到的属性，加载到properties属性集对象中
            properties.load(input);
            //4.根据键，获取properties中对应的值
            driver = properties.getProperty("out.driver");
            url = properties.getProperty("out.url");
            user = properties.getProperty("out.username");
            password = properties.getProperty("out.password");
        }catch(Exception e){
            System.err.println("读取配置文件错误："+e.getMessage());
        }
    }

    //返回数据库连接
    public static Connection getConnection(){
        try{
            //注册数据库的驱动
            Class.forName(driver);
            //获取数据库连接（里面内容依次是：主机名和端口、用户名、密码）
            Connection connection = DriverManager.getConnection(url,user,password);
            //返回数据库连接
            return connection;
        }catch (Exception e){
            System.err.println("获取内网数据库连接出错："+e.getMessage());
        }
        return null;
    }
}

---

参考：https://www.cnblogs.com/oukele/p/9626006.html

package com.merit.common.controller;

import java.sql.*;

public class CopyMysql {
    private Connection getIteconn() {
        try {
            Class.forName("org.sqlite.JDBC");
            return DriverManager.getConnection("jdbc:sqlite:E:\\MyDB\\lagou.db");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private Connection getMysqlconn() {
        try {
            Class.forName("org.mariadb.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mariadb://localhost:3306/test", "oukele", "oukele");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void deal() throws SQLException {
        //SQLite数据库
        Connection iteconn = getIteconn();
        Statement itestmt = iteconn.createStatement();
        ResultSet iters = itestmt.executeQuery("select * from lagou_position");
        //结果集获取到的长度
        int size = iters.getMetaData().getColumnCount();
        //比较懒，拼接insert into 语句
        StringBuffer sbf = new StringBuffer();
        sbf.append("insert into lagou values (");
        String link = "";
        for (int i = 0; i < size; i++) {
            sbf.append(link).append("?");
            link = ",";
        }
        sbf.append(")");
        //MySQL数据库
        Connection mysqlconn = getMysqlconn();
        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);
        // 关闭资源
        close(mysqlconn, mysqlpstmt, null);
        close(iteconn, itestmt, iters);
    }

    public void close(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 //调用
 public static void main(String[] args) {
        SQLite_To_MySQL test = new SQLite_To_MySQL();
        try {
            test.deal();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

参考连接：https://blog.csdn.net/qq_27184497/article/details/82454997

package com.mysql.bak;
 
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
 
import com.utils.FileUtils;
 
/**
 * 利用jdbc备份mysql数据库--不用mysqldump
 *
 */
public class BakDateBase {
 
	private String DRIVER = "com.mysql.jdbc.Driver";
	private String URL = null; // "jdbc:mysql://182.xxx.xxx.xxx:3306/xd_love_dev?useUnicode=true&characterEncoding=utf8";
	private String USERNAME = null;// "root";
	private String PASSWORD = null;//"woaini";
 
	// 备份的文件地址
	private String filePath;
 
	private Connection conn = null;
 
	private String SQL = "SELECT * FROM ";// 数据库操作
 
	/**
	 * 
	 * <构造函数>
	 * 
	 * @param ip
	 *            数据库ip地址
	 * @param database
	 *            数据库名称
	 * @param userName
	 *            数据库用户名
	 * @param password
	 *            密码
	 * @param bakFilePath
	 *            备份的地址
	 */
	public BakDateBase(String ip, String database, String userName, String password, String bakFilePath) {
		try {
			Class.forName(this.DRIVER);
			this.URL = String.format("jdbc:mysql://%s:3306/%s?useUnicode=true&characterEncoding=utf8", ip, database);
 
			this.USERNAME = userName;
			this.PASSWORD = password;
			
			SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-ddHH时mm分ss秒");
			String datetime = tempDate.format(new java.util.Date());
			//自动加上时间戳
			datetime = datetime + "_数据库名称：" + database ;
			if(bakFilePath.indexOf(".") != -1) {
				bakFilePath = bakFilePath.replace(".", datetime+".");
			} else {
				bakFilePath = datetime + ".sql";
			}
			this.filePath = bakFilePath;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.err.println("can not load jdbc driver");
		}
	}
 
	/**
	 * 获取数据库连接
	 *
	 * @return
	 */
	private Connection getConnection() {
 
		try {
			if (null == conn) {
				conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.err.println("get connection failure");
		}
		return conn;
	}
 
	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	private void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				System.err.println("close connection failure");
			}
		}
	}
 
	/**
	 * 获取数据库下的所有表名
	 */
	private List<String> getTableNames() {
		List<String> tableNames = new ArrayList<String>();
		Connection conn = getConnection();
		ResultSet rs = null;
		try {
			// 获取数据库的元数据
			DatabaseMetaData db = conn.getMetaData();
			// 从元数据中获取到所有的表名
			rs = db.getTables(null, null, null, new String[] { "TABLE" });
			while (rs.next()) {
				tableNames.add(rs.getString(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.err.println("getTableNames failure");
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
 
			} catch (SQLException e) {
				e.printStackTrace();
				System.err.println("close ResultSet failure");
			}
		}
		return tableNames;
	}
 
	/**
	 * 获取表中所有字段名称
	 * 
	 * @param tableName
	 *            表名
	 * @return
	 */
	private List<String> getColumnNames(String tableName) {
		List<String> columnNames = new ArrayList<String>();
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		try {
			pStemt = conn.prepareStatement(tableSql);
			// 结果集元数据
			ResultSetMetaData rsmd = pStemt.getMetaData();
			// 表列数
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnNames.add(rsmd.getColumnName(i + 1));
			}
		} catch (SQLException e) {
			System.err.println("getColumnNames failure");
			e.printStackTrace();
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
 
				} catch (SQLException e) {
					e.printStackTrace();
					System.err.println("getColumnNames close pstem and connection failure");
				}
			}
		}
		return columnNames;
	}
 
	/**
	 * 获取表中所有字段类型
	 * 
	 * @param tableName
	 * @return
	 */
	private List<String> getColumnTypes(String tableName) {
		List<String> columnTypes = new ArrayList<String>();
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		try {
			pStemt = conn.prepareStatement(tableSql);
			// 结果集元数据
			ResultSetMetaData rsmd = pStemt.getMetaData();
			// 表列数
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnTypes.add(rsmd.getColumnTypeName(i + 1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.err.println("getColumnTypes failure");
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
 
				} catch (SQLException e) {
					e.printStackTrace();
					System.err.println("getColumnTypes close pstem and connection failure");
				}
			}
		}
		return columnTypes;
	}
 
	/**
	 * 
	 * <p>
	 * 生成建表语句
	 * </p>
	 * 
	 * @param tableName
	 * @return
	 * @author 叶新东（18126064335） 2018年9月6日 上午9:35:49
	 */
	private String generateCreateTableSql(String tableName) {
		String sql = String.format("SHOW CREATE TABLE %s", tableName);
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				// 返回建表语句语句，查询结果的第二列是建表语句，第一列是表名
				return rs.getString(2);
 
			}
 
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (null != pstmt) {
					pstmt.close();
				}
 
			} catch (Exception e2) {
				e.printStackTrace();
				System.err.println("关闭流异常");
			}
		}
		return null;
	}
 
	/**
	 * 获取表中字段的所有注释
	 * 
	 * @param tableName
	 * @return
	 */
	private List<String> getColumnComments(String tableName) {
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		List<String> columnComments = new ArrayList<String>();// 列名注释集合
		ResultSet rs = null;
		try {
			pStemt = conn.prepareStatement(tableSql);
			rs = pStemt.executeQuery("show full columns from " + tableName);
			while (rs.next()) {
				columnComments.add(rs.getString("Comment"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
 
				} catch (SQLException e) {
					e.printStackTrace();
					System.err.println("getColumnComments close ResultSet and connection failure");
				}
			}
		}
		return columnComments;
	}
 
	/**
	 * 
	 * <p>
	 * 备份表数据
	 * </p>
	 * 
	 * @param tableName
	 * @return
	 * @author （） 2018年9月6日 上午10:18:07
	 */
	private String bakTableData(String tableName) {
 
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
 
			// 备份建表语句
			String createTableSql = generateCreateTableSql(tableName);
			createTableSql = String.format(
					"\n\n\n/**\n * table name :<%s>\n *\n */\n%s\n",
					tableName, createTableSql);
			FileUtils.writeFileContent(filePath, createTableSql);
			// 获取字段类型
			List<String> columnTypes = getColumnTypes(tableName);
			// 获取所有 字段
			List<String> columnNames = getColumnNames(tableName);
			String columnArrayStr = null;
			for (String column : columnNames) {
				if (null == columnArrayStr) {
					columnArrayStr = "`" + column + "`";
				} else {
					columnArrayStr = columnArrayStr + "," + "`" + column + "`";
				}
			}
 
			String sql = String.format("select %s from %s", columnArrayStr, tableName);
 
			conn = getConnection();
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String rowValues = getRowValues(rs, columnNames.size(), columnTypes);
				// 返回建表语句语句，查询结果的第二列是建表语句，第一列是表名
				String insertSql = String.format("insert into %s (%s) values(%s);", tableName, columnArrayStr,
						rowValues);
				System.out.println(insertSql);
				insertSql = insertSql.replaceAll("\n", "<br/>");
				insertSql = insertSql + "\n";
				FileUtils.writeFileContent(filePath, insertSql);
			}
		} catch (Exception e) {
			e.printStackTrace();
 
			try {
				if (null != pstmt) {
					pstmt.close();
				}
 
			} catch (Exception e2) {
				e.printStackTrace();
				System.err.println("关闭流异常");
			}
		}
		return null;
	}
 
	/**
	 * 
	 * <p>
	 * 获取表数据一行的所有值
	 * </p>
	 * 
	 * @param rs
	 * @param size
	 * @author  2018年9月6日 上午11:03:05
	 */
	private String getRowValues(ResultSet rs, int size, List<String> columnTypeList) {
		try {
			String rowValues = null;
			for (int i = 1; i <= size; i++) {
				String columnValue = null;
 
				// 获取字段值
				columnValue = getValue(rs, i, columnTypeList.get(i - 1));
				// 如果是空值不添加单引号
				if (null != columnValue) {
					columnValue = "'" + columnValue + "'";
				}
				// 拼接字段值
				if (null == rowValues) {
					rowValues = columnValue;
				} else {
					rowValues = rowValues + "," + columnValue;
				}
			}
 
			return rowValues;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("获取表数据一行的所有值异常");
			return null;
		}
	}
 
	/**
	 * 
	 * <p>
	 * 根据类型获取字段值
	 * </p>
	 * 
	 * @param obj
	 * @return
	 * @author  2018年9月6日 上午11:16:00
	 */
	private String getValue(ResultSet resultSet, Integer index, String columnType) {
		try {
 
			if ("int".equals(columnType) || "INT".equals(columnType)) {
				// 整数
				Object intValue = resultSet.getObject(index);
 
				if (null == intValue) {
					return null;
				}
				return intValue + "";
			} else if ("bigint".equals(columnType) || "BIGINT".equals(columnType)) {
 
				// 长整形
				Object value = resultSet.getObject(index);
				if (null == value) {
					return null;
				}
				return value + "";
			} else if ("smallint".equals(columnType) || "SMALLINT".equals(columnType)) {
				// 整数
				Object value = resultSet.getObject(index);
				if (null == value) {
					return null;
				}
				return value + "";
			} else if ("tinyint".equals(columnType) || "TINYINT".equals(columnType)) {
				// 整数
				Object value = resultSet.getObject(index);
				if (null == value) {
					return null;
				}
				return value + "";
			} else if ("mediumint".equals(columnType) || "MEDIUMINT".equals(columnType)) {
				// 长整形
				Object value = resultSet.getObject(index);
				if (null == value) {
					return null;
				}
				return value + "";
			} else if ("integer".equals(columnType) || "INTEGER".equals(columnType)) {
				// 整数
				Object value = resultSet.getObject(index);
				if (null == value) {
					return null;
				}
				return value + "";
			} else if ("float".equals(columnType) || "FLOAT".equals(columnType)) {
 
				// 浮点数
				Object value = resultSet.getObject(index);
				if (null == value) {
					return null;
				}
				return value + "";
			} else if ("double".equals(columnType) || "DOUBLE".equals(columnType)) {
				// 浮点数
				Object value = resultSet.getObject(index);
				if (null == value) {
					return null;
				}
				return value + "";
			} else if ("decimal".equals(columnType) || "DECIMAL".equals(columnType)) {
				// 浮点数-金额类型
				BigDecimal value = resultSet.getBigDecimal(index);
				if (null == value) {
					return null;
				}
				return value.toString();
			} else if ("char".equals(columnType) || "CHAR".equals(columnType)) {
				// 字符串类型
				String value = resultSet.getString(index);
				return value;
			} else if ("varchar".equals(columnType) || "VARCHAR".equals(columnType)) {
				// 字符串类型
				String value = resultSet.getString(index);
				return value;
			} else if ("tinytext".equals(columnType) || "TINYTEXT".equals(columnType)) {
				// 字符串类型
				String value = resultSet.getString(index);
				return value;
			} else if ("text".equals(columnType) || "TEXT".equals(columnType)) {
				// 字符串类型
				String value = resultSet.getString(index);
				return value;
			} else if ("mediumtext".equals(columnType) || "MEDIUMTEXT".equals(columnType)) {
				// 字符串类型
				String value = resultSet.getString(index);
				return value;
			} else if ("longtext".equals(columnType) || "LONGTEXT".equals(columnType)) {
				// 字符串类型
				String value = resultSet.getString(index);
				return value;
			} else if ("year".equals(columnType) || "YEAR".equals(columnType)) {
				// 时间类型：范围 1901/2155 格式 YYYY
				String year = resultSet.getString(index);
				if (null == year) {
					return null;
				}
				// 只需要年的字符即可，
				return year.substring(0, 4);
			} else if ("date".equals(columnType) || "DATE".equals(columnType)) {
				// 时间类型：范围 '1000-01-01'--'9999-12-31' 格式 YYYY-MM-DD
				return resultSet.getString(index);
			} else if ("time".equals(columnType) || "TIME".equals(columnType)) {
				// 时间类型：范围 '-838:59:59'到'838:59:59' 格式 HH:MM:SS
				return resultSet.getString(index);
			} else if ("datetime".equals(columnType) || "DATETIME".equals(columnType)) {
				// 时间类型：范围 '1000-01-01 00:00:00'--'9999-12-31 23:59:59' 格式 YYYY-MM-DD HH:MM:SS
				return resultSet.getString(index);
			} else if ("timestamp".equals(columnType) || "TIMESTAMP".equals(columnType)) {
				// 时间类型：范围 1970-01-01 00:00:00/2037 年某时 格式 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
				return resultSet.getString(index);
			} else {
				return null;
			}
 
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("获取数据库类型值异常");
			return null;
		}
	}
 
	/**
	 * 
	 * <开始备份>
	 * 
	 * @author  2018年9月6日 下午3:30:43
	 */
	public void startBak() {
		try {
			List<String> tableNames = getTableNames();
			System.out.println("tableNames:" + tableNames);
			for (String tableName : tableNames) {
				bakTableData(tableName);
				// System.out.println(generateCreateTableSql(tableName));
				// System.out.println("ColumnNames:" + getColumnNames(tableName));
				// System.out.println("ColumnTypes:" + getColumnTypes(tableName));
				// System.out.println("ColumnComments:" + getColumnComments(tableName));
			}
			// 统一关闭连接
			closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
 
	public static void main(String[] args) {
		new BakDateBase("182.xxx.xxx.xxx", "xd_love_dev", "root", "woaini", "f:\\bak.sql").startBak();
	}
}

---

参考：https://blog.csdn.net/z1589714/article/details/104937335
（未验证）

package com.test.web.service;

/**
 * 
 * @Date: 2020/2/28 14:20
 * @Version: 1.0
 */
public class TestMysqlToOracle {
    public static final String dirver = "com.mysql.jdbc.Driver";
    public static final String useranem="root";
    public static final String password="root";
    public static final String type = "mysql";
    public static final String url="jdbc:mysql://XXXXXX/test";

    public static final String dirverR = "oracle.jdbc.OracleDriver";
    public static final String useranemR="root";
    public static final String passwordR="root";
    public static final String urlR = "jdbc:oracle:thin:@localhost:1522:orcl";
    public static final String typeR = "oracle";


    public static void main(String[] args) throws Exception{
        Connection conn = null;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        StringBuffer sql = new StringBuffer();
        Class.forName(dirver);
        conn = DriverManager.getConnection(url, useranem, password);
        DatabaseMetaData databaseMetaData = conn.getMetaData();
        ResultSet tables = databaseMetaData.getTables(null, null, "%", null);
        while (tables.next()) {
            new TestG().sync(tables.getString("TABLE_NAME"));
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> sync(String tableName) throws Exception {
        Map<String, Object> map = new HashMap<>();
        long start = System.currentTimeMillis();
//        生成建表sql
        Map<String, Object> sql = sql(tableName);
        Map<String, Object> columnMap = (Map<String, Object>) sql.get("map");
        Map<String, Object> sqlMap = (Map<String, Object>) sql.get("sql");
        String createTableSQL = (String) sqlMap.get("sql");

        System.out.println("-------获取表结构成功，建表sql生成成功-----");

        Boolean tableExit = false;

        createTable(dirverR, urlR, useranemR, passwordR , createTableSQL, tableName);
 
        return map;
    }

    // 获取建表sql
    public Map<String, Object> sql(String tableName) throws Exception {
        Map<String, Object> map = new HashMap<>();
        Map<String, Object> sqlMap = new HashMap<>();
        Connection conn = null;
        DatabaseMetaData metaData = null;
        ResultSet rs = null;
        ResultSet pt = null;
        String sql = null;
        boolean isExit = false;
        List<String> primaryKeyList = new ArrayList<>();
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            Class.forName(dirver);
            conn = DriverManager.getConnection(url, useranem,  password);
            String catalog = conn.getCatalog(); // catalog 是数据库名
            System.out.println("---------连接成功，数据库：" + catalog);
            metaData = conn.getMetaData();
            // 获取表
            rs = metaData.getColumns(null, null, tableName, null);
            Map<String, Object> dataMap = new HashMap<>();
            List<HashMap<String, Object>> rows = new ArrayList<HashMap<String, Object>>();
            // 获取信息
            while (rs.next()) {
                HashMap<String, Object> row = new HashMap<String, Object>();
                dataMap.put("TABLE_NAME", tableName);
                row.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));   //字段名
                row.put("TYPE_NAME", rs.getString("TYPE_NAME"));        //字段类型
                if ("DATETIME".equals(rs.getString("TYPE_NAME"))) {
                    row.put("COLUMN_SIZE", Integer.valueOf(0));                         //如果事dataTime类型修改为0，调试返回时19，创表失败
                } else {
                    row.put("COLUMN_SIZE", rs.getInt("COLUMN_SIZE"));
                }
                map.put(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"));
                row.put("NULLABLE", rs.getInt("NULLABLE") == 0 ? "NOT NULL" : " ");      //可否为null
                rows.add(row);
            }
            //主键
            pt = metaData.getPrimaryKeys(conn.getCatalog(), null, tableName);
            while (pt.next()) {
                primaryKeyList.add( pt.getString("COLUMN_NAME"));
            }
            dataMap.put("PRIMARYS",primaryKeyList);     //获取主键
            dataMap.put("rows", rows);
            sql = getSql(dataMap, tableName, typeR);
            sqlMap.put("sql", sql);
            Map<String, Object> all = new HashMap<>();
            all.put("map", map);
            all.put("sql", sqlMap);
            return all;
        } finally {
            if (null != pt) {
                pt.close();
            }
            if (null != rs) {
                rs.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

    //拼接建表sql
    public String getSql(Map<String, Object> map, String tableName, String typeR) {
        StringBuffer sb = new StringBuffer();
        List<String> PRIMARYS =(List<String>) map.get("PRIMARYS");


        sb.append("CREATE TABLE ").append(map.get("TABLE_NAME")).append(" (").append("\n");
        List<HashMap<String, Object>> rows = (List<HashMap<String, Object>>) map.get("rows");
        for (Map<String, Object> rowMap : rows) {
            if ("mysql".equals(typeR)){
                sb.append("`").append(rowMap.get("COLUMN_NAME") + "` ");
            }
            else {
                sb.append("   ").append(rowMap.get("COLUMN_NAME") + "  ");
            }
            sb.append(caseVale(rowMap.get("TYPE_NAME"), typeR));
            // sql server 除了varchar类型，其他都加大小
            if ("sql_server".equals(typeR)) {
                if ((caseVale(rowMap.get("TYPE_NAME"), typeR)).equals("VARCHAR")) {
                    sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
                }
                // sql server 不存在float和double,转换成decimal
                if ((caseVale(rowMap.get("TYPE_NAME"), typeR)).equals("DECIMAL")) {
                    sb.append("(12,4)");
                }
            }
            // mysql , dateTime和时间戳类型不需要大小
            else if ("mysql".equals(typeR)) {
                if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DATETIME") || caseVale(rowMap.get("TYPE_NAME"), typeR).equals("TIMESTAMP(6)")
                        || caseVale(rowMap.get("TYPE_NAME"), typeR).equals("LONGTEXT") || caseVale(rowMap.get("TYPE_NAME"), typeR).equals("TEXT")) {
//                        sb.append("(0)");
                } else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("CHAR(1)")) {
                } else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE") || caseVale(rowMap.get("TYPE_NAME"), typeR).equals("FLOAT")) {
                    sb.append("(12,4)");
                } else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("BIT")) {
                    sb.append("(1)");
                }
                else {
                    sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
                }
            } else {
                // 如果是oracle的date类型，不需要加大小
                if (!caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DATE") && !caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE")
                        && !caseVale(rowMap.get("TYPE_NAME"), typeR).equals("FLOAT")) {
                    if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE")) {
                        sb.append("(12,4)");
                    } else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("CHAR")) {
                        sb.append("(1)");
                    } else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("VARCHAR2")) {
                        sb.append("(255)");
                    }
                    else {
                        sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
                    }
                }
            }
            sb.append((rowMap.get("NULLABLE") == "" ? "" : " " + rowMap.get("NULLABLE")) + ",");
            sb.append("\n");
        }

        if (PRIMARYS.size() > 0) {
            sb.append("  PRIMARY KEY (");
        }
        for (int i = 0; i < PRIMARYS.size(); i++) {
            sb.append(PRIMARYS.get(i) + ",");
        }
        sb.deleteCharAt(sb.lastIndexOf(","));
        if (PRIMARYS.size() > 0) {
            sb.append(")\n");
        }
        sb.append(")");
        System.out.println("-----------建表语句\n" + sb.toString());
        return sb.toString();
    }

    //两方不同数据库，需要对类型进行转换
    public String caseVale(Object typeName, String type) {
        String typeClound = null;
        if ("mysql".equals(type)) {
            switch (((String) typeName).toUpperCase()) {
                case "NUMBER":
                    typeClound = "BIGINT";
                    break;
                case "DATE":
                    typeClound = "DATETIME";
                    break;
                case "VARCHAR2":
                    typeClound = "VARCHAR";
                    break;
                case "TIMESTAMP(6)":
                    typeClound = "DATETIME";
                    break;
                case "TIMESTAMP":
                    typeClound = "DATETIME";
                    break;
                default:
                    typeClound = (String) ((String) typeName).toUpperCase();
                    break;
            }
        } else if ("oracle".equals(type)) {
            switch (((String) typeName).toUpperCase()) {
                case "BIGINT":
                    typeClound = "NUMBER";
                    break;
                case "DATETIME":
                    typeClound = "DATE";
                    break;
                case "BIT":
                    typeClound = "CHAR";
                    break;
                case "TIMESTAMP(6)":
                    typeClound = "DATE";
                    break;
                case "TIMESTAMP":
                    typeClound = "DATE";
                    break;
                case "INT":
                    typeClound = "NUMBER";
                    break;
                case "FLOAT":
                    typeClound = "NUMBER";
                    break;
                case "DOUBLE":
                    typeClound = "NUMBER";
                    break;
                case "TEXT":
                    typeClound = "VARCHAR2";
                    break;
                case "SMALLINT":
                    typeClound = "NUMBER";
                    break;
                default:
                    typeClound = ((String) typeName).toUpperCase();
                    break;
            }
        } else {
            switch (((String) typeName).toUpperCase()) {
                case "NUMBER":
                    typeClound = "BIGINT";
                    break;
                case "DATE":
                    typeClound = "DATETIME";
                    break;
                case "CHAR":
                    typeClound = "BIT";
                    break;
                case "VARCHAR2":
                    typeClound = "VARCHAR";
                    break;
                case "TIMESTAMP(6)":
                    typeClound = "DATETIME";
                    break;
                case "FLOAT":
                    typeClound = "DECIMAL";
                    break;
                case "DOUBLE":
                    typeClound = "DECIMAL";
                    break;
                case "LONGTEXT":
                    typeClound = "TEXT";
                    break;
                case "BLOB":
                    typeClound = "IMAGE";
                    break;
                default:
                    typeClound = ((String) typeName).toUpperCase();
                    break;
            }
        }
        return typeClound;
    }

    //连接对方系统进行建表
    public boolean createTable(String dirverR, String urlR, String useranemR, String passwordR, String sql, String tableName) throws Exception {
        Connection remoteConn = null;
        PreparedStatement preparedStatement = null;
        Boolean bl = false;
        try {
            Class.forName(dirverR);
            remoteConn = DriverManager.getConnection(urlR, useranemR, passwordR);
            System.out.println("-------连接对方系统成功-------");
            if (!typeR.equals("oracle")) {
                if (exitsTable(dirverR, urlR, useranemR, passwordR, tableName)) {
                    return true;
                } else {
                    preparedStatement = remoteConn.prepareStatement(sql);
                    preparedStatement.execute();
                    return exitsTable(dirverR, urlR, useranemR, passwordR, tableName);
                }
            } else {
                if (exitsTable(dirverR, urlR, useranemR, passwordR, tableName)) {
                    return true;
                } else {
                    preparedStatement = remoteConn.prepareStatement(sql);
                    preparedStatement.execute();
                    return exitsTable(dirverR, urlR, useranemR, passwordR, tableName);
                }
            }
        } finally {
            if (preparedStatement != null) preparedStatement.close();
            if (remoteConn != null) remoteConn.close();
        }
    }

    //判断对方表是否存在
    public boolean exitsTable(String dirverR, String urlR, String useranemR, String passwordR, String tableName) throws Exception {
        Connection remoteConn = null;
        ResultSet rs = null;
        try {
            Class.forName(dirverR);
            remoteConn = DriverManager.getConnection(urlR, useranemR, passwordR);
            rs = remoteConn.getMetaData().getTables(null, null, tableName.toUpperCase(), null);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } finally {
            if (null != rs) rs.close();
            if (null != remoteConn) remoteConn.close();
        }
    }

    //获取insert模板
    public Map<String, Object> sqlTemplet(List<Map<String, Object>> list, String tableName) {
        List<String> fieldList = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        Map<String, Object> map = new HashMap<>();
        sb.append("insert into ").append(tableName).append(" (");
        for (Map.Entry<String, Object> entry : list.get(0).entrySet()) {
            sb.append(entry.getKey()).append(",");
            fieldList.add(entry.getKey());
        }
        sb.deleteCharAt(sb.lastIndexOf(",")).append(") values (");
        for (Map.Entry<String, Object> entry : list.get(0).entrySet()) {
            sb.append("?").append(",");
        }
        sb.deleteCharAt(sb.lastIndexOf(","));
        sb.append(")\n");
        System.out.println(sb.toString());
        map.put("fieldList", fieldList);
        map.put("sqlTemplet", sb.toString());
        return map;
    }
}
原文链接：https://blog.csdn.net/z1589714/article/details/104937335