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 vote down vote up 
@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 vote down vote up 
/**
 * 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 vote down vote up 
/**
 * 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 vote down vote up 
/**
 * 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 vote down vote up 
/**
 * 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 vote down vote up 
/**
 * 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 vote down vote up 
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 vote down vote up 
/**
 * 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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
/**
 * 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();
}