Example usage for java.sql DatabaseMetaData getExportedKeys

List of usage examples for java.sql DatabaseMetaData getExportedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. getExportedKeys

Introduction

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

Prototype

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

Source Link

Document

Retrieves a description of the foreign key columns that reference the given table's primary key columns (the foreign keys exported 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.getExportedKeys(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   www. j a  v  a  2  s. c om*/

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

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;/*from w  w  w. j  av a 2 s .  c om*/
    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.getExportedKeys(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

public static boolean hasParentTable(@Nonnull final Connection conn, @Nullable final String pkTableName)
        throws SQLException {
    DatabaseMetaData metadata = conn.getMetaData();
    String catalog = conn.getCatalog();
    final ResultSet rs = metadata.getExportedKeys(catalog, null, pkTableName);
    try {/*from ww w .j  a  v a  2  s. co m*/
        return rs.next();
    } finally {
        rs.close();
    }
}

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

/**
 * @return column position is not provided in the returning foreign keys
 */// ww w  .j a v  a 2  s . c om
@Nonnull
public static Collection<ForeignKey> getExportedKeys(@Nonnull final Connection conn,
        @Nullable final String pkTableName, 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.getExportedKeys(catalog, null, pkTableName);
    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");
                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.dbsvg.models.JdbcMainDAO.java

/**
 * This method should be run after all the tables are created. It cross
 * references two tables to see if there is a foreign key between them. If
 * so, it transforms the parent column into a foreign key.
 * //  w w w  .j a va 2s .  co  m
 * @param t
 * @param fTable
 * @param meta
 * @throws java.lang.Exception
 */
private void checkForForeignKeys(Table t, DatabaseMetaData meta, Connection conn, Map<String, Table> tablemap)
        throws Exception {
    ResultSet rs = meta.getExportedKeys(conn.getCatalog(), null, t.getName());
    while (rs.next()) {
        String parentColumn = rs.getString("PKCOLUMN_NAME");
        String fkTableName = rs.getString("FKTABLE_NAME");
        String foreignColumn = rs.getString("FKCOLUMN_NAME");
        Table fTable = tablemap.get(fkTableName);

        ForeignKey fk = fTable.getColumns().get(foreignColumn).transformToFK();
        fTable.getColumns().put(foreignColumn, fk);
        fTable.getForeignKeys().put(foreignColumn, fk);
        // increase the width of the table so that FK->table.column will fit
        // also
        int newWidth = CHAR_WIDTH * parentColumn.length() + CHAR_WIDTH * t.getName().length() + PAD_WIDTH
                + 8 * CHAR_WIDTH + CHAR_WIDTH * foreignColumn.length();
        if (newWidth > fTable.getWidth())
            fTable.setWidth(newWidth);
        t.getReferencingTables().put(fTable.getName(), fTable);
        fk.setReferencedColumn(parentColumn);
        fk.setReferencedTable(t.getName());
        fk.setUpdateRule(rs.getString("UPDATE_RULE"));
        fk.setDeleteRule(rs.getString("DELETE_RULE"));
        try {
            fk.setReference(t.getColumns().get(parentColumn));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

From source file:cz.lbenda.dataman.db.DbStructureFactory.java

private void generateStructureForeignKeys(Map<String, CatalogDesc> catalogs, DatabaseMetaData dmd)
        throws SQLException {
    SQLDialect di = dbConfig.getJdbcConfiguration().getDialect();
    StatusHelper.getInstance().progressNextStep(this, STEP_READ_FOREIGN_KEYS,
            catalogs.values().stream().mapToInt(
                    cat -> cat.getSchemas().stream().mapToInt(schema -> schema.getTables().size()).sum())
                    .sum());/*from   www . j a v a  2  s . c o m*/
    for (CatalogDesc ch : catalogs.values()) {
        for (SchemaDesc schema : ch.getSchemas()) {
            for (TableDesc td : schema.getTables()) {
                StatusHelper.getInstance().progress(this);
                ResultSet rsEx = dmd.getExportedKeys(ch.getName(), schema.getName(), td.getName());
                writeColumnNames("generateStructureForeignKeys", rsEx.getMetaData());
                while (rsEx.next()) {
                    String slaveCatalogName = rsEx.getString(di.fkSlaveTableCatalog());
                    String slaveSchemaName = rsEx.getString(di.fkSlaveTableSchema());
                    String slaveTableName = rsEx.getString(di.fkSlaveTableName());
                    TableDesc slaveTD = catalogs.get(slaveCatalogName).getSchema(slaveSchemaName)
                            .getTable(slaveTableName);
                    //noinspection ConstantConditions
                    ForeignKey fk = new ForeignKey(rsEx.getString(di.fkName()), td,
                            td.getColumn(rsEx.getString(di.fkMasterColumnName())), slaveTD,
                            slaveTD.getColumn(rsEx.getString(di.fkSlaveColumnName())),
                            rsEx.getString(di.fkUpdateRule()), rsEx.getString(di.fkDeleteRule()));
                    td.addForeignKey(fk);
                    slaveTD.addForeignKey(fk);
                }
            }
        }
    }
}

From source file:madgik.exareme.master.queryProcessor.analyzer.stat.ExternalStat.java

@Override
public Map<String, Table> extractStats() throws Exception {

    DatabaseMetaData dbmd = con.getMetaData(); // dtabase metadata object

    // listing tables and columns
    String catalog = null;//from w  w  w  .  ja  va 2 s .c  o m
    String schemaPattern = sch;
    String tableNamePattern = tblName;
    String columnNamePattern = "%";
    if (con.getClass().getName().contains("postgresql")) {
        // tableNamePattern="\""+tableNamePattern+"\"";
        schemaPattern = "public";
    }

    // ResultSet resultTables = dbmd.getTables(catalog, "public",
    // tableNamePattern, types);
    ResultSet resultColumns = dbmd.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
    if (con.getClass().getName().contains("postgresql")) {
        tableNamePattern = "\"" + tableNamePattern + "\"";
    } else if (con.getClass().getName().contains("oracle")) {
        tableNamePattern = schemaPattern + "." + tableNamePattern;
    }
    log.debug("Starting extracting stats");
    // while (resultTables.next()) {
    Map<String, Column> columnMap = new HashMap<String, Column>();
    // StringEscapeUtils.escapeJava(resultTables.getString(3));
    log.debug("Analyzing table " + tblName);

    int toupleSize = 0; // in bytes

    // tableNamePattern = tableName;

    int columnCount = resultColumns.getMetaData().getColumnCount();
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("select count(*) from " + tableNamePattern);
    int count = 0;
    if (rs.next()) {
        count = rs.getInt(1);
    } else {
        log.error("could not get count for table " + tableNamePattern);
    }
    rs.close();
    st.close();

    ResultSet pkrs = dbmd.getExportedKeys("", "", tblName);
    String pkey = "DEFAULT_KEY";

    while (pkrs.next()) {
        pkey = pkrs.getString("PKCOLUMN_NAME");
        break;
    }
    pkrs.close();
    if (count == 0) {
        log.debug("Empty table");
        Table t = new Table(tblName, columnCount, toupleSize, columnMap, count, pkey);
        schema.put(tblName, t);
        return schema;
    }

    while (resultColumns.next()) {

        String columnName = StringEscapeUtils.escapeJava(resultColumns.getString(4));
        try {
            String colNamePattern = columnName;
            if (con.getClass().getName().contains("postgresql")) {
                colNamePattern = "\"" + columnName + "\"";
            }
            int columnType = resultColumns.getInt(5);

            // computing column's size in bytes
            int columnSize = computeColumnSize(colNamePattern, columnType, tableNamePattern);
            toupleSize += columnSize;

            // execute queries for numberOfDiffValues, minVal, maxVal
            // Map<String, Integer> diffValFreqMap = new HashMap<String,
            // Integer>();

            // computing column's min and max values
            String minVal = "0";
            String maxVal = "0";
            if (columnType != Types.BLOB) {
                MinMax mm = computeMinMax(tableNamePattern, colNamePattern);
                minVal = mm.getMin();
                maxVal = mm.getMax();
            }
            Map<String, Integer> diffValFreqMap = new HashMap<String, Integer>();
            //only for equidepth!

            // for (ValFreq k : freqs) {
            // diffValFreqMap.put(k.getVal(), k.getFreq());

            // }

            // /add min max diff vals in the sampling values

            int minOcc = 1;
            int maxOcc = 1;
            int diffVals = 0;
            boolean equidepth = false;
            if (equidepth) {

                //diffValFreqMap is used only in equidepth, do not compute it
                //if we have primitive
                diffValFreqMap = computeDistinctValuesFrequency(tableNamePattern, colNamePattern);

                String minValChar = minVal;
                String maxValChar = maxVal;
                if (columnType == Types.VARCHAR || columnType == Types.CHAR || columnType == Types.LONGNVARCHAR
                        || columnType == Types.DATE) {
                    minValChar = "\'" + minVal + "\'";
                    maxValChar = "\'" + maxVal + "\'";
                }
                try {
                    minOcc = computeValOccurences(tableNamePattern, colNamePattern, minValChar);
                } catch (Exception e) {
                    log.error("Could not compute value occurences for column:" + colNamePattern + " and value:"
                            + minValChar);
                }
                if (equidepth && !diffValFreqMap.containsKey(minVal))
                    diffValFreqMap.put(minVal, minOcc);

                try {
                    maxOcc = computeValOccurences(tableNamePattern, colNamePattern, maxValChar);
                } catch (Exception e) {
                    log.error("Could not compute value occurences for column:" + colNamePattern + " and value:"
                            + maxValChar);
                }
                if (diffValFreqMap.containsKey(maxVal))
                    diffValFreqMap.put(maxVal, maxOcc);

                diffVals = diffValFreqMap.size();
            } else {
                diffVals = computeDiffVals(tableNamePattern, colNamePattern, columnType);
            }
            if (diffVals == 0) {
                //all values are null!
                continue;
            }
            Column c = new Column(columnName, columnType, columnSize, diffVals, minVal, maxVal, diffValFreqMap);
            columnMap.put(columnName, c);
        } catch (Exception ex) {
            log.error("could not analyze column " + columnName + ":" + ex.getMessage());
        }

    }

    Table t = new Table(tblName, columnCount, toupleSize, columnMap, count, pkey);
    schema.put(tblName, t);

    // }
    // resultTables.close();
    resultColumns.close();
    return schema;

}

From source file:jef.database.DbMetaData.java

/**
 * ?//from ww  w. j ava  2  s.  c  o  m
 * 
 * @param tableName
 *            
 * @return 
 * @throws SQLException
 */
public List<ForeignKeyItem> getForeignKeyReferenceTo(String tableName) throws SQLException {
    tableName = info.profile.getObjectNameToUse(tableName);
    Connection conn = getConnection(false);
    DatabaseMetaData databaseMetaData = conn.getMetaData();
    ResultSet rs = null;
    try {
        rs = databaseMetaData.getExportedKeys(null, schema, tableName);
        List<ForeignKeyItem> fks = ResultPopulatorImpl.instance
                .toPlainJavaObject(new ResultSetImpl(rs, getProfile()), FK_TRANSFORMER);
        return fks;
    } catch (RuntimeException e) {
        // JDBC?
        LogUtil.exception(e);
        return Collections.emptyList();
    } finally {
        DbUtils.close(rs);
        releaseConnection(conn);
    }
}

From source file:madgik.exareme.master.queryProcessor.analyzer.stat.Stat.java

@Override
public Map<String, Table> extractStats() throws Exception {

    DatabaseMetaData dbmd = con.getMetaData(); // dtabase metadata object

    // listing tables and columns
    String catalog = null;/*  w w  w  .j  a  va  2 s .  c om*/
    String schemaPattern = null;
    String tableNamePattern = null;
    String[] types = null;
    String columnNamePattern = null;

    ResultSet resultTables = dbmd.getTables(catalog, schemaPattern, tableNamePattern, types);
    log.debug("Starting extracting stats");
    while (resultTables.next()) {
        Map<String, Column> columnMap = new HashMap<String, Column>();
        String tableName = StringEscapeUtils.escapeJava(resultTables.getString(3));
        log.debug("Analyzing table " + tableName);

        int columnCount = resultTables.getMetaData().getColumnCount();
        int toupleSize = 0; // in bytes

        tableNamePattern = tableName;
        ResultSet resultColumns = dbmd.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);

        int count = OptiqueAnalyzer.getCountFor(tableName, sch);

        if (count == 0) {
            log.debug("Empty table");
            continue;
        }

        while (resultColumns.next()) {

            String columnName = StringEscapeUtils.escapeJava(resultColumns.getString(4));
            int columnType = resultColumns.getInt(5);

            // computing column's size in bytes
            int columnSize = computeColumnSize(columnName, columnType, tableName);
            toupleSize += columnSize;

            // execute queries for numberOfDiffValues, minVal, maxVal
            Map<String, Integer> diffValFreqMap = new HashMap<String, Integer>();

            // computing column's min and max values
            MinMax mm = computeMinMax(tableName, columnName);
            String minVal = mm.getMin();
            String maxVal = mm.getMax();

            // /
            List<ValFreq> freqs = computeDistinctValuesFrequency(tableName, columnName);

            for (ValFreq k : freqs) {
                diffValFreqMap.put(k.getVal(), k.getFreq());

            }

            // /add min max diff vals in the sampling values
            int minOcc = computeValOccurences(tableName, columnName, minVal);
            if (!diffValFreqMap.containsKey(minVal))
                diffValFreqMap.put(minVal, minOcc);
            int maxOcc = computeValOccurences(tableName, columnName, maxVal);
            if (!diffValFreqMap.containsKey(maxVal))
                diffValFreqMap.put(maxVal, maxOcc);

            int diffVals = diffValFreqMap.size();

            Column c = new Column(columnName, columnType, columnSize, diffVals, minVal, maxVal, diffValFreqMap);
            columnMap.put(columnName, c);

        }

        ResultSet pkrs = dbmd.getExportedKeys("", "", tableName);
        String pkey = "DEFAULT_KEY";

        while (pkrs.next()) {
            pkey = pkrs.getString("PKCOLUMN_NAME");
            break;
        }

        Table t = new Table(tableName, columnCount, toupleSize, columnMap, count, pkey);
        schema.put(tableName, t);

    }

    return schema;

}

From source file:org.apache.cayenne.dbsync.reverse.dbload.ExportedKeyLoader.java

@Override
ResultSet getResultSet(DbEntity dbEntity, DatabaseMetaData metaData) throws SQLException {
    return metaData.getExportedKeys(dbEntity.getCatalog(), dbEntity.getSchema(), dbEntity.getName());
}