Example usage for java.sql DatabaseMetaData getTables

List of usage examples for java.sql DatabaseMetaData getTables

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. getTables

Introduction

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

Prototype

ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
        throws SQLException;

Source Link

Document

Retrieves a description of the tables available in the given catalog.

Usage

From source file:org.batoo.jpa.core.jdbc.adapter.JdbcAdaptor.java

private JdbcTable getTableMetadata(DataSourceImpl datasource, String catalog, String schema, String table)
        throws SQLException {
    final ConnectionImpl connection = datasource.getConnection();

    ResultSet tables = null;/*from w w  w  . j  a  v  a2s  . co m*/
    try {
        final DatabaseMetaData dbMetadata = connection.getMetaData();
        if (StringUtils.isBlank(catalog)) {
            catalog = null;
        }
        if (StringUtils.isBlank(schema)) {
            schema = null;
        }

        if (dbMetadata.storesUpperCaseIdentifiers()) {
            tables = dbMetadata.getTables(//
                    BatooUtils.upper(catalog), //
                    BatooUtils.upper(schema), //
                    BatooUtils.upper(table), //
                    JdbcAdaptor.TABLE_OR_VIEW);
        } else if (dbMetadata.storesLowerCaseIdentifiers()) {
            tables = dbMetadata.getTables(//
                    BatooUtils.lower(catalog), //
                    BatooUtils.lower(schema), //
                    BatooUtils.lower(table), //
                    JdbcAdaptor.TABLE_OR_VIEW);
        } else {
            tables = dbMetadata.getTables(catalog, schema, table, JdbcAdaptor.TABLE_OR_VIEW);
        }

        if (tables.next()) {
            final String tableName = tables.getString(JdbcAdaptor.TABLE_NAME);
            if (table.equalsIgnoreCase(tableName)) {
                return new JdbcTable(dbMetadata, tables);
            }
        }
    } finally {
        DbUtils.closeQuietly(connection);
        DbUtils.closeQuietly(tables);
    }

    return null;
}

From source file:com.jaxio.celerio.configuration.database.support.MetadataExtractor.java

private void loadTables(JdbcConnectivity configuration, DatabaseMetaData databaseMetaData, Metadata metaData)
        throws SQLException {

    Set<String> tableNamePatterns = new HashSet<String>();

    if (configuration.getTableNamePatterns() == null || configuration.getTableNamePatterns().isEmpty()) {
        log.info("No table name pattern defined, using '%'");
        tableNamePatterns.add("%");
    } else {/*from w ww  . j  a va2  s . c o m*/
        log.info("Custom table pattern defined");
        tableNamePatterns.addAll(configuration.getTableNamePatterns());
    }

    log.info("Working with " + tableNamePatterns.size() + " table name pattern(s)");

    for (String tableNamePattern : tableNamePatterns) {

        if (log.isInfoEnabled()) {
            log.info("Loading with catalog=[" + configuration.getCatalog() + "] schemaPattern=["
                    + configuration.getSchemaName() + "] tableNamePattern=[" + tableNamePattern + "] types=["
                    + Joiner.on(",").join(configuration.getTableTypes()) + "]");
        }
        ResultSet resultSet = databaseMetaData.getTables(configuration.getCatalog(),
                configuration.getSchemaName(), tableNamePattern,
                getTableTypesAsStringArray(configuration.getTableTypes()));
        ResultSetWrapper rsw = new ResultSetTables(resultSet, useLabel);

        while (resultSet.next()) {
            Table table = new Table();

            table.setName(getString(rsw, "TABLE_NAME"));
            table.setType(TableType.valueOf(getString(rsw, "TABLE_TYPE")));
            String remarks = getString(rsw, "REMARKS");
            if (notEmpty(remarks)) {
                table.setRemarks(remarks);
            }

            if (!skipTable(table)) {
                if (log.isInfoEnabled()) {
                    log.info("Table reversed: " + table.getName());
                }
                metaData.add(table);
            } else {
                if (log.isInfoEnabled()) {
                    log.info("Table ignored : " + table.getName());
                }
            }
        }
    }
}

From source file:org.nuclos.server.dblayer.impl.standard.StandardSqlDBAccess.java

protected boolean existsTableOrView(final String tableOrView) {
    try {/*from   www  .j av a 2  s  .  com*/
        return executor.execute(new ConnectionRunner<Boolean>() {
            @Override
            public Boolean perform(Connection conn) throws SQLException {
                final DatabaseMetaData meta = conn.getMetaData();
                // we NEED  toLowerCase() here, at least for PostgreSQL (tp)
                final ResultSet columns = meta.getTables(catalog, schema, tableOrView.toLowerCase(), null);
                return Boolean.valueOf(columns.next());
            }
        });
    } catch (SQLException e) {
        LOG.error("existsTableOrView failed with " + e.toString());
        throw wrapSQLException(null, "existsTableOrView failed", e);
    }
}

From source file:uk.ac.ebi.bioinvindex.utils.test.DBUnitTest.java

