如何获得外键引用Derby JDBC？ [英] How to get foreign key reference with Derby JDBC?

问题描述

我有用

CREATE TABLE COUNTRY (NAME CHAR(16) PRIMARY KEY)
CREATE TABLE PERSON (NAME CAHR(32) PRIMARY KEY,
       CITIZENSHIP CHAR(16) CONSTRAINT CY_FK REFERENCES COUNTRY (NAME))

那么如何在执行 SELECT 之后获得外键的Table.Column（ COUNTRY.NAME / code>查询 PERSON 表？
是否可以通过JDBC ResultSet ， ResultSetMetaData 或类似的东西来做？

事实上，我需要知道：

So how can I get Table.Column (COUNTRY.NAME) reference of the foreign key after I've performed SELECT query on PERSON table? Is it possible to do via JDBC ResultSet, ResultSetMetaData or something alike?
In fact, I need to know:

• 任一列都有一个外键约束;


• 约束值列表。
  

• either column has a Foreign Key constraint;
• list of constraint values.

好吧，我在 SYS上做了 SELECT SYSCONSTRAINTS ，但只有类型的约束（'P'，'F'等），但没有引用表'名称。

Well, I did SELECT on SYS.SYSCONSTRAINTS but there are only types of constraints ('P', 'F', etc) but no Referenced Tables' names.

/ db> $ db
$ b

I send queries to the Database via JDBC

PreparedStatement stmtDerbyDb = DriverManager.getConnection(dbConnectString).prepareStatement("SELECT * FROM \"" + dbTableOrView + "\"");
ResultSet rsltDerbyDb = stmtDerbyDb.executeQuery();
ResultSetMetaData rsmdDerbyDb = rsltDerbyDb.getMetaData();
...

所有变量 stmtDerbyDb > rsltDerbyDb ， rsmdDerbyDb 用于其他代码。我需要知道：是否可能通过JDBC从 dbTableOrView 获取外键约束，或者我应该查询 SYS。* 系统表吗？ p>

All the variables stmtDerbyDb, rsltDerbyDb, rsmdDerbyDb are used in further code. I need to know: is it possible to fetch Foreign Key constraints from dbTableOrView via JDBC or shall I query SYS.* system tables somehow?

推荐答案

可能的决定：

...
Connection connTableView = DriverManager.getConnection("jdbc:Derby:database_name:create=false");
DatabaseMetaData dbmdTableView = connTableView.getMetaData();
ResultSet rsltTableConstraints = dbmdTableView.getImportedKeys(null, null, "PERSON");
PreparedStatement prstTableContents = connTableView.prepareStatement("SELECT * FROM PERSON");
ResultSet rsltTableContents = prstTableContents.executeQuery();
...
buildTableView(rsltTableContents);
rsltTableContents.close();
...
while (rsltTableConstraints.next()) {
    PreparedStatement prstConstraints = connTableView.prepareStatement("SELECT " + rsltTableConstraints.getString("PKCOLUMN_NAME") + " FROM " + rsltTableConstraints.getString("PKTABLE_NAME"));
    ResultSet rsltConstraintsForCol = prstConstraints.execute();
    ...
    setColumnConstraints(rsltConstraintsForCol, rsltTableConstraints.getString("FKCOLUMN_NAME"));
    ...
    rsltConstraintForCol.close();
    prstConstraints.close();
}
...

这应该用作 ComboBox 下拉列表，每列有约束（当然是FK）。

Which is supposed to be used as ComboBox dropdowns with constraints per column (with FK of course).

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