package read.mysql.demo;

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


/**
 *  获取数据库表结构，字段，字段类型，字段注释
 */

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/guan";
    private static final String USER = "root";
    private static final String Password = "admin";


    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    public static Connection getConnection() {

        Connection connect = null;
        try {
            connect = DriverManager.getConnection(URL, USER, Password);

        } catch (Exception e) {
            e.printStackTrace();
        }

        return connect;
    }

    /**
     * 关闭连接
     *
     * @param connect
     */

    public static void closeConnection(Connection connect) {

        if (connect != null) {
            try {
                connect.close();
            } catch (SQLException e) {
                System.out.println("数据库连接错误");
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取数据库下的所有表名
     *
     * @return
     */

    public static List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;

        try {
//            获取数据库元数据信息
            DatabaseMetaData metaData = conn.getMetaData();


            System.out.println(metaData);

//            从元数据中获取所有的表名
            rs = metaData.getTables(null, null, null
                    , new String[]{"TABLE"});

            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                rs.close();
                closeConnection(conn);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return tableNames;
    }


    /**
     * 获取表中所有字段名称
     *
     * @param tableName 表名
     * @throws Exception
     */


    public static List<String> getColumnNames(String tableName) {

        ArrayList<String> columnNames = new ArrayList<>();

        PreparedStatement pst = null;

        String tableSql = "SELECT * FROM " + tableName;

        Connection connection = getConnection();

        try {
            pst = connection.prepareStatement(tableSql);

            ResultSetMetaData rsmd = pst.getMetaData();

            int size = rsmd.getColumnCount();

            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (pst != null) {

                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                closeConnection(connection);
            }
        }

        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName
     * @return
     */

    public static List<String> getColumnTypes(String tableName) {

        ArrayList<String> columnTyps = new ArrayList<>();
//        与数据库的连接
        Connection connection = getConnection();
        PreparedStatement pst = null;

        String sqlStr = "SELECT * FROM " + tableName;
        try {

            pst = connection.prepareStatement(sqlStr);
//          结果集数据
            ResultSetMetaData metaData = pst.getMetaData();
//          表列表
            int size = metaData.getColumnCount();

            for (int i = 0; i < size; i++) {
                columnTyps.add(metaData.getColumnTypeName(i + 1));
            }


        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (pst != null) {

                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                closeConnection(connection);
            }
        }

        return columnTyps;
    }

    public static List<String> getColumnComments(String tableName) {
        ArrayList<String> columnComments = new ArrayList<>();


        Connection connection = getConnection();
        PreparedStatement pst = null;
        ResultSet rs =null;
        String sqlStr = "SELECT * FROM " + tableName;
        try {

            pst = connection.prepareStatement(sqlStr);
//          结果集数据

             rs = pst.executeQuery("show FULL COLUMNS FROM " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }


        } catch (SQLException e) {
            e.printStackTrace();

        } finally {

            if (pst != null) {

                try {
                    rs.close();
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                closeConnection(connection);
            }
        }

        return columnComments;

    }


    public static void main(String[] args) {

        List<String> tableNames = getTableNames();
        System.out.println("tableNames = " + tableNames);
        
        for (String tableName : tableNames) {
            System.out.println("ColumnNames :" + getColumnNames(tableName));
            System.out.println("ColumnTypes :" + getColumnTypes(tableName));
            System.out.println("ColumnComments :" + getColumnComments(tableName));
        }

    }
}