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.apache.jackrabbit.core.persistence.db.OraclePersistenceManager.java

/**
 * {@inheritDoc}/*from   w w  w . ja  va 2s  .c o m*/
 * <p/>
 * Overridden in order to support multiple oracle schemas. Note that
 * schema names in Oracle correspond to the username of the connection.
 * See http://issues.apache.org/jira/browse/JCR-582
 *
 * @throws Exception if an error occurs
 */
protected void checkSchema() throws Exception {
    DatabaseMetaData metaData = con.getMetaData();
    String tableName = schemaObjectPrefix + "NODE";
    if (metaData.storesLowerCaseIdentifiers()) {
        tableName = tableName.toLowerCase();
    } else if (metaData.storesUpperCaseIdentifiers()) {
        tableName = tableName.toUpperCase();
    }
    String userName = metaData.getUserName();

    ResultSet rs = metaData.getTables(null, userName, tableName, null);
    boolean schemaExists;
    try {
        schemaExists = rs.next();
    } finally {
        rs.close();
    }

    if (!schemaExists) {
        // read ddl from resources
        InputStream in = getSchemaDDL();
        if (in == null) {
            String msg = "Configuration error: unknown schema '" + schema + "'";
            log.debug(msg);
            throw new RepositoryException(msg);
        }
        BufferedReader reader = new BufferedReader(new InputStreamReader(in));
        Statement stmt = con.createStatement();
        try {
            String sql = reader.readLine();
            while (sql != null) {
                // Skip comments and empty lines
                if (!sql.startsWith("#") && sql.length() > 0) {
                    // replace prefix variable
                    sql = Text.replace(sql, SCHEMA_OBJECT_PREFIX_VARIABLE, schemaObjectPrefix);

                    // set the tablespace if it is defined
                    String tspace;
                    if (tableSpace == null || "".equals(tableSpace)) {
                        tspace = "";
                    } else {
                        tspace = "tablespace " + tableSpace;
                    }
                    sql = Text.replace(sql, TABLE_SPACE_VARIABLE, tspace).trim();

                    // execute sql stmt
                    stmt.executeUpdate(sql);
                }
                // read next sql stmt
                sql = reader.readLine();
            }
            // commit the changes
            con.commit();
        } finally {
            IOUtils.closeQuietly(in);
            closeStatement(stmt);
        }
    }
}

From source file:com.ikon.servlet.admin.DatabaseQueryServlet.java

/**
 * List tables from database//w  w w.  j  av a  2 s  .c  o  m
 */
private List<String> listTables(Session session) {
    final List<String> tables = new ArrayList<String>();
    final String[] tableTypes = { "TABLE" };
    final String[] tablePatterns = new String[] { "JBPM_%", "OKM_%", "DEFAULT_%", "VERSION_%", "jbpm_%",
            "okm_%", "default_%", "version_%" };

    session.doWork(new Work() {
        @Override
        public void execute(Connection con) throws SQLException {
            DatabaseMetaData md = con.getMetaData();

            for (String table : tablePatterns) {
                ResultSet rs = md.getTables(null, null, table, tableTypes);

                while (rs.next()) {
                    tables.add(rs.getString(3));
                }

                rs.close();
            }
        }
    });

    return tables;
}

From source file:org.easyrec.store.dao.web.impl.LoaderDAOMysqlImpl.java

@Override
public void testConnection(String url, String username, String password) throws Exception {

    HikariConfig config = new HikariConfig();
    config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
    config.setJdbcUrl(url);/*  w  ww .  j  av  a2  s  . co  m*/
    config.setUsername(username);
    config.setPassword(password);
    config.setPoolName("easyrecPool");
    config.addDataSourceProperty("url", url);
    HikariDataSource ds = new HikariDataSource(config);

    setDataSource(ds);
    sqlScriptService.setDataSource(ds);

    boolean tablesOk = false;

    DatabaseMetaDataCallback callback = new DatabaseMetaDataCallback() {
        public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException {
            ResultSet rs = dbmd.getTables(null, null, "operator", null);
            return rs.next();
        }
    };

    tablesOk = (Boolean) JdbcUtils.extractDatabaseMetaData(ds, callback);
}

From source file:org.easyrec.store.dao.web.impl.LoaderDAOMysqlImpl.java

/**
 * This function returns the current version of easyrec,
 * depending on the presence of a version table. If
 * no version table is present return the inital version
 *
 *
 *//*from   ww  w.java 2 s. c o m*/
