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.eclipse.ecr.core.storage.sql.jdbc.JDBCMapper.java

/** Finds uppercase table names. */
protected static Set<String> findTableNames(DatabaseMetaData metadata, String schemaName) throws SQLException {
    Set<String> tableNames = new HashSet<String>();
    ResultSet rs = metadata.getTables(null, schemaName, "%", new String[] { "TABLE" });
    while (rs.next()) {
        String tableName = rs.getString("TABLE_NAME");
        tableNames.add(tableName.toUpperCase());
    }//from  www. j  ava  2 s.c o  m
    return tableNames;
}

From source file:org.openmrs.module.spreadsheetimport.DatabaseBackend.java

private static void reverseEngineerDatabaseTable() throws Exception {
    tableColumnMap = new TreeMap<String, String>();
    tableColumnListMap = new TreeMap<String, List<String>>();
    Connection conn = null;// w w w  .  j  a  v  a  2 s .c o m
    Exception exception = null;
    try {
        // Connect to db
        Class.forName("com.mysql.jdbc.Driver").newInstance();

        Properties p = Context.getRuntimeProperties();
        String url = p.getProperty("connection.url");

        conn = DriverManager.getConnection(url, p.getProperty("connection.username"),
                p.getProperty("connection.password"));

        // All tables
        DatabaseMetaData dmd = conn.getMetaData();
        ResultSet rs = dmd.getTables(null, null, "", null);
        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");

            // All columns
            List<String> columnNames = new ArrayList<String>();
            ResultSet rsColumns = dmd.getColumns(null, null, tableName, "");
            while (rsColumns.next()) {
                columnNames.add(rsColumns.getString("COLUMN_NAME"));
            }
            rsColumns.close();

            //            // Remove imported keys
            ResultSet rsImportedKeys = dmd.getImportedKeys(null, null, tableName);
            while (rsImportedKeys.next()) {
                String columnName = rsImportedKeys.getString("FKCOLUMN_NAME");
                if (columnNames.contains(columnName) && "obs".equalsIgnoreCase(tableName)
                        && !"value_coded".equalsIgnoreCase(columnName)) { // hack: only allow obs.value_coded to go through
                    columnNames.remove(columnName);
                }
            }
            rsImportedKeys.close();

            List<String> clonedColumns = new ArrayList<String>();
            clonedColumns.addAll(columnNames);

            // Add to map
            for (String columnName : clonedColumns) {
                String tableDotColumn = tableName + "." + columnName;
                tableColumnMap.put(tableDotColumn, makePrettyTableDotColumn(tableDotColumn));
            }

            // Remove primary key
            ResultSet rsPrimaryKeys = dmd.getPrimaryKeys(null, null, tableName);
            while (rsPrimaryKeys.next()) {
                String columnName = rsPrimaryKeys.getString("COLUMN_NAME");
                if (columnNames.contains(columnName)) {
                    columnNames.remove(columnName);
                }
            }
            rsPrimaryKeys.close();

            tableColumnListMap.put(tableName, columnNames);

        }
    } catch (Exception e) {
        log.debug(e.toString());
        exception = e;
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
    }

    if (exception != null) {
        throw exception;
    }
}

From source file:org.xenei.bloomgraph.bloom.MysqlBigLoadTest.java

@Override
protected BloomIO getBloomIO() throws SQLException {
    LoggingConfig.setLogger("org.xenei.bloomgraph.bloom", Level.INFO);

    final DataSource ds = MySQLBloomGraphTest.getMySQLDataSource(URL, USR, PWD);
    final Connection c = ds.getConnection();
    final Statement stmt = c.createStatement();
    ResultSet rs = null;/*from  ww  w.  j ava2 s.  c om*/
    try {
        final SQLCommands sqlCmd = new MySQLCommands();
        final DatabaseMetaData metaData = c.getMetaData();
        rs = metaData.getTables(c.getCatalog(), c.getSchema(), sqlCmd.getPageIndexTableName(),
                new String[] { "TABLE" });
        while (rs.next()) {
            stmt.execute("TRUNCATE " + rs.getString(3));
        }
        DbUtils.closeQuietly(rs);
        rs = metaData.getTables(c.getCatalog(), c.getSchema(), sqlCmd.getPageStatsTableName(),
                new String[] { "TABLE" });
        while (rs.next()) {
            stmt.execute("TRUNCATE " + rs.getString(3));
        }
        DbUtils.closeQuietly(rs);
        rs = metaData.getTables(c.getCatalog(), c.getSchema(), "Page\\_%", new String[] { "TABLE" });
        while (rs.next()) {
            stmt.execute("DROP TABLE " + rs.getString(3));
        }
        return new DBIO(ds, sqlCmd);
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(c);
    }
}

From source file:org.openmrs.module.databasebackup.util.DbDump.java

