Duplicate columns while using DatabaseMetaData.getColumns()

Want to post a potential pit fall in using JDBC API DatabaseMetaData.getColumns() where most of developers tend to do.

In some of the projects, there might be requirement to fetch the details of a column like its name etc during the run time. And the API in the subject of discussion perfectly does the job.

Below is the code snippet typically used:

String TABLE_NAME = "EMPLOYEE";

DatabaseMetaData meta = connection.getMetaData();

ResultSet columns = meta.getColumns(null, null, TABLE_NAME, null);

With above code, we faced issue of duplicate columns being returned. For example: Multiple times EMP_NAME is being returned by this API. 

When I further debugged the code to the level of DB, found that Connection.getMetaData() returns meta-data for the entire database, not just the schema you happen to be connected to. So when you supply null as the first two arguments to meta.getColumns(), the results are not filtered for your schema. You need to supply the name of the Oracle schema as second parameter of meta.getColumns() e.g., meta.getColumns(null, "DBSCHEMA_01", TABLE_NAME, null). At least this is the case when used with Oracle database (not sure of other providers).

If one carefully looks at the above code snippet, it is clearly visible that the DatabaseMetaData is fetched from the Connection. In short API returns all the columns (if no filtering is specified as above) of the specified table in the database which are visible to this connection.

Also the above issue is due to visibility of the tables provided to other schemas of the database. This may be a mistake or purposely configured by DBAs and unfortunately it was the later in our case. 

On a high level, the SQL used by ORACLE for fetching the results of this API is as below:

SELECT   *

    FROM all_tab_columns t

   WHERE t.owner LIKE '%' ESCAPE '/'

     AND t.table_name LIKE 'EMPLOYEE' ESCAPE '/'

     AND t.column_name LIKE '%' ESCAPE '/'

ORDER BY table_schem, table_name, ordinal_position

To avoid duplicates in the result returned by this API, below are two possible solutions that I could think of:

1. Provide the schema name while calling this API.
2. Avoid this API and use a SELECT SQL like select * from EMPLOYEE where rownum < 1. The metadata of the result set contains the details of the column. And the code will be something like: resultSet.getMetaData().getColumnCount() and resultSet.getMetaData().getColumnName(int columnIndex)

       I hope the above was useful. Please add your comments if any