是否可以使用JDBC获取FK的目标?
我只使用getExportedKeys方法获得了源关系属性:
ResultSet fksE = dbm.getExportedKeys(conn.getCatalog(),null,"SourceRelName");
String fkTableNameE = fksE.getString("FKTABLE_NAME");
String fkColumnNameE = fksE.getString("FKCOLUMN_NAME");
然后我尝试使用getImportedKeys方法,但它没有用.
这是我的班级:
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 ArrayListMysqL:
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,"%",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,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(),relation);
while (fksE.next()) {
String fkTableNameE = fksE.getString("FKTABLE_NAME");
String fkColumnNameE = fksE.getString("FKCOLUMN_NAME");
ResultSet fksI = dbm.getImportedKeys(conn.getCatalog(),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();
}
}
}
}
最佳答案
getImportedKeys对我有用.一些代码:
private static void printForeignKeys(Connection connection,String tableName) throws sqlException {
DatabaseMetaData MetaData = connection.getMetaData();
ResultSet foreignKeys = MetaData.getImportedKeys(connection.getCatalog(),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);
}
}