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:com.quartzdesk.executor.dao.AbstractDao.java

/**
 * Checks if the specified table exists in the specified schema and returns true if
 * it exists, false otherwise. This method tries to look up the table using both
 * lower-case and upper-case schema and table names because some databases seems to
 * require the names to be in upper case (DB2, Oracle), whereas other databases require
 * the names to be in lower-case./*  w  w  w.j ava 2  s .  c o m*/
 *
 * @param session    a Hibernate session.
 * @param schemaName an optional schema name where to look for the table name.
 * @param tableName  a table name.
 * @return true if the table exists, false otherwise.
 */
public boolean tableExists(Session session, final String schemaName, final String tableName) {
    final AtomicBoolean tableExists = new AtomicBoolean(false);

    session.doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            log.debug("Checking if table '{}' exists.", tableName);

            DatabaseMetaData metaData = connection.getMetaData();

            // 1. attempt - try schema and table name in lower-case (does not work in DB2 and Oracle)
            ResultSet res = metaData.getTables(null,
                    schemaName == null ? null : schemaName.toLowerCase(Locale.US),
                    tableName.toLowerCase(Locale.US), new String[] { "TABLE" });

            tableExists.set(res.next());
            DbUtils.close(res);

            if (tableExists.get()) {
                log.debug("Table '{}' exists.", tableName);
            } else {
                // 2. attempt - try schema and table name in upper-case (required for DB2 and Oracle)
                res = metaData.getTables(null, schemaName == null ? null : schemaName.toUpperCase(Locale.US),
                        tableName.toUpperCase(Locale.US), new String[] { "TABLE" });

                tableExists.set(res.next());
                DbUtils.close(res);

                if (tableExists.get()) {
                    log.debug("Table '{}' exists.", tableName);
                } else {
                    log.debug("Table '{}' does not exist.", tableName);
                }
            }
        }
    });

    return tableExists.get();
}

From source file:org.sonar.core.persistence.InMemoryDatabase.java

private void truncateTables() {
    Connection connection = null;
    try {//from  w w  w.  j  a  v  a 2  s .c om
        connection = datasource.getConnection();

        DatabaseMetaData meta = connection.getMetaData();
        Statement statement = connection.createStatement();

        ResultSet res = meta.getTables(null, null, null, new String[] { "TABLE" });
        while (res.next()) {
            String tableName = res.getString("TABLE_NAME");
            statement.executeUpdate("TRUNCATE TABLE " + tableName);
        }
        res.close();

        // See https://issues.apache.org/jira/browse/DERBY-5403
        res = meta.getColumns(null, null, null, "ID");
        while (res.next()) {
            String tableName = res.getString("TABLE_NAME");
            statement.executeUpdate("ALTER TABLE " + tableName + " ALTER COLUMN ID RESTART WITH 1");
        }
        res.close();

        statement.close();
    } catch (SQLException e) {
        throw new IllegalStateException("Fail to truncate tables", e);

    } finally {
        closeQuietly(connection); // Important, otherwise tests can stuck
    }
}

From source file:jp.co.tis.gsp.tools.dba.s2jdbc.gen.DbTableMetaReaderWithView.java

protected String getObjectTypeName(DatabaseMetaData metaData, DbTableMeta tableMeta) throws SQLException {
    ResultSet rs = metaData.getTables(tableMeta.getCatalogName(), tableMeta.getSchemaName(),
            tableMeta.getName(), null);//from   w  w  w  .  j  a v a2 s  .  c  o m
    try {
        rs.next();
        String typeName = rs.getString("TABLE_TYPE");
        return typeName;
    } finally {
        ResultSetUtil.close(rs);
    }
}

From source file:com.dbsvg.models.JdbcMainDAO.java

/**
 * Scrubs the database, Generates Pojo Tables with columns, and Primary and
 * Foreign Keys delineated.//from   w  w  w . ja v a  2 s . c o  m
 * 
 * @param conn
 *            (null will use the default DB connection Table)
 * @return
 * @throws java.lang.Exception
 */