@Override
public Float checkVersion() throws Exception {

    HikariDataSource bds = (HikariDataSource) getDataSource();
    float tableCount;

    DatabaseMetaDataCallback callback = new DatabaseMetaDataCallback() {
        public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException {
            ResultSet rs = dbmd.getTables(null, null, null, null);
            float f = 0;
            while (rs.next()) {
                f++;
            }
            return f;
        }
    };

    tableCount = (Float) JdbcUtils.extractDatabaseMetaData(bds, callback);

    if (tableCount != 0) {
        try {
            return getJdbcTemplate().queryForObject("SELECT MAX(VERSION) FROM easyrec ", Float.class);
        } catch (Exception e) {
            // else return initial version 0.9
            return INITIAL_VERSION;
        }
    } else {
        return tableCount;
    }
}

From source file:com.webapp.security.SecurityDataContext.java

/**
* Initialize the context/* w  ww .  j  av  a  2s .c  om*/
*/
public void init() throws Exception {
    // Get a listing of the tables in this data store
    Set<String> tableset = new HashSet<String>();
    DatabaseMetaData md = dataSource.getConnection().getMetaData();
    ResultSet rs = md.getTables(null, null, "%", null);
    while (rs.next()) {
        tableset.add(rs.getString(3));
    }

    if (!tableset.contains("SECURITY_LOGIN")) {
        LOG.info("Creating SECURITY_LOGIN table");

        // Create the table
        String createLoginTable = "CREATE TABLE SECURITY_LOGIN ( LOGIN BIGINT NOT NULL AUTO_INCREMENT, CONTEXT VARCHAR(64) NOT NULL, NAME VARCHAR(64) NOT NULL, PASSWORD VARCHAR(64) NOT NULL, PASSWORD_HINT VARCHAR(64), IS_SYSTEM BOOLEAN, IS_ENABLED BOOLEAN, IS_LOGGEDOUT BOOLEAN, REQUIRE_PASSWORD_CHANGE BOOLEAN, CURRENCY_UOM VARCHAR(64), LOCALE VARCHAR(64), TIMEZONE VARCHAR(64), DISABLED_DATETIME TIMESTAMP, PARTY BIGINT );";
        jdbcTemplate.execute(createLoginTable);

        // Create the primary key
        String createLoginkey = "ALTER TABLE SECURITY_LOGIN ADD CONSTRAINT pk_security_login PRIMARY KEY (CONTEXT,LOGIN);";
        jdbcTemplate.execute(createLoginkey);

        // Create the primary index
        String createLoginIndex = "CREATE UNIQUE INDEX IDX_SECURITY_LOGIN ON SECURITY_LOGIN(CONTEXT,NAME);";
        jdbcTemplate.execute(createLoginIndex);
    }

    if (!tableset.contains("SECURITY_ROLE")) {
        LOG.info("Creating SECURITY_ROLE table");
        String createRoleTable = "CREATE TABLE SECURITY_ROLE ( CONTEXT VARCHAR(64) NOT NULL, ROLE VARCHAR(64) NOT NULL, DESCRIPTION VARCHAR(255) NOT NULL );";
        jdbcTemplate.execute(createRoleTable);

        String createRolekey = "ALTER TABLE SECURITY_ROLE ADD CONSTRAINT PK_SECURITY_ROLE PRIMARY KEY (CONTEXT, ROLE);";
        jdbcTemplate.execute(createRolekey);
    }

    if (!tableset.contains("SECURITY_ROLE_PERMISSION")) {
        LOG.info("Creating SECURITY_ROLE_PERMISSION table");
        String createRolePermTable = " CREATE TABLE SECURITY_ROLE_PERMISSION ( CONTEXT VARCHAR(64) NOT NULL, ROLE VARCHAR(64) NOT NULL, TARGET VARCHAR(255) NOT NULL, PERMISSION BIGINT );";
        jdbcTemplate.execute(createRolePermTable);

        String createLoginkey = "ALTER TABLE SECURITY_ROLE_PERMISSION ADD CONSTRAINT PK_SECURITY_ROLE_PERMISSION PRIMARY KEY (CONTEXT, ROLE, TARGET);";
        jdbcTemplate.execute(createLoginkey);
    }

    if (!tableset.contains("SECURITY_LOGIN_ROLE")) {
        LOG.info("Creating SECURITY_LOGIN_ROLE table");
        String createLoginRoleTable = "CREATE TABLE SECURITY_LOGIN_ROLE ( CONTEXT VARCHAR(64) NOT NULL, LOGIN BIGINT NOT NULL, ROLE VARCHAR(64) NOT NULL, FROM_DATE TIMESTAMP, THRU_DATE TIMESTAMP );";
        jdbcTemplate.execute(createLoginRoleTable);

        String createLoginRolekey = "ALTER TABLE SECURITY_LOGIN_ROLE ADD CONSTRAINT PK_SECURITY_LOGIN_ROLE PRIMARY KEY (CONTEXT, LOGIN, ROLE);";
        jdbcTemplate.execute(createLoginRolekey);
    }

    if (!tableset.contains("SECURITY_LOGIN_PERMISSION")) {
        LOG.info("Creating SECURITY_LOGIN_PERMISSION table");
        String createLoginPermTable = "CREATE TABLE SECURITY_LOGIN_PERMISSION ( CONTEXT VARCHAR(64) NOT NULL, LOGIN BIGINT NOT NULL, TARGET VARCHAR(255) NOT NULL, PERMISSION BIGINT );";
        jdbcTemplate.execute(createLoginPermTable);

        String createLoginPermKey = "ALTER TABLE SECURITY_LOGIN_PERMISSION ADD CONSTRAINT pk_security_login_permission PRIMARY KEY (CONTEXT, LOGIN, TARGET);";
        jdbcTemplate.execute(createLoginPermKey);
    }

    if (!tableset.contains("SECURITY_LOGIN_REVOCATION")) {
        LOG.info("Creating SECURITY_LOGIN_REVOCATION table");
        String createLoginRevTable = "CREATE TABLE SECURITY_LOGIN_REVOCATION ( CONTEXT VARCHAR(64) NOT NULL, LOGIN BIGINT NOT NULL, TARGET VARCHAR(255) NOT NULL, PERMISSION BIGINT );";
        jdbcTemplate.execute(createLoginRevTable);

        String createLoginRevKey = "ALTER TABLE SECURITY_LOGIN_REVOCATION ADD CONSTRAINT pk_security_login_revocation PRIMARY KEY (CONTEXT, LOGIN, TARGET);";
        jdbcTemplate.execute(createLoginRevKey);
    }

    LOG.info("Security context initialized");
}

