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

I'd like to retrieve all table names from a database schema, and, if possible, get all table starting with a specified prefix.

我想从数据库模式中检索所有表名,如果可能的话,让所有表以指定的前缀开头。

I tried using JDBC's connection.getMetaData().getTables() but it didn't work at all.

我尝试使用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));}

Could someone help me on this?

有人可以帮我吗?

6 个解决方案

#1

You need to iterate over your ResultSet calling next().

您需要迭代ResultSet调用next()。

This is an example from java2s.com:

这是java2s.com的一个例子:

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

Column 3 is the TABLE_NAME (see documentation of DatabaseMetaData::getTables).

第3列是TABLE_NAME(请参阅DatabaseMetaData :: getTables的文档)。

#2

 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

In your example problem is passed table name pattern in getTables function of DatabaseMetaData.

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

Some database supports Uppercase identifier, some support lower case identifiers. For example oracle fetches the table name in upper case, while postgreSQL fetch it in lower case.

某些数据库支持大写标识符,有些支持小写标识符。例如,oracle以大写形式获取表名,而postgreSQL以小写形式获取它。

DatabaseMetaDeta provides a method to determine how the database stores identifiers, can be mixed case, uppercase, lowercase see:http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#storesMixedCaseIdentifiers()

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

From below example, you can get all tables and view of providing table name pattern, if you want only tables then remove "VIEW" from TYPES array.

从下面的示例中,您可以获取所有表和提供表名模式的视图,如果只需要表,则从TYPES数组中删除“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

If you want to use a high-level API, that hides a lot of the JDBC complexity around database schema metadata, take a look at this article: http://www.devx.com/Java/Article/32443/1954

如果您想使用高级API,这会隐藏围绕数据库模式元数据的大量JDBC复杂性,请查看以下文章:http://www.devx.com/Java/Article/32443/1954

#5

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;    }

#6

@Transactional@RequestMapping(value = { "/getDatabaseTables" }, method = RequestMethod.GET)public @ResponseBody String getDatabaseTables() throws Exception{     Connection con = ((SessionImpl) sessionFactory.getCurrentSession()).connection();    DatabaseMetaData md = con.getMetaData();    ResultSet rs = md.getTables(null, null, "%", null);    HashMap<String,List<String>> databaseTables = new HashMap<String,List<String>>();    List<String> tables = new ArrayList<String>();    String db = "";    while (rs.next()) {        tables.add(rs.getString(3));        db = rs.getString(1);    }    List<String> database = new ArrayList<String>();    database.add(db);    databaseTables.put("database", database);    Collections.reverse(tables);    databaseTables.put("tables", tables);    return new ObjectMapper().writeValueAsString(databaseTables);}@Transactional@RequestMapping(value = { "/getTableDetails" }, method = RequestMethod.GET)public @ResponseBody String getTableDetails(@RequestParam(value="tablename")String tablename) throws Exception{     System.out.println("...tablename......"+tablename);    Connection con = ((SessionImpl) sessionFactory.getCurrentSession()).connection();            Statement st = con.createStatement();     String sql = "select * from "+tablename;     ResultSet rs = st.executeQuery(sql);     ResultSetMetaData metaData = rs.getMetaData();     int rowCount = metaData.getColumnCount();         List<HashMap<String,String>> databaseColumns = new ArrayList<HashMap<String,String>>();     HashMap<String,String> columnDetails = new HashMap<String,String>();     for (int i = 0; i < rowCount; i++) {         columnDetails = new HashMap<String,String>();         Method method = com.mysql.jdbc.ResultSetMetaData.class.getDeclaredMethod("getField", int.class);         method.setAccessible(true);         com.mysql.jdbc.Field field = (com.mysql.jdbc.Field) method.invoke(metaData, i+1);         columnDetails.put("columnName", field.getName());//metaData.getColumnName(i + 1));         columnDetails.put("columnType", metaData.getColumnTypeName(i + 1));         columnDetails.put("columnSize", field.getLength()+"");//metaData.getColumnDisplaySize(i + 1)+"");         columnDetails.put("columnColl", field.getCollation());         columnDetails.put("columnNull", ((metaData.isNullable(i + 1)==0)?"NO":"YES"));         if (field.isPrimaryKey()) {             columnDetails.put("columnKEY", "PRI");         } else if(field.isMultipleKey()) {             columnDetails.put("columnKEY", "MUL");         } else if(field.isUniqueKey()) {             columnDetails.put("columnKEY", "UNI");         } else {             columnDetails.put("columnKEY", "");         }         columnDetails.put("columnAINC", (field.isAutoIncrement()?"AUTO_INC":""));         databaseColumns.add(columnDetails);     }    HashMap<String,List<HashMap<String,String>>> tableColumns = new HashMap<String,List<HashMap<String,String>>>();    Collections.reverse(databaseColumns);    tableColumns.put("columns", databaseColumns);    return new ObjectMapper().writeValueAsString(tableColumns);}