package com.zd.db;


import lombok.extern.slf4j.Slf4j;

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

@Slf4j
public class JdbcUtil {
    /**
     * 获取字段相关类:
     * 使用完记得关闭连接
     */
    public static ResultSetMetaData getResultSetMetaData(String tableName) {
        PreparedStatement preparedStatement = null;
        ResultSetMetaData resultSetMetaData = null;
        Connection connection = getConnection();
        try {
            preparedStatement = connection.prepareStatement("select * from " + tableName + " where 1=2");
            resultSetMetaData = preparedStatement.executeQuery().getMetaData();
        } catch (Exception e) {
            log.error("数据库连接异常", e);
        } finally {
            closePreparedStatement(preparedStatement);
            closeConnection(connection);
        }
        return resultSetMetaData;
    }


    /**
     * 创建表的新增记录触发器语句:
     * 使用完记得关闭连接
     */
    public static String getInsertTriggerSql(String tableName) {
        PreparedStatement preparedStatement = null;
        ResultSetMetaData resultSetMetaData = null;
        Connection connection = getConnection();
        String triggerColumns = "";
        String triggerOldValues = "";
        String triggerSql = "";
        try {
            preparedStatement = connection.prepareStatement("select * from " + tableName + " where 1=2");
            resultSetMetaData = preparedStatement.executeQuery().getMetaData();

            int columnCount = resultSetMetaData.getColumnCount();
            //System.out.println(tableName + "表中字段个数为：" + columnCount);

            for (int i = 1; i <= columnCount; i++) {
                String columnName = resultSetMetaData.getColumnName(i);
                triggerColumns += columnName + ",";
                triggerOldValues += "NEW." + columnName + ",";
            }

            triggerColumns = triggerColumns.substring(0, triggerColumns.length() - 1);
            triggerOldValues = triggerOldValues.substring(0, triggerOldValues.length() - 1);

            triggerSql = "create trigger " + tableName + "_bak " + "after insert on " + tableName + " for each row"
                    + " begin"
                    + " insert into " + tableName + "_history (" + triggerColumns + ") values (" + triggerOldValues + ");"
                    + " end";
        } catch (Exception e) {
            log.error("数据库连接异常", e);
        } finally {
            closePreparedStatement(preparedStatement);
            closeConnection(connection);
        }
        return triggerSql;
    }


    /**
     * 获取当前数据库下的所有表名称
     */
    public static List<String> getAllTableName() {
        List<String> tables = new ArrayList();
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SHOW TABLES ");
            while (resultSet.next()) {
                String tableName = resultSet.getString(1);
                tables.add(tableName);
            }
        } catch (SQLException e) {
            log.error("数据库连接异常", e);
        } finally {
            closeResultSet(resultSet);
            closeStatement(statement);
            closeConnection(connection);
        }


