Example usage for java.sql DatabaseMetaData getImportedKeys

List of usage examples for java.sql DatabaseMetaData getImportedKeys

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getImportedKeys.

Prototype

ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException;

Source Link

Document

Retrieves a description of the primary key columns that are referenced by the given table's foreign key columns (the primary keys imported by a table).

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    DatabaseMetaData meta = conn.getMetaData();

    ResultSet rs = meta.getImportedKeys(conn.getCatalog(), null, "survey");
    while (rs.next()) {
        String fkTableName = rs.getString("FKTABLE_NAME");
        String fkColumnName = rs.getString("FKCOLUMN_NAME");
        int fkSequence = rs.getInt("KEY_SEQ");
        System.out.println("getExportedKeys(): fkTableName=" + fkTableName);
        System.out.println("getExportedKeys(): fkColumnName=" + fkColumnName);
        System.out.println("getExportedKeys(): fkSequence=" + fkSequence);
    }/*from   ww w .ja v a  2s  .  co m*/

    st.close();
    conn.close();
}

From source file:ForeignKeysCoffees.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;/* w ww  .  j ava  2  s. com*/
    String createString = "create table COFFEESFK " + "(COF_NAME varchar(32) NOT NULL, " + "SUP_ID int, "
            + "PRICE float, " + "SALES int, " + "TOTAL int, " + "primary key(COF_NAME), "
            + "foreign key(SUP_ID) references SUPPLIERSPK)";
    Statement stmt;

    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();
        stmt.executeUpdate(createString);

        DatabaseMetaData dbmd = con.getMetaData();

        ResultSet rs = dbmd.getImportedKeys(null, null, "COFFEESFK");
        while (rs.next()) {
            String pkTable = rs.getString("PKTABLE_NAME");
            String pkColName = rs.getString("PKCOLUMN_NAME");
            String fkTable = rs.getString("FKTABLE_NAME");
            String fkColName = rs.getString("FKCOLUMN_NAME");
            short updateRule = rs.getShort("UPDATE_RULE");
            short deleteRule = rs.getShort("DELETE_RULE");
            System.out.println("primary key table name :  " + pkTable);
            System.out.print("primary key column name :  ");
            System.out.println(pkColName);
            System.out.println("foreign key table name :  " + fkTable);
            System.out.print("foreign key column name :  ");
            System.out.println(fkColName);
            System.out.println("update rule:  " + updateRule);
            System.out.println("delete rule:  " + deleteRule);
            System.out.println("");
        }

        rs.close();
        stmt.close();
        con.close();

    } catch (SQLException ex) {
        System.err.print("SQLException: ");
        System.err.println(ex.getMessage());
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet rs = null;/* w  w w . j  av a 2 s  .  com*/
    DatabaseMetaData meta = conn.getMetaData();
    // The Oracle database stores its table names as Upper-Case,
    // if you pass a table name in lowercase characters, it will not work.
    // MySQL database does not care if table name is uppercase/lowercase.
    //
    rs = meta.getImportedKeys(conn.getCatalog(), null, "survey");
    while (rs.next()) {
        String fkTableName = rs.getString("FKTABLE_NAME");
        String fkColumnName = rs.getString("FKCOLUMN_NAME");
        int fkSequence = rs.getInt("KEY_SEQ");
        System.out.println("getExportedKeys(): fkTableName=" + fkTableName);
        System.out.println("getExportedKeys(): fkColumnName=" + fkColumnName);
        System.out.println("getExportedKeys(): fkSequence=" + fkSequence);
    }

    st.close();
    conn.close();
}

From source file:gridool.db.helpers.GridDbUtils.java

/**
 * @return column position is provided in the returning foreign keys
 *//*from www .  ja  va  2s  .  c  o m*/
