Match table name and runtime alias from DatabaseMetaData?
I know how to use .getTables()
to get records TABLE
and ALIAS
.
final ResultSet rs = dmd.getTables(null, "MySchema", "%", new String[]{"TABLE","ALIAS"});
What do I need to determine which one ALIAS
comes with which TABLE
name?
I need to be able to do this on Oracle
, SQL Server
and DB2
.
And given that I have a table T00001
and it has an alias MyTable
, how can I match the two using a call DatabaseMetaData
from a call Connection.getMetaData()
?
source to share
After looking at the JDBC APIs and oracle.jdbc.OracleDatabaseMetaData , I'm pretty sure there is no way to get this information directly through DatabaseMetaData when using the Oracle JDBC fallback drivers. There may be specific drivers for DB2 or SQL Server as I have not tested them fully. As a cheat, you can getConnection()
from the DatabaseMetaData object and run database specific queries to get the information you need:
public class TableAliasMapperJdbc {
public Map<String, List<String>> mapTableAliases(String url, String user, String password, String sql) throws SQLException {
try (
Connection conn = DriverManager.getConnection(url, user, password);
// we use conn.getMetaData().getConnection() instead of conn here only to fit within the parameters of the question
PreparedStatement stmt = conn.getMetaData().getConnection().prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
) {
// this may not do what you want if you have synonyms of synonyms
Map<String, List<String>> tableAliases = new HashMap<>();
while (rs.next()) {
String table = rs.getString(1);
String alias = rs.getString(2);
List<String> aliases = tableAliases.get(table);
if (aliases == null) {
tableAliases.put(table, aliases = new ArrayList<>(2));
}
aliases.add(alias);
}
return tableAliases;
}
}
public void print(String dbName, Map<String, List<String>> tableAliases) {
System.out.format("\nThe following are the table aliases for %s:\n", dbName);
for (Map.Entry<String, List<String>> entry : tableAliases.entrySet()) {
System.out.format("The alias(es) for %s are: %s.\n", entry.getKey(), String.join(", ", entry.getValue()));
}
}
public static void main(String[] args) throws SQLException {
TableAliasMapperJdbc mapper = new TableAliasMapperJdbc();
mapper.print("Oracle",
mapper.mapTableAliases(
"jdbc:oracle:thin:@localhost:1521:xe",
"scott",
"tiger",
"SELECT table_name, synonym_name FROM user_synonyms")); // or maybe all_synonyms
mapper.print("DB2",
mapper.mapTableAliases(
"jdbc:db2://localhost:50000/SAMPLE",
"db2admin",
"db2admin",
"SELECT base_tabname, tabname FROM syscat.tables WHERE type = 'A' AND owner = 'DB2ADMIN'"));
mapper.print("SQL Server",
mapper.mapTableAliases(
"jdbc:sqlserver://localhost:1433",
"sa",
"Password123",
"SELECT PARSENAME(base_object_name,1), name FROM sys.synonyms"));
}
}
The code has been successfully tested using JDK 1.8.0_45, Oracle XE 11.2.0.2.0 and its associated JDBC driver, DB2 Express-C 10050500 and its associated JDBC driver, and SQL Server 2014 Express 12.0.2000.8 with the driver Microsoft JDBC 4.1.5605.100.
SQL Server <sub> based on http://sqlblog.com/blogs/john_paul_cook/archive/2010/08/24/script-to-list-synonym-contents.aspx . Sub>
source to share