代码之家 › 专栏 › 技术社区 › Maxime ARNSTAMM

如何从数据库中获取所有表名?

database-metadata jdbc database java

52

Maxime ARNSTAMM · 技术社区 · 16 年前

我想从数据库模式中检索所有表名,如果可能,从指定的前缀开始获取所有表。

我试着用JDBC的 connection.getMetaData().getTables() 但根本没用。

Connection jdbcConnection = DriverManager.getConnection("", "", "");
DatabaseMetaData m = jdbcConnection.getMetaData();
ResultSet tables = m.getTables(jdbcConnection.getCatalog(), null, "TAB_%", null);
for (int i = 0; i < tables.getMetaData().getColumnCount(); i++) {
   System.out.println("table = " + tables.getMetaData().getTableName(i));
}

有人能帮我一下吗?

5 回复 | 直到 9 年前

1

111

Basil Bourque 9 年前

您需要遍历resultset调用 next() .

这是一个来自 java2s.com :

DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
  System.out.println(rs.getString(3));
}

柱三是 TABLE_NAME (见文件 DatabaseMetaData::getTables )

2

26

Rohit 13 年前

 public void getDatabaseMetaData()
    {
        try {

            DatabaseMetaData dbmd = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet rs = dbmd.getTables(null, null, "%", types);
            while (rs.next()) {
                System.out.println(rs.getString("TABLE_NAME"));
            }
        } 
            catch (SQLException e) {
            e.printStackTrace();
        }
    }

3

5

Punit Patel 12 年前

在您的示例中,问题是在databasemetadata的gettables函数中传递表名模式。

有些数据库支持大写标识符,有些支持小写标识符。例如,Oracle以大写字母获取表名,而PostgreSQL以小写字母获取表名。

databasemetadeta提供了一种方法来确定数据库如何存储标识符,可以是大小写混合格式,请参见: http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#storesMixedCaseIdentifiers()

从下面的示例中,您可以获取提供表名模式的所有表和视图,如果您只需要表,则从类型数组中删除“view”。

public class DBUtility {
    private static final String[] TYPES = {"TABLE", "VIEW"};
    public static void getTableMetadata(Connection jdbcConnection, String tableNamePattern, String schema, String catalog, boolean isQuoted) throws HibernateException {
            try {
                DatabaseMetaData meta = jdbcConnection.getMetaData();
                ResultSet rs = null;
                try {
                    if ( (isQuoted && meta.storesMixedCaseQuotedIdentifiers())) {
                        rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
                    } else if ( (isQuoted && meta.storesUpperCaseQuotedIdentifiers())
                        || (!isQuoted && meta.storesUpperCaseIdentifiers() )) {
                        rs = meta.getTables(
                                StringHelper.toUpperCase(catalog),
                                StringHelper.toUpperCase(schema),
                                StringHelper.toUpperCase(tableNamePattern),
                                TYPES
                            );
                    }
                    else if ( (isQuoted && meta.storesLowerCaseQuotedIdentifiers())
                            || (!isQuoted && meta.storesLowerCaseIdentifiers() )) {
                        rs = meta.getTables( 
                                StringHelper.toLowerCase( catalog ),
                                StringHelper.toLowerCase(schema), 
                                StringHelper.toLowerCase(tableNamePattern), 
                                TYPES 
                            );
                    }
                    else {
                        rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
                    }

                    while ( rs.next() ) {
                        String tableName = rs.getString("TABLE_NAME");
                        System.out.println("table = " + tableName);
                    }



                }
                finally {
                    if (rs!=null) rs.close();
                }
            }
            catch (SQLException sqlException) {
                // TODO 
                sqlException.printStackTrace();
            }

    }

    public static void main(String[] args) {
        Connection jdbcConnection;
        try {
            jdbcConnection = DriverManager.getConnection("", "", "");
            getTableMetadata(jdbcConnection, "tbl%", null, null, false);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

4

Sualeh Fatehi 16 年前

如果要使用高级api,它隐藏了数据库模式元数据周围的许多jdbc复杂性,请看本文: http://www.devx.com/Java/Article/32443/1954

5

2

Dinesh Pundkar 9 年前

public static ArrayList<String> getTablesList(Connection conn)
            throws SQLException {

        ArrayList<String> listofTable = new ArrayList<String>();

        DatabaseMetaData md = conn.getMetaData();

        ResultSet rs = md.getTables(null, null, "%", null);

        while (rs.next()) {
            if (rs.getString(4).equalsIgnoreCase("TABLE")) {
                listofTable.add(rs.getString(3));
            }
        }
        return listofTable;
    }