Java Code Examples for java.sql.DatabaseMetaData#getSchemas()

/**
 * Checks if the default schema JBATCH or the schema defined in batch-config exists.
 * 
 * @return true if the schema exists, false otherwise.
 * @throws SQLException
 */
private boolean isSchemaValid() throws SQLException {
	logger.entering(CLASSNAME, "isSchemaValid");
	Connection conn = getConnectionToDefaultSchema();
	DatabaseMetaData dbmd = conn.getMetaData();
	ResultSet rs = dbmd.getSchemas();
	while(rs.next()) {
		if (schema.equalsIgnoreCase(rs.getString("TABLE_SCHEM")) ) {
			cleanupConnection(conn, rs, null);
			logger.exiting(CLASSNAME, "isSchemaValid", true);
			return true;
		}
	}
	cleanupConnection(conn, rs, null);
	logger.exiting(CLASSNAME, "isSchemaValid", false);
	return false;
}
 

@Test
public void testGetSchemasCorrect() throws Exception{
    String schemaName = "TEST_SCHEMA123456";
    TestConnection conn=methodWatcher.getOrCreateConnection();
    conn.setAutoCommit(false);
    try{
        try(Statement s=conn.createStatement()){
            s.execute("create schema "+schemaName);
            conn.setSchema(schemaName);
            s.execute("create table t (a int, b int)");
        }

        DatabaseMetaData dmd=conn.getMetaData();
        try(ResultSet rs=dmd.getSchemas(null,schemaName)){
            Assert.assertTrue("Did not find sys schema!",rs.next());
            String tableSchem=rs.getString("TABLE_SCHEM");
            Assert.assertEquals("Incorrect table schema!",schemaName,tableSchem);
            Assert.assertNull("Incorrect catalog!",rs.getString("TABLE_CATALOG"));
            Assert.assertFalse("Found more than one schema for specified schema value!",rs.next());
        }
    }finally{
        conn.rollback();
    }
}
 

@Override
public List<SchemaEntry> getSchemaEntries(Connection aConnection)
		throws SQLException {

	List<SchemaEntry> theList = new ArrayList<>();

	DatabaseMetaData theMetadata = aConnection.getMetaData();
	ResultSet theResult = theMetadata.getSchemas();

	while (theResult.next()) {
		String theSchemaName = theResult.getString("TABLE_SCHEM");
		String theCatalogName = null;

		theList.add(new SchemaEntry(theCatalogName, theSchemaName));
	}

	return theList;
}
 

@Override
public List<SchemaEntry> getSchemaEntries(Connection aConnection) throws SQLException {

    List<SchemaEntry> theList = new ArrayList<>();

    DatabaseMetaData theMetadata = aConnection.getMetaData();
    ResultSet theResult = theMetadata.getSchemas();

    while (theResult.next()) {
        String theSchemaName = theResult.getString("TABLE_SCHEM");
        String theCatalogName = null;

        theList.add(new SchemaEntry(theCatalogName, theSchemaName));
    }

    return theList;
}
 

@Override
public List<SchemaEntry> getSchemaEntries(Connection aConnection) throws SQLException {
	List<SchemaEntry> theList = new ArrayList<>();

	DatabaseMetaData theMetadata = aConnection.getMetaData();
	ResultSet theResult = theMetadata.getSchemas();

	while (theResult.next()) {
		String theSchemaName = theResult.getString("TABLE_SCHEM");
		String theCatalogName = null;

		theList.add(new SchemaEntry(theCatalogName, theSchemaName));
	}

	return theList;
}
 

private static void checkConn( Connection conn, String label)
{
    try
    {
        DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet rs = dbmd.getSchemas();
        while( rs.next());
        rs.close();
    }
    catch( SQLException sqle)
    {
        passed = false;
        System.out.println( "Could not use the " + label + " connection:");
        System.out.println( "  " + sqle.getMessage());
    }
}
 

public static String[] getSchemas(Connection connection) throws Exception {
    ResultSet schemas = null;
    try {
        if (connection != null) {
            List<String> schemaList = new ArrayList<String>();
            DatabaseMetaData mObject = connection.getMetaData();
            schemas = mObject.getSchemas();
            while (schemas.next()) {
                schemaList.add(schemas.getString(DBConstants.DataServiceGenerator.TABLE_SCHEM));
            }
            String str[] = schemaList.toArray(new String[schemaList.size()]);
            return str;
        } else {
            return null;
        }
    } finally {
        if (schemas != null) {
            schemas.close();
        }
    }
}
 