@Nonnull
public static Collection<ForeignKey> getForeignKeys(@Nonnull final Connection conn,
        @Nullable final String fkTableName, final boolean setColumnPositions) throws SQLException {
    DatabaseMetaData metadata = conn.getMetaData();
    String catalog = conn.getCatalog();
    final Map<String, ForeignKey> mapping = new HashMap<String, ForeignKey>(4);
    final ResultSet rs = metadata.getImportedKeys(catalog, null, fkTableName);
    try {
        while (rs.next()) {
            final String fkName = rs.getString("FK_NAME");
            ForeignKey fk = mapping.get(fkName);
            if (fk == null) {
                //String fkTableName = rs.getString("FKTABLE_NAME");
                String pkTableName = rs.getString("PKTABLE_NAME");
                fk = new ForeignKey(fkName, fkTableName, pkTableName);
                mapping.put(fkName, fk);
            }
            fk.addColumn(rs, metadata);
        }
    } finally {
        rs.close();
    }
    final Collection<ForeignKey> fkeys = mapping.values();
    if (setColumnPositions) {
        for (ForeignKey fk : fkeys) {
            fk.setColumnPositions(metadata);
        }
    }
    return fkeys;
}

From source file:com.clican.pluto.orm.tool.TableMetadata.java

private void initForeignKeys(DatabaseMetaData meta) throws SQLException {
    ResultSet rs = null;/*w  w w  . ja v a2 s  .c o m*/

    try {
        rs = meta.getImportedKeys(catalog, schema, name);
        while (rs.next())
            addForeignKey(rs);
    } finally {
        if (rs != null)
            rs.close();
    }
}

From source file:jp.co.tis.gsp.tools.db.EntityDependencyParser.java

private void parseReference(DatabaseMetaData metaData, String normalizedSchemaName, String tableName)
        throws SQLException {
    ResultSet rs = null;//from  www.  ja  v a 2  s  .  c o m
    try {
        rs = metaData.getImportedKeys(null, normalizedSchemaName, tableName);
        while (rs.next()) {
            String child = rs.getString("FKTABLE_NAME");
            String parent = rs.getString("PKTABLE_NAME");
            Table childTable = tableMap.get(child);
            if (childTable == null) {
                childTable = new Table(child);
                tableMap.put(child, childTable);
            }
            Table parentTable = tableMap.get(parent);
            if (parentTable == null) {
                parentTable = new Table(parent);
                tableMap.put(parent, parentTable);
            }
            childTable.parents.add(parentTable);
            parentTable.children.add(childTable);
        }
    } finally {
        if (rs != null) {
            rs.close();
        }
    }
}

From source file:org.jtalks.poulpe.util.databasebackup.persistence.DbTableKeys.java

/**
 * Obtains the list of tables foreign keys.
 * /*from   w w  w  .ja  va  2  s  .  c o m*/
 * @return A list of {@link ForeignKey} object represented foreign keys.
 * @throws SQLException
 *             Is thrown in case any errors during work with database occur.
 */
@SuppressWarnings("unchecked")
public Set<ForeignKey> getForeignKeys() throws SQLException {
    Set<ForeignKey> tableForeignKeySet = null;
    try {
        tableForeignKeySet = (Set<ForeignKey>) JdbcUtils.extractDatabaseMetaData(dataSource,
                new KeyListProcessor(tableName, new TableKeyPerformer() {
                    @Override
                    public ResultSet getResultSet(DatabaseMetaData dmd, String tableName) throws SQLException {
                        return dmd.getImportedKeys(null, null, tableName);
                    }

                    @Override
                    public void addKeyToSet(ResultSet rs, Set<TableKey> keySet) throws SQLException {
                        if (rs.getString(FK_NAME) != null) {
                            keySet.add(new ForeignKey(rs.getString(FK_NAME), rs.getString(FKCOLUMN_NAME),
                                    rs.getString(PKTABLE_NAME), rs.getString(PKCOLUMN_NAME)));
                        }

                    }
                }));
    } catch (MetaDataAccessException e) {
        throw new SQLException(e);
    }
    return tableForeignKeySet;
}

