数据迁移小工具

package com.sinosoft.migration.util;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@Component
public class DbUtil {

    public void mig_data() throws ClassNotFoundException, SQLException {
        log.info("进入DbUtil类~");

        //查询库信息
        String URL = "jdbc:mysql://127.0.0.1:3306/mytest?serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true&autoReconnect=true&autoReconnectForPools=true&useCompression=true";
        String USER = "root";
        String PASSWORD = "123456";

        //插入库信息
        String URL1 = "jdbc:mysql://127.0.0.1:3306/mytest1?serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true&autoReconnect=true&autoReconnectForPools=true&useCompression=true";
        String USER1 = "root";
        String PASSWORD1 = "123456";


        //1.加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 获得数据库连接
        log.info("驱动已加载完~");
        Connection source = DriverManager.getConnection(URL, USER, PASSWORD);
        Connection target = DriverManager.getConnection(URL1, USER1, PASSWORD1);
        long start = System.currentTimeMillis();
        target.setAutoCommit(false);
        String selectSql = "SELECT  family_number,family_members_number,relationship_with_master,create_time,create_operator,modify_time,modify_operator,effective_flag FROM family_members_number";
        PreparedStatement preparedStatement = source.prepareStatement(selectSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        log.info("每次查询20条~");
        preparedStatement.setFetchSize(Integer.MIN_VALUE);
        preparedStatement.setFetchDirection(ResultSet.FETCH_REVERSE);
        log.info("log~");
        ResultSet rs = preparedStatement.executeQuery();
        log.info("log1~");
        rs.setFetchSize(20);
        log.info("log2~");
        ResultSetMetaData md = rs.getMetaData();
        log.info("log3~");
        Map<String, Integer> colTypes = new HashMap<>();// 字段类型
        log.info("log4~");
        /**
         * 拼接出 insert into tablename values(?, ?, ?, ......, ? )
         * 为接下来拼接SQL做准备
         */
        int columnCount = md.getColumnCount();
        log.info(String.valueOf(columnCount));
        StringBuilder stringBuilder = new StringBuilder();
        for (int j = 1; j <= columnCount; j++) {
            String columnName = md.getColumnName(j); // 列名
            String columnLabel = md.getColumnLabel(j);// 别名 两者通常相等
            if (!"".equals(columnLabel)) {
                stringBuilder.append(columnLabel).append(",");
            } else if (!"".equals(columnName)) {
                stringBuilder.append(columnName).append(",");
            }
        }
        String columnList = stringBuilder.substring(0, stringBuilder.toString().length() - 1);
        String tableName = "family_members_number";
        log.info(columnList);
        StringBuilder insertSql = new StringBuilder("insert into " + tableName + "(" + columnList + ")" + " values (");
        System.out.println(insertSql);
        for (int i = 1; i <= columnCount; i++) {
            insertSql.append("?,"); // 占位符 绑定变量，防止SQL注入攻击
            colTypes.put(Integer.toString(i), md.getColumnType(i)); // 存储每个字段的数据类型
        }
        String substring = insertSql.substring(0, insertSql.toString().length() - 1);
        insertSql = new StringBuilder(substring);
        insertSql.append(")");
        log.info(String.valueOf(insertSql));

        PreparedStatement pstmt = target.prepareStatement(insertSql.toString());
        long time = System.currentTimeMillis();
        int batchCount = 0; // 用于记录当前批中的数据量，每3条提交一次
        int sumCount = 0; //用于记录当前select 操作查询到的总数据量
        int count = 1;
        while (rs.next()) {
            for (int i = 1; i <= columnCount; i++) {
                Integer type = colTypes.get(Integer.toString(i));
                switch (type) {
                    case Types.DATE: // 处理日期格式
                        pstmt.setDate(i, rs.getDate(i));
                        break;
                    case Types.DOUBLE:
                        pstmt.setDouble(i, rs.getDouble(i));
                        break;
                    case Types.INTEGER:
                        pstmt.setInt(i, rs.getInt(i));
                        break;
                    case Types.FLOAT:
                        pstmt.setFloat(i, rs.getFloat(i));
                        break;
                    case Types.DECIMAL: // 处理小数格式
                        pstmt.setBigDecimal(i, rs.getBigDecimal(i));
                        break;
                    default: // 处理默认类型 字符串
                        pstmt.setString(i, rs.getString(i));
                }
            }
            pstmt.addBatch(); // 添加批量执行
            batchCount++;
            sumCount++;
            if (batchCount == 20) {
                try {
                    System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");
                    log.info("准备提交20条");
                    long T1 = System.currentTimeMillis();// 开始计时
                    pstmt.executeBatch();
                    target.commit();
                    pstmt.clearBatch(); // 清空当前队列，预备下次提交数据
                    batchCount = 0;
                    long C1 = System.currentTimeMillis();// 开始计时
                    log.info("第" + count + "次20条提交耗时长：{}", (C1 - T1));
                    log.info("第" + count + "次20条处理耗时长为：{}", (C1 - time));
                    time = C1;
                    count++;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        if (batchCount > 0) { // 用于处理最后的记录不满20的数据提交
            try {
                long T1 = System.currentTimeMillis();// 开始计时
                pstmt.executeBatch();
                target.commit();
                pstmt.clearBatch(); // 清空当前队列，预备下次提交数据
                batchCount = 0;
                long C1 = System.currentTimeMillis();// 开始计时
                log.info("不满20条提交耗时长：{}", (C1 - T1));
                log.info("不满20条处理耗时长为：{}", (C1 - time));
                long end = System.currentTimeMillis();
                log.info("表:{}提取完成，总量为:{},总耗时:{}s", tableName, sumCount, (end - start) / 1000);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}