List of usage examples for java.sql DatabaseMetaData getSchemas
ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException;
From source file:com.netflix.metacat.connector.jdbc.services.JdbcConnectorDatabaseService.java
/** * {@inheritDoc}//from ww w . j a va2s. com */ @Override public List<QualifiedName> listNames(@Nonnull final ConnectorRequestContext context, @Nonnull final QualifiedName name, @Nullable final QualifiedName prefix, @Nullable final Sort sort, @Nullable final Pageable pageable) { final String catalogName = name.getCatalogName(); log.debug("Beginning to list database names for catalog {} for request {}", catalogName, context); try (final Connection connection = this.dataSource.getConnection()) { final DatabaseMetaData metaData = connection.getMetaData(); final List<QualifiedName> names = Lists.newArrayList(); try (final ResultSet schemas = prefix == null || StringUtils.isEmpty(prefix.getDatabaseName()) ? metaData.getSchemas(connection.getCatalog(), null) : metaData.getSchemas(connection.getCatalog(), prefix.getDatabaseName() + JdbcConnectorUtils.MULTI_CHARACTER_SEARCH)) { while (schemas.next()) { final String schemaName = schemas.getString("TABLE_SCHEM").toLowerCase(Locale.ENGLISH); // skip internal schemas if (!schemaName.equals("information_schema")) { names.add(QualifiedName.ofDatabase(name.getCatalogName(), schemaName)); } } } // Does user want sorting? if (sort != null) { // We can only really sort by the database name at this level so ignore SortBy field final Comparator<QualifiedName> comparator = Comparator.comparing(QualifiedName::getDatabaseName); JdbcConnectorUtils.sort(names, sort, comparator); } // Does user want pagination? final List<QualifiedName> results = JdbcConnectorUtils.paginate(names, pageable); log.debug("Finished listing database names for catalog {} for request {}", catalogName, context); return results; } catch (final SQLException se) { throw this.exceptionMapper.toConnectorException(se, name); } }
From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java
@Test public void testShow() throws Exception { Connection conn = getConnection("octopus", "bitnine"); System.out.println("* Transaction isolation level"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SHOW TRANSACTION ISOLATION LEVEL"); while (rs.next()) System.out.println(" " + rs.getString("transaction_isolation")); rs.close();//ww w . j av a2 s . c o m System.out.println("* DataSources"); DatabaseMetaData metaData = conn.getMetaData(); rs = metaData.getCatalogs(); while (rs.next()) { System.out.println(" " + rs.getString("TABLE_CAT") + ", " + rs.getString("REMARKS")); } rs.close(); System.out.println("* Schemas"); rs = metaData.getSchemas(dataMemDb.name, "%DEFAULT"); while (rs.next()) { System.out.println(" " + rs.getString("TABLE_SCHEM") + ", " + rs.getString("TABLE_CATALOG") + ", " + rs.getString("REMARKS") + ", " + rs.getString("TABLE_CAT_REMARKS")); } rs.close(); System.out.println("* Tables"); rs = metaData.getTables(dataMemDb.name, "%DEFAULT", "employee", null); while (rs.next()) { System.out.println(" " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", " + rs.getString("TABLE_NAME") + ", " + rs.getString("REMARKS") + ", " + rs.getString("TABLE_CAT_REMARKS") + ", " + rs.getString("TABLE_SCHEM_REMARKS")); } rs.close(); System.out.println("* Columns"); rs = metaData.getColumns(dataMemDb.name, "%DEFAULT", "employee", "%"); while (rs.next()) { System.out.println(" " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", " + rs.getString("TABLE_NAME") + ", " + rs.getString("COLUMN_NAME") + ", " + rs.getString("REMARKS") + ", " + rs.getString("TABLE_CAT_REMARKS") + ", " + rs.getString("TABLE_SCHEM_REMARKS") + ", " + rs.getString("TABLE_NAME_REMARKS")); } rs.close(); System.out.println("* Users"); rs = stmt.executeQuery("SHOW ALL USERS"); while (rs.next()) { System.out.println(" " + rs.getString("USER_NAME") + ", " + rs.getString("REMARKS")); } rs.close(); stmt.execute("CREATE USER \"jsyang\" IDENTIFIED BY '0009'"); stmt.execute("GRANT ALL ON \"" + dataMemDb.name + "\".\"__DEFAULT\" TO \"jsyang\""); rs = stmt.executeQuery("SHOW OBJECT PRIVILEGES FOR \"jsyang\""); while (rs.next()) { System.out.println(" " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", " + rs.getString("PRIVILEGE")); } rs.close(); stmt.execute("DROP USER \"jsyang\""); stmt.close(); conn.close(); }
From source file:org.apache.bigtop.itest.hive.TestJdbc.java
@Test public void setSchema() throws SQLException { try (Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) { final String dbName = "bigtop_jdbc_test_db"; final String tableName = "bigtop_jdbc_test_table"; stmt.execute("drop table if exists " + tableName); stmt.execute("drop database if exists " + dbName + " cascade"); stmt.execute("create database " + dbName); conn.setSchema(dbName);//from w w w . j a v a2 s .c om DatabaseMetaData md = conn.getMetaData(); ResultSet rs = md.getSchemas(null, dbName); while (rs.next()) { String schemaName = rs.getString(2); LOG.debug("Schema name is " + schemaName); } stmt.execute("create table " + tableName + " (i int, s varchar(32))"); rs = md.getTables(null, dbName, tableName, null); while (rs.next()) { String tName = rs.getString(3); LOG.debug("Schema name is " + tName); } rs = md.getColumns(null, dbName, tableName, "i"); while (rs.next()) { String colName = rs.getString(4); LOG.debug("Schema name is " + colName); } rs = md.getFunctions(null, dbName, "foo"); while (rs.next()) { String funcName = rs.getString(3); LOG.debug("Schema name is " + funcName); } } }
From source file:org.apache.hadoop.vertica.VerticaUtil.java
public static void checkOutputSpecs(Configuration conf) throws IOException { VerticaConfiguration vtconfig = new VerticaConfiguration(conf); String writerTable = vtconfig.getOutputTableName(); if (writerTable == null) throw new IOException("Vertica output requires a table name defined by " + VerticaConfiguration.OUTPUT_TABLE_NAME_PROP); String[] def = vtconfig.getOutputTableDef(); boolean dropTable = vtconfig.getDropTable(); String schema = null;/*from ww w . ja v a 2 s. co m*/ String table = null; String[] schemaTable = writerTable.split("\\."); if (schemaTable.length == 2) { schema = schemaTable[0]; table = schemaTable[1]; } else table = schemaTable[0]; Statement stmt = null; try { Connection conn = vtconfig.getConnection(true); DatabaseMetaData dbmd = conn.getMetaData(); ResultSet rs = dbmd.getTables(null, schema, table, null); boolean tableExists = rs.next(); stmt = conn.createStatement(); if (tableExists && dropTable) { if (verticaVersion(conf, true) >= 305) { stmt = conn.createStatement(); stmt.execute("TRUNCATE TABLE " + writerTable); } else { // for version < 3.0 drop the table if it exists // if def is empty, grab the columns first to redfine the table if (def == null) { rs = dbmd.getColumns(null, schema, table, null); ArrayList<String> defs = new ArrayList<String>(); while (rs.next()) defs.add(rs.getString(4) + " " + rs.getString(5)); def = defs.toArray(new String[0]); } stmt = conn.createStatement(); stmt.execute("DROP TABLE " + writerTable + " CASCADE"); tableExists = false; // force create } } // create table if it doesn't exist if (!tableExists) { if (def == null) throw new RuntimeException( "Table " + writerTable + " does not exist and no table definition provided"); if (schema != null) { rs = dbmd.getSchemas(null, schema); if (!rs.next()) stmt.execute("CREATE SCHEMA " + schema); } StringBuffer tabledef = new StringBuffer("CREATE TABLE ").append(writerTable).append(" ("); for (String column : def) tabledef.append(column).append(","); tabledef.replace(tabledef.length() - 1, tabledef.length(), ")"); stmt.execute(tabledef.toString()); // TODO: create segmented projections stmt.execute("select implement_temp_design('" + writerTable + "')"); } } catch (Exception e) { throw new RuntimeException(e); } finally { if (stmt != null) try { stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } }
From source file:org.talend.metadata.managment.model.DBConnectionFillerImpl.java
@Override public List<Schema> fillSchemaToCatalog(DatabaseConnection dbConn, DatabaseMetaData dbJDBCMetadata, Catalog catalog, List<String> schemaFilter) throws Throwable { ResultSet schemaRs = null;//from www .jav a 2 s . co m try { // Case of JDK 1.6 if (dbJDBCMetadata.getDriverName().equals(DatabaseConstant.MSSQL_DRIVER_NAME_JDBC2_0)) { Method getSchemaMethod = dbJDBCMetadata.getClass().getMethod("getSchemas", String.class, //$NON-NLS-1$ String.class); schemaRs = (ResultSet) getSchemaMethod.invoke(dbJDBCMetadata, catalog.getName(), null); } } catch (SecurityException e) { // Case of JDK1.5 } catch (NoSuchMethodException e) { // do nothing here !!! } catch (IllegalArgumentException e) { // do nothing here !!! } catch (IllegalAccessException e) { // do nothing here !!! } catch (InvocationTargetException e) { // Case of JDK1.5 if (e.getTargetException().getClass().toString().equals("SQLServerException")) { //$NON-NLS-1$ throw e.getTargetException(); } } catch (SQLException e) { log.error(e, e); } if (schemaRs == null) { try { if (dbJDBCMetadata instanceof SybaseDatabaseMetaData) { schemaRs = ((SybaseDatabaseMetaData) dbJDBCMetadata).getSchemas(catalog.getName(), null); } else if (dbJDBCMetadata instanceof AS400DatabaseMetaData) { String schemaPattern = null; if (!schemaFilter.isEmpty()) { schemaPattern = schemaFilter.get(0); } schemaRs = dbJDBCMetadata.getSchemas(catalog.getName(), schemaPattern); } else { schemaRs = dbJDBCMetadata.getSchemas(); } } catch (SQLException e) { if (log.isDebugEnabled()) { log.debug(e, e); } } } List<String> schemaNameCacheTmp = new ArrayList<String>(); List<Schema> schemaList = new ArrayList<Schema>(); if (schemaRs == null) { log.error("Schema result set is null"); //$NON-NLS-1$ } else { try { while (schemaRs.next()) { String schemaName = getSchemaName(schemaRs, dbJDBCMetadata, catalog); if (schemaName == null) { continue; } // MOD mzhao bug 9606 filter duplicated schemas. if (!schemaNameCacheTmp.contains(schemaName) && !MetadataConnectionUtils.isMysql(dbJDBCMetadata)) { if (dbConn != null && !isNullUiSchema(dbConn)) { // this case we only create one schema which name is same as UiSchema Schema createByUiSchema = createSchemaByUiSchema(dbConn); schemaList.add(createByUiSchema); break; } else if (isCreateElement(schemaFilter, schemaName)) { Schema schema = SchemaHelper.createSchema(schemaName); schemaList.add(schema); schemaNameCacheTmp.add(schemaName); } } } } catch (Exception e) { if (log.isDebugEnabled()) { log.debug(e, e); } } finally { schemaRs.close(); } } return schemaList; }