Java 使用 JDBC 获取所有外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18716651/
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
Get all foreign keys using JDBC
提问by Dáire
I am using postgreSQL. I am trying to get all of the foreign keys from a table. This is the method that I am currently using.
我正在使用 postgreSQL。我正在尝试从表中获取所有外键。这是我目前使用的方法。
public String getFKeyData(String tableName, int i) throws SQLException {
DatabaseMetaData dm = connection.getMetaData();
ResultSet rs = dm.getImportedKeys(null, null, tableName);
while (rs.next()) {
fkTableData = rs.getString(i);
}
return fkTableData;
}
This code works but it only gets me the last foreign key which is fine if there is only one in the table but this does not fit my needs. All of the examples I have looked at online are very similar to this and only give one foreign key as an output. Currently I am just printing the data when a button is pressed.
这段代码有效，但它只为我提供最后一个外键，如果表中只有一个，这很好，但这不符合我的需要。我在网上看到的所有示例都与此非常相似，并且只给出一个外键作为输出。目前我只是在按下按钮时打印数据。
System.out.println(databaseConnection.getFKeyData(tableName,3));
System.out.println(databaseConnection.getFKeyData(tableName,4));
System.out.println(databaseConnection.getFKeyData(tableName,8));
3 gets the table the foreign key was imported from. 4 gets the name of the primary key column which is imported. 8 gets the name of foreign key column. If anyone can help I would greatly appreciate it.
3 获取导入外键的表。4 获取导入的主键列的名称。8 获取外键列的名称。如果有人可以提供帮助，我将不胜感激。
采纳答案by a_horse_with_no_name
Even though your while loop iterates over the whole ResultSet, the function will only return the last column in a FK constraint because on each iteration you overwrite the value of the previous iteration (fkTableData = rs.getString(i);). Btw: `fkTableData should actually be a local variable to the method, not an instance variable.
即使您的 while 循环遍历整个ResultSet，该函数也只会返回 FK 约束中的最后一列，因为在每次迭代中您都会覆盖上一次迭代 ( fkTableData = rs.getString(i);)的值。顺便说一句：`fkTableData 实际上应该是方法的局部变量，而不是实例变量。
Your function should return a List<String>not a String.
你的函数应该返回一个List<String>not a String。
Additionally: you are calling getImportedKeys()once for each columnin the ResultSet. That is extremely inefficient. If you were using Oracle you'd notice that immediately because retrieving FK information is extremely slow there (Postgres is much faster when accessing the system catalogs).
此外：您getImportedKeys()为ResultSet 中的每一列调用一次。这是极其低效的。如果您使用 Oracle，您会立即注意到这一点，因为在那里检索 FK 信息非常慢（Postgres 在访问系统目录时要快得多）。
As getImportedKeys()returns one row for each FK columnyou also need to collect all rows that belong to one single constraint definition (i.e for one parent/child table combination).
当getImportedKeys()为每个 FK列返回一行时，您还需要收集属于一个单一约束定义（即一个父/子表组合）的所有行。
Probably the best thing would be to define a class PkDefinitionthat stores all involved columns and the table names involved and have your function return List<PkDefinition>to avoid multiple calls for the same result set row.
可能最好的办法是定义一个类PkDefinition来存储所有涉及的列和涉及的表名，并让您的函数返回List<PkDefinition>以避免对同一结果集行进行多次调用。
