List of usage examples for java.sql DatabaseMetaData getExportedKeys
ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException;
From source file:org.apache.cayenne.unit.UnitDbAdapter.java
/** * Returns a map of database constraints with DbEntity names used as keys, * and Collections of constraint names as values. *//*ww w.ja va2 s .c om*/ protected Map<String, Collection<String>> getConstraints(Connection conn, DataMap map, Collection<String> includeTables) throws SQLException { Map<String, Collection<String>> constraintMap = new HashMap<>(); DatabaseMetaData metadata = conn.getMetaData(); for (String name : includeTables) { DbEntity entity = map.getDbEntity(name); if (entity == null) { continue; } QuotingStrategy strategy = adapter.getQuotingStrategy(); // Get all constraints for the table ResultSet rs = metadata.getExportedKeys(entity.getCatalog(), entity.getSchema(), entity.getName()); try { while (rs.next()) { String fk = rs.getString("FK_NAME"); String fkTable = rs.getString("FKTABLE_NAME"); if (fk != null && fkTable != null) { Collection<String> constraints = constraintMap.get(fkTable); if (constraints == null) { // use a set to avoid duplicate constraints constraints = new HashSet<String>(); constraintMap.put(fkTable, constraints); } constraints.add(strategy.quotedIdentifier(entity, fk)); } } } finally { rs.close(); } } return constraintMap; }
From source file:org.biomart.configurator.controller.MartController.java
/** * @param fksToBeDropped/* w w w.j a v a 2 s . co m*/ * @param dmd * @param schema * @param catalog * @param stepSize * @throws SQLException * @throws DataModelException */ public void synchroniseKeysUsingDMD(final SourceSchema ss, final Collection<ForeignKey> fksToBeDropped, final DatabaseMetaData dmd, final String schema, final String catalog) throws SQLException, DataModelException { Log.debug("Running DMD key synchronisation"); // Loop through all the tables in the database, which is the same // as looping through all the primary keys. Log.debug("Finding tables"); for (final Iterator<Table> i = ss.getTables().iterator(); i.hasNext();) { // Obtain the table and its primary key. final SourceTable pkTable = (SourceTable) i.next(); final PrimaryKey pk = pkTable.getPrimaryKey(); // Skip all tables which have no primary key. if (pk == null) continue; Log.debug("Processing primary key " + pk); // Make a list of relations that already exist in this schema, // from some previous run. Any relations that are left in this // list by the end of the loop for this table no longer exist in // the database, and will be dropped. final Collection<Relation> relationsToBeDropped = new TreeSet<Relation>(pk.getRelations()); // Tree for // order // Identify all foreign keys in the database metadata that refer // to the current primary key. Log.debug("Finding referring foreign keys"); String searchCatalog = catalog; String searchSchema = schema; final ResultSet dbTblFKCols = dmd.getExportedKeys(searchCatalog, searchSchema, pkTable.getName()); // Loop through the results. There will be one result row per // column per key, so we need to build up a set of key columns // in a map. // The map keys represent the column position within a key. Each // map value is a list of columns. In essence the map is a 2-D // representation of the foreign keys which refer to this PK, // with the keys of the map (Y-axis) representing the column // position in the FK, and the values of the map (X-axis) // representing each individual FK. In all cases, FK columns are // assumed to be in the same order as the PK columns. The map is // sorted by key column position. // An assumption is made that the query will return columns from // the FK in the same order as all other FKs, ie. all column 1s // will be returned before any 2s, and then all 2s will be // returned // in the same order as the 1s they are associated with, etc. final TreeMap<Short, List<Column>> dbFKs = new TreeMap<Short, List<Column>>(); while (dbTblFKCols.next()) { final String fkTblName = dbTblFKCols.getString("FKTABLE_NAME"); final String fkColName = dbTblFKCols.getString("FKCOLUMN_NAME"); final Short fkColSeq = new Short(dbTblFKCols.getShort("KEY_SEQ")); if (fkTblName != null && fkTblName.contains("$")) { // exclude ORACLE's temporary tables (unlikely to be // found here though) continue; } // Note the column. if (!dbFKs.containsKey(fkColSeq)) dbFKs.put(fkColSeq, new ArrayList<Column>()); // In some dbs, FKs can be invalid, so we need to check // them. final Table fkTbl = ss.getTableByName(fkTblName); if (fkTbl != null) { final Column fkCol = (Column) fkTbl.getColumnByName(fkColName); if (fkCol != null) (dbFKs.get(fkColSeq)).add(fkCol); } } dbTblFKCols.close(); // Sort foreign keys by name (case insensitive) for (List<Column> columnList : dbFKs.values()) { Collections.sort(columnList); } // Only construct FKs if we actually found any. if (!dbFKs.isEmpty()) { // Identify the sequence of the first column, which may be 0 // or 1, depending on database implementation. final int firstColSeq = ((Short) dbFKs.firstKey()).intValue(); // How many columns are in the PK? final int pkColCount = pkTable.getPrimaryKey().getColumns().size(); // How many FKs do we have? final int fkCount = dbFKs.get(dbFKs.firstKey()).size(); // Loop through the FKs, and construct each one at a time. for (int j = 0; j < fkCount; j++) { // Set up an array to hold the FK columns. final List<Column> candidateFKColumns = new ArrayList<Column>(); // For each FK column name, look up the actual column in // the table. for (final Iterator<Map.Entry<Short, List<Column>>> k = dbFKs.entrySet().iterator(); k .hasNext();) { final Map.Entry<Short, List<Column>> entry = k.next(); final Short keySeq = (Short) entry.getKey(); // Convert the db-specific column index to a // 0-indexed figure for the array of fk columns. final int fkColSeq = keySeq.intValue() - firstColSeq; candidateFKColumns.add((Column) (entry.getValue()).get(j)); } // Create a template foreign key based around the set // of candidate columns we found. ForeignKey fkObject; try { List<Column> columns = new ArrayList<Column>(); for (int k = 0; k < candidateFKColumns.size(); k++) { columns.add(candidateFKColumns.get(k)); } fkObject = new ForeignKey(columns); // new KeyController(fkObject); } catch (final Throwable t) { throw new BioMartError(t); } final Table fkTable = fkObject.getTable(); // If any FK already exists on the target table with the // same columns in the same order, then reuse it. boolean fkAlreadyExists = false; for (final Iterator<ForeignKey> f = fkTable.getForeignKeys().iterator(); f.hasNext() && !fkAlreadyExists;) { final ForeignKey candidateFK = f.next(); if (candidateFK.equals(fkObject)) { // Found one. Reuse it! fkObject = candidateFK; // Update the status to indicate that the FK is // backed by the database, if previously it was // handmade. if (fkObject.getStatus().equals(ComponentStatus.HANDMADE)) fkObject.setStatus(ComponentStatus.INFERRED); // Remove the FK from the list to be dropped // later, as it definitely exists now. fksToBeDropped.remove(candidateFK); // Flag the key as existing. fkAlreadyExists = true; } } // Has the key been reused, or is it a new one? if (!fkAlreadyExists) try { fkTable.getForeignKeys().add(fkObject); // fkTable.getForeignKeys().add(fk); } catch (final Throwable t) { throw new BioMartError(t); } // Work out whether the relation from the FK to // the PK should be 1:M or 1:1. The rule is that // it will be 1:M in all cases except where the // FK table has a PK with identical columns to // the FK, in which case it is 1:1, as the FK // is unique. Cardinality card = Cardinality.MANY_A; final PrimaryKey fkPK = fkTable.getPrimaryKey(); if (fkPK != null && fkObject.getColumns().equals(fkPK.getColumns())) card = Cardinality.ONE; // Check to see if it already has a relation. boolean relationExists = false; for (final Iterator<Relation> f = fkObject.getRelations().iterator(); f.hasNext();) { // Obtain the next relation. final Relation candidateRel = f.next(); // a) a relation already exists between the FK // and the PK. if (candidateRel.getOtherKey(fkObject).equals(pk)) { // If cardinality matches, make it // inferred. If doesn't match, make it // modified and update original cardinality. try { if (card.equals(candidateRel.getCardinality())) { if (!candidateRel.getStatus().equals(ComponentStatus.INFERRED_INCORRECT)) candidateRel.setStatus(ComponentStatus.INFERRED); } else { if (!candidateRel.getStatus().equals(ComponentStatus.INFERRED_INCORRECT)) candidateRel.setStatus(ComponentStatus.MODIFIED); candidateRel.setOriginalCardinality(card); } } catch (final AssociationException ae) { throw new BioMartError(ae); } // Don't drop it at the end of the loop. relationsToBeDropped.remove(candidateRel); // Say we've found it. relationExists = true; } // b) a handmade relation exists elsewhere which // should not be dropped. All other relations // elsewhere will be dropped. else if (candidateRel.getStatus().equals(ComponentStatus.HANDMADE)) // Don't drop it at the end of the loop. relationsToBeDropped.remove(candidateRel); } // If relation did not already exist, create it. if (!relationExists && !pk.equals(fkObject)) { // Establish the relation. try { new RelationSource(pk, fkObject, card); // pk.getObject().addRelation(relation); // fk.getObject().addRelation(relation); } catch (final Throwable t) { throw new BioMartError(t); } } } } // Remove any relations that we didn't find in the database (but // leave the handmade ones behind). for (final Iterator<Relation> j = relationsToBeDropped.iterator(); j.hasNext();) { final Relation r = j.next(); if (r.getStatus().equals(ComponentStatus.HANDMADE)) continue; r.getFirstKey().removeRelation(r); r.getSecondKey().removeRelation(r); } } }
From source file:org.executequery.databaseobjects.impl.DefaultDatabaseHost.java
/** * Returns the exported keys columns of the specified database object. * * @param catalog the table catalog name * @param schema the table schema name/*from w ww . j a va 2 s. c o m*/ * @param table the database object name * @return the exported keys */ public List<DatabaseColumn> getExportedKeys(String catalog, String schema, String table) throws DataSourceException { ResultSet rs = null; try { String _catalog = getCatalogNameForQueries(catalog); String _schema = getSchemaNameForQueries(schema); DatabaseMetaData dmd = getDatabaseMetaData(); List<DatabaseColumn> columns = new ArrayList<DatabaseColumn>(); String tableTagName = "TABLE"; // retrieve the base column info rs = dmd.getExportedKeys(_catalog, _schema, table); while (rs.next()) { String fkSchema = rs.getString(6); DatabaseSchema databaseSchema = getSchema(fkSchema); if (databaseSchema != null) { String fkTable = rs.getString(7); String fkColumn = rs.getString(8); DatabaseMetaTag metaTag = databaseSchema.getDatabaseMetaTag(tableTagName); DatabaseTable databaseTable = (DatabaseTable) metaTag.getNamedObject(fkTable); columns.add(databaseTable.getColumn(fkColumn)); } } return columns; } catch (SQLException e) { throw new DataSourceException(e); } finally { releaseResources(rs); } }
From source file:org.kuali.rice.test.ClearDatabaseLifecycle.java
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); }//from w w w .j av a 2s .co m final String fkName = keyResultSet.getString("FK_NAME"); final String fkTableName = keyResultSet.getString("FKTABLE_NAME"); exportedKeyNames.add(new String[] { fkName, fkTableName }); } } keyResultSet.close(); } return exportedKeys; }
From source file:org.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java
/** * This method reads foreign keys of the table. * * @param tableName Name of the table// w w w . j a va2 s. co m * @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); } }