/**
 * A facility to clean all the tables in the database, independently on the fact they appear or not in the dataset.
 *
 * TODO: Not completely sure it works with Oracle... :-\
 *
 * @throws SQLException/*from  w  w  w. j av  a 2 s .  co  m*/
 */
protected void cleanAll() throws SQLException {
    setReferentialIntegrityCheckings(false);

    try {
        Statement delstmt = connection.createStatement();
        DatabaseMetaData dbmsMeta = connection.getMetaData();
        String dbmsName = dbmsMeta.getDatabaseProductName().toLowerCase();
        String dbmsCatalog = connection.getCatalog();
        if (dbmsCatalog == null)
            // Let's try with the user name
            dbmsCatalog = dbmsMeta.getUserName().toUpperCase();

        String dbmsSchema = null;
        if (dbmsName.contains("oracle")) {
            // The damn Oracle needs the schema in getTables(), otherwise it returns undeletable
            // system tables too.
            //
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt
                    .executeQuery("select sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual");
            if (rs.next())
                dbmsSchema = rs.getString(1);
            stmt.close();
        }

        log.debug("DBMSUnitTest.cleanAll(), DBMS Name: '" + dbmsName + "' Catalog: '" + dbmsCatalog
                + "' Schema: '" + dbmsSchema + "'");

        ResultSet tbrs = dbmsMeta.getTables(dbmsCatalog, dbmsSchema, null, new String[] { "TABLE" });

        while (tbrs.next()) {
            String tbname = StringUtils.trimToNull(tbrs.getString("TABLE_NAME"));
            if (tbname == null)
                continue;
            // Oracle system tables
            String sql = "DROP TABLE " + tbname;
            if (!dbmsName.contains("mysql"))
                sql += " CASCADE CONSTRAINTS";
            log.debug("DBUnitTest, adding sql: " + sql);
            delstmt.addBatch(sql);
        }
        delstmt.executeBatch();
    } finally {
        setReferentialIntegrityCheckings(true);
        connection.close();
        // All tables were deleted, we need this to force schema recreation.
        initEntityManager(true);
    }
}

From source file:org.apache.hive.jdbc.TestJdbcWithMiniHS2.java

@Test
public void testTempTable() throws Exception {
    // Create temp table with current connection
    String tempTableName = "tmp1";
    Statement stmt = conTestDb.createStatement();
    stmt.execute("CREATE TEMPORARY TABLE " + tempTableName + " (key string, value string)");
    stmt.execute("load data local inpath '" + kvDataFilePath.toString() + "' into table " + tempTableName);

    String resultVal = "val_238";
    String queryStr = "SELECT * FROM " + tempTableName + " where value = '" + resultVal + "'";

    ResultSet res = stmt.executeQuery(queryStr);
    assertTrue(res.next());/*  w w  w.  java2 s.  c  o m*/
    assertEquals(resultVal, res.getString(2));
    res.close();
    stmt.close();

    // Test getTables()
    DatabaseMetaData md = conTestDb.getMetaData();
    assertTrue(md.getConnection() == conTestDb);

    ResultSet rs = md.getTables(null, null, tempTableName, null);
    boolean foundTable = false;
    while (rs.next()) {
        String tableName = rs.getString(3);
        if (tableName.equalsIgnoreCase(tempTableName)) {
            assertFalse("Table not found yet", foundTable);
            foundTable = true;
        }
    }
    assertTrue("Found temp table", foundTable);

    // Test getTables() with no table name pattern
    rs = md.getTables(null, null, null, null);
    foundTable = false;
    while (rs.next()) {
        String tableName = rs.getString(3);
        if (tableName.equalsIgnoreCase(tempTableName)) {
            assertFalse("Table not found yet", foundTable);
            foundTable = true;
        }
    }
    assertTrue("Found temp table", foundTable);

    // Test getColumns()
    rs = md.getColumns(null, null, tempTableName, null);
    assertTrue("First row", rs.next());
    assertTrue(rs.getString(3).equalsIgnoreCase(tempTableName));
    assertTrue(rs.getString(4).equalsIgnoreCase("key"));
    assertEquals(Types.VARCHAR, rs.getInt(5));

    assertTrue("Second row", rs.next());
    assertTrue(rs.getString(3).equalsIgnoreCase(tempTableName));
    assertTrue(rs.getString(4).equalsIgnoreCase("value"));
    assertEquals(Types.VARCHAR, rs.getInt(5));

    // A second connection should not be able to see the table
    Connection conn2 = DriverManager.getConnection(miniHS2.getJdbcURL(testDbName),
            System.getProperty("user.name"), "bar");
    Statement stmt2 = conn2.createStatement();
    stmt2.execute("USE " + testDbName);
    boolean gotException = false;
    try {
        res = stmt2.executeQuery(queryStr);
    } catch (SQLException err) {
        // This is expected to fail.
        assertTrue("Expecting table not found error, instead got: " + err,
                err.getMessage().contains("Table not found"));
        gotException = true;
    }
    assertTrue("Exception while querying non-existing temp table", gotException);
    conn2.close();
}

From source file:it.eng.spagobi.meta.initializer.PhysicalModelInitializer.java

