JDBC DatabaseMetaData.getColumns（）返回重复的列 [英] JDBC DatabaseMetaData.getColumns() returns duplicate columns

问题描述

我忙于编写一段代码来获取Oracle数据库中表的列名。我想出的代码如下所示：

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)\n", i++, columns.getString("COLUMN_NAME"), 
    columns.getInt("ORDINAL_POSITION"));
}

当我运行此代码时，我惊讶地发现了太多的列。仔细观察发现ResultSet包含所有列的重复集合，即每列返回两次。这是我得到的输出：

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)

当我使用Oracle SQL Developer查看表时，它显示该表只有三列（ID，NAME，CITY）。我已经在我的数据库中对几个不同的表尝试了这个代码，有些工作得很好，而其他人则表现出这种奇怪的行为。

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.

Oracle JDBC中是否存在错误驱动程序？或者我在这里做错了什么？

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

更新：感谢Kenster 我现在有另一种检索列名的方法。您可以从ResultSet中获取它们，如下所示：

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

这似乎工作正常，不会返回重复项！对于那些想知道：根据这个博客你应该使用getColumnLabel（）而不是getColumnName（）。

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().

推荐答案

在oracle中， Connection.getMetaData（） 返回整个数据库的元数据，而不仅仅是您碰巧连接到的模式。因此当您提供 null 作为 meta.getColumns（）的前两个参数时，您不会过滤结果仅适用于您的架构。

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.

您需要将Oracle模式的名称提供给 meta.getColumns（），可能是第二个，例如

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

这样做有点恼人，但这就是Oracle人员选择实现JDBC的方式司机。

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

这篇关于JDBC DatabaseMetaData.getColumns（）返回重复的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！