java - JDBC DatabaseMetaData.getColumns() returns duplicate columns

Question

I'm busy on a piece of code to get alle the column names of a table from an Oracle database. The code I came up with looks like this:

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
  "jdbc:oracle:thin:@<server>:1521:<sid>", <username>, <password>);

DatabaseMetaData meta = conn.getMetaData();
ResultSet columns = meta.getColumns(null, null, "EMPLOYEES", null);
int i = 1;
while (columns.next())
{
  System.out.printf("%d: %s (%d)
", i++, columns.getString("COLUMN_NAME"), 
    columns.getInt("ORDINAL_POSITION"));
}

When I ran this code to my surprise too many columns were returned. A closer look revealed that the ResultSet contained a duplicate set of all the columns, i.e. every column was returned twice. Here's the output I got:

1: ID (1)
2: NAME (2)
3: CITY (3)
4: ID (1)
5: NAME (2)
6: CITY (3)

When I look at the table using Oracle SQL Developer it shows that the table only has three columns (ID, NAME, CITY). I've tried this code against several different tables in my database and some work just fine, while others exhibit this weird behaviour.

Could there be a bug in the Oracle JDBC driver? Or am I doing something wrong here?

---

Update: Thanks to Kenster I now have an alternative way to retrieve the column names. You can get them from a ResultSet, like this:

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@<server>:1521:<sid>", <username>, <password>);

Statement st = conn.createStatement();
ResultSet rset = st.executeQuery("SELECT * FROM "EMPLOYEES"");
ResultSetMetaData md = rset.getMetaData();
for (int i=1; i<=md.getColumnCount(); i++)
{
    System.out.println(md.getColumnLabel(i));
}

This seems to work just fine and no duplicates are returned! And for those who wonder: according to this blog you should use getColumnLabel() instead of getColumnName().

Answer 1

In oracle, 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(), you're not filtering the results for just your schema.

You need to supply the name of the Oracle schema to one of the first two parameters of meta.getColumns(), probably the second one, e.g.

meta.getColumns(null, "myuser", "EMPLOYEES", null);

It's a bit irritating having to do this, but that's the way the Oracle folks chose to implement their JDBC driver.