/**
 * Test getSchemas() without modifying the database.
 *
 * @throws SQLException
 */
public void testGetSchemasReadOnly() throws SQLException {

    DatabaseMetaData dmd = getDMD();

    ResultSet rs = dmd.getSchemas();
    checkSchemas(rs, new String[0]);
}
 

@Test public void testGetSchemas() throws SQLException {
  DatabaseMetaData metadata = getDatabaseMetadata();
  try (ResultSet rs = metadata.getSchemas(null, null)) {
    ResultSetMetaData rsMeta = rs.getMetaData();

    assertEquals(2, rsMeta.getColumnCount());
    assertColumn(rsMeta, 1, "TABLE_SCHEM", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
    assertColumn(rsMeta, 2, "TABLE_CATALOG", Types.VARCHAR, DatabaseMetaData.columnNullable);
  }
}
 

/**
 * Basic test of DatabaseMetaData functions that access system tables
 */
public void testTwo() throws Exception {

    Connection conn = newConnection();
    int        updateCount;

    try {
        TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt");

        DatabaseMetaData dbmeta = conn.getMetaData();

        dbmeta.allProceduresAreCallable();
        dbmeta.getBestRowIdentifier(null, null, "T_1",
                                    DatabaseMetaData.bestRowTransaction,
                                    true);
        dbmeta.getCatalogs();
        dbmeta.getColumnPrivileges(null, "PUBLIC", "T_1", "%");
        dbmeta.getColumns("PUBLIC", "PUBLIC", "T_1", "%");
        dbmeta.getCrossReference(null, null, "T_1", null, null, "T_1");
        dbmeta.getExportedKeys(null, null, "T_1");
        dbmeta.getFunctionColumns(null, "%", "%", "%");
        dbmeta.getFunctions(null, "%", "%");
        dbmeta.getImportedKeys("PUBLIC", "PUBLIC", "T_1");
        dbmeta.getIndexInfo("PUBLIC", "PUBLIC", "T1", true, true);
        dbmeta.getPrimaryKeys("PUBLIC", "PUBLIC", "T_1");
        dbmeta.getProcedureColumns(null, null, "%", "%");
        dbmeta.getProcedures("PUBLIC", "%", "%");
        dbmeta.getSchemas(null, "#");
        dbmeta.getTablePrivileges(null, "%", "%");
        dbmeta.getUDTs(null, "%", "%", new int[]{ Types.DISTINCT });
    } catch (Exception e) {
        assertTrue("unable to prepare or execute DDL", false);
    } finally {
        conn.close();
    }
}
 

private String performQuerySchema( final Connection conn ) {
  String schema = null;
  try {
    final DatabaseMetaData data = conn.getMetaData();
    final boolean isHsql = ( "HSQL Database Engine".equals( data.getDatabaseProductName() ) );
    if ( data.supportsSchemasInTableDefinitions() ) {
      final LinkedMap schemas = new LinkedMap();
      final ResultSet rs = data.getSchemas();
      while ( rs.next() ) {
        final String schemaName = rs.getString( 1 ).trim();
        if ( isHsql && "INFORMATION_SCHEMA".equals( schemaName ) ) {
          continue;
        }

        schemas.put( schemaName, Boolean.TRUE );
      }
      rs.close();

      // bring up schema selection dialog only if preferences is set
      final String[] schemasArray = (String[]) schemas.keys( new String[schemas.size()] );
      if ( schemas.size() > 1 ) {
        final Preferences properties = Preferences.userRoot().node( "org/pentaho/reporting/ui/datasources/jdbc/Settings" ); // NON-NLS
        if ( properties.getBoolean( "show-schema-dialog", false ) ) {
          final SchemaSelectionDialog schemaSelectionDialog = new SchemaSelectionDialog( JdbcDataSourceDialog.this, schemasArray );
          schema = schemaSelectionDialog.getSchema();
        }
      } else if ( schemas.size() == 1 ) {
        // Usually PUBLIC schema
        schema = schemasArray[0];
      }
    }
  } catch ( Exception ex ) {
    logger.warn( "Error on InvokeQueryDesignerAction.performQuerySchema()", ex );
  }
  return schema;
}
 

/**
 * Test getSchemas().
 *
 * @throws SQLException
 */
public void testGetSchemasModify() throws SQLException {
    createSchemasForTests();
    DatabaseMetaData dmd = getDMD();
    ResultSet rs = dmd.getSchemas();
    checkSchemas(rs, IDS);
}
 

/**
 * Checks if the default schema JBATCH or the schema defined in batch-config exists.
 *
 * @return true if the schema exists, false otherwise.
 * @throws SQLException
 */
private boolean isSchemaValid() throws SQLException {
    final Connection conn = getConnectionToDefaultSchema();
    final DatabaseMetaData dbmd = conn.getMetaData();
    final ResultSet rs = dbmd.getSchemas();
    while (rs.next()) {
        if (schema.equalsIgnoreCase(rs.getString("TABLE_SCHEM"))) {
            cleanupConnection(conn, rs, null);
            return true;
        }
    }
    cleanupConnection(conn, rs, null);
    return false;
}
 

@Test
public void testSchemaMetadataScan() throws SQLException {
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(), CUSTOM_ENTITY_DATA_FULL_NAME, null, ts);
    ensureTableCreated(getUrl(), PTSDB_NAME, null, ts);
    Properties props = new Properties();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet rs;
    rs = dbmd.getSchemas(null, CUSTOM_ENTITY_DATA_SCHEMA_NAME);
    assertTrue(rs.next());
    assertEquals(rs.getString("TABLE_SCHEM"),CUSTOM_ENTITY_DATA_SCHEMA_NAME);
    assertEquals(rs.getString("TABLE_CATALOG"),null);
    assertFalse(rs.next());

    rs = dbmd.getSchemas(null, null);
    assertTrue(rs.next());
    assertEquals(rs.getString("TABLE_SCHEM"),null);
    assertEquals(rs.getString("TABLE_CATALOG"),null);
    assertTrue(rs.next());
    assertEquals(rs.getString("TABLE_SCHEM"),CUSTOM_ENTITY_DATA_SCHEMA_NAME);
    assertEquals(rs.getString("TABLE_CATALOG"),null);
    assertTrue(rs.next());
    assertEquals(rs.getString("TABLE_SCHEM"),PhoenixDatabaseMetaData.TYPE_SCHEMA);
    assertEquals(rs.getString("TABLE_CATALOG"),null);
    assertFalse(rs.next());
}
 