From source file:com.cloudera.sqoop.metastore.hsqldb.HsqldbJobStorage.java

private boolean tableExists(String table) throws SQLException {
    LOG.debug("Checking for table: " + table);
    DatabaseMetaData dbmd = connection.getMetaData();
    String[] tableTypes = { "TABLE" };
    ResultSet rs = dbmd.getTables(null, null, null, tableTypes);
    if (null != rs) {
        try {/*  w  w  w .  j a va2  s  .  com*/
            while (rs.next()) {
                if (table.equalsIgnoreCase(rs.getString("TABLE_NAME"))) {
                    LOG.debug("Found table: " + table);
                    return true;
                }
            }
        } finally {
            rs.close();
        }
    }

    LOG.debug("Could not find table.");
    return false;
}

From source file:com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.java

public boolean dbInitialized() throws DbInitializationException {
    log.trace("Checking if the database is initialized");
    Connection connection = null;
    try {/*from  w  w w  . java2s.  c om*/
        connection = getDataSource().getConnection();
        DatabaseMetaData dbMetaData = connection.getMetaData();
        DbProperties dbProperties = new DbProperties(getConnectionUrl(connection));
        boolean isSchemaByUser = DbmsSpecificFactory.getDbmsFeatures().isSchemaByUser();
        String schemaName = isSchemaByUser ? dbMetaData.getUserName() : dbProperties.getCurrentSchemaProperty();
        ResultSet tables = dbMetaData.getTables(null, schemaName, null, null);
        boolean found = false;
        while (tables.next()) {
            String tableName = tables.getString("TABLE_NAME");
            if ("SYS_DB_CHANGELOG".equalsIgnoreCase(tableName)) {
                log.trace("Found SYS_DB_CHANGELOG table");
                changelogTableExists = true;
            }
            if ("SEC_USER".equalsIgnoreCase(tableName)) {
                log.trace("Found SEC_USER table");
                found = true;
            }
        }
        return found;
    } catch (SQLException e) {
        throw new DbInitializationException(true, "Error connecting to database: " + e.getMessage(), e);
    } finally {
        if (connection != null)
            try {
                connection.close();
            } catch (SQLException ignored) {
            }
    }
}

From source file:org.seasar.dbflute.logic.jdbc.metadata.basic.DfTableExtractor.java

