Match Table Name and Alias at runtime from DatabaseMetaData?

Question

I know how to use .getTables() to get the TABLE and ALIAS entries.

final ResultSet rs = dmd.getTables(null, "MySchema", "%", new String[]{"TABLE","ALIAS"});

What I need to be able to determine is which ALIAS goes with which TABLE name?

I specifically need to be able to do this on Oracle, SQL Server and DB2.

And given I have a table T00001 and it has an alias of MyTable how can I match the two up using the DatabaseMetaData from a Connection.getMetaData() call?

Answer 1

After reviewing the JDBC APIs and oracle.jdbc.OracleDatabaseMetaData, I'm pretty certain that there is no way to retrieve this information directly through DatabaseMetaData when using stock Oracle JDBC drivers. There may be driver-specific ways for DB2 or SQL Server as I didn't check them out thoroughly. As a cheat, you can getConnection() from the DatabaseMetaData object and run the database-specific queries to retrieve the information you need, as follows:

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"));
    }
}

Code was successfully tested using JDK 1.8.0_45, Oracle XE 11.2.0.2.0 and its bundled JDBC driver, DB2 Express-C 10050500 and its bundled JDBC driver, and SQL Server 2014 Express 12.0.2000.8 with Microsoft JDBC Driver 4.1.5605.100.

_{SQL Server query based on http://sqlblog.com/blogs/john_paul_cook/archive/2010/08/24/script-to-list-synonym-contents.aspx.}