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?
Add a comment
|
5 Answers
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.
14 Comments
johnny-b-goode
You're right. String with schema id name i am looking for is an upper case username string.
Lee Chee Kiam
Since schema/username is case-sensitive, why Oracle stores the username in uppercase??
@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.FOObarLee Chee Kiam
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?
@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.
|
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 Comments
johnny-b-goode
Don't know why but the result set is empty.
Victor
@ThaiTran Do you have any idea of why the method is called getCatalogs and not getSchemas ?
Victor
@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 stackoverflow.com/questions/7942520/…)
Since Java 7, Connection has a getSchema method: https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#getSchema--
2 Comments
Darrell Teague
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
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.
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.
Comments
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());
