Example usage for java.sql DatabaseMetaData getSchemas

List of usage examples for java.sql DatabaseMetaData getSchemas

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getSchemas.

Prototype

ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException;

Source Link

Document

Retrieves the schema names available in this database.

Usage

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;
}