public Map<String, Table> getTables(Connection conn, String schemaId) throws Exception {
    Map<String, Table> tableMap = new HashMap<String, Table>();
    if (conn == null) {
        conn = getDefaultConnection();
    }
    Statement st = conn.createStatement();
    ResultSet rs = null;
    DatabaseMetaData meta = conn.getMetaData();

    LOG.debug("Grabbing Table Data");
    rs = meta.getTables(null, null, null, new String[] { "TABLE", "VIEW" });
    while (rs.next()) {
        String tableOrViewName = rs.getString("TABLE_NAME");
        Table t = new Table(tableOrViewName);
        tableMap.put(tableOrViewName, t);
        t.setSchemaId(schemaId);
        populateTable(t, conn);
    }

    LOG.debug("Populating Foreign Keys");
    int i = 0;
    for (Table t : tableMap.values()) {
        i++;
        LOG.debug("Checking " + i + " of " + tableMap.size() + "(" + t.getName() + ")");
        checkForForeignKeys(t, meta, conn, tableMap);
        // for (Table fTable: tableMap.values()) {
        // checkForForeignKeys(t, fTable, meta);
        // }
    }

    st.close();
    conn.close();

    return tableMap;
}

From source file:com.amediamanager.config.DatabaseSchemaResource.java

private Boolean doesDataSourceExist(final String tableName) throws Exception {
    boolean dataSourceExists = false;

    Connection connection = null;
    ResultSet results = null;/*from  w w w .  j  a v  a2  s  . com*/
    DatabaseMetaData metadata;

    try {
        connection = dataSource.getConnection();
        metadata = connection.getMetaData();
        results = metadata.getTables(null, null, tableName, null);

        dataSourceExists = results.next();
    } catch (Exception e) {
        LOG.error("Failed to check datasource.", e);
        throw e;
    } finally {
        try {
            results.close();
            connection.close();
        } catch (Exception x) {
        }
    }

    return dataSourceExists;
}

From source file:gobblin.writer.jdbc.JdbcWriterInitializerTest.java

public void initializeWithCreatingStagingTable() throws SQLException {
    when(this.commands.isEmpty(DB, STAGING_TABLE)).thenReturn(Boolean.TRUE);
    DatabaseMetaData metadata = mock(DatabaseMetaData.class);
    when(this.conn.getMetaData()).thenReturn(metadata);
    ResultSet rs = mock(ResultSet.class);
    when(metadata.getTables(anyString(), anyString(), anyString(), any(String[].class))).thenReturn(rs);
    when(rs.next()).thenReturn(Boolean.FALSE);

    this.initializer.initialize();

    Assert.assertTrue(!StringUtils.isEmpty(this.workUnit.getProp(ConfigurationKeys.WRITER_STAGING_TABLE)));

    InOrder inOrder = inOrder(this.commands);
    inOrder.verify(this.commands, times(1)).createTableStructure(anyString(), anyString(), anyString());
    inOrder.verify(this.commands, times(1)).drop(anyString(), anyString());
    inOrder.verify(this.commands, times(1)).createTableStructure(anyString(), anyString(), anyString());

    this.initializer.close();
    inOrder.verify(this.commands, times(1)).drop(anyString(), anyString());
    inOrder.verify(this.commands, never()).truncate(anyString(), anyString());
}

From source file:it.unibas.spicy.persistence.json.ExportJsonInstances.java

