List of usage examples for java.sql DatabaseMetaData getConnection
Connection getConnection() throws SQLException;
From source file:org.apache.zeppelin.impala.SqlCompleter.java
private static void getColumnNames(DatabaseMetaData meta, Set<String> names) throws SQLException { try {/*from w ww .j ava 2 s . co m*/ ResultSet columns = meta.getColumns(meta.getConnection().getCatalog(), null, "%", "%"); try { while (columns.next()) { // Add the following strings: (1) column name, (2) table name String name = columns.getString("TABLE_NAME"); if (!isBlank(name)) { names.add(name); names.add(columns.getString("COLUMN_NAME")); // names.add(columns.getString("TABLE_NAME") + "." + columns.getString("COLUMN_NAME")); } } } finally { columns.close(); } logger.debug(Joiner.on(',').join(names)); } catch (Exception e) { logger.error("Failed to retrieve the column name", e); } }
From source file:org.apache.zeppelin.mysql.SqlCompleter.java
private static void getColumnNames(DatabaseMetaData meta, Set<String> names) { try {//from w w w. ja v a 2 s .co m try (ResultSet columns = meta.getColumns(meta.getConnection().getCatalog(), null, "%", "%")) { while (columns.next()) { // Add the following strings: (1) column name, (2) table name String name = columns.getString("TABLE_NAME"); if (!isBlank(name)) { names.add(name); names.add(columns.getString("COLUMN_NAME")); // names.add(columns.getString("TABLE_NAME") + "." + columns.getString("COLUMN_NAME")); } } } logger.debug(Joiner.on(',').join(names)); } catch (Throwable t) { logger.error("Failed to retrieve the column name", t); } }
From source file:org.apache.zeppelin.postgresql.SqlCompleter.java
private static void getColumnNames(DatabaseMetaData meta, Set<String> names) throws SQLException { try {/*from ww w.jav a 2s . c o m*/ ResultSet columns = meta.getColumns(meta.getConnection().getCatalog(), null, "%", "%"); try { while (columns.next()) { // Add the following strings: (1) column name, (2) table name String name = columns.getString("TABLE_NAME"); if (!isBlank(name)) { names.add(name); names.add(columns.getString("COLUMN_NAME")); // names.add(columns.getString("TABLE_NAME") + "." + columns.getString("COLUMN_NAME")); } } } finally { columns.close(); } logger.debug(Joiner.on(',').join(names)); } catch (Throwable t) { logger.error("Failed to retrieve the column name", t); } }
From source file:org.executequery.gui.editor.autocomplete.AutoCompleteSelectionsFactory.java
private void databaseObjectsForHost(DatabaseHost databaseHost, String type, String databaseObjectDescription, AutoCompleteListItemType autocompleteType) { trace("Building autocomplete object list using [ " + databaseHost.getName() + " ] for type - " + type); ResultSet rs = null;//from w w w.ja v a 2s. c om DatabaseMetaData databaseMetaData = databaseHost.getDatabaseMetaData(); try { String catalog = databaseHost.getCatalogNameForQueries(defaultCatalogForHost(databaseHost)); String schema = databaseHost.getSchemaNameForQueries(defaultSchemaForHost(databaseHost)); String typeName = null; List<String> tableNames = new ArrayList<String>(); List<AutoCompleteListItem> list = new ArrayList<AutoCompleteListItem>(); String[] types = new String[] { type }; int count = 0; rs = databaseMetaData.getTables(catalog, schema, null, types); while (rs.next()) { try { if (Thread.interrupted() || databaseMetaData.getConnection().isClosed()) { return; } } catch (SQLException e) { } typeName = rs.getString(4); // only include if the returned reported type matches if (type != null && type.equalsIgnoreCase(typeName)) { tableNames.add(rs.getString(3)); count++; } if (count >= INCREMENT) { addTablesToProvider(databaseObjectDescription, autocompleteType, tableNames, list); count = 0; list.clear(); tableNames.clear(); } } addTablesToProvider(databaseObjectDescription, autocompleteType, tableNames, list); } catch (SQLException e) { error("Tables not available for type " + type + " - driver returned: " + e.getMessage()); } finally { releaseResources(rs); trace("Finished autocomplete object list using [ " + databaseHost.getName() + " ] for type - " + type); } }
From source file:org.executequery.gui.editor.autocomplete.AutoCompleteSelectionsFactory.java
@SuppressWarnings("resource") private void databaseExecutableForHost(DatabaseHost databaseHost, String type, String databaseObjectDescription, AutoCompleteListItemType autocompleteType) { trace("Building autocomplete object list using [ " + databaseHost.getName() + " ] for type - " + type); ResultSet rs = null;//from w w w . j a v a2 s .co m try { DatabaseMetaData databaseMetaData = databaseHost.getDatabaseMetaData(); String catalog = databaseHost.getCatalogNameForQueries(defaultCatalogForHost(databaseHost)); String schema = databaseHost.getSchemaNameForQueries(defaultSchemaForHost(databaseHost)); List<String> names = new ArrayList<String>(); List<AutoCompleteListItem> list = new ArrayList<AutoCompleteListItem>(); if (autocompleteType == AutoCompleteListItemType.DATABASE_FUNCTION) { try { rs = databaseMetaData.getFunctions(catalog, schema, null); } catch (Throwable e) { trace("Functions not available using [ getFunctions() ] - reverting to [ getProcedures() ] - " + e.getMessage()); rs = getProcedures(databaseMetaData, catalog, schema); } } else { rs = getProcedures(databaseMetaData, catalog, schema); } if (rs != null) { int count = 0; while (rs.next()) { try { if (Thread.interrupted() || databaseMetaData.getConnection().isClosed()) { return; } } catch (SQLException e) { } names.add(rs.getString(3)); count++; if (count >= INCREMENT) { addTablesToProvider(databaseObjectDescription, autocompleteType, names, list); count = 0; list.clear(); names.clear(); } } addTablesToProvider(databaseObjectDescription, autocompleteType, names, list); } } catch (Exception e) { error("Tables not available for type " + type + " - driver returned: " + e.getMessage()); } finally { releaseResources(rs); trace("Finished autocomplete object list using [ " + databaseHost.getName() + " ] for type - " + type); } }
From source file:org.executequery.gui.editor.autocomplete.AutoCompleteSelectionsFactory.java
private void databaseColumnsForTables(DatabaseHost databaseHost, List<AutoCompleteListItem> tables) { trace("Retrieving column names for tables for host [ " + databaseHost.getName() + " ]"); ResultSet rs = null;//w ww .j a v a2s.c om List<ColumnInformation> columns = new ArrayList<ColumnInformation>(); List<AutoCompleteListItem> list = new ArrayList<AutoCompleteListItem>(); String catalog = databaseHost.getCatalogNameForQueries(defaultCatalogForHost(databaseHost)); String schema = databaseHost.getSchemaNameForQueries(defaultSchemaForHost(databaseHost)); DatabaseMetaData dmd = databaseHost.getDatabaseMetaData(); for (int i = 0, n = tables.size(); i < n; i++) { try { if (Thread.interrupted() || dmd.getConnection().isClosed()) { return; } } catch (SQLException e) { } AutoCompleteListItem table = tables.get(i); if (table == null) { continue; } trace("Retrieving column names for table [ " + table.getValue() + " ]"); try { rs = dmd.getColumns(catalog, schema, table.getValue(), null); while (rs.next()) { String name = rs.getString(4); columns.add( columnInformationFactory.build(table.getValue(), name, rs.getString(6), rs.getInt(5), rs.getInt(7), rs.getInt(9), rs.getInt(11) == DatabaseMetaData.columnNoNulls)); } for (ColumnInformation column : columns) { list.add(new AutoCompleteListItem(column.getName(), table.getValue(), column.getDescription(), DATABASE_COLUMN_DESCRIPTION, AutoCompleteListItemType.DATABASE_TABLE_COLUMN)); } provider.addListItems(list); releaseResources(rs); columns.clear(); list.clear(); } catch (Throwable e) { // don't want to break the editor here so just log and bail... error("Error retrieving column data for table " + table.getDisplayValue() + " - driver returned: " + e.getMessage()); } finally { releaseResources(rs); } } trace("Finished retrieving column names for tables for host [ " + databaseHost.getName() + " ]"); }
From source file:org.kuali.core.db.torque.KualiTorqueJDBCTransformTask.java
/** * Generates an XML database schema from JDBC metadata. * /* w w w . jav a 2 s. co m*/ * @throws Exception * a generic exception. */ public void generateXML() throws Exception { // Load the database Driver. Class.forName(dbDriver); log("DB driver sucessfuly instantiated"); Connection con = null; try { // Attempt to connect to a database. Properties p = new Properties(); p.setProperty("user", dbUser); p.setProperty("password", dbPassword); p.setProperty("oracle.jdbc.mapDateToTimestamp", "false"); // workaround for change in 11g JDBC driver con = DriverManager.getConnection(dbUrl, p); log("DB connection established"); Platform platform = PlatformFactory.getPlatformFor(dbType); // Get the database Metadata. DatabaseMetaData dbMetaData = con.getMetaData(); databaseNode = doc.createElement("database"); databaseNode.setAttribute("name", "kuali"); // JHK added naming method databaseNode.setAttribute("defaultJavaNamingMethod", "nochange"); if (processTables) { List<String> tableList = platform.getTableNames(dbMetaData, dbSchema); // ensure all are upper case before exporting tableList = upperCaseList(tableList); // ensure sorting is consistent (not DB-dependent) Collections.sort(tableList); for (String curTable : tableList) { if (!tableNameRegexPattern.matcher(curTable).matches()) { log("Skipping table: " + curTable); continue; } if (StringUtils.isNotBlank(tableNameExcludeRegex) && tableNameExcludeRegexPattern.matcher(curTable).matches()) { log("Skipping table: " + curTable); continue; } log("Processing table: " + curTable); Element table = doc.createElement("table"); table.setAttribute("name", curTable.toUpperCase()); // Add Columns. // TableMap tblMap = dbMap.getTable(curTable); List columns = getColumns(dbMetaData, curTable); List<String> primKeys = platform.getPrimaryKeys(dbMetaData, dbSchema, curTable); Map<String, Object[]> foreignKeys = getForeignKeys(dbMetaData, curTable); // Set the primary keys. primaryKeys = new HashMap<String, String>(); for (int k = 0; k < primKeys.size(); k++) { String curPrimaryKey = (String) primKeys.get(k); primaryKeys.put(curPrimaryKey, curPrimaryKey); } for (int j = 0; j < columns.size(); j++) { List col = (List) columns.get(j); String name = (String) col.get(0); Integer jdbcType = ((Integer) col.get(1)); int size = ((Integer) col.get(2)).intValue(); int scale = ((Integer) col.get(5)).intValue(); // From DatabaseMetaData.java // // Indicates column might not allow NULL values. Huh? // Might? Boy, that's a definitive answer. /* int columnNoNulls = 0; */ // Indicates column definitely allows NULL values. /* int columnNullable = 1; */ // Indicates NULLABILITY of column is unknown. /* int columnNullableUnknown = 2; */ Integer nullType = (Integer) col.get(3); String defValue = (String) col.get(4); Element column = doc.createElement("column"); column.setAttribute("name", name); ; column.setAttribute("type", platform.getTorqueColumnType(jdbcType)); // TypeMap.getTorqueType( type ).getName() ); if (size > 0 && (jdbcType.intValue() == Types.CHAR || jdbcType.intValue() == Types.VARCHAR || jdbcType.intValue() == Types.DECIMAL || jdbcType.intValue() == Types.NUMERIC)) { column.setAttribute("size", String.valueOf(size)); } if (scale > 0 && (jdbcType.intValue() == Types.DECIMAL || jdbcType.intValue() == Types.NUMERIC)) { column.setAttribute("scale", String.valueOf(scale)); } if (primaryKeys.containsKey(name)) { column.setAttribute("primaryKey", "true"); // JHK: protect MySQL from excessively long column in the PK //System.out.println( curTable + "." + name + " / " + size ); if (column.getAttribute("size") != null && size > 765) { log("updating column " + curTable + "." + name + " length from " + size + " to 255"); column.setAttribute("size", "255"); } } else { if (nullType.intValue() == DatabaseMetaData.columnNoNulls) { column.setAttribute("required", "true"); } } if (StringUtils.isNotEmpty(defValue)) { defValue = platform.getColumnDefaultValue(platform.getTorqueColumnType(jdbcType), defValue); if (StringUtils.isNotEmpty(defValue)) { column.setAttribute("default", defValue); } } table.appendChild(column); } List<String> foreignKeyNames = new ArrayList<String>(foreignKeys.keySet()); Collections.sort(foreignKeyNames); // Foreign keys for this table. for (String fkName : foreignKeyNames) { Element fk = doc.createElement("foreign-key"); fk.setAttribute("name", fkName.toUpperCase()); Object[] forKey = foreignKeys.get(fkName); String foreignKeyTable = (String) forKey[0]; List refs = (List) forKey[1]; fk.setAttribute("foreignTable", foreignKeyTable.toUpperCase()); String onDelete = (String) forKey[2]; // gmcgrego - just adding onDelete if it's cascade so as not to affect kfs behavior if (onDelete == "cascade") { fk.setAttribute("onDelete", onDelete); } for (int m = 0; m < refs.size(); m++) { Element ref = doc.createElement("reference"); String[] refData = (String[]) refs.get(m); ref.setAttribute("local", refData[0]); ref.setAttribute("foreign", refData[1]); fk.appendChild(ref); } table.appendChild(fk); } List<TableIndex> indexes = getIndexes(dbMetaData, curTable); Collections.sort(indexes, new Comparator<TableIndex>() { public int compare(TableIndex o1, TableIndex o2) { return o1.name.compareTo(o2.name); } }); for (TableIndex idx : indexes) { if (foreignKeyNames.contains(idx.name)) { log(idx.name + " is also a foreign key, skipping"); continue; } String tagName = idx.unique ? "unique" : "index"; Element index = doc.createElement(tagName); index.setAttribute("name", idx.name.toUpperCase()); for (String colName : idx.columns) { Element col = doc.createElement(tagName + "-column"); col.setAttribute("name", colName); index.appendChild(col); } table.appendChild(index); } databaseNode.appendChild(table); } } if (processViews) { log("Getting view list..."); List<String> viewNames = platform.getViewNames(dbMetaData, dbSchema); log("Found " + viewNames.size() + " views."); viewNames = upperCaseList(viewNames); Collections.sort(viewNames); for (String viewName : viewNames) { if (!tableNameRegexPattern.matcher(viewName).matches()) { log("Skipping view: " + viewName); continue; } Element view = doc.createElement("view"); view.setAttribute("name", viewName.toUpperCase()); /* * <view name="" viewdefinition="" /> * */ String definition = platform.getViewDefinition(dbMetaData.getConnection(), dbSchema, viewName); definition = definition.replaceAll("\0", ""); view.setAttribute("viewdefinition", definition); databaseNode.appendChild(view); } } if (processSequences) { log("Getting sequence list..."); List<String> sequenceNames = platform.getSequenceNames(dbMetaData, dbSchema); log("Found " + sequenceNames.size() + " sequences."); sequenceNames = upperCaseList(sequenceNames); Collections.sort(sequenceNames); for (String sequenceName : sequenceNames) { if (!tableNameRegexPattern.matcher(sequenceName).matches()) { log("Skipping sequence: " + sequenceName); continue; } Element sequence = doc.createElement("sequence"); sequence.setAttribute("name", sequenceName.toUpperCase()); /* * <view name="" nextval="" /> * */ Long nextVal = platform.getSequenceNextVal(dbMetaData.getConnection(), dbSchema, sequenceName); sequence.setAttribute("nextval", nextVal.toString()); databaseNode.appendChild(sequence); } doc.appendChild(databaseNode); } } finally { if (con != null) { con.close(); con = null; } } }
From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectSQLServer.java
public DialectSQLServer(DatabaseMetaData metadata, BinaryManager binaryManager, RepositoryDescriptor repositoryDescriptor) throws StorageException { super(metadata, binaryManager, repositoryDescriptor); try {/* w ww . j av a2 s . c o m*/ checkDatabaseConfiguration(metadata.getConnection()); majorVersion = metadata.getDatabaseMajorVersion(); engineEdition = getEngineEdition(metadata.getConnection()); } catch (SQLException e) { throw new StorageException(e); } if (engineEdition == 5) { // 5 = SQL Azure azure = true; fulltextDisabled = true; if (repositoryDescriptor != null) { repositoryDescriptor.setFulltextDisabled(true); } } fulltextAnalyzer = repositoryDescriptor == null ? null : repositoryDescriptor.fulltextAnalyzer == null ? DEFAULT_FULLTEXT_ANALYZER : repositoryDescriptor.fulltextAnalyzer; fulltextCatalog = repositoryDescriptor == null ? null : repositoryDescriptor.fulltextCatalog == null ? DEFAULT_FULLTEXT_CATALOG : repositoryDescriptor.fulltextCatalog; usersSeparator = repositoryDescriptor == null ? null : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR : repositoryDescriptor.usersSeparatorKey; pathOptimizationsEnabled = repositoryDescriptor == null ? false : repositoryDescriptor.getPathOptimizationsEnabled(); String idt = repositoryDescriptor == null ? null : repositoryDescriptor.idType; if (idt == null || "".equals(idt) || "varchar".equalsIgnoreCase(idt)) { idType = DialectIdType.VARCHAR; } else if (idt.toLowerCase().startsWith("sequence")) { idType = DialectIdType.SEQUENCE; if (idt.toLowerCase().startsWith("sequence:")) { String[] split = idt.split(":"); idSequenceName = split[1]; } else { idSequenceName = "hierarchy_seq"; } } else { throw new StorageException("Unknown id type: '" + idt + "'"); } }
From source file:org.schemaspy.SchemaAnalyzer.java
public Database analyze(String schema, Config config, File outputDir, ProgressListener progressListener) throws SQLException, IOException { try {/* w ww. j av a 2 s . c om*/ LOGGER.info("Starting schema analysis"); FileUtils.forceMkdir(outputDir); String dbName = config.getDb(); String catalog = commandLineArguments.getCatalog(); DatabaseMetaData meta = sqlService.connect(config); LOGGER.debug("supportsSchemasInTableDefinitions: {}", meta.supportsSchemasInTableDefinitions()); LOGGER.debug("supportsCatalogsInTableDefinitions: {}", meta.supportsCatalogsInTableDefinitions()); // set default Catalog and Schema of the connection if (schema == null) schema = meta.getConnection().getSchema(); if (catalog == null) catalog = meta.getConnection().getCatalog(); SchemaMeta schemaMeta = config.getMeta() == null ? null : new SchemaMeta(config.getMeta(), dbName, schema); if (config.isHtmlGenerationEnabled()) { FileUtils.forceMkdir(new File(outputDir, "tables")); FileUtils.forceMkdir(new File(outputDir, "diagrams/summary")); LOGGER.info("Connected to {} - {}", meta.getDatabaseProductName(), meta.getDatabaseProductVersion()); if (schemaMeta != null && schemaMeta.getFile() != null) { LOGGER.info("Using additional metadata from {}", schemaMeta.getFile()); } } // // create our representation of the database // Database db = new Database(config, meta, dbName, catalog, schema, schemaMeta, progressListener); databaseService.gatheringSchemaDetails(config, db, progressListener); long duration = progressListener.startedGraphingSummaries(); Collection<Table> tables = new ArrayList<>(db.getTables()); tables.addAll(db.getViews()); if (tables.isEmpty()) { dumpNoTablesMessage(schema, config.getUser(), meta, config.getTableInclusions() != null); if (!config.isOneOfMultipleSchemas()) // don't bail if we're doing the whole enchilada throw new EmptySchemaException(); } if (config.isHtmlGenerationEnabled()) { generateHtmlDoc(config, progressListener, outputDir, db, duration, tables); } outputProducers.forEach(outputProducer -> { try { outputProducer.generate(db, outputDir); } catch (OutputException oe) { if (config.isOneOfMultipleSchemas()) { LOGGER.warn("Failed to produce output", oe); } else { throw oe; } } }); List<ForeignKeyConstraint> recursiveConstraints = new ArrayList<>(); // create an orderer to be able to determine insertion and deletion ordering of tables TableOrderer orderer = new TableOrderer(); // side effect is that the RI relationships get trashed // also populates the recursiveConstraints collection List<Table> orderedTables = orderer.getTablesOrderedByRI(db.getTables(), recursiveConstraints); writeOrders(outputDir, orderedTables); duration = progressListener.finishedGatheringDetails(); long overallDuration = progressListener.finished(tables, config); if (config.isHtmlGenerationEnabled()) { LOGGER.info("Wrote table details in {} seconds", duration / 1000); LOGGER.info("Wrote relationship details of {} tables/views to directory '{}' in {} seconds.", tables.size(), outputDir, overallDuration / 1000); LOGGER.info("View the results by opening {}", new File(outputDir, "index.html")); } return db; } catch (Config.MissingRequiredParameterException missingParam) { config.dumpUsage(missingParam.getMessage(), missingParam.isDbTypeSpecific()); return null; } }
From source file:org.talend.core.model.metadata.builder.database.manager.ExtractManager.java
protected Map<String, String> retrievePrimaryKeys(DatabaseMetaData dbMetaData, String catalogName, String schemaName, String tableName) throws SQLException { Map<String, String> primaryKeys = new HashMap<String, String>(); try {//from ww w . j av a 2s . com ResultSet keys = null; try { keys = dbMetaData.getPrimaryKeys(catalogName, schemaName, tableName); primaryKeys.clear(); while (keys.next()) { primaryKeys.put(keys.getString("COLUMN_NAME"), "PRIMARY KEY"); //$NON-NLS-1$ //$NON-NLS-2$ } } finally { if (keys != null) { keys.close(); } } } catch (Exception e) { throw new SQLException(e); } checkUniqueKeyConstraint(tableName, primaryKeys, dbMetaData.getConnection()); return primaryKeys; }