package com.bcxin.autodownloadupload.common.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

@Slf4j
public class DatabaseUtil {

    public static Connection getConnection(String url, String username, String password) throws Exception {
        return DriverManager.getConnection(url, username, password);
    }

    public static void executeSql(Connection connection, String sql) throws Exception {
        Statement statement = null;
        try {
            connection.setAutoCommit(false);// 关闭自动提交
            statement = connection.createStatement();
            statement.execute(sql);
            connection.commit();  // 提交事务
        } catch (Exception e) {
            try {
                connection.rollback(); // 回滚事务
            } catch (SQLException excep) {
                excep.printStackTrace();
            }
            log.error("批量执行sql脚本出错，{}", e.getMessage(), e);
            throw e;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void batchExecuteSql(Connection connection, List<String> sqlList, int batchSize) throws Exception {
        Statement statement = null;
        try {
            connection.setAutoCommit(false);// 关闭自动提交
            statement = connection.createStatement();
            int count = 0;
            for (String sql : sqlList) {
                if (StringUtils.isBlank(sql)) {
                    continue;
                }
                statement.addBatch(sql);
                count++;

                if (count % batchSize == 0) {
                    int[] execCount = statement.executeBatch();
                    log.info("JDBC批量执行条数：{}", execCount.length);
                    statement.clearBatch();
                }
            }
            if (count % batchSize != 0) {
                int[] execCount = statement.executeBatch();
                log.info("JDBC批量执行条数：{}", execCount.length);
                statement.clearBatch();
            }
            connection.commit();  // 提交事务
        } catch (Exception e) {
            try {
                connection.rollback(); // 回滚事务
            } catch (SQLException excep) {
                excep.printStackTrace();
            }
            log.error("批量执行sql脚本出错，{}", e.getMessage(), e);
            throw e;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * description: 通过命令行执行sql脚本
     * author: linchunpeng
     * date:  2023-05-26 17:42
     */
    public static String execCommand(boolean isWindows, String ip, String port, String username, String password, String filePath) throws Exception {
        StringBuilder result = new StringBuilder("");
        Process process = null;
        InputStream ins = null;
        String command = String.format("mysql -h%s -P%s -u%s -p%s -D baiduutil -e \"source %s\" ", ip, port, username, password, filePath);
        log.info("通过命令行执行sql脚本，命令：{}", command);
        try {
            if (isWindows) {
                // Windows版本
                process = Runtime.getRuntime().exec(new String[]{"cmd", "/c", command});
            } else {
                // Linux版本
                process = Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c",command});
            }
            process.waitFor();
            ins = process.getErrorStream();
            byte[] b = new byte[100];
            int num = 0;
            try {
                while ((num = ins.read(b)) != -1) {
                    result.append(new String(b, "gb2312"));
                }
            } catch (IOException e) {
                log.error("获取命令行执行结果失败，filePath：{}，{}", filePath, e.getMessage(), e);
                throw e;
            }
        } catch (Exception e) {
            log.error("命令行执行脚本失败，filePath：{}，{}",  filePath, e.getMessage(), e);
            throw e;
        } finally {
            try {
                assert ins != null;
                ins.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            process.destroy();
        }
        return result.toString();
    }


    public static void main(String[] args) throws Exception {
        String result = execCommand(true, "127.0.0.1", "3306", "root", "123456", "D:\\data\\baidu\\push\\202305260944585482\\sql\\__KDXZvc8mnEmDqMARhK7GTLK_LEGALCHANGE_CHANGE___KDXZvc8mnEmDqMARhK7G.sql");
        log.info(result);
        if (result.contains("ERROR")) {
            result = result.substring(result.indexOf("ERROR"));
            log.info(result);
        }
    }

}