/**
 * Get columns names that are present in the database but not in the passed physical model
 * /*from w w  w .  j av a  2 s .  com*/
 * @param connection
 *            jdbc connection to the database
 * @param physicalModel
 *            physical model to check
 */
public List<String> getMissingColumnsNames(Connection connection, PhysicalModel physicalModel) {
    try {
        DatabaseMetaData dbMeta = connection.getMetaData();

        List<String> tablesOnDatabase = new ArrayList<String>();
        List<String> newColumnsNames = new ArrayList<String>();
        ResultSet tableRs = dbMeta.getTables(physicalModel.getCatalog(), physicalModel.getSchema(), null,
                new String[] { "TABLE", "VIEW" });

        while (tableRs.next()) {
            String tableName = tableRs.getString("TABLE_NAME");
            tablesOnDatabase.add(tableName);
        }
        tableRs.close();

        EList<PhysicalTable> originalTables = physicalModel.getTables();
        Iterator<String> tablesIterator = tablesOnDatabase.iterator();

        // iterate for each table
        while (tablesIterator.hasNext()) {
            String tableName = tablesIterator.next();
            PhysicalTable physicalTable = findTable(tableName, originalTables);
            if (physicalTable != null) {
                ResultSet rs = dbMeta.getColumns(physicalModel.getCatalog(), physicalModel.getSchema(),
                        physicalTable.getName(), null);
                while (rs.next()) {
                    String columnName = rs.getString("COLUMN_NAME");
                    // check if the column exists in the physicalModel
                    PhysicalColumn physicalColumn = findColumn(columnName, physicalTable.getColumns());
                    if (physicalColumn == null) {
                        // new column on database
                        newColumnsNames.add(tableName + "." + columnName);
                    }
                }

            }
        }
        return newColumnsNames;

    } catch (SQLException e) {
        throw new RuntimeException("Physical Model - Impossible to get missing tables names", e);

    }
}

From source file:org.jumpmind.db.platform.AbstractJdbcDdlReader.java

public List<String> getTableNames(final String catalog, final String schema, final String[] tableTypes) {
    JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate();
    return sqlTemplate.execute(new IConnectionCallback<List<String>>() {
        public List<String> execute(Connection connection) throws SQLException {
            ArrayList<String> list = new ArrayList<String>();
            DatabaseMetaData meta = connection.getMetaData();
            ResultSet rs = null;/*from w w w  . j  a va  2s . c om*/
            try {
                rs = meta.getTables(catalog, schema, null, tableTypes);
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    list.add(tableName);
                }
                return list;
            } finally {
                close(rs);
            }
        }
    });
}

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();//w w  w .j  a v  a 2 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.executequery.databasemediators.spi.DefaultStatementExecutor.java

private SqlStatementResult showTables() throws SQLException {

    DatabaseHost host = null;/*from ww  w.j a  v a 2 s .c  om*/
    try {

        host = new DatabaseObjectFactoryImpl().createDatabaseHost(databaseConnection);

        DatabaseSource defaultCatalog = host.getDefaultCatalog();
        DatabaseSource defaultSchema = host.getDefaultSchema();

        String catalog = null;
        String schema = null;

        if (defaultCatalog != null) {

            catalog = defaultCatalog.getName();
        }

        if (defaultSchema != null) {

            schema = defaultSchema.getName();
        }

        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet resultSet = metaData.getTables(catalog, schema, null, new String[] { "TABLE" });
        statementResult.setResultSet(resultSet);

    } catch (SQLException e) {

        statementResult.setSqlException(e);
        finished();

    } finally {

        if (host != null) {

            host.close();
        }
    }

    return statementResult;
}

From source file:org.executequery.databaseobjects.impl.DefaultDatabaseHost.java

/**
 * Returns the table names hosted by this host of the specified type and
 * belonging to the specified catalog and schema.
 *
 * @param catalog the table catalog name
 * @param schema the table schema name/*from  www. ja  v  a 2 s .co m*/
 * @param type the table type
 * @return the hosted tables
 */
public List<String> getTableNames(String catalog, String schema, String type) throws DataSourceException {

    ResultSet rs = null;
    try {
        String _catalog = getCatalogNameForQueries(catalog);
        String _schema = getSchemaNameForQueries(schema);
        DatabaseMetaData dmd = getDatabaseMetaData();

        String typeName = null;
        List<String> tables = new ArrayList<String>();

        String[] types = null;
        if (type != null) {

            types = new String[] { type };
        }

        rs = dmd.getTables(_catalog, _schema, null, types);
        while (rs.next()) {

            typeName = rs.getString(4);

            // only include if the returned reported type matches
            if (type != null && type.equalsIgnoreCase(typeName)) {

                tables.add(rs.getString(3));
            }

        }

        return tables;

    } catch (SQLException e) {

        if (Log.isDebugEnabled()) {

            Log.error("Tables not available for type " + type + " - driver returned: " + e.getMessage());
        }

        return new ArrayList<String>(0);

    } finally {

        releaseResources(rs);
    }

}