Java获取mysql数据库表信息代码

ps酷教程

已于 2024-07-28 21:23:10 修改

阅读量134

点赞数 3

分类专栏： # 工具类文章标签：数据库 java mysql

于 2024-07-28 19:56:44 首次发布

本文链接：https://blog.csdn.net/qq_16992475/article/details/140755437

版权

工具类专栏收录该内容

19 篇文章 0 订阅

订阅专栏

Java中使用JDBC获取MySQL数据库的名称、表名和字段名及相关信息
 原生Jdbc获取库、表、字段

代码

import java.sql.*;
import java.util.Objects;

public class MysqlDemo {

    // 1.加载JDBC驱动
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败：" + e);
        }
    }

    // 2.获取连接
    public static Connection getConnection(String url, String userName, String password) {
        Connection connection = null;
        try {
            // 创建连接
            connection = DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            System.out.println("获取连接失败：" + e);
        }
        return connection;
    }

    // 3. 获取数据库列表名称
    public static void getDataBase(String url, String userName, String password) {
        DatabaseMetaData databaseMetaData;
        ResultSet catalogResultSet = null;
        try (Connection connection = getConnection(url, userName, password)) {
            databaseMetaData = connection.getMetaData();
            catalogResultSet = databaseMetaData.getCatalogs();
            while (catalogResultSet.next()) {
                String databaseName = catalogResultSet.getString(1);
                // 去除系统表
                if ("information_schema".equals(databaseName)
                        || "mysql".equals(databaseName)
                        || "performance_schema".equals(databaseName)
                        || "sys".equals(databaseName)) {
                    continue;
                }
                System.out.println("DataBase : " + databaseName);
            }
        } catch (SQLException e) {
            System.out.println("获取数据库名称失败：" + e);
        } finally {
            close(catalogResultSet);
        }
    }

    // 4.获取数据库表名称
    public static void getTables(String url, String userName, String password) {
        // 1、连接数据库， 控制台打印出这个数据库里面所有的表
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password)) {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            // 可用可不用
            String[] types = {"TABLE"};
            resultSet = databaseMetaData.getTables(connection.getCatalog(), null, "%", types);
            while (resultSet.next()) {
                System.out.println("database : " + resultSet.getString(1) + " ==== table : " + resultSet.getString(3));
            }
        } catch (SQLException e) {
            System.out.println("获取数据库所有表名失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 5. 获取表的字段及类型
    public static void getColumns(String tableName, String url, String userName, String password) {
        String query = "SELECT * FROM " + tableName;
        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query)) {
            ResultSetMetaData resultSetMetaData = statement.getMetaData();
            System.out.println("table : " + tableName);
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                System.out.println("ColumnName : " + resultSetMetaData.getColumnName(i + 1));
                System.out.println("ColumnTypeName : " + resultSetMetaData.getColumnTypeName(i + 1));
                // 获取字段注释
                DatabaseMetaData dbmd = connection.getMetaData();
                ResultSet columnResultSet = dbmd.getColumns(null, null, tableName, resultSetMetaData.getColumnLabel(i + 1));
                if (columnResultSet.next()) {
                    String columnComment = columnResultSet.getString("REMARKS");
                    System.out.println("columnComment : " + columnComment);
                } else {
                    System.out.println("columnComment : " + "");
                }
            }
        } catch (SQLException e) {
            System.out.println("获取数据库表字段和类型失败：" + e);
        }
    }

    // 6.获取表字段对应的内容
    public static void getRows(String tableName, String url, String userName, String password) {
        String query = "SELECT * FROM " + tableName + " LIMIT 0, 10";
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query)) {
            ResultSetMetaData resultSetMetaData = statement.getMetaData();
            resultSet = statement.executeQuery();
            System.out.println("table : " + tableName);
            while (resultSet.next()) {
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    System.out.println(resultSetMetaData.getColumnName(i + 1) + ": " + resultSet.getString(i + 1));
                }
            }
        } catch (SQLException e) {
            System.out.println("获取数据库表字段对应内容失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 7. 获取数据库相关信息
    public static void getDataBaseInfo(String url, String userName, String password) {
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password)) {
            DatabaseMetaData dbmd = connection.getMetaData();
            System.out.println("数据库已知的用户: " + dbmd.getUserName());
            System.out.println("数据库的系统函数: " + dbmd.getSystemFunctions());
            System.out.println("数据库的时间和日期函数: " + dbmd.getTimeDateFunctions());
            System.out.println("数据库的字符串函数: " + dbmd.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: " + dbmd.getSchemaTerm());
            System.out.println("数据库URL: " + dbmd.getURL());
            System.out.println("是否允许只读:" + dbmd.isReadOnly());
            System.out.println("数据库的产品名称:" + dbmd.getDatabaseProductName());
            System.out.println("数据库的版本:" + dbmd.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + dbmd.getDriverName());
            System.out.println("驱动程序的版本:" + dbmd.getDriverVersion());
            System.out.println("数据库中使用的表类型:");
            resultSet = dbmd.getTableTypes();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("TABLE_TYPE"));
            }
        } catch (SQLException e) {
            System.out.println("获取数据库相关信息失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 8.关闭连接
    public static void close(Object o) {
        if (Objects.isNull(o)) {
            return;
        }
        try {
            if (o instanceof ResultSet) {
                ((ResultSet) o).close();
            } else if (o instanceof Statement) {
                ((Statement) o).close();
            } else if (o instanceof Connection) {
                Connection con = (Connection) o;
                if (!con.isClosed()) {
                    con.close();
                }
            }
        } catch (SQLException e) {
            System.out.println("关闭连接失败：" + e);
        }
    }

    // 测试
    public static void main(String[] args) throws SQLException {
        // 对应url、userName、password需换成自己配置的内容
        System.out.println("======================");
        getDataBase("jdbc:mysql://127.0.0.1:3306?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
        getTables("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
        getColumns("u_user", "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
        getRows("u_user", "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
        getDataBaseInfo("jdbc:mysql://127.0.0.1:3306?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
    }
}

输出如下

======================
DataBase : 0520erp
DataBase : activiti2
DataBase : ambient_monitor
DataBase : blog
DataBase : blog_db
DataBase : boot-blog
DataBase : box-im
DataBase : car_o2o
DataBase : chat
DataBase : cloud-order
DataBase : cloud-user
DataBase : crud
DataBase : customer_center
DataBase : db_admin
DataBase : db_mblog
DataBase : demo_01
DataBase : easybbs
DataBase : easyblog
DataBase : easypan
DataBase : eblog
DataBase : facility_db
DataBase : family_finance
DataBase : friday
DataBase : fyblog
DataBase : guigu-oa
DataBase : huge
DataBase : ihrm
DataBase : im
DataBase : im-core
DataBase : infipark_crm
DataBase : kefu
DataBase : lenos
DataBase : ljs_rtpt_test
DataBase : m_db
DataBase : mushan
DataBase : mybatis
DataBase : mybatis-demo
DataBase : myemployees
DataBase : nacos
DataBase : open-his
DataBase : partner
DataBase : perm
DataBase : psq_screen
DataBase : quartz-demo
DataBase : quartz-test
DataBase : resource_manage
DataBase : ry-vue
DataBase : ry_act_all
DataBase : s_db
DataBase : seata
DataBase : seata_demo
DataBase : security_db
DataBase : shiro_db
DataBase : sob_blog_system
DataBase : space
DataBase : spring_cache
DataBase : sqlpractice
DataBase : studentmanagement
DataBase : students
DataBase : study-security
DataBase : test
DataBase : thatblog
DataBase : timeless_chat_websocket
DataBase : tj
DataBase : tp_music
DataBase : trade
DataBase : user
DataBase : vadmin
DataBase : video-system
DataBase : videodb
DataBase : vue-springboot
DataBase : vueblog
DataBase : xxl_job
DataBase : yd_cloud
DataBase : yd_cloud_operation
DataBase : yd_cloud_uaa
DataBase : yeb
DataBase : z-blog
DataBase : zblog
DataBase : zipkin
======================
database : test ==== table : u_user
======================
table : u_user
ColumnName : id
ColumnTypeName : INT
columnComment : 主键
ColumnName : name
ColumnTypeName : VARCHAR
columnComment : 姓名
ColumnName : hobbies
ColumnTypeName : VARCHAR
columnComment : ing别
ColumnName : age
ColumnTypeName : INT
columnComment : 
======================
table : u_user
id: 1
name: zj
hobbies: ps,blender,java
age: null
id: 2
name: ls
hobbies: u8,u9,pmp,cpa
age: null
id: 3
name: zzhua
hobbies: spring
age: null
id: 4
name: zengjian
hobbies: null
age: null
id: 5
name: halo
hobbies: 
age: null
id: 6
name: netty
hobbies: a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
age: null
======================
数据库已知的用户: root@localhost
数据库的系统函数: DATABASE,USER,SYSTEM_USER,SESSION_USER,PASSWORD,ENCRYPT,LAST_INSERT_ID,VERSION
数据库的时间和日期函数: DAYOFWEEK,WEEKDAY,DAYOFMONTH,DAYOFYEAR,MONTH,DAYNAME,MONTHNAME,QUARTER,WEEK,YEAR,HOUR,MINUTE,SECOND,PERIOD_ADD,PERIOD_DIFF,TO_DAYS,FROM_DAYS,DATE_FORMAT,TIME_FORMAT,CURDATE,CURRENT_DATE,CURTIME,CURRENT_TIME,NOW,SYSDATE,CURRENT_TIMESTAMP,UNIX_TIMESTAMP,FROM_UNIXTIME,SEC_TO_TIME,TIME_TO_SEC
数据库的字符串函数: ASCII,BIN,BIT_LENGTH,CHAR,CHARACTER_LENGTH,CHAR_LENGTH,CONCAT,CONCAT_WS,CONV,ELT,EXPORT_SET,FIELD,FIND_IN_SET,HEX,INSERT,INSTR,LCASE,LEFT,LENGTH,LOAD_FILE,LOCATE,LOCATE,LOWER,LPAD,LTRIM,MAKE_SET,MATCH,MID,OCT,OCTET_LENGTH,ORD,POSITION,QUOTE,REPEAT,REPLACE,REVERSE,RIGHT,RPAD,RTRIM,SOUNDEX,SPACE,STRCMP,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING_INDEX,TRIM,UCASE,UPPER
数据库供应商用于 'schema' 的首选术语: 
数据库URL: jdbc:mysql://127.0.0.1:3306?serverTimezone=UTC
是否允许只读:false
数据库的产品名称:MySQL
数据库的版本:5.7.17-log
驱动程序的名称:MySQL Connector/J
驱动程序的版本:mysql-connector-java-8.0.13 (Revision: 66459e9d39c8fd09767992bc592acd2053279be6)
数据库中使用的表类型:
LOCAL TEMPORARY
SYSTEM TABLE
SYSTEM VIEW
TABLE
VIEW
======================


// 测试
    public static void main(String[] args) throws SQLException {
        getColumnInfos("biz_leave", "jdbc:mysql://127.0.0.1:3306/ry_act_all?serverTimezone=UTC", "root", "root");
        getColumnInfos("biz_todo_item", "jdbc:mysql://127.0.0.1:3306/ry_act_all?serverTimezone=UTC", "root", "root", 7);

    }

    // 9. 获取表的字段及类型
    public static void getColumnInfos(String tableName, String url, String userName, String password, Integer... gapLen) {
        System.out.println();
        String query = "SELECT * FROM " + tableName;

        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query);
             PreparedStatement statement2 = connection.prepareStatement(query)) {


            TableInfo tableInfo = new TableInfo();

            // 查询表的注释
            String url1 = connection.getMetaData().getURL();
            Matcher matcher = Pattern.compile(".*?3306/([^?]+)\\??").matcher(url1);
            if (matcher.find()) {
                String dbName = matcher.group(1);
                ResultSet rs = statement2.executeQuery("SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + dbName+ "' AND TABLE_NAME = '"+ tableName +"'");

                if (rs.next()) {
                    // String tableComment = rs.getString("TABLE_COMMENT");
                    // System.out.println("Table Comment: " + tableComment);
                    tableInfo.setTableComment(rs.getString("TABLE_COMMENT"));
                }
                rs.close();
            }
            statement2.close();

            ResultSetMetaData resultSetMetaData = statement.getMetaData();


            List<ColumnInfo> columnInfos = new ArrayList<>();
            tableInfo.setColumnInfos(columnInfos);

            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {

                ColumnInfo columnInfo = new ColumnInfo();
                columnInfos.add(columnInfo);

                // System.out.println("ColumnName : " + resultSetMetaData.getColumnName(i + 1));
                // System.out.println("ColumnTypeName : " + resultSetMetaData.getColumnTypeName(i + 1));

                columnInfo.setColumnName(resultSetMetaData.getColumnName(i + 1));

                // 获取字段注释
                DatabaseMetaData dbmd = connection.getMetaData();
                ResultSet columnResultSet = dbmd.getColumns(null, null, tableName, resultSetMetaData.getColumnLabel(i + 1));
                if (columnResultSet.next()) {
                    String columnComment = columnResultSet.getString("REMARKS");

                    columnInfo.setColumnComment(columnComment);

                } else {
                    System.out.println("columnComment : " + "");

                    columnInfo.setColumnComment("");
                }

            }

            int maxLen = 0;
            for (ColumnInfo columnInfo : tableInfo.getColumnInfos()) {
                if (columnInfo.getColumnName().length() > maxLen) {
                    maxLen = columnInfo.getColumnName().length();
                }
            }


            int gapChars = gapLen == null || gapLen.length == 0 ? 5 : gapLen[0];

            int maxLenPlusGapChars = maxLen + gapChars;

            System.out.println(tableName + "   " + tableInfo.getTableComment());

            for (ColumnInfo columnInfo : tableInfo.getColumnInfos()) {
                System.out.print(columnInfo.getColumnName());
                for (int i = 0; i < maxLenPlusGapChars - columnInfo.getColumnName().length(); i++) {
                    System.out.print(" ");
                }
                System.out.println(columnInfo.getColumnComment());
            }


        } catch (SQLException e) {
            System.out.println("获取数据库表字段和类型失败：" + e);
        }
    }

    @Data
    public static class TableInfo {
        private String tableComment;
        private List<ColumnInfo> columnInfos;
    }

    @Data
    private static class ColumnInfo {
        private String columnName;
        private String columnComment;
    }

biz_leave   
id                     主键ID
type                   请假类型
title                  标题
reason                 原因
start_time             开始时间
end_time               结束时间
total_time             请假时长，单位秒
instance_id            流程实例ID
create_by              创建者
create_time            创建时间
update_by              更新者
update_time            更新时间
apply_user             申请人
apply_time             申请时间
reality_start_time     实际开始时间
reality_end_time       实际结束时间

biz_todo_item   待办事项表
id                     主键 ID
item_name              事项标题
item_content           事项内容
module                 模块名称 (必须以 uri 一致)
task_id                任务 ID
instance_id            流程实例 ID
task_name              任务名称 (必须以表单页面名称一致)
node_name              节点名称
is_view                是否查看 default 0 (0 否 1 是)
is_handle              是否处理 default 0 (0 否 1 是)
todo_user_id           待办人 ID
todo_user_name         待办人名称
handle_user_id         处理人 ID
handle_user_name       处理人名称
todo_time              通知时间
handle_time            处理时间