通過JDBC查詢資料庫外鍵對象-適用DB2, Oracle 和 MSSQL

通過JDBC訪問資料庫是，通過Connection對象的getMetaData()方法可以得到Table, Table Column, View, Index, Primary Key資料庫物件，但是它沒有提供外鍵對象，下面介紹如何訪問外鍵對象。

程式片段如下：

stmt = conn.createStatement();

String strStmt = "";

if(conn.getDbDBMS().equals(DBConstants.DB_TYPE_DB2)){

strStmt =

"SELECT CONSTNAME AS CONSTNAME, TABNAME AS TABNAME, TRIM(REFTABSCHEMA) AS REFTABSCHEMA, TRIM(REFTABNAME) AS REFTABNAME, COLCOUNT AS COLCOUNT, DELETERULE AS DELETERULE, TRIM(FK_COLNAMES) AS FK_COLNAMES, TRIM(PK_COLNAMES) AS PK_COLNAMES FROM SYSCAT.REFERENCES WHERE TABSCHEMA = '%s' AND TABNAME = '%s' AND OWNERTYPE = 'U' ORDER BY CONSTNAME";

strStmt = String.format(strStmt, schemaName, tableName);

}

elseif(conn.getDbDBMS().equals(DBConstants.DB_TYPE_ORACLE)){

strStmt =

"WITH ALL_CONS AS (SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME,DELETE_RULE FROM ALL_CONSTRAINTS WHERE OWNER='%s' AND TABLE_NAME='%s' AND CONSTRAINT_TYPE = 'R')," +

" PK_TABLE AS (SELECT P.OWNER,P.CONSTRAINT_NAME,P.TABLE_NAME,P.COLUMN_NAME,P.POSITION FROM ALL_CONS_COLUMNS P JOIN ALL_CONS A ON A.R_CONSTRAINT_NAME= P.CONSTRAINT_NAME AND A.R_OWNER=P.OWNER)," +

" FK_TABLE AS (SELECT F.OWNER,F.CONSTRAINT_NAME,F.TABLE_NAME,F.COLUMN_NAME,F.POSITION,A.R_OWNER,A.R_CONSTRAINT_NAME,A.DELETE_RULE FROM ALL_CONS_COLUMNS F JOIN ALL_CONS A ON A.CONSTRAINT_NAME= F.CONSTRAINT_NAME AND A.OWNER=F.OWNER)" +

" SELECT DISTINCT F.CONSTRAINT_NAME AS CONSTNAME, F.TABLE_NAME AS TABNAME,F.R_OWNER AS REFTABSCHEMA, P.TABLE_NAME AS REFTABNAME, F.POSITION AS COLCOUNT, F.DELETE_RULE AS DELETERULE,F.COLUMN_NAME AS FK_COLNAMES, P.COLUMN_NAME AS PK_COLNAMES FROM FK_TABLE F" +

" JOIN PK_TABLE P ON P.CONSTRAINT_NAME=F.R_CONSTRAINT_NAME AND P.OWNER=F.R_OWNER ORDER BY F.CONSTRAINT_NAME ";

strStmt = String.format(strStmt, schemaName, tableName);

}

else{

strStmt =

"SELECT C.CONSTRAINT_NAME AS CONSTNAME, CU1.TABLE_NAME AS TABNAME, CASE WHEN C.UPDATE_RULE='CASCADE' THEN 1 ELSE 0 END AS UPDATERULE, CASE WHEN DELETE_RULE='CASCADE' THEN 1 ELSE 0 END AS DELETERULE, PK.TABLE_SCHEMA AS REFTABSCHEMA, PK.TABLE_NAME AS REFTABNAME, CU1.COLUMN_NAME AS FK_COLNAMES, CU2.COLUMN_NAME AS PK_COLNAMES FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C " 

" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA " +

" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND C.UNIQUE_CONSTRAINT_SCHEMA = PK.TABLE_SCHEMA " +

" JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU1 ON CU1.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND CU1.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA " +

" JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON CU2.CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND CU2.CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA WHERE FK.TABLE_SCHEMA ='%s' AND FK.TABLE_NAME = '%s' ORDER BY C.CONSTRAINT_NAME";

strStmt = String.format(strStmt, schemaName,tableName);

}

 rs = stmt.executeQuery(strStmt);