@Override
public List<String> listDatabases() throws SQLException {
    List<String> ret = new ArrayList<>();
    try (Connection con = getConnection()) {
        DatabaseMetaData metaData = con.getMetaData();
        ResultSet schemaList = metaData.getSchemas();
        while (schemaList.next()) {
            String schmea = schemaList.getString("TABLE_SCHEM");
            ret.add(schmea);
        }
    }
    return ret;
}
 

void showSchemas(DatabaseMetaData dbMetaData) throws SQLException {

        ResultSet schemasRs = dbMetaData.getSchemas();
        while (schemasRs.next()) {
            String schema = schemasRs.getString("TABLE_SCHEM");
            String catalog = schemasRs.getString("TABLE_CATALOG");
            System.out.println(catalog + " " + schema);
        }

        String schemaTerm = dbMetaData.getSchemaTerm();
        System.out.println("schema term: " + schemaTerm);

        int maxSchemaNameLength = dbMetaData.getMaxSchemaNameLength();
        System.out.println("maxSchemaNameLength: " + maxSchemaNameLength);
    }
 

/**
 * {@inheritDoc}
 */
@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);
    }
}
 

@Test
public void testGetObjectsDoesNotExists() throws Throwable
{
  try (Connection connection = getConnection())
  {
    String database = connection.getCatalog();
    String schema = connection.getSchema();
    final String targetTable = "T0";
    final String targetView = "V0";

    connection.createStatement().execute("create or replace table " + targetTable + "(C1 int)");
    connection.createStatement().execute("create or replace view " + targetView + " as select 1 as C");

    DatabaseMetaData metaData = connection.getMetaData();

    // sanity check if getTables really works.
    ResultSet resultSet = metaData.getTables(
        database, schema, "%", null);
    assertTrue(getSizeOfResultSet(resultSet) > 0);

    // invalid object type. empty result is expected.
    resultSet = metaData.getTables(
        database, schema, "%", new String[]{"INVALID_TYPE"}
    );
    assertEquals(0, getSizeOfResultSet(resultSet));

    // rest of the cases should return empty results.
    resultSet = metaData.getSchemas(
        "DB_NOT_EXIST", "SCHEMA_NOT_EXIST");
    assertFalse(resultSet.next());
    assertTrue(resultSet.isClosed());


    resultSet = metaData.getTables(
        "DB_NOT_EXIST", "SCHEMA_NOT_EXIST", "%", null);
    assertFalse(resultSet.next());

    resultSet = metaData.getTables(
        database, "SCHEMA\\_NOT\\_EXIST", "%", null);
    assertFalse(resultSet.next());

    resultSet = metaData.getColumns(
        "DB_NOT_EXIST", "SCHEMA_NOT_EXIST", "%", "%");
    assertFalse(resultSet.next());

    resultSet = metaData.getColumns(
        database, "SCHEMA\\_NOT\\_EXIST", "%", "%");
    assertFalse(resultSet.next());

    resultSet = metaData.getColumns(
        database, schema, "TBL\\_NOT\\_EXIST", "%");
    assertFalse(resultSet.next());
    connection.createStatement().execute("drop table if exists " + targetTable);
    connection.createStatement().execute("drop view if exists " + targetView);
  }
}
 