From source file:com.jaxio.celerio.configuration.database.support.MetadataExtractor.java

private void loadImportedKeys(JdbcConnectivity configuration, DatabaseMetaData databaseMetaData, Table table,
        Metadata metaData) throws SQLException {
    log.info("Extracting imported keys for table: " + table.getName());

    ResultSet resultSet = databaseMetaData.getImportedKeys(configuration.getCatalog(),
            configuration.getSchemaName(), table.getName());
    ResultSetWrapper rsw = new ResultSetImportedKeys(resultSet, useLabel);

    while (resultSet.next()) {
        ImportedKey importedKey = new ImportedKey();

        // fill it
        importedKey.setPkTableName(getString(rsw, "PKTABLE_NAME"));
        importedKey.setFkColumnName(getString(rsw, "FKCOLUMN_NAME"));
        importedKey.setPkColumnName(getString(rsw, "PKCOLUMN_NAME"));
        importedKey.setFkName(getString(rsw, "FK_NAME"));

        // With DB2 we observed some duplicate in FK due to presence of table alias. Here is an example:
        // <importedKey fkColumnName="ADMRDEPT" fkName="ROD" pkColumnName="DEPTNO" pkTableName="DEPARTMENT"/>
        // <importedKey fkColumnName="ADMRDEPT" fkName="ROD" pkColumnName="DEPTNO" pkTableName="DEPT"/>
        // DEPT is in fact a table alias!
        // to circumvent the issue, we make sure the imported key points to a table reversed.

        if (metaData.getTableByName(importedKey.getPkTableName()) != null) {
            // add it
            table.addImportedKey(importedKey);
        } else {//from   w  ww . j  a v  a  2  s. c o  m
            log.warn("Ignoring imported key whose 'pkTableName' cannot be found: " + importedKey);
        }
    }

    resultSet.close();
}

From source file:de.griffel.confluence.plugins.plantuml.AbstractDatabaseStructureMacroImpl.java

private List<KeysDef> getForeignKeys(DatabaseMetaData dbmd, Map<String, TableDef> tables) {
    final List<KeysDef> result = new LinkedList<KeysDef>();

    if (_errorMessage == null && _macroParams.isUseForeingKeys()) {
        for (Map.Entry<String, TableDef> entries : tables.entrySet()) {
            final String tableName = entries.getValue().getTableName();

            ResultSet rs = null;/*w w w. j  a va 2  s  .c om*/
            try {
                rs = dbmd.getImportedKeys(null, _macroParams.getSchemaName(), tableName);
                while (rs.next()) {
                    KeysDef tmp = new KeysDef(rs.getString(1), rs.getString(2), rs.getString(3),
                            rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7),
                            rs.getString(8));
                    result.add(tmp);
                    if (log.isDebugEnabled()) {
                        log.debug(tmp.getKeysColumnId());
                    }
                }
            } catch (SQLException e) {
                sqlException(_macroParams.getDatasource(), e);
            } finally {
                closeResource(rs);
            }
        }
    }
    return result;
}

From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java

/**
 * Wrapper for {@link java.sql.DatabaseMetaData#getImportedKeys(String, String, String)}
 *
 * @param connection An open JDBC connection
 * @param tableName table name that is optionally fully qualified with a schema in the form schema.tableName
 * @return List of Table Names whose primary key are referred as foreign key by the table tableName
 *
 * @throws SQLException//from w w w.  j  av a2 s .c o  m
 */
public Set<String> getReferredTables(Connection connection, String schema, String tableName)
        throws SQLException {
    DatabaseMetaData metadata = connection.getMetaData();

    ResultSet result = metadata.getImportedKeys(connection.getCatalog(), schema, tableName);
    Set<String> referredTables = new HashSet<>();
    while (result.next()) {
        referredTables.add(result.getString(PK_TABLE_NAME));
    }
    return referredTables;
}