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

爱被打了一巴掌 2021-12-14 14:49 756阅读 0赞

转自：  [https://www.cnblogs.com/zuidongfeng/p/7895090.html][https_www.cnblogs.com_zuidongfeng_p_7895090.html]

[动态获取数据库表中的字段名][Link 1]: [https://www.cnblogs.com/airycode/p/5280515.html][Link 1]

获取数据库表字段名，长度，类型，备注等信息: [https://blog.csdn.net/weixin\_42686556/article/details/82739595][https_blog.csdn.net_weixin_42686556_article_details_82739595]

获取数据库表字段: [https://blog.csdn.net/qq\_33234164/article/details/79615912][https_blog.csdn.net_qq_33234164_article_details_79615912]

java如何获得数据库表中各字段的字段名: [https://zhidao.baidu.com/question/540137701.html][https_zhidao.baidu.com_question_540137701.html]

JDBC获取数据库表中字段详细信息: [https://my.oschina.net/marjeylee/blog/787006][https_my.oschina.net_marjeylee_blog_787006]

[JAVA获取MYSQL数据库表、字段、字段类型、字段注释][JAVA_MYSQL]:  [https://www.cnblogs.com/sunBinary/p/10412622.html][JAVA_MYSQL]

[如何动态获取数据库表中的数据，数据库中的字段是在变化的][Link 2]: [https://www.cnblogs.com/airycode/p/4828251.html][Link 2]

[java如何获得数据库表中各字段的字段名][java]: [https://www.cnblogs.com/ydymz/p/8329551.html][java]

jsp访问数据库（完整总结）: [https://blog.csdn.net/pangqiandou/article/details/52888485][https_blog.csdn.net_pangqiandou_article_details_52888485]

mysql+jsp，在jsp页面连接了数据库，已成功获取数据库字段，请问如何取到当前用户对应的信息:

[JAVA获取MYSQL数据库表、字段、字段类型、字段注释][https_www.cnblogs.com_zuidongfeng_p_7895090.html]: [https://www.cnblogs.com/zuidongfeng/p/7895090.html][https_www.cnblogs.com_zuidongfeng_p_7895090.html]

数据库连接通过getcolumns（）函数获取表字段信息: [https://blog.csdn.net/xiaofeng6072/article/details/76850391][https_blog.csdn.net_xiaofeng6072_article_details_76850391]

[JAVA获取MYSQL数据库表、字段、字段类型、字段注释][JAVA_MYSQL]: [https://www.cnblogs.com/sunBinary/p/10412622.html][JAVA_MYSQL]

获取oracle数据库的表名、字段名等系统信息: [https://blog.csdn.net/chenwunan/article/details/82664840][https_blog.csdn.net_chenwunan_article_details_82664840]

[java如何获得数据库表中各字段的字段名][java]: [https://www.cnblogs.com/ydymz/p/8329551.html][java]

最近想要写一个根据数据库表结构生成实体、mapper接口、mapping映射文件、service类的简单代码生成工具，所以查阅了一些资料，怎样获取数据库的表、表中字段、字段类型、字段注释等信息。

最后写了一个简单的工具类，代码如下：[https://blog.csdn.net/xipinghuan2106/article/details/53980831][https_blog.csdn.net_xipinghuan2106_article_details_53980831]

![复制代码][copycode.gif]

package com.lnjecit.generator.util;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    public class DatabaseUtil {
        private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class);
    
        private static final String DRIVER = "com.mysql.jdbc.Driver";
        private static final String URL = "jdbc:mysql://localhost:3306/javaweb?useUnicode=true&characterEncoding=utf8";
        private static final String USERNAME = "root";
        private static final String PASSWORD = "mysql";
    
        private static final String SQL = "SELECT * FROM ";// 数据库操作
    
        static {
            try {
                Class.forName(DRIVER);
            } catch (ClassNotFoundException e) {
                LOGGER.error("can not load jdbc driver", e);
            }
        }
    
        /**
         * 获取数据库连接
         *
         * @return
         */
        public static Connection getConnection() {
            Connection conn = null;
            try {
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            } catch (SQLException e) {
                LOGGER.error("get connection failure", e);
            }
            return conn;
        }
    
        /**
         * 关闭数据库连接
         * @param conn
         */
        public static void closeConnection(Connection conn) {
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    LOGGER.error("close connection failure", e);
                }
            }
        }
    
        /**
         * 获取数据库下的所有表名
         */
        public static List<String> getTableNames() {
            List<String> tableNames = new ArrayList<>();
            Connection conn = getConnection();
            ResultSet rs = null;
            try {
                //获取数据库的元数据
                DatabaseMetaData db = conn.getMetaData();
                //从元数据中获取到所有的表名
                rs = db.getTables(null, null, null, new String[] { "TABLE" });
                while(rs.next()) {
                    tableNames.add(rs.getString(3));
                }
            } catch (SQLException e) {
                LOGGER.error("getTableNames failure", e);
            } finally {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("close ResultSet failure", e);
                }
            }
            return tableNames;
        }
    
        /**
         * 获取表中所有字段名称
         * @param tableName 表名
         * @return
         */
        public static List<String> getColumnNames(String tableName) {
            List<String> columnNames = new ArrayList<>();
            //与数据库的连接
            Connection conn = getConnection();
            PreparedStatement pStemt = null;
            String tableSql = SQL + tableName;
            try {
                pStemt = conn.prepareStatement(tableSql);
                //结果集元数据
                ResultSetMetaData rsmd = pStemt.getMetaData();
                //表列数
                int size = rsmd.getColumnCount();
                for (int i = 0; i < size; i++) {
                    columnNames.add(rsmd.getColumnName(i + 1));
                }
            } catch (SQLException e) {
                LOGGER.error("getColumnNames failure", e);
            } finally {
                if (pStemt != null) {
                    try {
                        pStemt.close();
                        closeConnection(conn);
                    } catch (SQLException e) {
                        LOGGER.error("getColumnNames close pstem and connection failure", e);
                    }
                }
            }
            return columnNames;
        }
    
        /**
         * 获取表中所有字段类型
         * @param tableName
         * @return
         */
        public static List<String> getColumnTypes(String tableName) {
            List<String> columnTypes = new ArrayList<>();
            //与数据库的连接
            Connection conn = getConnection();
            PreparedStatement pStemt = null;
            String tableSql = SQL + tableName;
            try {
                pStemt = conn.prepareStatement(tableSql);
                //结果集元数据
                ResultSetMetaData rsmd = pStemt.getMetaData();
                //表列数
                int size = rsmd.getColumnCount();
                for (int i = 0; i < size; i++) {
                    columnTypes.add(rsmd.getColumnTypeName(i + 1));
                }
            } catch (SQLException e) {
                LOGGER.error("getColumnTypes failure", e);
            } finally {
                if (pStemt != null) {
                    try {
                        pStemt.close();
                        closeConnection(conn);
                    } catch (SQLException e) {
                        LOGGER.error("getColumnTypes close pstem and connection failure", e);
                    }
                }
            }
            return columnTypes;
        }
    
        /**
         * 获取表中字段的所有注释
         * @param tableName
         * @return
         */
        public static List<String> getColumnComments(String tableName) {
            List<String> columnTypes = new ArrayList<>();
            //与数据库的连接
            Connection conn = getConnection();
            PreparedStatement pStemt = null;
            String tableSql = SQL + tableName;
            List<String> columnComments = new ArrayList<>();//列名注释集合
            ResultSet rs = null;
            try {
                pStemt = conn.prepareStatement(tableSql);
                rs = pStemt.executeQuery("show full columns from " + tableName);
                while (rs.next()) {
                    columnComments.add(rs.getString("Comment"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                        closeConnection(conn);
                    } catch (SQLException e) {
                        LOGGER.error("getColumnComments close ResultSet and connection failure", e);
                    }
                }
            }
            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));
            }
        }
    }

[https_www.cnblogs.com_zuidongfeng_p_7895090.html]: https://www.cnblogs.com/zuidongfeng/p/7895090.html
[Link 1]: https://www.cnblogs.com/airycode/p/5280515.html
[https_blog.csdn.net_weixin_42686556_article_details_82739595]: https://blog.csdn.net/weixin_42686556/article/details/82739595
[https_blog.csdn.net_qq_33234164_article_details_79615912]: https://blog.csdn.net/qq_33234164/article/details/79615912
[https_zhidao.baidu.com_question_540137701.html]: https://zhidao.baidu.com/question/540137701.html
[https_my.oschina.net_marjeylee_blog_787006]: https://my.oschina.net/marjeylee/blog/787006
[JAVA_MYSQL]: https://www.cnblogs.com/sunBinary/p/10412622.html
[Link 2]: https://www.cnblogs.com/airycode/p/4828251.html
[java]: https://www.cnblogs.com/ydymz/p/8329551.html
[https_blog.csdn.net_pangqiandou_article_details_52888485]: https://blog.csdn.net/pangqiandou/article/details/52888485
[https_blog.csdn.net_xiaofeng6072_article_details_76850391]: https://blog.csdn.net/xiaofeng6072/article/details/76850391
[https_blog.csdn.net_chenwunan_article_details_82664840]: https://blog.csdn.net/chenwunan/article/details/82664840
[https_blog.csdn.net_xipinghuan2106_article_details_53980831]: https://blog.csdn.net/xipinghuan2106/article/details/53980831
[copycode.gif]: /images/20211214/b8ea57bfe6f84d45b3be11771bbdc8bd.png