使用JDBC获取所有外键 [英] Get all foreign keys using JDBC

问题描述

我正在使用postgreSQL。我试图从一个表中获取所有的外键。这是我目前使用的方法。
$ b $ pre $ 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;



$ b $ p
$ b

这个代码可以工作，但是它只能得到最后一个外键，表中只有一个，但这不符合我的需要。我在网上看到的所有例子都非常相似，只给一个外键作为输出。
当前我只是在按下按钮时打印数据。

  System.out.println（databaseConnection.getFKeyData （表名，3））; 
 System.out.println（databaseConnection.getFKeyData（tableName，4））; 
 System.out.println（databaseConnection.getFKeyData（tableName，8））; 
  

3获取从外键导入的表。 4获取导入的主键列的名称。 8获取外键列的名称。
如果有人可以帮忙，我将不胜感激。

解决方案

即使您的while循环遍历整个 ResultSet ，函数将只返回FK约束中的最后一列，因为在每次迭代中，您将覆盖前一次迭代的值（ fkTableData = rs.getString（ ⅰ）; ）。 Btw：`fkTableData实际上应该是方法的局部变量，而不是实例变量。

你的函数应该返回一个 List< String> / code>不是字符串。

另外：您正在调用 getImportedKeys （） 每列一次在ResultSet中。这是非常低效的。如果您使用的是Oracle，您会立即注意到，因为检索FK信息的速度非常缓慢（Postgres在访问系统目录时速度更快）。
$ b

由于 getImportedKeys（）为每个FK 列返回一行，您还需要收集属于单个约束定义的所有行（即对于一个父/子表组合）。

可能最好的办法是定义一个类 PkDefinition 来存储所涉及的所有列和表名，让您的函数返回 List< PkDefinition> ，以避免对同一个结果集行进行多次调用。

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.

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.

解决方案

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.

Your function should return a List<String> not a String.

Additionally: you are calling getImportedKeys() once for each column in 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).

As getImportedKeys() returns one row for each FK column you also need to collect all rows that belong to one single constraint definition (i.e for one parent/child table combination).

Probably the best thing would be to define a class PkDefinition that 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.

这篇关于使用JDBC获取所有外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！