        return tables;
    }


    /**
     * 获得某表的建表语句
     */
    public static String getCreateTableDDL(String tableName) {
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        String createDDLSql = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SHOW CREATE TABLE " + tableName);
            if (resultSet != null && resultSet.next()) {
                createDDLSql = resultSet.getString(2);
            }
        } catch (SQLException e) {
            log.error("数据库连接异常", e);
        } finally {
            closeResultSet(resultSet);
            closeStatement(statement);
            closeConnection(connection);
        }
        return createDDLSql;
    }

    public static String getCreateTableHistoryDDL(String sourceCreateTableDDL, String sourceTableName, String targetTableName) {
        return sourceCreateTableDDL.replaceFirst(sourceTableName, targetTableName);
    }

    public static String getAlterColumnCreateTableDDl(String sourceCreateTableDDL, String addColumnDefine) {
        return null;
    }

    public static String getAddColumnCreateTableDDl(String sourceCreateTableDDL, String addColumnDefine) {
        StringBuilder sb = new StringBuilder();
        sb.append(sourceCreateTableDDL).insert(sourceCreateTableDDL.indexOf("(")+1, addColumnDefine);

        return sb.toString();
    }

    /**
     * 获得某表的注释
     */
    public static String getTableCommnet(String tableName) {
        String creatDDLSql = getCreateTableDDL(tableName);
        String comment = null;
        int index = creatDDLSql.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = creatDDLSql.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }


    /**
     * 获取表中字段的所有注释
     */
    public static List<String> getColumnComments(String tableName) {
        //与数据库的连接
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        String tableSql = "select * from " + tableName;
        //列名注释集合
        List<String> columnComments = new ArrayList<>();
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(tableSql);
            resultSet = preparedStatement.executeQuery("show full columns from " + tableName);
            while (resultSet.next()) {
                columnComments.add(resultSet.getString("Comment"));
            }
        } catch (SQLException e) {
            log.error("数据库连接异常", e);
        } finally {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
            closeConnection(connection);
        }
        return columnComments;
    }


    /**
     * 获取数据连接
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/testdb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true", "root", "root");
        } catch (Exception e) {
            log.error("get connection failure", e);
        }
        return connection;
    }

    /**
     * 关闭数据库连接
     */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("close connection failure", e);
            }
        }
    }

    public static void closePreparedStatement(PreparedStatement preparedStatement) {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                log.error("close preparedStatement failure ", e);
            }
        }
    }

    public static void closeResultSet(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                log.error("close resultSet failure ", e);
            }
        }
    }

    public static void closeStatement(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error("close statement failure ", e);
            }
        }
    }


    public static void main(String[] args) throws Exception{
        //System.out.println("获取所有数据库的表名" + getAllTableName());

        String tableName = "orders";
        String createTableDDL = getCreateTableDDL(tableName);
        System.out.println("获取建表语句：" + createTableDDL);

        String createTableHistoryDDL = getCreateTableHistoryDDL(createTableDDL, tableName, tableName + "_history");
        System.out.println("获取创建history表语句：" + createTableHistoryDDL);

        String addColumnCreateTableDDl = getAddColumnCreateTableDDl(createTableHistoryDDL.replaceFirst("AUTO_INCREMENT", "").replace("PRIMARY KEY","KEY `idx_key`"), "`rowId` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,");
        System.out.println("新增字段创建表ddl：" + addColumnCreateTableDDl);

        String insertTriggerSql = getInsertTriggerSql(tableName);
        System.out.println("获取表的insert触发器：" + insertTriggerSql);

        //System.out.println("获取表的备注：" + getTableCommnet(tableName));


        /*ResultSetMetaData resultSetMetaData = getResultSetMetaData(tableName);
        int columnCount = resultSetMetaData.getColumnCount();
        System.out.println(tableName + "表中字段个数为：" + columnCount);

        System.out.println("打印字段信息：");
        List<String> columnComments = getColumnComments(tableName);


        for (int i = 1; i <= columnCount; i++) {
            String columnClassName = resultSetMetaData.getColumnClassName(i);
            System.out.print("java类型：" + columnClassName);

            String columnTypeName = resultSetMetaData.getColumnTypeName(i).toLowerCase();
            System.out.print("  数据库类型:" + columnTypeName);

            String columnName = resultSetMetaData.getColumnName(i);
            System.out.print("  字段名称:" + columnName);

            int columnDisplaySize = resultSetMetaData.getColumnDisplaySize(i);
            System.out.print("  字段长度:" + columnDisplaySize);

            System.out.print(" notNull：" + (resultSetMetaData.isNullable(i) == 1 ? false : true));

            String columnComment = columnComments.get(i - 1);
            System.out.println(" 注释为：" + columnComment);
        }*/



    }

}

获取建表语句：CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ordertime` varchar(255) DEFAULT NULL,
  `total` double(5,2) DEFAULT NULL,
  `uid` int(11) NOT NULL DEFAULT '0',
  `des` varchar(80) DEFAULT '' COMMENT '描述',
  `data` varchar(10) CHARACTER SET utf8mb4 DEFAULT 'aaaa',
  `createdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
  `updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COMMENT='ZZZZZZ'

获取创建history表语句：CREATE TABLE `orders_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ordertime` varchar(255) DEFAULT NULL,
  `total` double(5,2) DEFAULT NULL,
  `uid` int(11) NOT NULL DEFAULT '0',
  `des` varchar(80) DEFAULT '' COMMENT '描述',
  `data` varchar(10) CHARACTER SET utf8mb4 DEFAULT 'aaaa',
  `createdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
  `updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COMMENT='ZZZZZZ'

新增字段创建表ddl:CREATE TABLE `orders_history` (`rowId` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id` int(11) NOT NULL ,
  `ordertime` varchar(255) DEFAULT NULL,
  `total` double(5,2) DEFAULT NULL,
  `uid` int(11) NOT NULL DEFAULT '0',
  `des` varchar(80) DEFAULT '' COMMENT '描述',
  `data` varchar(10) CHARACTER SET utf8mb4 DEFAULT 'aaaa',
  `createdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
  `updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_key` (`id`),
  KEY `uid` (`uid`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COMMENT='ZZZZZZ'

获取表的insert触发器：
create trigger orders_bak after insert on orders for each row begin insert into orders_history (id,ordertime,total,uid,des,data,createdate,updatetime) values (NEW.id,NEW.ordertime,NEW.total,NEW.uid,NEW.des,NEW.data,NEW.createdate,NEW.updatetime); end

打印字段信息：
orders表中字段个数为：8

Process finished with exit code 0