使用Java从MySql中的所有模式查看表名

Question

我想知道如何通过JSF从两个模式(在本例中是world和world2)查看表。我可以使用指定的架构登录到我的应用程序中，并查看该模式中的表，但是我不知道如何登录和查看不同的模式。(例如)作为世界登录，查看world2模式表名。)

因此，我想问一问是否有一种重写表列表的方法，或者可能重新使用代码来处理sql查询，即使连接是针对特定模式的。

目前我正在使用：

    public TableList[] getTableList() {
    try {
        String[] TABLE_TYPES = { "TABLE", "VIEW" };
        DatabaseMetaData databaseMetaData;
        String query = "";

        // st = conn.createStatement();
        st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        databaseMetaData = conn.getMetaData();
        rs = databaseMetaData.getTables(null, dbaseBean.getUserName(),
                null, TABLE_TYPES);
        rs.last();
        int count = rs.getRow();
        tableList = new TableList[count];
        rs.beforeFirst();
        int i = 0;
        while (rs.next()) {
            tableList[i] = new TableList(rs.getString("TABLE_NAME"));
            i++;
        }
    } catch (Exception e) {
        e.printStackTrace();
        FacesMessage msg = new FacesMessage("" + " Error Occured");
        FacesContext.getCurrentInstance().addMessage(null, msg);
    }
    setTableList(tableList);
    return tableList;
}

与以下有关：

public boolean connect() {
    FacesContext context = FacesContext.getCurrentInstance();
    Map<String, Object> m = context.getExternalContext().getSessionMap();
    messageBean = (MessageBean) m.get("messageBean");
    dbmsUserBean = (DbmsUserBean) m.get("dbmsUserBean");
    userName = dbmsUserBean.getUserName();
    password = dbmsUserBean.getPassword();

    switch (dbmsUserBean.getDbms().toLowerCase()) {

    case "mysql":
        jdbcDriver = "com.mysql.jdbc.Driver";
        url = "jdbc:mysql://" + dbmsUserBean.getDbmsHost() + ":3306/"
                + dbmsUserBean.getDatabaseSchema();
        break;

    case "db2":
        jdbcDriver = "com.ibm.db2.jcc.DB2Driver";
        url = "jdbc:db2://" + dbmsUserBean.getDbmsHost() + ":50000/"
                + dbmsUserBean.getDatabaseSchema();
        userName = userName.toUpperCase();
        break;

    case "oracle":
        jdbcDriver = "oracle.jdbc.driver.OracleDriver";
        url = "jdbc:oracle:thin:@" + dbmsUserBean.getDbmsHost() + ":1521:"
                + dbmsUserBean.getDatabaseSchema();
        userName = userName.toUpperCase();
        break;

    case "odbc":
    default:
        //
        break;
    } // end switch

    try {
        // register driver
        Class.forName(jdbcDriver);
        // get connection
        connection = DriverManager.getConnection(url, userName, password);
        // get SQL statement object instance
        statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        // retrieve DB meta data
        databaseMetaData = connection.getMetaData();

        DbaseBean dbaseBean = new DbaseBean();
        dbaseBean.setConnection(connection);
        dbaseBean.setDatabaseMetaData(databaseMetaData);
        dbaseBean.setJdbcDriver(jdbcDriver);
        dbaseBean.setUserName(userName);
        dbaseBean.setPassword(password);
        dbaseBean.setUrl(url);
        dbaseBean.setResultSet(resultSet);
        m.put("dbaseBean", dbaseBean);

        status = true;
    }

    catch (ClassNotFoundException e) {
        // assumes there is a corresponding printException method
        // PrintException("Connect: Class not found Exception - could not loadJDBC driver");
        status = false;
    }

    catch (SQLException e) {
        // printException(e,"Connect: SQLException information");
        status = false;
    } catch (Exception e) {
        // printException(e,"Connect: Exception information");
        status = false;
    }

    return status;

}

谢谢。

Answer 1

如果您反对Oracle，并且您可以访问系统表，您可以使用"select user，table_name从system.all_tables；其中user in (‘myuser2 1’，‘myuser2’)检索表；

如果您想要所有东西，您可以在all_objects上输入“表”、“视图”(以及过程和触发器以及.)

如果您要针对不同的数据库，那么您必须确定每个DB的数据字典在哪里和什么位置，并为每个数据库编写代码。

你在标题中提到了MySQL：

从table_schema，table_name从INFORMATION_SCHEMA.TABLES中选择table_schema in ('myfirstone'，‘myfirstone’)；