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.glaf.core.util.DBUtils.java

public static List<String> getTables() {
    List<String> tables = new java.util.ArrayList<String>();
    String[] types = { "TABLE" };
    Connection connection = null;
    try {//from  ww w . ja  va  2s.  com
        connection = DBConnectionFactory.getConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getTables(null, null, null, types);
        while (rs.next()) {
            tables.add(rs.getObject("TABLE_NAME").toString());
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(connection);
    }
    return tables;
}

From source file:org.apache.phoenix.query.BaseTest.java

private static void deletePriorTables(long ts, Connection globalConn, String url) throws Exception {
    DatabaseMetaData dbmd = globalConn.getMetaData();
    // Drop VIEWs first, as we don't allow a TABLE with views to be dropped
    // Tables are sorted by TENANT_ID
    List<String[]> tableTypesList = Arrays.asList(new String[] { PTableType.VIEW.toString() },
            new String[] { PTableType.TABLE.toString() });
    for (String[] tableTypes : tableTypesList) {
        ResultSet rs = dbmd.getTables(null, null, null, tableTypes);
        String lastTenantId = null;
        Connection conn = globalConn;
        while (rs.next()) {
            String fullTableName = SchemaUtil.getEscapedTableName(
                    rs.getString(PhoenixDatabaseMetaData.TABLE_SCHEM),
                    rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
            String ddl = "DROP " + rs.getString(PhoenixDatabaseMetaData.TABLE_TYPE) + " " + fullTableName;
            String tenantId = rs.getString(1);
            if (tenantId != null && !tenantId.equals(lastTenantId)) {
                if (lastTenantId != null) {
                    conn.close();/*from w ww .  j av  a2 s . co  m*/
                }
                // Open tenant-specific connection when we find a new one
                Properties props = PropertiesUtil.deepCopy(globalConn.getClientInfo());
                props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
                conn = DriverManager.getConnection(url, props);
                lastTenantId = tenantId;
            }
            try {
                conn.createStatement().executeUpdate(ddl);
            } catch (NewerTableAlreadyExistsException ex) {
                logger.info("Newer table " + fullTableName
                        + " or its delete marker exists. Ignore current deletion");
            } catch (TableNotFoundException ex) {
                logger.info("Table " + fullTableName + " is already deleted.");
            }
        }
        rs.close();
        if (lastTenantId != null) {
            conn.close();
        }
    }
}

From source file:com.mirth.connect.server.controllers.DefaultMessageObjectController.java

public void removeAllFilterTables() {
    Connection conn = null;/*  ww w.  ja va 2s  . c om*/
    ResultSet resultSet = null;

    try {
        conn = SqlConfig.getSqlMapClient().getDataSource().getConnection();
        // Gets the database metadata
        DatabaseMetaData dbmd = conn.getMetaData();

        // Specify the type of object; in this case we want tables
        String[] types = { "TABLE" };
        String tablePattern = "MSG_TMP_%";
        resultSet = dbmd.getTables(null, null, tablePattern, types);

        boolean resultFound = resultSet.next();

        // Some databases only accept lowercase table names
        if (!resultFound) {
            resultSet = dbmd.getTables(null, null, tablePattern.toLowerCase(), types);
            resultFound = resultSet.next();
        }

        while (resultFound) {
            // Get the table name
            String tableName = resultSet.getString(3);
            // Get the uid and remove its filter tables/indexes/sequences
            removeFilterTable(tableName.substring(8));
            resultFound = resultSet.next();
        }

    } catch (SQLException e) {
        logger.error(e);
    } finally {
        DbUtils.closeQuietly(resultSet);
        DbUtils.closeQuietly(conn);
    }
}

From source file:de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.java

/**
 * Reverse engineer an existing view./*from   w  w w  . ja va2s .  c  om*/
 *
 * @param aModel   the model
 * @param aOptions   the options
 * @param aNotifier the notifier
 * @param aViewEntry the table
 * @param aConnection the connection
 * @throws SQLException   is thrown in case of an error
 * @throws ReverseEngineeringException is thrown in case of an error
 */
protected void reverseEngineerView(Model aModel, ReverseEngineeringOptions aOptions,
        ReverseEngineeringNotifier aNotifier, TableEntry aViewEntry, Connection aConnection)
        throws SQLException, ReverseEngineeringException {

    aNotifier.notifyMessage(ERDesignerBundle.ENGINEERINGTABLE, aViewEntry.getTableName());

    DatabaseMetaData theMetaData = aConnection.getMetaData();

    String theTablePattern = getEscapedPattern(theMetaData, aViewEntry.getTableName());
    String theSchemaPattern = getEscapedPattern(theMetaData, aViewEntry.getSchemaName());

    ResultSet theViewsResultSet = theMetaData.getTables(aViewEntry.getCatalogName(), theSchemaPattern,
            theTablePattern, new String[] { aViewEntry.getTableType().toString() });

    while (theViewsResultSet.next()) {

        String theViewRemarks = theViewsResultSet.getString("REMARKS");

        View theView = new View();

        theView.setName(dialect.getCastType().cast(aViewEntry.getTableName()));
        theView.setOriginalName(aViewEntry.getTableName());
        switch (aOptions.getTableNaming()) {
        case INCLUDE_SCHEMA:
            theView.setSchema(aViewEntry.getSchemaName());
            break;
        default:
        }

        if (!StringUtils.isEmpty(theViewRemarks)) {
            theView.setComment(theViewRemarks);
        }

        String theStatement = reverseEngineerViewSQL(aViewEntry, aConnection, theView);

        try {
            SQLUtils.updateViewAttributesFromSQL(theView, theStatement);
        } catch (Exception e) {
            LOGGER.warn("View " + theView.getName() + " has a strange SQL : " + theStatement);
        }

        theView.setSql(theStatement);

        // We are done here
        try {
            aModel.addView(theView);
        } catch (VetoException | ElementInvalidNameException | ElementAlreadyExistsException e) {
            throw new ReverseEngineeringException(e.getMessage(), e);
        }

    }

    theViewsResultSet.close();
}

From source file:it.unibas.spicy.persistence.relational.DAORelational.java

private void loadForeignKeys(IDataSourceProxy dataSource, DatabaseMetaData databaseMetaData, String catalog,
        String schemaName, boolean source, int scenarioNo) throws DAOException {
    IConnectionFactory connectionFactory = new SimpleDbConnectionFactory();
    Connection connection = getConnectionToPostgres(connectionFactory);
    try {//from w  ww  . j  a  va  2  s .  c  o  m
        Statement statement = connection.createStatement();
        String[] tableTypes = new String[] { "TABLE" };
        ResultSet tableResultSet = databaseMetaData.getTables(catalog, schemaName, null, tableTypes);
        while (tableResultSet.next()) {
            String tableName = tableResultSet.getString("TABLE_NAME");
            if (!this.dataDescription.checkLoadTable(tableName)) {
                continue;
            }
            if (logger.isDebugEnabled())
                logger.debug("Searching foreign keys. ANALYZING TABLE  = " + tableName);
            ResultSet resultSet = databaseMetaData.getImportedKeys(catalog, null, tableName);
            List<String> listOfPrimaryKey = new ArrayList<String>();
            List<String> listOfForeignKey = new ArrayList<String>();
            String previousTableName = "";
            while (resultSet.next()) {
                String pkTableName = resultSet.getString("PKTABLE_NAME");
                String pkColumnName = resultSet.getString("PKCOLUMN_NAME");
                String keyPrimaryKey = pkTableName + "." + pkColumnName;
                //AttributeNode primaryKey = (AttributeNode)DataSourceFactory.getNode(keyPrimary);
                String fkTableName = resultSet.getString("FKTABLE_NAME");
                String fkColumnName = resultSet.getString("FKCOLUMN_NAME");
                String keyForeignKey = fkTableName + "." + fkColumnName;

                if (logger.isDebugEnabled())
                    logger.debug("Analyzing foreign key: " + keyForeignKey + " references " + keyPrimaryKey);
                if (!this.dataDescription.checkLoadTable(pkTableName)
                        || !this.dataDescription.checkLoadTable(fkTableName)) {
                    if (logger.isDebugEnabled())
                        logger.debug("Check load tables. Foreign key discarded: " + keyForeignKey
                                + " references " + keyPrimaryKey);
                    continue;
                }
                if (!this.dataDescription.checkLoadAttribute(pkTableName, pkColumnName)
                        || !this.dataDescription.checkLoadAttribute(fkTableName, fkColumnName)) {
                    if (logger.isDebugEnabled())
                        logger.debug("Check load attributes. Foreign key discarded: " + keyForeignKey
                                + " references " + keyPrimaryKey);
                    continue;
                }
                if (logger.isDebugEnabled())
                    logger.debug("Analyzing Primary Key: " + keyPrimaryKey + " Found a Foreign Key: "
                            + fkColumnName + " in table " + fkTableName);

                //giannisk alter table, add foreign key
                String fkTable, pkTable;

                if (source) {
                    fkTable = SpicyEngineConstants.SOURCE_SCHEMA_NAME + String.valueOf(scenarioNo) + ".\""
                            + fkTableName + "\"";
                    pkTable = SpicyEngineConstants.SOURCE_SCHEMA_NAME + String.valueOf(scenarioNo) + ".\""
                            + pkTableName + "\"";
                } else {
                    fkTable = SpicyEngineConstants.TARGET_SCHEMA_NAME + String.valueOf(scenarioNo) + ".\""
                            + fkTableName + "\"";
                    pkTable = SpicyEngineConstants.TARGET_SCHEMA_NAME + String.valueOf(scenarioNo) + ".\""
                            + pkTableName + "\"";
                }
                statement.executeUpdate("ALTER TABLE " + fkTable + " ADD FOREIGN KEY (" + fkColumnName
                        + ") REFERENCES " + pkTable + " (" + pkColumnName + ");");

                if (!listOfPrimaryKey.contains(keyPrimaryKey)
                        && (previousTableName.equals("") || previousTableName.equals(pkTableName))) {
                    if (logger.isDebugEnabled())
                        logger.debug("Adding nodes to collection: " + keyPrimaryKey + " - " + keyForeignKey);
                    listOfPrimaryKey.add(keyPrimaryKey);
                    listOfForeignKey.add(keyForeignKey);
                } else if (!listOfPrimaryKey.isEmpty() && !listOfForeignKey.isEmpty()) {
                    if (logger.isDebugEnabled())
                        logger.debug("Generating constraint: " + listOfForeignKey + " reference "
                                + listOfPrimaryKey);
                    DAORelationalUtility.generateConstraint(listOfForeignKey.toArray(),
                            listOfPrimaryKey.toArray(), dataSource);
                    listOfPrimaryKey.clear();
                    listOfForeignKey.clear();
                    listOfPrimaryKey.add(keyPrimaryKey);
                    listOfForeignKey.add(keyForeignKey);
                }
                previousTableName = pkTableName;
            }
            if (logger.isDebugEnabled())
                logger.debug("Main loop: " + listOfForeignKey + " reference " + listOfPrimaryKey);
            if (!listOfPrimaryKey.isEmpty() && !listOfForeignKey.isEmpty()) {
                DAORelationalUtility.generateConstraint(listOfForeignKey.toArray(), listOfPrimaryKey.toArray(),
                        dataSource);
            }
            if (logger.isDebugEnabled())
                logger.debug("Foreign keys loaded. Exiting");
        }
    } catch (SQLException ex) {
        logger.error(ex);
    } finally {
        //close connection
        if (connection != null)
            connectionFactory.close(connection);
    }
}

From source file:com.nextep.designer.sqlgen.postgre.impl.PostgreSqlCapturer.java

@Override
public Collection<ISequence> getSequences(ICaptureContext context, IProgressMonitor m) {
    final IProgressMonitor monitor = new CustomProgressMonitor(SubMonitor.convert(m, 500), PROGRESS_RANGE);
    final Connection conn = (Connection) context.getConnectionObject();
    final Collection<ISequence> sequences = new ArrayList<ISequence>();
    final String seqSql = "SELECT min_value, max_value, increment_by, is_cycled, cache_value, last_value FROM "; //$NON-NLS-1$

    Statement stmt = null;//from  w w  w.j a  va  2s  . c  om
    ResultSet rset = null;
    ResultSet rsetInfo = null;
    long start = 0;
    try {
        stmt = conn.createStatement();

        if (LOGGER.isDebugEnabled())
            start = System.currentTimeMillis();

        DatabaseMetaData md = conn.getMetaData();
        rset = md.getTables(context.getCatalog(), context.getSchema(), "%", //$NON-NLS-1$
                new String[] { "SEQUENCE" }); //$NON-NLS-1$

        while (rset.next()) {
            final String name = rset.getString("TABLE_NAME"); //$NON-NLS-1$
            final String desc = rset.getString("REMARKS"); //$NON-NLS-1$
            final IVersionable<ISequence> seqV = VersionableFactory.createVersionable(ISequence.class);
            final ISequence seq = seqV.getVersionnedObject().getModel();

            seq.setName(name);
            seq.setDescription(desc);
            seq.setOrdered(false); // Ordered sequences are not supported by
            // PostgreSQL

            try {
                rsetInfo = stmt.executeQuery(seqSql + name);

                if (rsetInfo.next()) {
                    monitor.worked(1);

                    final BigDecimal min = rsetInfo.getBigDecimal("min_value"); //$NON-NLS-1$
                    final BigDecimal max = rsetInfo.getBigDecimal("max_value"); //$NON-NLS-1$
                    final Long inc = rsetInfo.getLong("increment_by"); //$NON-NLS-1$
                    final String cycle = rsetInfo.getString("is_cycled"); //$NON-NLS-1$
                    final int cacheSize = rsetInfo.getInt("cache_value"); //$NON-NLS-1$
                    final BigDecimal seqStart = rsetInfo.getBigDecimal("last_value"); //$NON-NLS-1$

                    seq.setMinValue(min);
                    seq.setMaxValue(max);
                    seq.setIncrement(inc);
                    seq.setCycle("Y".equals(cycle)); //$NON-NLS-1$
                    seq.setCacheSize(cacheSize);
                    seq.setCached(cacheSize > 0);
                    seq.setStart(seqStart);

                    sequences.add(seq);
                }
            } catch (SQLException e) {
                LOGGER.warn(
                        MessageFormat.format(SQLGenMessages.getString("capturer.error.genericCapturerError"), //$NON-NLS-1$
                                DBVendor.POSTGRE) + e.getMessage(),
                        e);
            } finally {
                CaptureHelper.safeClose(rsetInfo, null);
            }
        }
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("[Sequences] fetching time: " + (System.currentTimeMillis() - start) //$NON-NLS-1$
                    + "ms"); //$NON-NLS-1$
    } catch (SQLException e) {
        LOGGER.warn(MessageFormat.format(SQLGenMessages.getString("capturer.error.genericCapturerError"), //$NON-NLS-1$
                DBVendor.POSTGRE) + e.getMessage(), e);
    } finally {
        CaptureHelper.safeClose(rset, stmt);
    }

    return sequences;
}

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

@SuppressWarnings("resource")
public boolean hasTablesForType(String catalog, String schema, String type) {

    ResultSet rs = null;//from  w w w . j  a  v a  2 s.  c o m
    try {
        String _catalog = getCatalogNameForQueries(catalog);
        String _schema = getSchemaNameForQueries(schema);
        DatabaseMetaData dmd = getDatabaseMetaData();

        rs = dmd.getTables(_catalog, _schema, null, new String[] { type });
        while (rs.next()) {

            if (StringUtils.equalsIgnoreCase(type, rs.getString(4))) {

                return true;
            }

        }

        return false;

    } catch (SQLException e) {

        if (Log.isDebugEnabled()) {

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

        return false;

    } finally {

        releaseResources(rs);
    }

}

From source file:com.glaf.core.util.DBUtils.java

@SuppressWarnings("resource")
public static boolean tableExists(Connection connection, String tableName) {
    DatabaseMetaData dbmd = null;
    ResultSet rs = null;// w ww  .  j  a v  a2 s .c  om
    try {
        dbmd = connection.getMetaData();
        rs = dbmd.getTables(null, null, null, new String[] { "TABLE" });
        while (rs.next()) {
            String table = rs.getString("TABLE_NAME");
            if (StringUtils.equalsIgnoreCase(tableName, table)) {
                return true;
            }
        }
        logger.info(tableName + " not exist.");
        return false;
    } catch (Exception ex) {
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
    }
}

From source file:org.alinous.plugin.derby.DerbyDataSource.java

public DataTable getDataTable(Object connectionHandle, String tableName) throws DataSourceException {
    DataTable dataTable = null;/*from  w w w.j a  v  a2 s . c  o  m*/
    Connection con = (Connection) connectionHandle;

    DatabaseMetaData metaData;
    ResultSet rsSet = null;
    try {
        metaData = con.getMetaData();
        rsSet = metaData.getTables(null, null, "%", new String[] { "TABLE" });
    } catch (SQLException e) {
        throw new DataSourceException(e);
    }

    try {
        while (rsSet.next()) {
            String tbl = rsSet.getString("TABLE_NAME");

            if (!tableName.equals(tbl.toUpperCase())) {
                continue;
            }
            // Create DataTable object
            dataTable = new DataTable();

            // Add column informations
            setupDataTableColumns(metaData, dataTable, tableName);

        }
    } catch (SQLException e) {
        throw new DataSourceException(e);
    } finally {
        try {
            rsSet.close();
        } catch (SQLException ignore) {
        }
    }

    return dataTable;
}

From source file:org.alinous.plugin.mysql.MySQLDataSource.java

public DataTable getDataTable(Object connectionHandle, String tableName) throws DataSourceException {
    DataTable dataTable = null;/*  ww w  .  j av a 2s  . c  o m*/
    Connection con = (Connection) connectionHandle;

    DatabaseMetaData metaData;
    ResultSet rsSet = null;
    try {
        metaData = con.getMetaData();
        rsSet = metaData.getTables(null, null, "%", new String[] { "TABLE" });
    } catch (SQLException e) {
        throw new DataSourceException(e);
    }

    try {
        while (rsSet.next()) {
            String tbl = rsSet.getString("TABLE_NAME");

            if (!tableName.toUpperCase().equals(tbl.toUpperCase())) {
                continue;
            }
            // Create DataTable object
            dataTable = new DataTable();

            // Add column informations
            setupDataTableColumns(metaData, dataTable, tableName);

        }
    } catch (SQLException e) {
        throw new DataSourceException(e);
    } finally {
        try {
            rsSet.close();
        } catch (SQLException ignore) {
        }
    }

    return dataTable;
}