Java获取数据库所有表的所有字段、类型、注释、值

夜高_风黑

已于 2023-11-10 17:52:40 修改

阅读量944

点赞数

文章标签： java 数据库开发语言

于 2023-11-10 17:51:45 首次发布

本文链接：https://blog.csdn.net/qq_41484287/article/details/134338513

版权

Java获取数据库所有表的所有字段、类型、注释、值

@Test
    public void testUpdate(){
        //数据库连接信息
        String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "root";
        //获取数据库连接
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            //获取数据库元数据
            DatabaseMetaData metaData = connection.getMetaData();
            //获取表名信息
            ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                System.out.println("Table: " + tableName);
                //需要跳过的表名
                if (shouldSkipTable(tableName)) {
                    continue;
                }
                //获取元数据
                try(Statement statement = connection.createStatement();
                    ResultSet resultSet = statement.executeQuery("SELECT * FROM " + tableName)) {
                    ResultSetMetaData rsMetaData = resultSet.getMetaData();
                    //获取表列数
                    int columnCount = rsMetaData.getColumnCount();
                    //获取字段注释map
                    Map<String, String> columnComments = getColumnComments(metaData, tableName);
                    //循环行
                    while (resultSet.next()) {
                        //循环列
                        for (int i = 1; i <= columnCount; i++) {
                            //获取字段名
                            String columnName = rsMetaData.getColumnName(i);
                            //获取字段类型
                            String columnTypeName = rsMetaData.getColumnTypeName(i);
                            //获取值
                            String columnValue = resultSet.getString(i);
                            //获取注释
                            String columnComment = columnComments.get(columnName);
                            System.out.println("表名：->>"+tableName+"  字段名：->>"+columnName + "  字段类型：->>"+columnTypeName + "  字段值：" + columnValue + "  字段注释：" + columnComment);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

   /**不需要读取的表*/
    private boolean shouldSkipTable(String tableName) {
        return tableName.matches("QRTZ_.*") || tableName.contains("log") || tableName.contains("blade");
    }

    /**获取字段注解*/
    private Map<String, String> getColumnComments(DatabaseMetaData metaData, String tableName) throws SQLException {
        Map<String, String> columnComments = new HashMap<>();
        try (ResultSet columns = metaData.getColumns(null, null, tableName, null)) {
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String columnComment = columns.getString("REMARKS");
                columnComments.put(columnName, columnComment);
            }
        }
        return columnComments;
    }