Get all foreign keys using JDBC

问题:

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.

回答1:

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.