9

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?

wolφi
  • 8,091
  • 2
  • 35
  • 64
johnny-b-goode
  • 3,792
  • 12
  • 45
  • 68

5 Answers5

9

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.

  • You're right. String with schema id name i am looking for is an upper case username string. – johnny-b-goode Nov 12 '12 at 09:52
  • Since schema/username is case-sensitive, why Oracle stores the username in uppercase?? – Lee Chee Kiam Apr 15 '14 at 06:31
  • 2
    @CKLee: because the SQL standard requires identifiers to be folded to uppercase, that's why non-quoted identifiers are stored in uppercase in Oracle. The case-sensitivity in this case comes from the fact that the JDBC driver in the background does something like `where owner = ?` and *that* comparison is case sensitive (as every other string comparison). The username as part of an *identifier* is not case sensitive. `select * from SCOTT.foobar` is identicial to `select * from scott.FOObar` –  Apr 15 '14 at 06:41
  • I am now working on DDL layer for different databases. The Oracle behavior make me can't generalize my implementation. Anyway to force Oracle to store username with original case? – Lee Chee Kiam Apr 15 '14 at 06:57
  • @CKLee: there is no single solution to this: some DBMS store it in uppercase, some in lowercase, some in mixed-case. Some are case-sensitive when comparing strings, some are not, some are sometimes. Some always work the same independently of the operating system and installation options, for some this behavior depends on the configuration or the operating system. –  Apr 15 '14 at 07:03
  • Just tested `DatabaseMetaData.getUserName()` returns uppercase for Oracle. – Lee Chee Kiam Apr 15 '14 at 07:33
  • `OracleDataSource.getUser()` return original case. – Lee Chee Kiam Apr 15 '14 at 07:46
  • Yea this works, but why can't I simply call connection.getSchema()? In my environment it throws a NPE. Aces Oracle! Just a real nice job. – chubbsondubs Feb 25 '15 at 05:49
  • Unfortunately there seems to be no consistency between databases (Oracle, MySQL, etc) in testing. MySQL gives the actual username@host and [connection].getSchema() returns null, even when set on properties for database instance ID (SID in Oracle parlance). – Darrell Teague Jul 12 '18 at 22:02
  • @DarrellTeague: that's because MySQL does not distinguish between "database" and "schema". They only have databases (but use them like schemas). As they _call_ it "database" the JDBC driver exposes that as a "catalog" (which is the equivalent of a "database" in the SQL standard and JDBC). So to call `getTables()` for MySQL (which this question and answer is **not** about) you need to provide the correct _catalog_, not a schema. But that is completely unrelated to initial question. My answer is only valid for **Oracle** not for any other DBMS. –  Jul 13 '18 at 07:06
  • @a_horse_with_no_name - Given the nature of the forum as an educational and answer source ... it seems wholly appropriate to broach the topic of JDBC in general. This was a comment versus an answer in any case. The provided answer lends to the argument that the JDBC implementation is mismatched between at least two major vendors - namely the answer being to use an incorrectly named method (getUserName) to retrieve the requested "schema" name (for which there is a getSchemaName() method). Lastly, all databases support a 1-to-many relationship between database names/schemas and users. – Darrell Teague Jul 13 '18 at 20:53
  • @DarrellTeague: Oracle does not support a 1-to-may relationship between users and schemas and thus `getUserName()` **will** return the schema. Additionally: there is no `getSchemaName()` method in `DatabaseMetaData`. And for the "schema" argument of `getTables()` the username is the appropriate information to pass in Oracle. (and `Connection.getSchema()` was not supported back in 2012 when I wrote the answer) –  Jul 13 '18 at 21:40
  • @a_horse_with_no_name - Java 7 (release date 2011) has Connection.getSchema(). https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#getSchema() and... yes there can be many different users in Oracle for the same DB, with access to the same schema. Granted the schema will be "owned" by only one of those users but with privileges, Joe could access the Sally.Table (in Sally schema). Thus connection made by Joe referencing Sally schema objects. In such case, getUserName() for Joe would not resolve to the Sally schema. – Darrell Teague Jul 14 '18 at 23:12
  • @DarrellTeague: good luck trying `Connection.getSchema()` with an Oracle 11 driver (which was the only one available when I wrote the answer). Feel free to write a new answer which reflects the new situation 6 years later –  Jul 15 '18 at 05:14
3

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;

    }
Thai Tran
  • 9,815
  • 7
  • 43
  • 64
  • Don't know why but the result set is empty. – johnny-b-goode Nov 12 '12 at 09:58
  • @ThaiTran Do you have any idea of why the method is called _getCatalogs_ and not _getSchemas_ ? – Victor Feb 22 '19 at 10:40
  • @johnny-b-goode - if you found out why in the meanwhile :) - it was the DB that was interpreting the method differently ? I mean, it works on MySql, but on Oracle or other type of DB it may return other types of objects (I came across this hint while reading https://stackoverflow.com/questions/7942520/relationship-between-catalog-schema-user-and-database-instance) – Victor Feb 22 '19 at 10:42
3

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

Tim Moore
  • 8,958
  • 2
  • 23
  • 34
  • 1
    Good call but... on at least MySQL (v5.7.x) [Connection].getSchema() returns null - even though the schema was defined on the connection string. MySQL interprets this a bit differently as a database instance ID and does not apparently translate this into the appropriate data model for JDBC (catalog/schema/{object}). – Darrell Teague Jul 12 '18 at 21:13
  • 1
    The OP question was specifically related to Oracle but given that JDBC is supposed to be abstract ... one would think "getSchema()" would return the schema and "getUserName()" et al would return the expected data associated with a common model of users, database instance IDs and schemas but... alas this is apparently not consistent for Oracle nor MySQL. – Darrell Teague Jul 13 '18 at 21:28
3

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.

Darrell Teague
  • 4,132
  • 1
  • 26
  • 38
1

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());
Jacob
  • 14,463
  • 65
  • 207
  • 320