/** Dump the whole database to an SQL string */
public static void dumpDB(Properties props, boolean showProgress, Class showProgressToClass) throws Exception {
    String filename = props.getProperty("filename");
    String folder = props.getProperty("folder");
    String driverClassName = props.getProperty("driver.class");
    String driverURL = props.getProperty("driver.url");
    DatabaseMetaData dbMetaData = null;
    Connection dbConn = null;/*from   www  . jav a  2 s .  co m*/

    Class.forName(driverClassName);
    dbConn = DriverManager.getConnection(driverURL, props);
    dbMetaData = dbConn.getMetaData();

    FileOutputStream fos = new FileOutputStream(folder + filename);
    OutputStreamWriter result = new OutputStreamWriter(fos, fileEncoding);

    String catalog = props.getProperty("catalog");
    String schema = props.getProperty("schemaPattern");

    String tablesIncluded = props.getProperty("tables.included");
    List<String> tablesIncludedVector = Arrays.asList(tablesIncluded.split(","));

    String tablesExcluded = props.getProperty("tables.excluded");
    List<String> tablesExcludedVector = Arrays.asList(tablesExcluded.split(","));

    ResultSet rs = dbMetaData.getTables(catalog, schema, null, null);
    int progressCnt = 0;

    log.debug("tablesIncluded: " + tablesIncluded);
    log.debug("tablesExcluded: " + tablesExcluded);

    result.write("/*\n" + " * DB jdbc url: " + driverURL + "\n" + " * Database product & version: "
            + dbMetaData.getDatabaseProductName() + " " + dbMetaData.getDatabaseProductVersion() + "\n"
            + " */");

    result.write("\nSET FOREIGN_KEY_CHECKS=0;\n");

    List<String> tableVector = new Vector<String>();
    int progressTotal = 0;
    while (rs.next()) {
        String tableName = rs.getString("TABLE_NAME");
        if ((tablesIncluded.contains("all") && !tablesExcludedVector.contains(tableName)
                || tablesIncluded.contains(tableName))
                || (tablesExcludedVector.contains("none") && !tablesIncludedVector.contains("none"))) {
            progressTotal++;
            tableVector.add(tableName);
        }
    }
    rs.beforeFirst();

    if (!rs.next()) {
        log.error("Unable to find any tables matching: catalog=" + catalog + " schema=" + schema + " tables="
                + tableVector.toArray().toString());
        rs.close();
    } else {
        do {
            String tableName = rs.getString("TABLE_NAME");
            String tableType = rs.getString("TABLE_TYPE");

            if (tableVector.contains(tableName)) {

                progressCnt++;
                //BackupFormController.getProgressInfo().put(filename, "Backing up table " + progressCnt + " of " + progressTotal + " (" + tableName + ")...");

                if (showProgress) {
                    Map<String, String> info = (Map<String, String>) showProgressToClass
                            .getMethod("getProgressInfo", null).invoke(showProgressToClass);
                    info.put(filename, "Backing up table " + progressCnt + " of " + progressTotal + " ("
                            + tableName + ")...");
                    showProgressToClass.getMethod("setProgressInfo", new Class[] { Map.class })
                            .invoke(showProgressToClass, info);
                }

                if ("TABLE".equalsIgnoreCase(tableType)) {

                    result.write("\n\n-- Structure for table `" + tableName + "`\n");
                    result.write("DROP TABLE IF EXISTS `" + tableName + "`;\n");

                    PreparedStatement tableStmt = dbConn
                            .prepareStatement("SHOW CREATE TABLE " + tableName + ";");
                    ResultSet tablesRs = tableStmt.executeQuery();
                    while (tablesRs.next()) {
                        result.write(tablesRs.getString("Create Table") + ";\n\n");
                    }
                    tablesRs.close();
                    tableStmt.close();

                    dumpTable(dbConn, result, tableName);
                    System.gc();
                }
            }
        } while (rs.next());
        rs.close();
    }

    result.write("\nSET FOREIGN_KEY_CHECKS=1;\n");

    result.flush();
    result.close();

    dbConn.close();
}

From source file:org.apache.cayenne.dbsync.reverse.dbload.EntityLoader.java

@Override
protected ResultSet getResultSet(String catalogName, String schemaName, DatabaseMetaData metaData)
        throws SQLException {
    return metaData.getTables(catalogName, schemaName, WILDCARD, types);
}

From source file:org.cloudfoundry.identity.uaa.db.TableAndColumnNormalizationTest.java

@Test
public void checkTables() throws Exception {
    Connection connection = dataSource.getConnection();
    try {//from   w  w w.  j  a v a 2s  .  com
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getTables(null, null, null, new String[] { "TABLE" });
        int count = 0;
        while (rs.next()) {
            String name = rs.getString("TABLE_NAME");
            logger.info("Checking table [" + name + "]");
            if (name != null && DatabaseInformation1_5_3.tableNames.contains(name.toLowerCase())) {
                count++;
                logger.info("Validating table [" + name + "]");
                assertTrue("Table[" + name + "] is not lower case.", name.toLowerCase().equals(name));
            }
        }
        assertEquals("Table count:", DatabaseInformation1_5_3.tableNames.size(), count);

    } finally {
        try {
            connection.close();
        } catch (Exception ignore) {
        }
    }
}

