JAVA获取MYSQL数据库表、字段、字段类型、字段注释 2.0

2020-06-29

126

2020-06-29 06:07:50

原创

基于[【JAVA获取MYSQL数据库表、字段、字段类型、字段注释】](https://hyuga.top/blog/142/)做了下优化。

## CODE
```
public final class MysqlDatabaseOperator {

private final static Logger LOGGER = LoggerFactory.getLogger(MysqlDatabaseOperator.class);

private String url;
    private String username;
    private String password;

private Connection conn;

/**
     * 数据库操作
     */
    private static final String SELECT_SQL = "SELECT * FROM %s";
    private static final String SHOW_CREATE_TABLE = "SHOW CREATE TABLE %s";
    private static final String SHOW_FULL_COLUMNS = "show full columns from %s";

/**
     * 初始化操作实体
     *
     * @param url      mysql链接url
     * @param username 账号
     * @param password 密码
     * @return 操作者实体
     */
    public static MysqlDatabaseOperator init(String url, String username, String password) {
        try {
            String driver = "com.mysql.cj.jdbc.Driver";
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            LOGGER.error("can not load jdbc driver", e);
        }
        return new MysqlDatabaseOperator(url, username, password);
    }

/**
     * 私有操作者实体构造函数
     *
     * @param url      url
     * @param username username
     * @param password password
     */
    private MysqlDatabaseOperator(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
    }

/**
     * 获取数据库连接
     */
    private Connection getConnection() {
        try {
            conn = DriverManager.getConnection(url, username, password);
            LOGGER.info("mysql连接池已开启.");
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("get connection failure", e);
        }
        return conn;
    }

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

/**
     * 获得某表的建表语句
     *
     * @param tableNames 表名集合
     * @return 表名注释
     * @throws Exception exception
     */
    public Map<String, String> getCommentByTableName(List<String> tableNames) throws Exception {
        Map<String, String> map = MapUtil.NEW();
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        for (String tableName : tableNames) {
            ResultSet rs = stmt.executeQuery(String.format(SHOW_CREATE_TABLE, tableName));
            if (rs != null && rs.next()) {
                String createDdl = rs.getString(2);
                String comment = getTableCommentForCreateDdl(createDdl);
                map.put(tableName, comment);
            }
            if (rs != null) {
                rs.close();
            }
        }
        stmt.close();
        conn.close();
        return map;
    }

/**
     * 根据表DDL获取表注释信息
     */
    private String getTableCommentForCreateDdl(String createDdl) {
        String comment;
        int index = createDdl.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = createDdl.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }

/**
     * 获取数据库下的所有表名
     */
    public List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables(conn.getCatalog(), null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("getTableNames failure", e);
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
                LOGGER.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }

/**
     * 获取表中所有字段名称
     *
     * @param tableName 表名
     */
    public List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement statement = null;
        String tableSql = String.format(SELECT_SQL, tableName);
        try {
            statement = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData metaData = statement.getMetaData();
            //表列数
            int size = metaData.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(metaData.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("getColumnNames failure", e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    closeConnection();
                } catch (SQLException e) {
                    e.printStackTrace();
                    LOGGER.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return columnNames;
    }

/**
     * 获取表中所有字段类型
     */
    public List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement statement = null;
        String tableSql = String.format(SELECT_SQL, tableName);
        try {
            statement = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData metaData = statement.getMetaData();
            //表列数
            int size = metaData.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(metaData.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("getColumnTypes failure", e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    closeConnection();
                } catch (SQLException e) {
                    e.printStackTrace();
                    LOGGER.error("getColumnTypes close pstem and connection failure", e);
                }
            }
        }
        return columnTypes;
    }

/**
     * 获取表中字段的所有注释
     */
    @SuppressWarnings("all")
    public List<String> getColumnComments(String tableName) {
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt;
        String tableSql = String.format(SELECT_SQL, tableName);
        //列名注释集合
        List<String> columnComments = new ArrayList<>();
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery(String.format(SHOW_FULL_COLUMNS, tableName));
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection();
                } catch (SQLException e) {
                    e.printStackTrace();
                    LOGGER.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return columnComments;
    }

/**
     * 获取某张表的所有字段属性
     *
     * @param tableName 表名
     * @return 所有字段属性对象
     */
    public List<MysqlField> getFieldsByTableName(String tableName) {
        List<MysqlField> fields = ListUtil.NEW();

//与数据库的连接
        Connection conn = getConnection();
        PreparedStatement statement = null;
        String tableSql = String.format(SELECT_SQL, tableName);
        try {
            List<String> columnsComment = getColumnsComment(tableName, conn, tableSql);
            statement = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData metaData = statement.getMetaData();
            //表列数
            int size = metaData.getColumnCount();
            for (int i = 1; i < size - 1; i++) {
                MysqlField field = new MysqlField();
                field.setIsAutoIncrement(metaData.isAutoIncrement(i));
                field.setIsCaseSensitive(metaData.isCaseSensitive(i));
                field.setIsSearchable(metaData.isSearchable(i));
                field.setIsCurrency(metaData.isCurrency(i));
                field.setIsNullable(metaData.isNullable(i));
                field.setIsSigned(metaData.isSigned(i));
                field.setColumnDisplaySize(metaData.getColumnDisplaySize(i));
                field.setColumnLabel(metaData.getColumnLabel(i));
                field.setColumnName(metaData.getColumnName(i));
                field.setSchemaName(metaData.getSchemaName(i));
                field.setPrecision(metaData.getPrecision(i));
                field.setScale(metaData.getScale(i));
                field.setTableName(metaData.getTableName(i));
                field.setCatalogName(metaData.getCatalogName(i));
                field.setColumnType(metaData.getColumnType(i));
                field.setColumnTypeName(metaData.getColumnTypeName(i));
                field.setIsReadOnly(metaData.isReadOnly(i));
                field.setIsWritable(metaData.isWritable(i));
                field.setIsDefinitelyWritable(metaData.isDefinitelyWritable(i));
                field.setColumnClassName(metaData.getColumnClassName(i));

field.setComment(columnsComment.get(i - 1));
                fields.add(field);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("getColumnNames failure", e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    closeConnection();
                } catch (SQLException e) {
                    e.printStackTrace();
                    LOGGER.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return fields;
    }

/**
     * 获取某张表的所有字段注释
     *
     * @param tableName 表名
     * @param conn      连接对象
     * @param tableSql  sql
     * @return 所有字段注释
     * @throws SQLException SQLException
     */
    private List<String> getColumnsComment(String tableName, Connection conn, String tableSql) throws SQLException {
        List<String> comments = ListUtil.NEW();
        PreparedStatement statement = conn.prepareStatement(tableSql);
        ResultSet rs = statement.executeQuery(String.format(SHOW_FULL_COLUMNS, tableName));
        while (rs.next()) {
            comments.add(rs.getString("Comment"));

}
        return comments;
    }

}
```

## DEMO
```
public class DatabaseTest {

private static final String username = "root";
    private static final String password = "123456";

public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql:replication://localhost:3306,localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8";
        MysqlDatabaseOperator mysqlDatabaseOperator = MysqlDatabaseOperator.init(url, username, password);
        // 获取表所有列名
        List<String> t_garden_basic_columnNames = mysqlDatabaseOperator.getColumnNames("t_garden_basic");
        t_garden_basic_columnNames.forEach(bean -> System.out.println(StringUtil.formatString(bean)));

// 获取表所有列类型
        List<String> t_garden_basic_columnTypes = mysqlDatabaseOperator.getColumnTypes("t_garden_basic");
        t_garden_basic_columnTypes.forEach(bean -> System.out.println(StringUtil.formatString(bean)));

// 获取表所有列注释
        List<String> t_garden_basic_columnComments = mysqlDatabaseOperator.getColumnComments("t_garden_basic");
        t_garden_basic_columnComments.forEach(bean -> System.out.println(StringUtil.formatString(bean)));

// 获取所有表名
        List<String> tableNames = mysqlDatabaseOperator.getTableNames();
        tableNames.forEach(bean -> System.out.println(StringUtil.formatString(bean)));

// 获取表名对应的表注释
        Map<String, String> tableComment = mysqlDatabaseOperator.getCommentByTableName(ListUtil.NEW("t_garden_basic"));
        tableComment.forEach((k, v) -> System.out.println(k + "," + v));

// 获取表所有字段的详细对象
        List<MysqlField> t_garden_basic = mysqlDatabaseOperator.getFieldsByTableName("t_garden_basic");
        t_garden_basic.forEach(bean -> System.out.println(StringUtil.formatString(bean)));

mysqlDatabaseOperator.closeConnection();
    }
}
```

## PS
比之前那个简化了些，方便使用！

JAVA MySQL

作者：hyuga（联系作者）

发表时间：2020-06-29 06:06

JAVA获取MYSQL数据库表、字段、字段类型、字段注释 2.0

评论