public void exportJsonInstances(MappingTask mappingTask, String directoryPath, String suffix, int scenarioNo)
        throws DAOException, SQLException, IOException {
    String folderPath = generateFolderPath(mappingTask.getTargetProxy().getIntermediateSchema(), directoryPath,
            suffix, 0);/* w  ww .  ja v a2s . c  om*/
    //create CSV Folder
    new File(folderPath).mkdir();
    //connection to Postgres
    IConnectionFactory connectionFactory = new SimpleDbConnectionFactory();
    Connection connection = getConnectionToPostgres(connectionFactory, scenarioNo);
    try {
        Statement statement = connection.createStatement();

        //get table names from target database
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        String[] tableTypes = new String[] { "TABLE" };

        ResultSet tableResultSet = databaseMetaData.getTables(
                SpicyEngineConstants.MAPPING_TASK_DB_NAME + scenarioNo,
                SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo, null, tableTypes);
        while (tableResultSet.next()) {
            String tableName = tableResultSet.getString("TABLE_NAME");
            createJsonDocument(tableName, SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo,
                    mappingTask.getTargetProxy().getIntermediateSchema().getChild(tableName), folderPath,
                    statement);
        }
    } finally {
        //close connection
        if (connection != null)
            connectionFactory.close(connection);
    }
}

From source file:com.yahoo.sql4d.indexeragent.sql.DBAccessor.java

/**
 * Check the metadata to see if table already exists, else create one.
 * @param tableName /*from   ww w .j  av a 2 s .c  o  m*/
 */
private void createTable(String tableName) {
    Tuple2<DataSource, Connection> conn = null;
    try {
        conn = getConnection();
        DatabaseMetaData meta = conn._2().getMetaData();
        ResultSet rs = meta.getTables(null, null, null, new String[] { "TABLE" });
        boolean tableExists = false;
        while (rs.next()) {
            if (rs.getString("TABLE_NAME").equalsIgnoreCase(tableName)) {
                tableExists = true;
                break;
            }
        }
        if (!tableExists) {
            execute(null, tableName);
        }
    } catch (Exception ex) {
        Logger.getLogger(DBAccessor.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        returnConnection(conn);
    }
}

From source file:net.sf.classifier4J.bayesian.JDBCWordsDataSource.java

/**
 * Create the word_probability table if it does not already
 * exist. Tested successfully with MySQL 4 & HSQLDB. See
 * comments in code for Axion 1.0M1 issues. 
 *   //from  w ww. j  a  v a 2 s .  co  m
 * 
 * @throws WordsDataSourceException
 */
private void createTable() throws WordsDataSourceException {
    Connection con = null;
    try {
        con = connectionManager.getConnection();

        // check if the word_probability table exists 
        DatabaseMetaData dbm = con.getMetaData();
        ResultSet rs = dbm.getTables(null, null, "word_probability", null);
        if (!rs.next()) {
            // the table does not exist
            Statement stmt = con.createStatement();
            //   Under Axion 1.0M1, use          
            //    stmt.executeUpdate( "CREATE TABLE word_probability ( "
            //         + " word         VARCHAR(255) NOT NULL,"
            //         + " category      VARCHAR(20) NOT NULL,"
            //         + " match_count      INTEGER NOT NULL,"
            //         + " nonmatch_count   INTEGER NOT NULL, "
            //         + " PRIMARY KEY(word, category) ) ");            
            stmt.executeUpdate("CREATE TABLE word_probability ( " + " word         VARCHAR(255) NOT NULL,"
                    + " category      VARCHAR(20) NOT NULL," + " match_count      INT DEFAULT 0 NOT NULL,"
                    + " nonmatch_count   INT DEFAULT 0 NOT NULL, " + " PRIMARY KEY(word, category) ) ");
        }
    } catch (SQLException e) {
        throw new WordsDataSourceException("Problem creating table", e); // we can't recover from this            
    } finally {
        if (con != null) {
            try {
                connectionManager.returnConnection(con);
            } catch (SQLException e1) {
                // ignore
            }
        }
    }
}

From source file:edu.cornell.mannlib.vivo.utilities.rdbmigration.RdbMigrator.java

private void checkThatRdbExists() throws SQLException {
    try (Connection conn = getSqlConnection()) {
        DatabaseMetaData md = conn.getMetaData();
        try (ResultSet rs = md.getTables(null, null, TABLE_RDB, null);) {
            if (!rs.next()) {
                quit("The database at '" + jdbcUrl + "' contains no RDB tables.");
            }// w  ww . java 2s. co m
        }
    }
}