protected List<DfTableMeta> doGetTableList(DatabaseMetaData metaData, UnifiedSchema unifiedSchema)
        throws SQLException {
    final String[] objectTypes = getRealObjectTypeTargetArray(unifiedSchema);
    final List<DfTableMeta> tableList = new ArrayList<DfTableMeta>();
    ResultSet rs = null;// w w w . j  a va  2  s  .  c  om
    try {
        _log.info("...Getting tables:");
        _log.info("  schema = " + unifiedSchema);
        _log.info("  types  = " + DfCollectionUtil.newArrayList(objectTypes));
        final String catalogName = unifiedSchema.getPureCatalog();
        final String schemaName = unifiedSchema.getPureSchema();
        rs = metaData.getTables(catalogName, schemaName, "%", objectTypes);
        while (rs.next()) {
            // /- - - - - - - - - - - - - - - - - - - - - - - - - - - -
            // basically uses getString() because
            // a JDBC driver might return an unexpected accident
            // (other methods are used only when an item can be trust)
            // - - - - - - - - - -/

            final String tableName = rs.getString("TABLE_NAME");
            final String tableType = rs.getString("TABLE_TYPE");
            final String tableCatalog;
            {
                final String plainCatalog = rs.getString("TABLE_CAT");
                if (Srl.is_NotNull_and_NotTrimmedEmpty(plainCatalog)) { // because PostgreSQL returns null
                    tableCatalog = plainCatalog;
                } else {
                    tableCatalog = catalogName;
                }
            }
            final String tableSchema = rs.getString("TABLE_SCHEM");
            final String tableComment = rs.getString("REMARKS");

            // create new original unified schema for this table
            final UnifiedSchema tableUnifiedSchema = createAsDynamicSchema(tableCatalog, tableSchema);

            if (isTableExcept(tableUnifiedSchema, tableName)) {
                _log.info(tableName + " is excepted!");
                continue;
            }
            if (isSystemTableForDBMS(tableName)) {
                _log.info(tableName + " is excepted! {system table}");
                continue;
            }

            final DfTableMeta tableMetaInfo = new DfTableMeta();
            tableMetaInfo.setTableName(tableName);
            tableMetaInfo.setTableType(tableType);
            tableMetaInfo.setUnifiedSchema(tableUnifiedSchema);
            tableMetaInfo.setTableComment(tableComment);
            tableList.add(tableMetaInfo);
        }
    } finally {
        if (rs != null) {
            rs.close();
        }
    }
    return tableList;
}

From source file:org.alfresco.util.schemacomp.ExportDb.java