private cfData typeDbnames(cfSession _session, cfDataSource datasource) throws cfmRunTimeException{
	Connection c = null;
	try {
		c = datasource.getPooledConnection();
		
		DatabaseMetaData metaData = c.getMetaData();
		
		cfQueryResultData queryResult = new cfQueryResultData(new String[] { "database_name", "type" }, "DBINFO");
		
		cfStringData	catalogSD = new cfStringData("catalog");
		cfStringData	schemaSD = new cfStringData("schema");
		
		ResultSet rset = metaData.getCatalogs();
		int row=1;
		while ( rset.next() ){
			queryResult.addRow(1);
			queryResult.setCell(row, 1, new cfStringData(rset.getString(1)) );
			queryResult.setCell(row, 2, catalogSD );
			row++;
		}
		rset.close();
		
		
		rset = metaData.getSchemas();
		while ( rset.next() ){
			queryResult.addRow(1);
			queryResult.setCell(row, 1, new cfStringData(rset.getString(1)) );
			queryResult.setCell(row, 2, schemaSD );
			row++;
		}
		rset.close();
		
		return queryResult;
		
	} catch (SQLException e) {
		throwException(_session,  e.getMessage() );
	} finally {
		datasource.close(c);
	}
	
	return null;
}
 

@Test
public void testUseConnectionCtx() throws SQLException
{
  try (Connection connection = getConnection())
  {
    connection.createStatement().execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true");
    String schema = connection.getSchema();
    DatabaseMetaData databaseMetaData = connection.getMetaData();

    // create tables within current schema.
    connection.createStatement().execute("create or replace schema TEST_CTX");
    connection.createStatement().execute("create or replace table CTX_TBL_A (colA string, colB decimal, " +
                                         "colC number PRIMARY KEY);");
    connection.createStatement().execute("create or replace table CTX_TBL_B (colA string, colB decimal, " +
                                         "colC number FOREIGN KEY REFERENCES CTX_TBL_A (colC));");
    connection.createStatement().execute("create or replace table CTX_TBL_C (colA string, colB decimal, " +
                                         "colC number, colD int, colE timestamp, colF string, colG number);");
    // now create more tables under current schema
    connection.createStatement().execute("use schema " + schema);
    connection.createStatement().execute("create or replace table CTX_TBL_D (colA string, colB decimal, " +
                                         "colC number PRIMARY KEY);");
    connection.createStatement().execute("create or replace table CTX_TBL_E (colA string, colB decimal, " +
                                         "colC number FOREIGN KEY REFERENCES CTX_TBL_D (colC));");
    connection.createStatement().execute("create or replace table CTX_TBL_F (colA string, colB decimal, " +
                                         "colC number, colD int, colE timestamp, colF string, colG number);");

    // this should only return TEST_CTX schema and tables
    connection.createStatement().execute("use schema TEST_CTX");

    ResultSet resultSet = databaseMetaData.getSchemas(null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getTables(null, null, null, null);
    assertEquals(3, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getColumns(null, null, null, null);
    assertEquals(13, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getImportedKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getExportedKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getCrossReference(null, null, null, null,
                                                   null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    // Now compare results to setting client metadata to false.
    connection.createStatement().execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=false");
    databaseMetaData = connection.getMetaData();

    resultSet = databaseMetaData.getSchemas(null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getTables(null, null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(6));

    resultSet = databaseMetaData.getColumns(null, null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(26));

    resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getImportedKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getExportedKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getCrossReference(null, null, null, null,
                                                   null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

  }
}