How to get database schema name when using oracle

问题:

I am trying to get all db tables using DatabaseMetaData.getTables() method. But this method requires database schema name pattern. Is it possible to get schema name for current db connection?

回答1:

The standard schema for your current connection is the name of the user you use to log in. So if your user is SCOTT you have to use SCOTT for DatabaseMetaData.getTables().

You can obtain the username through DatabaseMetaData.getUserName().

But remember that the comparison of schema/username done in the JDBC driver is case-sensititve and normally usernames are in uppercase.

I am not 100% sure if DatabaseMetaData.getUserName() will return the name in the correct case in all situations. To be sure, you might want to do an upperCase() before using that value.

回答2:

Try to play with getCatalogs(). This is a quick draft

  public List<String> getDatabases(DBEnv dbEnv) {

        Connection conn = getConnection(dbEnv);
        List<String> resultSet = new ArrayList<String>();

        try {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet res = metaData.getCatalogs();

            while (res.next()) {
                resultSet.add(res.getString("TABLE_CAT"));
            }

        } catch (SQLException e) {
            logger.error(e.toString());
        }

        return resultSet;

    }

回答3:

Since Java 7, Connection has a getSchema method: https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#getSchema--

回答4:

The answer unfortunately is that there are no consistent solutions. If John has access to Sally.Table ... the query will work but getUserName() will return John and not Sally schema. For Oracle the user owns their schema and while others may have access, that user ID is the default schema on that connection.

Further, neither getSchemaName() nor getCatalog() will return the schema name.

@horse_with_no_name has the closest answer for Oracle since a given user name is the (default) schema name unless overridden in object reference as shown.

For other databases the same rules do not apply consistently.

回答5:

You can get schema name using

Connection conn = 
DriverManager.getConnection("jdbc:oracle:thin:@server:port:SID",prop);    
DatabaseMetaData databaseMetaData = conn.getMetaData();
System.out.println("schema name >>>> "+databaseMetaData.getUserName());