读取数据库元数据的工具类

读取数据库元数据的工具类

以下是一个使用JDBC读取数据库元数据信息的工具类实现，支持获取表、列、索引等元数据信息。

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

public class DatabaseMetadataUtil {
    private Connection connection;
    
    public DatabaseMetadataUtil(String url, String username, String password) throws SQLException {
        this.connection = DriverManager.getConnection(url, username, password);
    }
    
    public List<String> getTableNames() throws SQLException {
        List<String> tables = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
        
        while (rs.next()) {
            tables.add(rs.getString("TABLE_NAME"));
        }
        rs.close();
        return tables;
    }
    
    public List<String> getColumnNames(String tableName) throws SQLException {
        List<String> columns = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getColumns(null, null, tableName, null);
        
        while (rs.next()) {
            columns.add(rs.getString("COLUMN_NAME"));
        }
        rs.close();
        return columns;
    }
    
    public List<String> getPrimaryKeys(String tableName) throws SQLException {
        List<String> primaryKeys = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getPrimaryKeys(null, null, tableName);
        
        while (rs.next()) {
            primaryKeys.add(rs.getString("COLUMN_NAME"));
        }
        rs.close();
        return primaryKeys;
    }
    
    public void printTableMetadata(String tableName) throws SQLException {
        System.out.println("Table: " + tableName);
        System.out.println("Columns:");
        for (String column : getColumnNames(tableName)) {
            System.out.println(" - " + column);
        }
        System.out.println("Primary Keys:");
        for (String pk : getPrimaryKeys(tableName)) {
            System.out.println(" - " + pk);
        }
    }
    
    public void close() throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}
 

读取MySql数据库元数据

public class MySqlMetadataReader {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String username = "your_username";
        String password = "your_password";
        
        try {
            DatabaseMetadataUtil util = new DatabaseMetadataUtil(url, username, password);
            
            System.out.println("All Tables:");
            for (String table : util.getTableNames()) {
                System.out.println(table);
            }
            
            util.printTableMetadata("your_table_name");
            
            util.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
 

扩展功能
如果需要获取更详细的元数据信息，可以添加以下方法：

public Map<String, String> getColumnDetails(String tableName) throws SQLException {
    Map<String, String> columnDetails = new LinkedHashMap<>();
    DatabaseMetaData metaData = connection.getMetaData();
    ResultSet rs = metaData.getColumns(null, null, tableName, null);
    
    while (rs.next()) {
        String columnName = rs.getString("COLUMN_NAME");
        String typeName = rs.getString("TYPE_NAME");
        int columnSize = rs.getInt("COLUMN_SIZE");
        String nullable = rs.getString("IS_NULLABLE");
        columnDetails.put(columnName, 
            "Type: " + typeName + 
            ", Size: " + columnSize + 
            ", Nullable: " + nullable);
    }
    rs.close();
    return columnDetails;
}
 

注意事项
使用前需要添加JDBC驱动依赖，例如MySQL:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>
 

注意事项
使用前需要添加JDBC驱动依赖，例如MySQL:

读取SQL Server数据库元数据

import java.sql.*;

public class SQLServerMetadataReader {
    public static void main(String[] args) {
        String url = "jdbc:sqlserver://localhost:1433;databaseName=YourDatabase";
        String user = "username";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            DatabaseMetaData metaData = conn.getMetaData();

            System.out.println("Database Product Name: " + metaData.getDatabaseProductName());
            System.out.println("Database Product Version: " + metaData.getDatabaseProductVersion());

            ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
            while (tables.next()) {
                System.out.println("Table Name: " + tables.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
 

读取Oracle数据库元数据

import java.sql.*;

public class OracleMetadataReader {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
        String user = "username";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            DatabaseMetaData metaData = conn.getMetaData();

            System.out.println("Database Product Name: " + metaData.getDatabaseProductName());
            System.out.println("Database Product Version: " + metaData.getDatabaseProductVersion());

            ResultSet tables = metaData.getTables(null, user.toUpperCase(), "%", new String[]{"TABLE"});
            while (tables.next()) {
                System.out.println("Table Name: " + tables.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
 

读取PostgreSQL数据库元数据

import java.sql.*;

public class PostgreSQLMetadataReader {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/YourDatabase";
        String user = "username";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            DatabaseMetaData metaData = conn.getMetaData();

            System.out.println("Database Product Name: " + metaData.getDatabaseProductName());
            System.out.println("Database Product Version: " + metaData.getDatabaseProductVersion());

            ResultSet tables = metaData.getTables(null, "public", "%", new String[]{"TABLE"});
            while (tables.next()) {
                System.out.println("Table Name: " + tables.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
 

通用数据库元数据读取方法

如果需要更通用的方法读取多个数据库的元数据，可以使用以下代码：

import java.sql.*;

public class UniversalMetadataReader {
    public static void readMetadata(String driver, String url, String user, String password) {
        try {
            Class.forName(driver);
            try (Connection conn = DriverManager.getConnection(url, user, password)) {
                DatabaseMetaData metaData = conn.getMetaData();

                System.out.println("Database Product Name: " + metaData.getDatabaseProductName());
                System.out.println("Database Product Version: " + metaData.getDatabaseProductVersion());

                String schema = null;
                if (url.contains("sqlserver")) {
                    schema = null;
                } else if (url.contains("oracle")) {
                    schema = user.toUpperCase();
                } else if (url.contains("postgresql")) {
                    schema = "public";
                }

                ResultSet tables = metaData.getTables(null, schema, "%", new String[]{"TABLE"});
                while (tables.next()) {
                    System.out.println("Table Name: " + tables.getString("TABLE_NAME"));
                }
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // SQL Server示例
        readMetadata("com.microsoft.sqlserver.jdbc.SQLServerDriver",
                   "jdbc:sqlserver://localhost:1433;databaseName=YourDatabase",
                   "username", "password");

        // Oracle示例
        readMetadata("oracle.jdbc.driver.OracleDriver",
                   "jdbc:oracle:thin:@localhost:1521:ORCL",
                   "username", "password");

        // PostgreSQL示例
        readMetadata("org.postgresql.Driver",
                   "jdbc:postgresql://localhost:5432/YourDatabase",
                   "username", "password");
    }
}
 

SQL Server 驱动依赖

SQL Server 的官方 JDBC 驱动由 Microsoft 提供，最新版本可通过 Maven 中央仓库获取。

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.6.1.jre11</version> <!-- 根据 JDK 版本选择 jre8/jre11 -->
</dependency>
 

Oracle 驱动依赖

Oracle 的 JDBC 驱动需从 Oracle 官网下载或通过 Maven 配置私有仓库。以下是依赖配置：

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>23.3.0.23.09</version> <!-- 最新稳定版本 -->
</dependency>
 

PostgreSQL 驱动依赖

PostgreSQL 的 JDBC 驱动是开源的，可直接从 Maven 中央仓库获取。

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version> <!-- 当前最新版本 -->
</dependency>