List of usage examples for java.sql DatabaseMetaData getImportedKeys
ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException;
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; }