Java获取DB2数据库表信息接口

使用Java接口获取DB2数据库表信息

有关DB2数据库表名、表注释、字段名、字段类型、字段注释的信息可以通过以下接口获取：

public interface Db2MetaDataService {
    
    /**
     * 获取数据库中的所有表名
     * @param schema 数据库schema名
     * @return 表名列表
     * @throws SQLException
     */
    List<String> getTableNames(String schema) throws SQLException;
    
    /**
     * 获取指定表的注释
     * @param schema 数据库schema名
     * @param tableName 表名
     * @return 表注释
     * @throws SQLException
     */
    String getTableComment(String schema, String tableName) throws SQLException;
    
    /**
     * 获取指定表的所有字段名
     * @param schema 数据库schema名
     * @param tableName 表名
     * @return 字段名列表
     * @throws SQLException
     */
    List<String> getColumnNames(String schema, String tableName) throws SQLException;
    
    /**
     * 获取指定表中指定字段的数据类型
     * @param schema 数据库schema名
     * @param tableName 表名
     * @param columnName 字段名
     * @return 数据类型
     * @throws SQLException
     */
    String getColumnType(String schema, String tableName, String columnName) throws SQLException;
    
    /**
     * 获取指定表中指定字段的注释
     * @param schema 数据库schema名
     * @param tableName 表名
     * @param columnName 字段名
     * @return 字段注释
     * @throws SQLException
     */
    String getColumnComment(String schema, String tableName, String columnName) throws SQLException;
}

其中，schema参数表示数据库中的模式名，通常为用户名。下面是一个实现该接口的例子：

public class Db2MetaDataServiceImpl implements Db2MetaDataService {
    
    private Connection connection;
    
    public Db2MetaDataServiceImpl(Connection connection) {
        this.connection = connection;
    }

    @Override
    public List<String> getTableNames(String schema) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getTables(null, schema, null, new String[] {"TABLE"});
        List<String> tableNames = new ArrayList<>();
        while (rs.next()) {
            tableNames.add(rs.getString("TABLE_NAME"));
        }
        return tableNames;
    }

    @Override
    public String getTableComment(String schema, String tableName) throws SQLException {
        String sql = "SELECT REMARKS FROM SYSCAT.TABLES WHERE TABSCHEMA = ? AND TABNAME = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, schema);
        statement.setString(2, tableName);
        ResultSet rs = statement.executeQuery();        
        if (rs.next()) {
            return rs.getString("REMARKS");
        } else {
            return null;
        }
    }

    @Override
    public List<String> getColumnNames(String schema, String tableName) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getColumns(null, schema, tableName, null);
        List<String> columnNames = new ArrayList<>();
        while (rs.next()) {
            columnNames.add(rs.getString("COLUMN_NAME"));
        }
        return columnNames;
    }

    @Override
    public String getColumnType(String schema, String tableName, String columnName) throws SQLException {
        String sql = "SELECT TYPENAME FROM SYSCAT.COLUMNS WHERE TABSCHEMA = ? AND TABNAME = ? AND COLNAME = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, schema);
        statement.setString(2, tableName);
        statement.setString(3, columnName);
        ResultSet rs = statement.executeQuery();        
        if (rs.next()) {
            return rs.getString("TYPENAME");
        } else {
            return null;
        }
    }

    @Override
    public String getColumnComment(String schema, String tableName, String columnName) throws SQLException {
        String sql = "SELECT REMARKS FROM SYSCAT.COLUMNS WHERE TABSCHEMA = ? AND TABNAME = ? AND COLNAME = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, schema);
        statement.setString(2, tableName);
        statement.setString(3, columnName);
        ResultSet rs = statement.executeQuery();        
        if (rs.next()) {
            return rs.getString("REMARKS");
        } else {
            return null;
        }
    }
}

使用示例

Connection connection = DriverManager.getConnection("jdbc:db2://localhost:50000/SAMPLE");
Db2MetaDataService service = new Db2MetaDataServiceImpl(connection);

// 获取所有表名
List<String> tableNames = service.getTableNames("USER");

// 获取表注释
String tableComment = service.getTableComment("USER", "TABLE1");

// 获取所有字段名
List<String> columnNames = service.getColumnNames("USER", "TABLE1");

// 获取字段类型
String columnType = service.getColumnType("USER", "TABLE1", "COLUMN1");

// 获取字段注释
String columnComment = service.getColumnComment("USER", "TABLE1", "COLUMN1");