From source file:org.xenei.bloomgraph.bloom.MySQLBloomGraphTest.java

@Override
protected BloomIO getBloomIO() throws SQLException {
    final DataSource ds = getMySQLDataSource(URL, USR, PWD);
    final Connection c = ds.getConnection();
    final Statement stmt = c.createStatement();
    ResultSet rs = null;//  w  w w  .  j  av a  2 s .  c o m
    try {
        final SQLCommands sqlCmd = new MySQLCommands();
        final DatabaseMetaData metaData = c.getMetaData();
        rs = metaData.getTables(c.getCatalog(), c.getSchema(), sqlCmd.getPageIndexTableName(),
                new String[] { "TABLE" });
        while (rs.next()) {
            stmt.execute("TRUNCATE " + rs.getString(3));
        }
        DbUtils.closeQuietly(rs);
        rs = metaData.getTables(c.getCatalog(), c.getSchema(), sqlCmd.getPageStatsTableName(),
                new String[] { "TABLE" });
        while (rs.next()) {
            stmt.execute("TRUNCATE " + rs.getString(3));
        }
        DbUtils.closeQuietly(rs);
        rs = metaData.getTables(c.getCatalog(), c.getSchema(), "Page\\_%", new String[] { "TABLE" });
        while (rs.next()) {
            stmt.execute("DROP TABLE " + rs.getString(3));
        }
        return new DBIO(ds, sqlCmd);
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(c);
    }
}

From source file:org.obiba.runtime.upgrade.support.DatabaseMetadataUtil.java

/**
 * Indicates whether the specified table exists.
 *
 * @param tableName the table name//w  ww .java 2s .  c o  m
 * @return <code>true</code> if the table exists
 */
public boolean isTableExists(final String tableName) {
    boolean tablePresent = false;

    try {
        tablePresent = (Boolean) JdbcUtils.extractDatabaseMetaData(dataSource, new DatabaseMetaDataCallback() {
            @Override
            public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException {
                return dbmd.getTables(null, null, tableName, null).next();
            }
        });
    } catch (MetaDataAccessException ex) {
        throw new RuntimeException(ex);
    }

    return tablePresent;
}

From source file:org.gaixie.jibu.security.dao.SchemaCreate.java

/**
 * ?? dbscripts ? type???? sql //from w w w  .j  av  a 2 s .com
 * <p>
 * @param type ? jibu.properties ?
 */
public void create(String type) {
    Connection conn = null;
    try {
        conn = ConnectionUtils.getConnection();
        DatabaseMetaData dbm = conn.getMetaData();

        ResultSet rs = dbm.getTables(null, null, "USERBASE", null);
        if (rs.next())
            throw new SQLException("Schema has been created!");

        String dpn = dbm.getDatabaseProductName();
        if (!"Apache Derby".equals(dpn))
            throw new SQLException("Database is not Apache Derby!");
        QueryRunner run = new QueryRunner();
        URL url = this.getClass().getResource("/dbscripts/" + type + "/");
        File dir = new File(url.toURI());
        File[] files = dir.listFiles();
        Arrays.sort(files);
        for (File file : files) {
            if (file.isFile()) {
                handleFile(run, conn, "/dbscripts/" + type + "/" + file.getName());
            }
        }
        DbUtils.commitAndClose(conn);
    } catch (SQLException se) {
        DbUtils.rollbackAndCloseQuietly(conn);
        logger.warn("Schema create failed: " + se.getMessage());
    } catch (IOException ie) {
        DbUtils.rollbackAndCloseQuietly(conn);
        logger.warn("Read SQL Scripts failed: " + ie.getMessage());
    } catch (URISyntaxException e) {
        DbUtils.rollbackAndCloseQuietly(conn);
        logger.warn("Get SQL Scripts Directory failed: " + e.getMessage());
    }
}

From source file:com.uit.anonymousidentity.Repository.IssuerKeys.IssuerJDBCTemplate.java

@Override
public void createTableIfNotExists() throws SQLException {
    DatabaseMetaData metaData = dataSource.getConnection().getMetaData();
    ResultSet rs = metaData.getTables(null, null, TABLE_NAME, null);
    if (rs.next()) {
        //Table exists
        return;/* w ww.j a v a 2 s.c  om*/
    } else {
        String tem_sql = "create table if not exists %s ( " + "%s int not null auto_increment,"
                + "%s text not null," + "%s text not null," + "%s text not null," + "%s text not null,"
                + "primary key ( %s ) " + " ) ";
        String sql = String.format(tem_sql, TABLE_NAME, ID, SID, BNCurveName, PK, SK, ID);
        jdbcTemplate.execute(sql);

    }
}