Java JDBC DatabaseMetaData.getColumns() 返回重复的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1601203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JDBC DatabaseMetaData.getColumns() returns duplicate columns
提问by LeonZandman
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:
我正忙于编写一段代码来从 Oracle 数据库中获取表的所有列名。我想出的代码如下所示：
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"));
}
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:
令我惊讶的是，当我运行此代码时，返回了太多列。仔细观察发现 ResultSet 包含所有列的重复集，即每列都返回了两次。这是我得到的输出：
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.
当我使用 Oracle SQL Developer 查看该表时，它显示该表只有三列（ID、NAME、CITY）。我已经针对我数据库中的几个不同表尝试了这段代码，有些工作得很好，而另一些则表现出这种奇怪的行为。
Could there be a bug in the Oracle JDBC driver? Or am I doing something wrong here?
Oracle JDBC 驱动程序中可能存在错误吗？或者我在这里做错了什么？
Update:Thanks to KensterI now have an alternative way to retrieve the column names. You can get them from a ResultSet, like this:
更新：感谢Kenster，我现在有了一种检索列名的替代方法。您可以从 ResultSet 中获取它们，如下所示：
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 blogyou should use getColumnLabel() instead of getColumnName().
这似乎工作得很好，并且没有返回重复项！对于那些想知道的人：根据此博客，您应该使用 getColumnLabel() 而不是 getColumnName()。
采纳答案by skaffman
In oracle, Connection.getMetaData()returns meta-data for the entiredatabase, not just the schema you happen to be connected to. So when you supply nullas the first two arguments to meta.getColumns(), you're not filtering the results for just your schema.
在 oracle 中，Connection.getMetaData()返回整个数据库的元数据，而不仅仅是您碰巧连接到的模式。因此，当您null将前两个参数作为提供给 时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.
您需要将 Oracle 模式的名称提供给 的前两个参数之一meta.getColumns()，可能是第二个参数，例如
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.
不得不这样做有点烦人，但这就是 Oracle 人员选择实现他们的 JDBC 驱动程序的方式。
回答by Kenster
This doesn't directly answer your question, but another approach is to execute the query:
这不会直接回答您的问题，但另一种方法是执行查询：
select * from tablename where 1 = 0
This will return a ResultSet, even though it doesn't select any rows. The result set metadata will match the table that you selected from. Depending on what you're doing, this can be more convenient. tablenamecan be anything that you can select on--you don't have to get the case correct or worry about what schema it's in.
这将返回一个 ResultSet，即使它没有选择任何行。结果集元数据将匹配您从中选择的表。根据您的操作，这可能更方便。tablename可以是您可以选择的任何内容——您不必正确处理大小写或担心它所在的架构。
回答by Jeffrey Harmon
In the update to your question I noticed that you missed one key part of Kenster's answer. He specified a 'where' clause of 'where 1 = 0', which you don't have. This is important because if you leave it off, then oracle will try and return the ENTIRE table. And if you don't pull all of the records over, oracle will hold unto them, waiting for you to page through them. Adding that where clause still gives you the metadata, but without any of the overhead.
在您问题的更新中，我注意到您错过了肯斯特回答的一个关键部分。他指定了“where 1 = 0”的“where”子句，而您没有。这很重要，因为如果您不使用它，那么 oracle 将尝试返回整个表。如果您不拉出所有记录，oracle 将保留它们，等待您翻阅它们。添加 where 子句仍然为您提供元数据，但没有任何开销。
Also, I personally use 'where rownum < 1', since oracle knows immediately that all rownums are past that, and I'm not sure if it's smart enough to not try and test each record for '1 = 0'.
另外，我个人使用'where rownum < 1'，因为oracle 立即知道所有rownums 都超过了，我不确定它是否足够聪明，不会尝试测试每条记录是否为'1 = 0'。
回答by ujace
In addition to skaffman's answer -
除了斯卡夫曼的回答 -
use the following query in Oracle:
在 Oracle 中使用以下查询：
select sys_context( 'userenv', 'current_schema' ) from dual;
to access your current schema name if you are restricted to do so in Java.
如果您被限制在 Java 中访问您当前的模式名称。
回答by Vladislav Vaintroub
This is the behavior mandated by the JDBC API - passing nulls as first and second parameter to getColumns means that neither catalog name nor schema name are used to narrow the search. Link to the documentation. It is true that some other JDBC drivers have different behavior by default (e.g MySQL's ConnectorJ by default restricts to the current catalog), but this is not standard, and documented as such
这是 JDBC API 强制要求的行为 - 将空值作为第一个和第二个参数传递给 getColumns 意味着既不使用目录名称也不使用架构名称来缩小搜索范围。 链接到文档。确实，某些其他 JDBC 驱动程序默认情况下具有不同的行为（例如，默认情况下 MySQL 的 ConnectorJ 限制为当前目录），但这不是标准的，并且已记录为
