Java Code Examples for java.sql.DatabaseMetaData#getExportedKeys()
The following examples show how to use
java.sql.DatabaseMetaData#getExportedKeys() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: Show.java From jsqsh with Apache License 2.0 | 6 votes |
|
private ResultSet doExportedKeys(Session session, Connection con, Options options)
throws SQLException {
if (options.arguments.size() != 3
|| !options.arguments.get(1).equalsIgnoreCase("keys")) {
session.err.println("Use: \\show exported keys [[catalog.]schema.]table-pattern");
return null;
}
SQLConnectionContext ctx = (SQLConnectionContext) session.getConnectionContext();
SQLObjectName name = new SQLObjectName(ctx, options.arguments.get(2));
DatabaseMetaData meta = con.getMetaData();
return meta.getExportedKeys(
(options.catalog != null ? options.catalog : name.getCatalog()),
(options.schemaPattern != null ? options.schemaPattern : name.getSchema()),
(options.tablePattern != null ? options.tablePattern : name.getName()));
}
Example 2
| Source File: RDBMSDataHandler.java From micro-integrator with Apache License 2.0 | 6 votes |
|
/**
* This method reads foreign keys of the table.
*
* @param tableName Name of the table
* @throws ODataServiceFault
*/
private NavigationTable readForeignKeys(String tableName, DatabaseMetaData metaData, String catalog)
throws ODataServiceFault {
ResultSet resultSet = null;
try {
resultSet = metaData.getExportedKeys(catalog, null, tableName);
NavigationTable navigationLinks = new NavigationTable();
while (resultSet.next()) {
// foreignKeyTableName means the table name of the table which used columns as foreign keys in that table.
String primaryKeyColumnName = resultSet.getString("PKCOLUMN_NAME");
String foreignKeyTableName = resultSet.getString("FKTABLE_NAME");
String foreignKeyColumnName = resultSet.getString("FKCOLUMN_NAME");
List<NavigationKeys> columnList = navigationLinks.getNavigationKeys(foreignKeyTableName);
if (columnList == null) {
columnList = new ArrayList<>();
navigationLinks.addNavigationKeys(foreignKeyTableName, columnList);
}
columnList.add(new NavigationKeys(primaryKeyColumnName, foreignKeyColumnName));
}
return navigationLinks;
} catch (SQLException e) {
throw new ODataServiceFault(e, "Error in reading " + tableName + " table meta data. :" + e.getMessage());
} finally {
releaseResources(resultSet, null);
}
}
Example 3
| Source File: ClearDatabaseLifecycle.java From rice with Educational Community License v2.0 | 6 votes |
|
protected Map<String, List<String[]>> indexExportedKeys(DatabaseMetaData metaData, String schemaName) throws SQLException {
Map<String, List<String[]>> exportedKeys = new HashMap<String, List<String[]>>();
if (!isUsingDerby(metaData) && isUsingOracle(metaData)) {
ResultSet keyResultSet = metaData.getExportedKeys(null, schemaName, null);
while (keyResultSet.next()) {
String tableName = keyResultSet.getString("PKTABLE_NAME");
if (shouldTableBeCleared(tableName)) {
List<String[]> exportedKeyNames = exportedKeys.get(tableName);
if (exportedKeyNames == null) {
exportedKeyNames = new ArrayList<String[]>();
exportedKeys.put(tableName, exportedKeyNames);
}
final String fkName = keyResultSet.getString("FK_NAME");
final String fkTableName = keyResultSet.getString("FKTABLE_NAME");
exportedKeyNames.add(new String[] { fkName, fkTableName });
}
}
keyResultSet.close();
}
return exportedKeys;
}
Example 4
| Source File: MetaDataRegressionTest.java From r-course with MIT License | 6 votes |
|
/**
* Bug #43714 - useInformationSchema with DatabaseMetaData.getExportedKeys()
* throws exception
*/
public void testBug43714() throws Exception {
Connection c_IS = null;
try {
c_IS = getConnectionWithProps("useInformationSchema=true");
DatabaseMetaData dbmd = c_IS.getMetaData();
this.rs = dbmd.getExportedKeys("x", "y", "z");
} finally {
try {
if (c_IS != null) {
c_IS.close();
}
} catch (SQLException ex) {
}
}
}
Example 5
| Source File: MetaDataRegressionTest.java From Komondor with GNU General Public License v3.0 | 6 votes |
|
/**
* Bug #43714 - useInformationSchema with DatabaseMetaData.getExportedKeys()
* throws exception
*/
public void testBug43714() throws Exception {
Connection c_IS = null;
try {
c_IS = getConnectionWithProps("useInformationSchema=true");
DatabaseMetaData dbmd = c_IS.getMetaData();
this.rs = dbmd.getExportedKeys("x", "y", "z");
} finally {
try {
if (c_IS != null) {
c_IS.close();
}
} catch (SQLException ex) {
}
}
}
Example 6
| Source File: MetaResultSetTest.java From calcite-avatica with Apache License 2.0 | 6 votes |
|
@Test public void testGetExportedKeys() throws SQLException {
DatabaseMetaData metadata = getDatabaseMetadata();
try (ResultSet rs = metadata.getExportedKeys(null, null, null)) {
ResultSetMetaData rsMeta = rs.getMetaData();
assertEquals(14, rsMeta.getColumnCount());
assertColumn(rsMeta, 1, "PKTABLE_CAT", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 2, "PKTABLE_SCHEM", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 3, "PKTABLE_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 4, "PKCOLUMN_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 5, "FKTABLE_CAT", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 6, "FKTABLE_SCHEM", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 7, "FKTABLE_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 8, "FKCOLUMN_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 9, "KEY_SEQ", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 10, "UPDATE_RULE", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 11, "DELETE_RULE", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 12, "FK_NAME", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 13, "PK_NAME", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 14, "DEFERABILITY", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
}
}
Example 7
| Source File: DataMigrator.java From scriptella-etl with Apache License 2.0 | 6 votes |
|
private static int[][] getTablesMatrix(DbSchema schema, String[] tables) throws SQLException {
final DatabaseMetaData metaData = schema.getMetaData();
int n = tables.length;
int m[][] = new int[n][n];
for (int[] a : m) {
Arrays.fill(a, 0);
}
for (int i = 0; i < n; i++) {
final ResultSet rs = metaData.getExportedKeys(schema.getCatalog(), schema.getSchema(), tables[i]);
while (rs.next()) {
String t2 = rs.getString("FKTABLE_NAME");
int i2 = indexOf(tables, t2);
if (i2 >= 0) {
m[i][i2] += ((rs.getInt("DELETE_RULE") != 2) ? 10 : 1);
}
}
rs.close();
}
return m;
}
Example 8
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* Helper method for testing getExportedKeys - calls dmd.getExportedKeys for
* the JDBC call, and getExportedKeysODBC for the ODBC procedure
* @throws SQLException
*/
private ResultSet[] getExportedKeys(
String catalog, String schema, String table) throws SQLException
{
ResultSet[] rss = new ResultSet[2];
DatabaseMetaData dmd = getDMD();
rss[0]= dmd.getExportedKeys(catalog, schema, table);
rss[1]= getExportedKeysODBC(catalog, schema, table);
assertGetImportedAndExportedKeysShape(rss);
return rss;
}
Example 9
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* Helper method for testing getExportedKeys - calls dmd.getExportedKeys for
* the JDBC call, and getExportedKeysODBC for the ODBC procedure
* @throws SQLException
*/
private ResultSet[] getExportedKeys(
String catalog, String schema, String table) throws SQLException
{
ResultSet[] rss = new ResultSet[2];
DatabaseMetaData dmd = getDMD();
rss[0]= dmd.getExportedKeys(catalog, schema, table);
rss[1]= getExportedKeysODBC(catalog, schema, table);
assertGetImportedAndExportedKeysShape(rss);
return rss;
}
Example 10
| Source File: TestDatabaseMetaData.java From evosql with Apache License 2.0 | 5 votes |
|
/**
* Basic test of DatabaseMetaData functions that access system tables
*/
public void testTwo() throws Exception {
Connection conn = newConnection();
int updateCount;
try {
TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt");
DatabaseMetaData dbmeta = conn.getMetaData();
dbmeta.allProceduresAreCallable();
dbmeta.getBestRowIdentifier(null, null, "T_1",
DatabaseMetaData.bestRowTransaction,
true);
dbmeta.getCatalogs();
dbmeta.getColumnPrivileges(null, "PUBLIC", "T_1", "%");
dbmeta.getColumns("PUBLIC", "PUBLIC", "T_1", "%");
dbmeta.getCrossReference(null, null, "T_1", null, null, "T_1");
dbmeta.getExportedKeys(null, null, "T_1");
dbmeta.getFunctionColumns(null, "%", "%", "%");
dbmeta.getFunctions(null, "%", "%");
dbmeta.getImportedKeys("PUBLIC", "PUBLIC", "T_1");
dbmeta.getIndexInfo("PUBLIC", "PUBLIC", "T1", true, true);
dbmeta.getPrimaryKeys("PUBLIC", "PUBLIC", "T_1");
dbmeta.getProcedureColumns(null, null, "%", "%");
dbmeta.getProcedures("PUBLIC", "%", "%");
dbmeta.getSchemas(null, "#");
dbmeta.getTablePrivileges(null, "%", "%");
dbmeta.getUDTs(null, "%", "%", new int[]{ Types.DISTINCT });
} catch (Exception e) {
assertTrue("unable to prepare or execute DDL", false);
} finally {
conn.close();
}
}
Example 11
| Source File: MetadataTest.java From r-course with MIT License | 5 votes |
|
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetExportedKeysUsingInfoSchema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
this.stmt.executeUpdate("CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB");
this.stmt.executeUpdate(
"CREATE TABLE child(id INT, parent_id INT, " + "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getExportedKeys(null, null, "parent");
this.rs.next();
assertEquals("parent", this.rs.getString("PKTABLE_NAME"));
assertEquals("id", this.rs.getString("PKCOLUMN_NAME"));
assertEquals("child", this.rs.getString("FKTABLE_NAME"));
assertEquals("parent_id", this.rs.getString("FKCOLUMN_NAME"));
} finally {
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
if (conn1 != null) {
conn1.close();
}
}
}
}
Example 12
| Source File: ConcurrentConnTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
public static void getExportedKeys(DatabaseMetaData dmd, String tablePattern,PrintStream out)
throws SQLException
{
ResultSet rs = dmd.getExportedKeys(null, null, tablePattern);
while (rs.next())
{
// 1.PKTABLE_CAT String => primary key table catalog (may be null)
String pkTableCat = rs.getString(1);
// 2.PKTABLE_SCHEM String => primary key table schema (may be null)
String pkTableSchem = rs.getString(2);
// 3.PKTABLE_NAME String => primary key table name
String pkTableName = rs.getString(3);
// 4.PKCOLUMN_NAME String => primary key column name
String pkColumnName = rs.getString(4);
// 5.FKTABLE_CAT String => foreign key table catalog
// (may be null) being exported (may be null)
String fkTableCat = rs.getString(5);
// 6.FKTABLE_SCHEM String => foreign key table schema
// (may be null) being exported (may be null)
String fkTableSchem = rs.getString(6);
// 7.FKTABLE_NAME String => foreign key table name being exported
String fkTableName = rs.getString(7);
// 8.FKCOLUMN_NAME String => foreign key column name being exported
String fkColumnName = rs.getString(8);
// 9.KEY_SEQ short => sequence number within foreign key
short keySeq = rs.getShort(9);
// 10.UPDATE_RULE short => What happens to foreign key when
// primary is updated:
// importedNoAction - do not allow update of primary key if
// it has been imported
// importedKeyCascade - change imported key to agree
// with primary key update
// importedKeySetNull - change imported key to NULL if its
// primary key has been updated
// importedKeySetDefault - change imported key to default
// values if its primary key has
// been updated
// importedKeyRestrict - same as importedKeyNoAction
// (for ODBC 2.x compatibility)
short updateRule = rs.getShort(10);
// 11.DELETE_RULE short => What happens to the foreign key
// when primary is deleted.
// importedKeyNoAction - do not allow delete of primary key
// if it has been imported
// importedKeyCascade - delete rows that import a deleted key
// importedKeySetNull - change imported key to NULL if
// its primary key has been deleted
// importedKeyRestrict - same as importedKeyNoAction
// (for ODBC 2.x compatibility)
// importedKeySetDefault - change imported key to default
// if its primary key has
// been deleted
short deleteRule = rs.getShort(11);
// 12.FK_NAME String => foreign key name (may be null)
String fkName = rs.getString(12);
// 13.PK_NAME String => primary key name (may be null)
String pkName = rs.getString(13);
// 14.DEFERRABILITY short => can the evaluation of foreign key
// constraints be deferred until commit
// importedKeyInitiallyDeferred - see SQL92 for definition
// importedKeyInitiallyImmediate - see SQL92 for definition
// importedKeyNotDeferrable - see SQL92 for definition
short deferrability = rs.getShort(14);
}
rs.close();
}
Example 13
| Source File: metadataMultiConn.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
public static void getExportedKeys(DatabaseMetaData dmd, String tablePattern,PrintStream out)
throws SQLException
{
ResultSet rs = dmd.getExportedKeys(null, null, tablePattern);
while (rs.next())
{
// 1.PKTABLE_CAT String => primary key table catalog (may be null)
String pkTableCat = rs.getString(1);
// 2.PKTABLE_SCHEM String => primary key table schema (may be null)
String pkTableSchem = rs.getString(2);
// 3.PKTABLE_NAME String => primary key table name
String pkTableName = rs.getString(3);
// 4.PKCOLUMN_NAME String => primary key column name
String pkColumnName = rs.getString(4);
// 5.FKTABLE_CAT String => foreign key table catalog
// (may be null) being exported (may be null)
String fkTableCat = rs.getString(5);
// 6.FKTABLE_SCHEM String => foreign key table schema
// (may be null) being exported (may be null)
String fkTableSchem = rs.getString(6);
// 7.FKTABLE_NAME String => foreign key table name being exported
String fkTableName = rs.getString(7);
// 8.FKCOLUMN_NAME String => foreign key column name being exported
String fkColumnName = rs.getString(8);
// 9.KEY_SEQ short => sequence number within foreign key
short keySeq = rs.getShort(9);
// 10.UPDATE_RULE short => What happens to foreign key when
// primary is updated:
// importedNoAction - do not allow update of primary key if
// it has been imported
// importedKeyCascade - change imported key to agree
// with primary key update
// importedKeySetNull - change imported key to NULL if its
// primary key has been updated
// importedKeySetDefault - change imported key to default
// values if its primary key has
// been updated
// importedKeyRestrict - same as importedKeyNoAction
// (for ODBC 2.x compatibility)
short updateRule = rs.getShort(10);
// 11.DELETE_RULE short => What happens to the foreign key
// when primary is deleted.
// importedKeyNoAction - do not allow delete of primary key
// if it has been imported
// importedKeyCascade - delete rows that import a deleted key
// importedKeySetNull - change imported key to NULL if
// its primary key has been deleted
// importedKeyRestrict - same as importedKeyNoAction
// (for ODBC 2.x compatibility)
// importedKeySetDefault - change imported key to default
// if its primary key has
// been deleted
short deleteRule = rs.getShort(11);
// 12.FK_NAME String => foreign key name (may be null)
String fkName = rs.getString(12);
// 13.PK_NAME String => primary key name (may be null)
String pkName = rs.getString(13);
// 14.DEFERRABILITY short => can the evaluation of foreign key
// constraints be deferred until commit
// importedKeyInitiallyDeferred - see SQL92 for definition
// importedKeyInitiallyImmediate - see SQL92 for definition
// importedKeyNotDeferrable - see SQL92 for definition
short deferrability = rs.getShort(14);
}
rs.close();
}
Example 14
| Source File: DatabaseMetaDataInternalIT.java From snowflake-jdbc with Apache License 2.0 | 4 votes |
|
@Test
@ConditionalIgnoreRule.ConditionalIgnore(condition = RunningOnGithubAction.class)
public void testGetMetaDataUseConnectionCtx() throws SQLException
{
Connection connection = getConnection();
Statement statement = connection.createStatement();
// setup: reset session db and schema, enable the parameter
statement.execute("use database JDBC_DB1");
statement.execute("use schema JDBC_SCHEMA11");
statement.execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true");
DatabaseMetaData databaseMetaData = connection.getMetaData();
// this should only return JDBC_SCHEMA11
ResultSet resultSet = databaseMetaData.getSchemas(null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
// only returns tables in JDBC_DB1.JDBC_SCHEMA11
resultSet = databaseMetaData.getTables(null, null, null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
statement.execute("use schema JDBC_SCHEMA12");
resultSet = databaseMetaData.getTables(null, null, null, null);
assertEquals(2, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getColumns(null, null, null, null);
assertEquals(4, getSizeOfResultSet(resultSet));
statement.execute("use schema TEST_CTX");
resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getImportedKeys(null, null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getExportedKeys(null, null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getCrossReference(null, null, null, null,
null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
}
Example 15
| Source File: DatabaseMetaDataIT.java From snowflake-jdbc with Apache License 2.0 | 4 votes |
|
@Test
public void testUseConnectionCtx() throws SQLException
{
try (Connection connection = getConnection())
{
connection.createStatement().execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true");
String schema = connection.getSchema();
DatabaseMetaData databaseMetaData = connection.getMetaData();
// create tables within current schema.
connection.createStatement().execute("create or replace schema TEST_CTX");
connection.createStatement().execute("create or replace table CTX_TBL_A (colA string, colB decimal, " +
"colC number PRIMARY KEY);");
connection.createStatement().execute("create or replace table CTX_TBL_B (colA string, colB decimal, " +
"colC number FOREIGN KEY REFERENCES CTX_TBL_A (colC));");
connection.createStatement().execute("create or replace table CTX_TBL_C (colA string, colB decimal, " +
"colC number, colD int, colE timestamp, colF string, colG number);");
// now create more tables under current schema
connection.createStatement().execute("use schema " + schema);
connection.createStatement().execute("create or replace table CTX_TBL_D (colA string, colB decimal, " +
"colC number PRIMARY KEY);");
connection.createStatement().execute("create or replace table CTX_TBL_E (colA string, colB decimal, " +
"colC number FOREIGN KEY REFERENCES CTX_TBL_D (colC));");
connection.createStatement().execute("create or replace table CTX_TBL_F (colA string, colB decimal, " +
"colC number, colD int, colE timestamp, colF string, colG number);");
// this should only return TEST_CTX schema and tables
connection.createStatement().execute("use schema TEST_CTX");
ResultSet resultSet = databaseMetaData.getSchemas(null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getTables(null, null, null, null);
assertEquals(3, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getColumns(null, null, null, null);
assertEquals(13, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getImportedKeys(null, null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getExportedKeys(null, null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
resultSet = databaseMetaData.getCrossReference(null, null, null, null,
null, null);
assertEquals(1, getSizeOfResultSet(resultSet));
// Now compare results to setting client metadata to false.
connection.createStatement().execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=false");
databaseMetaData = connection.getMetaData();
resultSet = databaseMetaData.getSchemas(null, null);
assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));
resultSet = databaseMetaData.getTables(null, null, null, null);
assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(6));
resultSet = databaseMetaData.getColumns(null, null, null, null);
assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(26));
resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));
resultSet = databaseMetaData.getImportedKeys(null, null, null);
assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));
resultSet = databaseMetaData.getExportedKeys(null, null, null);
assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));
resultSet = databaseMetaData.getCrossReference(null, null, null, null,
null, null);
assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));
}
}
Example 16
| Source File: DatabaseMetaDataIT.java From snowflake-jdbc with Apache License 2.0 | 4 votes |
|
@Test
public void testGetExportedKeys() throws Throwable
{
try (Connection connection = getConnection())
{
String database = connection.getCatalog();
String schema = connection.getSchema();
final String targetTable1 = "T0";
final String targetTable2 = "T1";
connection.createStatement().execute(
"create or replace table " +
targetTable1 + "(C1 int primary key, C2 string)");
connection.createStatement().execute(
"create or replace table " +
targetTable2 + "(C1 int primary key, C2 string, C3 int references " + targetTable1 + ")");
DatabaseMetaData metaData = connection.getMetaData();
ResultSet resultSet = metaData.getExportedKeys(database, schema, targetTable1);
verifyResultSetMetaDataColumns(resultSet, DBMetadataResultSetMetadata.GET_FOREIGN_KEYS);
assertTrue(resultSet.next());
assertEquals(database, resultSet.getString("PKTABLE_CAT"));
assertEquals(schema, resultSet.getString("PKTABLE_SCHEM"));
assertEquals(targetTable1, resultSet.getString("PKTABLE_NAME"));
assertEquals("C1", resultSet.getString("PKCOLUMN_NAME"));
assertEquals(database, resultSet.getString("FKTABLE_CAT"));
assertEquals(schema, resultSet.getString("FKTABLE_SCHEM"));
assertEquals(targetTable2, resultSet.getString("FKTABLE_NAME"));
assertEquals("C3", resultSet.getString("FKCOLUMN_NAME"));
assertEquals(1, resultSet.getInt("KEY_SEQ"));
assertNotEquals("", resultSet.getString("PK_NAME"));
assertNotEquals("", resultSet.getString("FK_NAME"));
assertEquals(DatabaseMetaData.importedKeyNoAction,
resultSet.getShort("UPDATE_RULE"));
assertEquals(DatabaseMetaData.importedKeyNoAction,
resultSet.getShort("DELETE_RULE"));
assertEquals(DatabaseMetaData.importedKeyNotDeferrable,
resultSet.getShort("DEFERRABILITY"));
connection.createStatement().execute("drop table if exists " + targetTable1);
connection.createStatement().execute("drop table if exists " + targetTable2);
}
}
Example 17
| Source File: MetaDataJavaPrinter.java From aceql-http with GNU Lesser General Public License v2.1 | 4 votes |
|
private void printGetExportedKeys(String tableName) throws SQLException {
if (tableName == null) {
throw new NullPointerException("tableName is null!");
}
/**
<pre><code>
1.PKTABLE_CAT String => primary key table catalog (may be null)
2.PKTABLE_SCHEM String => primary key table schema (may be null)
3.PKTABLE_NAME String => primary key table name
4.PKCOLUMN_NAME String => primary key Column name
5.FKTABLE_CAT String => foreign key table catalog (may be null)being exported (may be null)
6.FKTABLE_SCHEM String => foreign key table schema (may be null)being exported (may be null)
7.FKTABLE_NAME String => foreign key table namebeing exported
8.FKCOLUMN_NAME String => foreign key Column namebeing exported
9.KEY_SEQ short => sequence number within foreign key( a valueof 1 represents the first Column of the foreign key, a value of 2 wouldrepresent the second Column within the foreign key).
10.UPDATE_RULE short => What happens toforeign key when primary is updated: ◦ importedNoAction - do not allow update of primarykey if it has been imported
◦ importedKeyCascade - change imported key to agreewith primary key update
◦ importedKeySetNull - change imported key to NULL ifits primary key has been updated
◦ importedKeySetDefault - change imported key to default valuesif its primary key has been updated
◦ importedKeyRestrict - same as importedKeyNoAction(for ODBC 2.x compatibility)
11.DELETE_RULE short => What happens tothe foreign key when primary is deleted. ◦ importedKeyNoAction - do not allow delete of primarykey if it has been imported
◦ importedKeyCascade - delete rows that import a deleted key
◦ importedKeySetNull - change imported key to NULL ifits primary key has been deleted
◦ importedKeyRestrict - same as importedKeyNoAction(for ODBC 2.x compatibility)
◦ importedKeySetDefault - change imported key to default ifits primary key has been deleted
12.FK_NAME String => foreign key name (may be null)
13.PK_NAME String => primary key name (may be null)
14.DEFERRABILITY short => can the evaluation of foreign keyconstraints be deferred until commit ◦ importedKeyInitiallyDeferred - see SQL92 for definition
◦ importedKeyInitiallyImmediate - see SQL92 for definition
◦ importedKeyNotDeferrable - see SQL92 for definition
</code></pre>
*/
DatabaseMetaData databaseMetaData = connection.getMetaData();
ResultSet rs = databaseMetaData.getExportedKeys(catalog, null, tableName);
while (rs.next()) {
System.out.println();
for (int i = 1; i < 15; i++) {
System.out.println("databaseMetaData.getExportedKeys( " + tableName + ") " + i + ": " + rs.getString(i));
}
}
}
Example 18
| Source File: metadataMultiConnTest.java From spliceengine with GNU Affero General Public License v3.0 | 4 votes |
|
public void getExportedKeys(DatabaseMetaData dmd) throws SQLException {
ResultSet rs = dmd.getExportedKeys(null, null, "%");
JDBC.assertDrainResults(rs);
}
Example 19
| Source File: metadataMultiConn.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
public static void getExportedKeys(DatabaseMetaData dmd, String tablePattern,PrintStream out)
throws SQLException
{
ResultSet rs = dmd.getExportedKeys(null, null, tablePattern);
while (rs.next())
{
// 1.PKTABLE_CAT String => primary key table catalog (may be null)
String pkTableCat = rs.getString(1);
// 2.PKTABLE_SCHEM String => primary key table schema (may be null)
String pkTableSchem = rs.getString(2);
// 3.PKTABLE_NAME String => primary key table name
String pkTableName = rs.getString(3);
// 4.PKCOLUMN_NAME String => primary key column name
String pkColumnName = rs.getString(4);
// 5.FKTABLE_CAT String => foreign key table catalog
// (may be null) being exported (may be null)
String fkTableCat = rs.getString(5);
// 6.FKTABLE_SCHEM String => foreign key table schema
// (may be null) being exported (may be null)
String fkTableSchem = rs.getString(6);
// 7.FKTABLE_NAME String => foreign key table name being exported
String fkTableName = rs.getString(7);
// 8.FKCOLUMN_NAME String => foreign key column name being exported
String fkColumnName = rs.getString(8);
// 9.KEY_SEQ short => sequence number within foreign key
short keySeq = rs.getShort(9);
// 10.UPDATE_RULE short => What happens to foreign key when
// primary is updated:
// importedNoAction - do not allow update of primary key if
// it has been imported
// importedKeyCascade - change imported key to agree
// with primary key update
// importedKeySetNull - change imported key to NULL if its
// primary key has been updated
// importedKeySetDefault - change imported key to default
// values if its primary key has
// been updated
// importedKeyRestrict - same as importedKeyNoAction
// (for ODBC 2.x compatibility)
short updateRule = rs.getShort(10);
// 11.DELETE_RULE short => What happens to the foreign key
// when primary is deleted.
// importedKeyNoAction - do not allow delete of primary key
// if it has been imported
// importedKeyCascade - delete rows that import a deleted key
// importedKeySetNull - change imported key to NULL if
// its primary key has been deleted
// importedKeyRestrict - same as importedKeyNoAction
// (for ODBC 2.x compatibility)
// importedKeySetDefault - change imported key to default
// if its primary key has
// been deleted
short deleteRule = rs.getShort(11);
// 12.FK_NAME String => foreign key name (may be null)
String fkName = rs.getString(12);
// 13.PK_NAME String => primary key name (may be null)
String pkName = rs.getString(13);
// 14.DEFERRABILITY short => can the evaluation of foreign key
// constraints be deferred until commit
// importedKeyInitiallyDeferred - see SQL92 for definition
// importedKeyInitiallyImmediate - see SQL92 for definition
// importedKeyNotDeferrable - see SQL92 for definition
short deferrability = rs.getShort(14);
}
rs.close();
}
Example 20
| Source File: AceQLMetaData.java From aceql-http with GNU Lesser General Public License v2.1 | 4 votes |
|
/**
* Returns the foreign exported keys for the passed table name
*
* @param tableName the table name for the passed table name
* @return the foreign exported keys
* @throws SQLException it any SQL Exception occurs
*/
public List<ExportedKey> getExportedKeys(String tableName) throws SQLException {
if (tableName == null) {
throw new NullPointerException("tableName is null!");
}
if (!tableNamesSet.contains(tableName.toLowerCase())) {
throw new IllegalArgumentException("table does not exists: " + tableName);
}
/**
* <pre>
<code>
databaseMetaData.getExportedKeys( customer) 1: null 1.PKTABLE_CAT String => primary key table catalog (may be null)
databaseMetaData.getExportedKeys( customer) 2: public 2.PKTABLE_SCHEM String => primary key table schema (may be null)
databaseMetaData.getExportedKeys( customer) 3: customer 3.PKTABLE_NAME String => primary key table name
databaseMetaData.getExportedKeys( customer) 4: customer_id 4.PKCOLUMN_NAME String => primary key column name
databaseMetaData.getExportedKeys( customer) 5: null 5.FKTABLE_CAT String => foreign key table catalog (may be null)being exported (may be null)
databaseMetaData.getExportedKeys( customer) 6: public 6.FKTABLE_SCHEM String => foreign key table schema (may be null)being exported (may be null)
databaseMetaData.getExportedKeys( customer) 7: orderlog2 7.FKTABLE_NAME String => foreign key table namebeing exported
databaseMetaData.getExportedKeys( customer) 8: customer_id 8.FKCOLUMN_NAME String => foreign key column namebeing exported
databaseMetaData.getExportedKeys( customer) 9: 1 9.KEY_SEQ short => sequence number within foreign key( a valueof 1 represents the first column
databaseMetaData.getExportedKeys( customer) 10: 3 10.UPDATE_RULE short => What happens toforeign key when primary is updated: ◦ importedNoAction
databaseMetaData.getExportedKeys( customer) 11: 3 11.DELETE_RULE short
databaseMetaData.getExportedKeys( customer) 12: orderlog2_customer_id_fkey 12.FK_NAME String => foreign key name (may be null)
databaseMetaData.getExportedKeys( customer) 13: customer_pkey 13.PK_NAME String => primary key name (may be null)
databaseMetaData.getExportedKeys( customer) 14: 7 14.DEFERRABILITY short => can the evaluation of foreign keyconstraints be deferred until commit
</code>
* </pre>
*/
DatabaseMetaData databaseMetaData = connection.getMetaData();
ResultSet rs = databaseMetaData.getExportedKeys(catalog, schema, tableName);
List<ExportedKey> exportedKeys = new ArrayList<>();
while (rs.next()) {
ExportedKey exportedKey = new ExportedKey();
int i = 1;
exportedKey.setCatalog(rs.getString(i++));
exportedKey.setSchema(rs.getString(i++));
exportedKey.setPrimaryKeyTable(rs.getString(i++));
exportedKey.setPrimaryKeyColumn(rs.getString(i++));
exportedKey.setForeignKeyCatalog(rs.getString(i++));
exportedKey.setForeignKeySchema(rs.getString(i++));
exportedKey.setForeignKeyTable(rs.getString(i++));
exportedKey.setForeignKeyColumn(rs.getString(i++));
exportedKey.setKeySequence(rs.getInt(i++));
exportedKey.setUpdateRule(MetaDataJavaUtil.decodeRule(rs.getInt(i++)));
exportedKey.setDeleteRule(MetaDataJavaUtil.decodeRule(rs.getInt(i++)));
exportedKey.setForeignKeyName(rs.getString(i++));
exportedKey.setPrimaryKeyName(rs.getString(i++));
exportedKey.setDeferrability(rs.getInt(i++));
exportedKeys.add(exportedKey);
}
return exportedKeys;
}