private void extractSchema(DatabaseMetaData dbmd, String schemaName, String prefixFilter)
        throws SQLException, IllegalArgumentException, IllegalAccessException {
    if (log.isDebugEnabled()) {
        log.debug("Retrieving tables: schemaName=[" + schemaName + "], prefixFilter=[" + prefixFilter + "]");
    }//from   w ww  . j a  va2 s.c  o m

    final ResultSet tables = dbmd.getTables(null, schemaName, prefixFilter,
            new String[] { "TABLE", "VIEW", "SEQUENCE" });

    while (tables.next()) {
        final String tableName = tables.getString("TABLE_NAME");

        if (log.isDebugEnabled()) {
            log.debug("Examining table tableName=[" + tableName + "]");
        }

        // Oracle hack: ignore tables in the recycle bin
        // ALF-14129 fix, check whether schema already contains object with provided name
        if (tableName.startsWith("BIN$") || schema.containsByName(tableName)) {
            continue;
        }

        if (tables.getString("TABLE_TYPE").equals("SEQUENCE")) {
            Sequence sequence = new Sequence(tableName);
            schema.add(sequence);
            continue;
        }

        Table table = new Table(tableName);
        schema.add(table);

        // Table columns
        final ResultSet columns = dbmd.getColumns(null, tables.getString("TABLE_SCHEM"), tableName, "%");
        while (columns.next()) {
            String columnName = columns.getString("COLUMN_NAME");
            Column column = new Column(columnName);

            String dbType = columns.getString("TYPE_NAME");
            int colSize = columns.getInt("COLUMN_SIZE");
            int scale = columns.getInt("DECIMAL_DIGITS");
            int jdbcType = columns.getInt("DATA_TYPE");
            String type = generateType(dbType, colSize, scale, jdbcType);
            column.setType(type);

            String nullableString = columns.getString("IS_NULLABLE");
            column.setNullable(parseBoolean(nullableString));

            column.setOrder(columns.getInt("ORDINAL_POSITION"));

            try {
                String autoIncString = columns.getString("IS_AUTOINCREMENT");
                column.setAutoIncrement(parseBoolean(autoIncString));
            } catch (SQLException jtdsDoesNOtHAveIsUatoincrement) {
                column.setAutoIncrement((dbType.endsWith("identity")));
            }

            column.setParent(table);
            table.getColumns().add(column);
        }
        columns.close();

        // Primary key
        final ResultSet primarykeycols = dbmd.getPrimaryKeys(null, tables.getString("TABLE_SCHEM"), tableName);

        PrimaryKey pk = null;

        while (primarykeycols.next()) {
            if (pk == null) {
                String pkName = primarykeycols.getString("PK_NAME");
                pk = new PrimaryKey(pkName);
            }
            String columnName = primarykeycols.getString("COLUMN_NAME");
            pk.getColumnNames().add(columnName);

            int columnOrder = primarykeycols.getInt("KEY_SEQ");
            pk.getColumnOrders().add(columnOrder);
        }
        primarykeycols.close();

        // If this table has a primary key, add it. 
        if (pk != null) {
            pk.setParent(table);
            table.setPrimaryKey(pk);
        }

        // Indexes
        final ResultSet indexes = dbmd.getIndexInfo(null, tables.getString("TABLE_SCHEM"), tableName, false,
                true);
        String lastIndexName = "";

        Index index = null;

        while (indexes.next()) {
            final String indexName = indexes.getString("INDEX_NAME");
            if (indexName == null) {
                // Oracle seems to have some dummy index entries
                continue;
            }
            // Skip the index corresponding to the PK if it is mentioned
            else if (indexName.equals(table.getPrimaryKey().getName())) {
                continue;
            }

            if (!indexName.equals(lastIndexName)) {
                index = new Index(indexName);
                index.setUnique(!indexes.getBoolean("NON_UNIQUE"));
                index.setParent(table);
                table.getIndexes().add(index);
                lastIndexName = indexName;
            }
            if (index != null) {
                String columnName = indexes.getString("COLUMN_NAME");
                index.getColumnNames().add(columnName);
            }
        }
        indexes.close();

        final ResultSet foreignkeys = dbmd.getImportedKeys(null, tables.getString("TABLE_SCHEM"), tableName);
        String lastKeyName = "";

        ForeignKey fk = null;

        while (foreignkeys.next()) {
            final String keyName = foreignkeys.getString("FK_NAME");
            if (!keyName.equals(lastKeyName)) {
                fk = new ForeignKey(keyName);
                fk.setParent(table);
                table.getForeignKeys().add(fk);
                lastKeyName = keyName;
            }
            if (fk != null) {
                fk.setLocalColumn(foreignkeys.getString("FKCOLUMN_NAME"));
                fk.setTargetTable(foreignkeys.getString("PKTABLE_NAME"));
                fk.setTargetColumn(foreignkeys.getString("PKCOLUMN_NAME"));
            }
        }
        foreignkeys.close();
    }
    tables.close();
}

From source file:org.easyrec.store.dao.web.impl.LoaderDAOMysqlImpl.java

@Override
public void migrateDB() throws Exception {

    HikariDataSource bds = (HikariDataSource) getDataSource();
    boolean tablesOk = false;

    DatabaseMetaDataCallback callback = new DatabaseMetaDataCallback() {
        public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException {
            ResultSet rs = dbmd.getTables(null, null, "operator", null);
            return rs.next();
        }/*from  ww w.  j  av  a  2 s. c  om*/
    };

    tablesOk = (Boolean) JdbcUtils.extractDatabaseMetaData(bds, callback);
    Float installedVersion = checkVersion();

    for (String migrateFile : migrateFiles) {
        logger.info("migrate File: " + migrateFile);
        Float scriptVersion = Float.parseFloat(migrateFile.substring(migrateFile.lastIndexOf("_") + 1));
        logger.info("scriptVersion: " + scriptVersion);
        if (installedVersion < scriptVersion) {
            File f = new File(dbMigrateFolder.getFile(), migrateFile + ".sql");
            if (f.exists()) {
                logger.info("Executing migrate script: " + f.getName());
                sqlScriptService.executeSqlScript(new FileSystemResource(f).getInputStream());
            }
            if (scriptVersion == 0.96) {
                update_0_96f();
            }
            if (scriptVersion == 0.98) {
                update_0_98();
            }
            if (scriptVersion == 1.00) {
                update_1_00();
            }
        }
    }

    //        if (installedVersion < 0.96f) {
    //            update_0_96f();
    //            // logs are not converted from ruleminerlog -> plugin_log
    //        }
    //
    //        if (installedVersion < 0.98) {
    //            update_0_98();
    //        }

    //updateVersion(); // done in migrate script!
}