Java Code Examples for java.sql.DatabaseMetaData#getCrossReference()
The following examples show how to use
java.sql.DatabaseMetaData#getCrossReference() .
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: MetaResultSetTest.java From calcite-avatica with Apache License 2.0 | 6 votes |
|
@Test public void testGetCrossReference() throws SQLException {
DatabaseMetaData metadata = getDatabaseMetadata();
try (ResultSet rs = metadata.getCrossReference(null, null, null, 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 2
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* Helper method for testing getCrossReference - calls dmd.getCrossReference for
* the JDBC call, and getCrossReferenceODBC for the ODBC procedure
* @throws SQLException
*/
private ResultSet[] getCrossReference(
String parentcatalog, String parentschema, String parenttable,
String foreigncatalog, String foreignschema, String foreigntable)
throws SQLException
{
ResultSet[] rss = new ResultSet[2];
DatabaseMetaData dmd = getDMD();
rss[0]= dmd.getCrossReference(parentcatalog, parentschema, parenttable,
foreigncatalog, foreignschema, foreigntable);
rss[1]= getCrossReferenceODBC(parentcatalog, parentschema, parenttable,
foreigncatalog, foreignschema, foreigntable);
assertGetImportedAndExportedKeysShape(rss);
return rss;
}
Example 3
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* Helper method for testing getCrossReference - calls dmd.getCrossReference for
* the JDBC call, and getCrossReferenceODBC for the ODBC procedure
* @throws SQLException
*/
private ResultSet[] getCrossReference(
String parentcatalog, String parentschema, String parenttable,
String foreigncatalog, String foreignschema, String foreigntable)
throws SQLException
{
ResultSet[] rss = new ResultSet[2];
DatabaseMetaData dmd = getDMD();
rss[0]= dmd.getCrossReference(parentcatalog, parentschema, parenttable,
foreigncatalog, foreignschema, foreigntable);
rss[1]= getCrossReferenceODBC(parentcatalog, parentschema, parenttable,
foreigncatalog, foreignschema, foreigntable);
assertGetImportedAndExportedKeysShape(rss);
return rss;
}
Example 4
| Source File: MetadataTest.java From r-course with MIT License | 5 votes |
|
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetCrossReferenceUsingInfoSchema() 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.getCrossReference(null, null, "parent", null, null, "child");
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 5
| 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 6
| Source File: MetadataTest.java From Komondor with GNU General Public License v3.0 | 5 votes |
|
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetCrossReferenceUsingInfoSchema() 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.getCrossReference(null, null, "parent", null, null, "child");
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 7
| Source File: PostgresqlTestUtils.java From sqoop-on-spark with Apache License 2.0 | 5 votes |
|
public void assertForeignKey(String schema, String table, String column,
String foreignKeyTable, String foreignKeyColumn) throws Exception {
DatabaseMetaData md = provider.getConnection().getMetaData();
ResultSet rs = md.getCrossReference(null, schema, table, null, schema, foreignKeyTable);
while (rs.next()) {
if (rs.getString(4).equals(column) && rs.getString(8).equals(foreignKeyColumn)) {
return;
}
}
throw new AssertionError("Could not find '" + table + "." + column
+ "' part of schema '" + schema + "' with reference to '" + table + "." + column + "'");
}
Example 8
| Source File: MetadataTest.java From FoxTelem with GNU General Public License v3.0 | 5 votes |
|
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetCrossReferenceUsingInfoSchema() throws Exception {
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.setProperty(PropertyKey.useInformationSchema.getKeyName(), "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getCrossReference(null, null, "parent", null, null, "child");
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 9
| 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 10
| 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 11
| Source File: DatabaseMetaDataIT.java From snowflake-jdbc with Apache License 2.0 | 4 votes |
|
@Test
public void testGetCrossReferences() 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.getCrossReference(
database, schema, targetTable1, database, schema, targetTable2);
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 12
| Source File: Select.java From jsqsh with Apache License 2.0 | 4 votes |
|
/**
* Attempts to fetch a join clause by asking the database about
* primary/foriegn key relationships between tables.
*
* @param session The session.
* @param tables The tables to join
* @param linesep Linesep
* @return The join
* @throws SQLException
*/
private String getKeyJoin(Session session,
Table []tables, String linesep)
throws SQLException {
Connection conn = session.getConnection();
DatabaseMetaData meta = conn.getMetaData();
StringBuilder join = new StringBuilder();
int joinCount = 0;
for (int t1 = 0; t1 < tables.length; ++t1) {
Table table1 = tables[t1];
for (int t2 = 0; t2 < tables.length; ++t2) {
Table table2 = tables[t2];
ResultSet rs = meta.getCrossReference(
table1.catalog,
table1.schema,
table1.tableName,
table2.catalog,
table2.schema,
table2.tableName
);
while (rs.next()) {
if (joinCount > 0) {
join.append(linesep)
.append(" AND ");
}
join.append(table1.alias)
.append('.')
.append(SQLTools.quoteIdentifier(rs.getString(4)))
.append(" = ")
.append(table2.alias)
.append('.')
.append(SQLTools.quoteIdentifier(rs.getString(8)));
++joinCount;
}
}
}
return join.toString();
}
