JDBC get the relation+attribute referenced by a foreign key

Question

Is it possible to get the target of a FK with JDBC?

I only got the Source Relation+Attributes with the getExportedKeys method:

ResultSet fksE = dbm.getExportedKeys(conn.getCatalog(), null, "SourceRelName");
String fkTableNameE = fksE.getString("FKTABLE_NAME");
String fkColumnNameE = fksE.getString("FKCOLUMN_NAME");

Then I tried using the getImportedKeys method, but it didnt work.

Here is my Class:

public class DbConnection {

  private String userName = null;
  private String password = null;
  private String driver = null;
  private String DbUrl = null;
  private Connection conn = null;
  private DatabaseMetaData dbm = null;
  private ArrayList<String> relationNames = null;
  private ResultSet tables = null;
  private Database database = null;

  public DbConnection(String user, String pwd, dbaCore.data.dBTypes.TypeEnum type, String url) {
    userName = user;
    password = pwd;
    switch (type) {
      case MYSQL:
        driver = "com.mysql.jdbc.Driver";
        break;
      case POSTGRES:
        break;
      case MSDB:
        break;
      case ORACLE:
        break;
      case SQLITE:
        break;
    }
    DbUrl = url;
    database = new Database();
    relationNames = new ArrayList<>();

    try {
      Class.forName(driver).newInstance();
      conn = DriverManager.getConnection(DbUrl, userName, password);
      System.out.println("Database connection established");

      //Get Relation Names
      dbm = conn.getMetaData();
      String[] types = {"TABLE"};
      tables = dbm.getTables(null, null, "%", types);
      while (tables.next()) {
        String table = tables.getString("TABLE_NAME");
        relationNames.add(table);
      }

      //Get Attributes
      for (String relation : relationNames) {
        RelationSchema tmpRelation = new RelationSchema(relation);

        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM " + relation);
        ResultSetMetaData md = rs.getMetaData();
        int col = md.getColumnCount();
        System.out.println("ATTRIBUTES: ");
        for (int i = 1; i <= col; i++) {
          String col_name = md.getColumnName(i);
          System.out.println(col_name);
          tmpRelation.addAttribute(col_name);
        }
        ResultSet pks = dbm.getPrimaryKeys(null, null, relation);
        while (pks.next()) {
          String columnName = pks.getString("COLUMN_NAME");
          System.out.println("Primary Key: " + columnName);
          tmpRelation.getAttributeByName(columnName).setIsPrimaryKey(true);
        }
        database.addRelationSchema(tmpRelation);
      }

      //Get Foreignkeys
      for (String relation : relationNames) {
        ResultSet fksE = dbm.getExportedKeys(conn.getCatalog(), null, relation);

        while (fksE.next()) {
          String fkTableNameE = fksE.getString("FKTABLE_NAME");
          String fkColumnNameE = fksE.getString("FKCOLUMN_NAME");
      ResultSet fksI = dbm.getImportedKeys(conn.getCatalog(), null, fkTableNameE);
      String fkTableNameI = fksI.getString("FKTABLE_NAME");
      String fkColumnNameI = fksI.getString("FKCOLUMN_NAME");

      System.out.println("FKTABLE_NAME_E: " + fkTableNameE + " - FKCOLUMN_NAME_E: " + fkColumnNameE);
      System.out.println("FKTABLE_NAME_I: " + fkTableNameI + " - FKCOLUMN_NAME_I: " + fkColumnNameI);
    }
  }

} catch (Exception e) {
  System.err.println("Cannot connect to database server");
  e.printStackTrace();
}
finally
{
  if (conn != null) {
    try {
      conn.close();
      System.out.println("Database connection closed");
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}


}

Answer 1

getImportedKeys works for me. Some code:

private static void printForeignKeys(Connection connection, String tableName) throws SQLException {
    DatabaseMetaData metaData = connection.getMetaData();
    ResultSet foreignKeys = metaData.getImportedKeys(connection.getCatalog(), null, tableName);
    while (foreignKeys.next()) {
        String fkTableName = foreignKeys.getString("FKTABLE_NAME");
        String fkColumnName = foreignKeys.getString("FKCOLUMN_NAME");
        String pkTableName = foreignKeys.getString("PKTABLE_NAME");
        String pkColumnName = foreignKeys.getString("PKCOLUMN_NAME");
        System.out.println(fkTableName + "." + fkColumnName + " -> " + pkTableName + "." + pkColumnName);
    }
}