List of usage examples for java.sql DatabaseMetaData getCatalogs
ResultSet getCatalogs() throws SQLException;
From source file:it.eng.spagobi.meta.initializer.PhysicalModelInitializer.java
private void addCatalog(Connection conn, PhysicalModel model, String defaultCatalog) { String catalog;/* w w w . j a v a2 s . com*/ List<String> catalogs; DatabaseMetaData dbMeta; ResultSet rs; Iterator<String> it; catalog = null; try { catalog = conn.getCatalog(); if (catalog == null) { dbMeta = conn.getMetaData(); rs = dbMeta.getCatalogs(); catalogs = new ArrayList(); while (rs.next()) { String catalogName = rs.getString(1); if (catalogName != null) { catalogs.add(catalogName); } } if (catalogs.size() == 0) { log("No schema [" + dbMeta.getSchemaTerm() + "] defined"); } else if (catalogs.size() == 1) { catalog = catalogs.get(1); } else { String targetCatalog = null; it = catalogs.iterator(); while (it.hasNext()) { String s = it.next(); log("s [" + s + "]"); if (s.equalsIgnoreCase(defaultCatalog)) { targetCatalog = defaultCatalog; break; } } if (targetCatalog == null) { throw new RuntimeException("No catalog named [" + defaultCatalog + "] is available on db"); } catalog = targetCatalog; } rs.close(); } model.setCatalog(catalog); } catch (Throwable t) { throw new RuntimeException("Impossible to initialize catalog metadata", t); } }
From source file:com.netspective.axiom.policy.AnsiDatabasePolicy.java
public void reverseEngineer(Writer writer, Connection conn, String catalog, String schemaPattern) throws IOException, SQLException { Map dataTypesMap = prepareJdbcTypeInfoMap(); DatabaseMetaData dbmd = conn.getMetaData(); TextUtils textUtils = TextUtils.getInstance(); writer.write("<?xml version=\"1.0\"?>\n\n"); writer.write("<!-- Reverse engineered by Axiom\n"); writer.write(" driver: " + dbmd.getDriverName() + "\n"); writer.write(" driver-version: " + dbmd.getDriverVersion() + "\n"); writer.write(" product: " + dbmd.getDatabaseProductName() + "\n"); writer.write(" product-version: " + dbmd.getDatabaseProductVersion() + "\n"); writer.write(" available catalogs:"); ResultSet rs = null;/*w w w .jav a 2 s .com*/ try { rs = dbmd.getCatalogs(); while (rs.next()) { writer.write(" " + rs.getObject(1).toString()); } } finally { if (rs != null) rs.close(); } writer.write("\n"); writer.write(" available schemas:"); try { rs = dbmd.getSchemas(); while (rs.next()) { writer.write(" " + rs.getObject(1).toString()); } } finally { if (rs != null) rs.close(); } writer.write("\n"); writer.write("-->\n\n"); writer.write("<component xmlns:xdm=\"http://www.netspective.org/Framework/Commons/XMLDataModel\">\n"); writer.write(" <xdm:include resource=\"com/netspective/axiom/conf/axiom.xml\"/>\n"); writer.write(" <schema name=\"" + catalog + "." + schemaPattern + "\">\n"); Map dbmdTypeInfoByName = new HashMap(); Map dbmdTypeInfoByJdbcType = new HashMap(); ResultSet typesRS = null; try { typesRS = dbmd.getTypeInfo(); while (typesRS.next()) { int colCount = typesRS.getMetaData().getColumnCount(); Object[] typeInfo = new Object[colCount]; for (int i = 1; i <= colCount; i++) typeInfo[i - 1] = typesRS.getObject(i); dbmdTypeInfoByName.put(typesRS.getString(1), typeInfo); dbmdTypeInfoByJdbcType.put(new Integer(typesRS.getInt(2)), typeInfo); } } finally { if (typesRS != null) typesRS.close(); } ResultSet tables = null; try { tables = dbmd.getTables(catalog, schemaPattern, null, new String[] { "TABLE" }); while (tables.next()) { String tableNameOrig = tables.getString(3); String tableName = textUtils.fixupTableNameCase(tableNameOrig); writer.write(" <table name=\"" + tableName + "\">\n"); Map primaryKeys = new HashMap(); ResultSet pkRS = null; try { pkRS = dbmd.getPrimaryKeys(null, null, tableNameOrig); while (pkRS.next()) { primaryKeys.put(pkRS.getString(4), pkRS.getString(5)); } } catch (Exception e) { // driver may not support this function } finally { if (pkRS != null) pkRS.close(); } Map fKeys = new HashMap(); ResultSet fkRS = null; try { fkRS = dbmd.getImportedKeys(null, null, tableNameOrig); while (fkRS.next()) { fKeys.put(fkRS.getString(8), textUtils.fixupTableNameCase(fkRS.getString(3)) + "." + fkRS.getString(4).toLowerCase()); } } catch (Exception e) { // driver may not support this function } finally { if (fkRS != null) fkRS.close(); } // we keep track of processed columns so we don't duplicate them in the XML Set processedColsMap = new HashSet(); ResultSet columns = null; try { columns = dbmd.getColumns(null, null, tableNameOrig, null); while (columns.next()) { String columnNameOrig = columns.getString(4); if (processedColsMap.contains(columnNameOrig)) continue; processedColsMap.add(columnNameOrig); String columnName = columnNameOrig.toLowerCase(); writer.write(" <column name=\"" + columnName + "\""); try { if (fKeys.containsKey(columnNameOrig)) writer.write(" lookup-ref=\"" + fKeys.get(columnNameOrig) + "\""); else { short jdbcType = columns.getShort(5); String dataType = (String) dataTypesMap.get(new Integer(jdbcType)); if (dataType == null) dataType = Short.toString(jdbcType); writer.write(" type=\"" + dataType + "\""); } if (primaryKeys.containsKey(columnNameOrig)) writer.write(" primary-key=\"yes\""); if (columns.getString(18).equals("NO")) writer.write(" required=\"yes\""); String defaultValue = columns.getString(13); if (defaultValue != null) writer.write(" default=\"" + defaultValue + "\""); String remarks = columns.getString(12); if (remarks != null) writer.write(" descr=\"" + remarks + "\""); } catch (Exception e) { } writer.write("/>\n"); } } finally { if (columns != null) columns.close(); } writer.write(" </table>\n"); } } finally { tables.close(); } writer.write(" </schema>\n"); writer.write("</component>"); }
From source file:jef.database.DbMetaData.java
/** * ??catalog// ww w .j a va 2s. co m * * @return catalog * @throws SQLException */ public String[] getCatalogs() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = databaseMetaData.getCatalogs(); try { List<String> list = ResultSets.toStringList(rs, "TABLE_CAT", 9999, this.getProfile()); return list.toArray(new String[list.size()]); } finally { DbUtils.close(rs); releaseConnection(conn); } }
From source file:org.apache.bigtop.itest.hive.TestJdbc.java
/** * Test simple DatabaseMetaData calls. getColumns is tested elsewhere, as we need to call * that on a valid table. Same with getFunctions. * * @throws SQLException//from w w w . ja v a2s .com */ @Test public void databaseMetaDataCalls() throws SQLException { DatabaseMetaData md = conn.getMetaData(); boolean boolrc = md.allTablesAreSelectable(); LOG.debug("All tables are selectable? " + boolrc); String strrc = md.getCatalogSeparator(); LOG.debug("Catalog separator " + strrc); strrc = md.getCatalogTerm(); LOG.debug("Catalog term " + strrc); ResultSet rs = md.getCatalogs(); while (rs.next()) { strrc = rs.getString(1); LOG.debug("Found catalog " + strrc); } Connection c = md.getConnection(); int intrc = md.getDatabaseMajorVersion(); LOG.debug("DB major version is " + intrc); intrc = md.getDatabaseMinorVersion(); LOG.debug("DB minor version is " + intrc); strrc = md.getDatabaseProductName(); LOG.debug("DB product name is " + strrc); strrc = md.getDatabaseProductVersion(); LOG.debug("DB product version is " + strrc); intrc = md.getDefaultTransactionIsolation(); LOG.debug("Default transaction isolation is " + intrc); intrc = md.getDriverMajorVersion(); LOG.debug("Driver major version is " + intrc); intrc = md.getDriverMinorVersion(); LOG.debug("Driver minor version is " + intrc); strrc = md.getDriverName(); LOG.debug("Driver name is " + strrc); strrc = md.getDriverVersion(); LOG.debug("Driver version is " + strrc); strrc = md.getExtraNameCharacters(); LOG.debug("Extra name characters is " + strrc); strrc = md.getIdentifierQuoteString(); LOG.debug("Identifier quote string is " + strrc); // In Hive 1.2 this always returns an empty RS rs = md.getImportedKeys("a", "b", "d"); // In Hive 1.2 this always returns an empty RS rs = md.getIndexInfo("a", "b", "d", true, true); intrc = md.getJDBCMajorVersion(); LOG.debug("JDBC major version is " + intrc); intrc = md.getJDBCMinorVersion(); LOG.debug("JDBC minor version is " + intrc); intrc = md.getMaxColumnNameLength(); LOG.debug("Maximum column name length is " + intrc); strrc = md.getNumericFunctions(); LOG.debug("Numeric functions are " + strrc); // In Hive 1.2 this always returns an empty RS rs = md.getPrimaryKeys("a", "b", "d"); // In Hive 1.2 this always returns an empty RS rs = md.getProcedureColumns("a", "b", "d", "e"); strrc = md.getProcedureTerm(); LOG.debug("Procedures are called " + strrc); // In Hive 1.2 this always returns an empty RS rs = md.getProcedures("a", "b", "d"); strrc = md.getSchemaTerm(); LOG.debug("Schemas are called " + strrc); rs = md.getSchemas(); while (rs.next()) { strrc = rs.getString(1); LOG.debug("Found schema " + strrc); } strrc = md.getSearchStringEscape(); LOG.debug("Search string escape is " + strrc); strrc = md.getStringFunctions(); LOG.debug("String functions are " + strrc); strrc = md.getSystemFunctions(); LOG.debug("System functions are " + strrc); rs = md.getTableTypes(); while (rs.next()) { strrc = rs.getString(1); LOG.debug("Found table type " + strrc); } strrc = md.getTimeDateFunctions(); LOG.debug("Time/date functions are " + strrc); rs = md.getTypeInfo(); while (rs.next()) { strrc = rs.getString(1); LOG.debug("Found type " + strrc); } // In Hive 1.2 this always returns an empty RS rs = md.getUDTs("a", "b", "d", null); boolrc = md.supportsAlterTableWithAddColumn(); LOG.debug("Supports alter table with add column? " + boolrc); boolrc = md.supportsAlterTableWithDropColumn(); LOG.debug("Supports alter table with drop column? " + boolrc); boolrc = md.supportsBatchUpdates(); LOG.debug("Supports batch updates? " + boolrc); boolrc = md.supportsCatalogsInDataManipulation(); LOG.debug("Supports catalogs in data manipulation? " + boolrc); boolrc = md.supportsCatalogsInIndexDefinitions(); LOG.debug("Supports catalogs in index definition? " + boolrc); boolrc = md.supportsCatalogsInPrivilegeDefinitions(); LOG.debug("Supports catalogs in privilege definition? " + boolrc); boolrc = md.supportsCatalogsInProcedureCalls(); LOG.debug("Supports catalogs in procedure calls? " + boolrc); boolrc = md.supportsCatalogsInTableDefinitions(); LOG.debug("Supports catalogs in table definition? " + boolrc); boolrc = md.supportsColumnAliasing(); LOG.debug("Supports column aliasing? " + boolrc); boolrc = md.supportsFullOuterJoins(); LOG.debug("Supports full outer joins? " + boolrc); boolrc = md.supportsGroupBy(); LOG.debug("Supports group by? " + boolrc); boolrc = md.supportsLimitedOuterJoins(); LOG.debug("Supports limited outer joins? " + boolrc); boolrc = md.supportsMultipleResultSets(); LOG.debug("Supports limited outer joins? " + boolrc); boolrc = md.supportsNonNullableColumns(); LOG.debug("Supports non-nullable columns? " + boolrc); boolrc = md.supportsOuterJoins(); LOG.debug("Supports outer joins? " + boolrc); boolrc = md.supportsPositionedDelete(); LOG.debug("Supports positioned delete? " + boolrc); boolrc = md.supportsPositionedUpdate(); LOG.debug("Supports positioned update? " + boolrc); boolrc = md.supportsResultSetHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT); LOG.debug("Supports result set holdability? " + boolrc); boolrc = md.supportsResultSetType(ResultSet.HOLD_CURSORS_OVER_COMMIT); LOG.debug("Supports result set type? " + boolrc); boolrc = md.supportsSavepoints(); LOG.debug("Supports savepoints? " + boolrc); boolrc = md.supportsSchemasInDataManipulation(); LOG.debug("Supports schemas in data manipulation? " + boolrc); boolrc = md.supportsSchemasInIndexDefinitions(); LOG.debug("Supports schemas in index definitions? " + boolrc); boolrc = md.supportsSchemasInPrivilegeDefinitions(); LOG.debug("Supports schemas in privilege definitions? " + boolrc); boolrc = md.supportsSchemasInProcedureCalls(); LOG.debug("Supports schemas in procedure calls? " + boolrc); boolrc = md.supportsSchemasInTableDefinitions(); LOG.debug("Supports schemas in table definitions? " + boolrc); boolrc = md.supportsSelectForUpdate(); LOG.debug("Supports select for update? " + boolrc); boolrc = md.supportsStoredProcedures(); LOG.debug("Supports stored procedures? " + boolrc); boolrc = md.supportsTransactions(); LOG.debug("Supports transactions? " + boolrc); boolrc = md.supportsUnion(); LOG.debug("Supports union? " + boolrc); boolrc = md.supportsUnionAll(); LOG.debug("Supports union all? " + boolrc); }
From source file:org.apache.ddlutils.task.DumpMetadataTask.java
/** * Dumps the catalogs and schemas of the database. * //w w w .java 2 s .c om * @param xmlWriter The xml writer to write to * @param metaData The database meta data */ private void dumpCatalogsAndSchemas(PrettyPrintingXmlWriter xmlWriter, final DatabaseMetaData metaData) { performResultSetXmlOperation(xmlWriter, "catalogs", new ResultSetXmlOperation() { public ResultSet getResultSet() throws SQLException { return metaData.getCatalogs(); } public void handleRow(PrettyPrintingXmlWriter xmlWriter, ResultSet result) throws SQLException { String catalogName = result.getString("TABLE_CAT"); if ((catalogName != null) && (catalogName.length() > 0)) { xmlWriter.writeElementStart(null, "catalog"); xmlWriter.writeAttribute(null, "name", catalogName); xmlWriter.writeElementEnd(); } } public void handleError(SQLException ex) { log("Could not read the catalogs from the result set: " + ex.getStackTrace(), Project.MSG_ERR); } }); performResultSetXmlOperation(xmlWriter, "schemas", new ResultSetXmlOperation() { public ResultSet getResultSet() throws SQLException { return metaData.getSchemas(); } public void handleRow(PrettyPrintingXmlWriter xmlWriter, ResultSet result) throws SQLException { String schemaName = result.getString("TABLE_SCHEM"); if ((schemaName != null) && (schemaName.length() > 0)) { xmlWriter.writeElementStart(null, "schema"); xmlWriter.writeAttribute(null, "name", schemaName); xmlWriter.writeElementEnd(); } } public void handleError(SQLException ex) { log("Could not read the schemas from the result set: " + ex.getStackTrace(), Project.MSG_ERR); } }); }
From source file:org.apache.empire.db.codegen.CodeGenParser.java
private String getCatalogs(DatabaseMetaData dbMeta) throws SQLException { String retVal = ""; ResultSet rs = dbMeta.getCatalogs(); while (rs.next()) { retVal += rs.getString("TABLE_CAT") + ", "; }//from ww w . j a v a 2s . c o m if (retVal.length() > 2) retVal = retVal.substring(0, retVal.length() - 2); return retVal; }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testParentReferences() throws Exception { /* Test parent references from Statement */ Statement s = this.con.createStatement(); ResultSet rs = s.executeQuery("SELECT * FROM " + dataTypeTableName); assertTrue(s.getConnection() == this.con); assertTrue(rs.getStatement() == s);/* ww w . ja v a 2s .c o m*/ rs.close(); s.close(); /* Test parent references from PreparedStatement */ PreparedStatement ps = this.con.prepareStatement("SELECT * FROM " + dataTypeTableName); rs = ps.executeQuery(); assertTrue(ps.getConnection() == this.con); assertTrue(rs.getStatement() == ps); rs.close(); ps.close(); /* Test DatabaseMetaData queries which do not have a parent Statement */ DatabaseMetaData md = this.con.getMetaData(); assertTrue(md.getConnection() == this.con); rs = md.getCatalogs(); assertNull(rs.getStatement()); rs.close(); rs = md.getColumns(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getFunctions(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getImportedKeys(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getPrimaryKeys(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getProcedureColumns(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getProcedures(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getSchemas(); assertNull(rs.getStatement()); rs.close(); rs = md.getTableTypes(); assertNull(rs.getStatement()); rs.close(); rs = md.getTables(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getTypeInfo(); assertNull(rs.getStatement()); rs.close(); }
From source file:org.apache.kylin.jdbc.ITJDBCDriverTest.java
@Test public void testMetadata1() throws Exception { // check the JDBC API here: http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html Connection conn = getConnection(); // test getSchemas(); List<String> schemaList = Lists.newArrayList(); DatabaseMetaData dbMetadata = conn.getMetaData(); ResultSet resultSet = dbMetadata.getSchemas(); while (resultSet.next()) { String schema = resultSet.getString("TABLE_SCHEM"); String catalog = resultSet.getString("TABLE_CATALOG"); System.out.println("Get schema: schema=" + schema + ", catalog=" + catalog); schemaList.add(schema);//ww w .jav a 2s . c om } resultSet.close(); Assert.assertTrue(schemaList.contains("DEFAULT")); Assert.assertTrue(schemaList.contains("EDW")); // test getCatalogs(); resultSet = dbMetadata.getCatalogs(); List<String> catalogList = Lists.newArrayList(); while (resultSet.next()) { String catalog = resultSet.getString("TABLE_CAT"); System.out.println("Get catalog: catalog=" + catalog); catalogList.add(catalog); } resultSet.close(); Assert.assertTrue(catalogList.size() > 0 && catalogList.contains("defaultCatalog")); /** //Disable the test on getTableTypes() as it is not ready resultSet = dbMetadata.getTableTypes(); List<String> tableTypes = Lists.newArrayList(); while (resultSet.next()) { String type = resultSet.getString("TABLE_TYPE"); System.out.println("Get table type: type=" + type); tableTypes.add(type); } Assert.assertTrue(tableTypes.size() > 0 && tableTypes.contains("TABLE")); resultSet.close(); **/ conn.close(); }
From source file:org.apache.kylin.jdbc.JDBCDriverTest.java
@Test public void testMetadata1() throws Exception { // check the JDBC API here: http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html Connection conn = getConnection(); // test getSchemas(); List<String> schemaList = Lists.newArrayList(); DatabaseMetaData dbMetadata = conn.getMetaData(); ResultSet resultSet = dbMetadata.getSchemas(); while (resultSet.next()) { String schema = resultSet.getString("TABLE_SCHEM"); String catalog = resultSet.getString("TABLE_CATALOG"); System.out.println("Get schema: schema=" + schema + ", catalog=" + catalog); schemaList.add(schema);// w w w .ja va 2 s .c o m } resultSet.close(); Assert.assertTrue(schemaList.contains("DEFAULT")); Assert.assertTrue(schemaList.contains("EDW")); // test getCatalogs(); resultSet = dbMetadata.getCatalogs(); List<String> catalogList = Lists.newArrayList(); while (resultSet.next()) { String catalog = resultSet.getString("TABLE_CAT"); System.out.println("Get catalog: catalog=" + catalog); catalogList.add(catalog); } Assert.assertTrue(catalogList.size() > 0 && catalogList.contains("defaultCatalog")); /** //Disable the test on getTableTypes() as it is not ready resultSet = dbMetadata.getTableTypes(); List<String> tableTypes = Lists.newArrayList(); while (resultSet.next()) { String type = resultSet.getString("TABLE_TYPE"); System.out.println("Get table type: type=" + type); tableTypes.add(type); } Assert.assertTrue(tableTypes.size() > 0 && tableTypes.contains("TABLE")); resultSet.close(); **/ conn.close(); }
From source file:org.apache.zeppelin.jdbc.SqlCompleter.java
/** * Return list of catalog names within the database * * @param meta metadata from connection to database * @param schemaFilter a catalog name pattern; must match the catalog name * as it is stored in the database; "" retrieves those without a catalog; * <code>null</code> means that the schema name should not be used to narrow * the search; supports '%' and '_' symbols; for example "prod_v_%" * @return set of all catalog names in the database *//*from w w w . jav a 2 s . c o m*/ private static Set<String> getCatalogNames(DatabaseMetaData meta, String schemaFilter) { Set<String> res = new HashSet<>(); try { ResultSet schemas = meta.getCatalogs(); try { while (schemas.next()) { String schemaName = schemas.getString("TABLE_CAT"); if (schemaFilter.equals("") || schemaFilter == null || schemaName.matches(schemaFilter.replace("_", ".").replace("%", ".*?"))) { res.add(schemaName); } } } finally { schemas.close(); } } catch (SQLException t) { logger.error("Failed to retrieve the schema names", t); } return res; }