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.nuxeo.ecm.core.storage.sql.Mapper.java

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);/* w w  w .  j a v  a  2s  .  c  o m*/
        // normalize to uppercase too
        tableNames.add(tableName.toUpperCase());
    }
    return tableNames;
}

From source file:org.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java

/**
 * This method creates a list of tables available in the DB.
 *
 * @return Table List of the DB/*from  ww  w  .j  a va 2  s  .c om*/
 * @throws ODataServiceFault
 */
private List<String> generateTableList() throws ODataServiceFault {
    List<String> tableList = new ArrayList<>();
    Connection connection = null;
    ResultSet rs = null;
    try {
        connection = initializeConnection();
        DatabaseMetaData meta = connection.getMetaData();
        rs = meta.getTables(null, null, null, new String[] { "TABLE" });
        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");
            tableList.add(tableName);
        }
        return tableList;
    } catch (SQLException e) {
        throw new ODataServiceFault(e, "Error in reading tables from the database. :" + e.getMessage());
    } finally {
        releaseResources(rs, null);
        releaseConnection(connection);
    }
}

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

protected List<TableEntry> getTablesForSchemaEntry(Connection aConnection, SchemaEntry aEntry)
        throws SQLException {
    List<TableEntry> theResult = new ArrayList<>();

    DatabaseMetaData theMetaData = aConnection.getMetaData();

    // Reverse engineer tables
    ResultSet theTablesResultSet;
    String theCatalogName = null;
    String theSchemaName = null;//from   w  ww  . ja  v  a2 s.  c  o  m
    if (aEntry != null) {
        theCatalogName = aEntry.getCatalogName();
        theSchemaName = aEntry.getSchemaName();
        theTablesResultSet = theMetaData.getTables(theCatalogName, theSchemaName, null, TableType.toArray());
    } else {
        theTablesResultSet = theMetaData.getTables(null, null, null, TableType.toArray());
    }

    while (theTablesResultSet.next()) {

        TableType theTableType = TableType.fromString(theTablesResultSet.getString("TABLE_TYPE"));
        String theTableName = theTablesResultSet.getString("TABLE_NAME");

        if (TableType.VIEW.equals(theTableType)) {
            if (isValidView(theTableName)) {
                TableEntry theEntry = new TableEntry(theCatalogName, theSchemaName, theTableName, theTableType);
                theResult.add(theEntry);
            }
        } else {
            if (isValidTable(theTableName)) {
                TableEntry theEntry = new TableEntry(theCatalogName, theSchemaName, theTableName, theTableType);
                theResult.add(theEntry);
            }
        }
    }

    theTablesResultSet.close();

    return theResult;
}

From source file:org.efaps.db.databases.AbstractDatabase.java

/**
 * The method tests, if a view with given name exists.
 *
 * @param _con sql connection/*from   w  ww. j a v  a 2 s.co m*/
 * @param _viewName name of view to test
 * @return <i>true</i> if view exists, otherwise <i>false</i>
 * @throws SQLException if the exist check failed
 */
public boolean existsView(final Connection _con, final String _viewName) throws SQLException {
    boolean ret = false;

    final DatabaseMetaData metaData = _con.getMetaData();

    // first test with lower case
    final ResultSet rs = metaData.getTables(null, null, _viewName.toLowerCase(), new String[] { "VIEW" });
    if (rs.next()) {
        ret = true;
    }
    rs.close();

    // then test with upper case
    if (!ret) {
        final ResultSet rsUC = metaData.getTables(null, null, _viewName.toUpperCase(), new String[] { "VIEW" });
        if (rsUC.next()) {
            ret = true;
        }
        rsUC.close();
    }

    return ret;
}

From source file:org.efaps.db.databases.AbstractDatabase.java

/**
 * The method tests, if a view with given name exists.
 *
 * @param _con sql connection//  ww w.ja  v  a  2 s  .  co m
 * @param _tableName name of table to test
 * @return <i>true</i> if SQL table exists, otherwise <i>false</i>
 * @throws SQLException if the exist check for the table failed
 */
public boolean existsTable(final Connection _con, final String _tableName) throws SQLException {
    boolean ret = false;

    final DatabaseMetaData metaData = _con.getMetaData();

    // first test with lower case
    final ResultSet rs = metaData.getTables(null, null, _tableName.toLowerCase(), new String[] { "TABLE" });
    if (rs.next()) {
        ret = true;
    }
    rs.close();

    // then test with upper case
    if (!ret) {
        final ResultSet rsUC = metaData.getTables(null, null, _tableName.toUpperCase(),
                new String[] { "TABLE" });
        if (rsUC.next()) {
            ret = true;
        }
        rsUC.close();
    }
    return ret;
}

From source file:org.talend.core.model.metadata.builder.database.manager.ExtractManager.java

protected void retrieveTables(DatabaseMetaData dbMetaData, String schema, List<IMetadataTable> medataTables,
        Set<String> availableTableTypes, List<String> tablesToFilter, int... limit) throws SQLException {
    ResultSet rsTables = null;/*from  w w  w . j a  v  a  2  s  . com*/
    if (EMPTY.equals(schema)) {
        schema = null; // if empty, same as null, no schema.
    }
    rsTables = dbMetaData.getTables(null, schema, null, availableTableTypes.toArray(new String[] {}));
    if (rsTables != null) {
        try {
            getMetadataTables(medataTables, rsTables, dbMetaData.supportsSchemasInTableDefinitions(),
                    tablesToFilter, limit);
        } finally {
            rsTables.close();
        }
    }
}

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

/**
 * Reverse engineer an existing table./*from   ww  w . j av  a  2 s.  c  o  m*/
 *
 * @param aModel     the model
 * @param aOptions     the options
 * @param aNotifier   the notifier
 * @param aTableEntry the table
 * @param aConnection the connection
 * 
 * @return a Map of former ModelProperties of model items in case they have been replaced during reverse engineering into an existing model
 * 
 * @throws SQLException   is thrown in case of an error
 * @throws ReverseEngineeringException is thrown in case of an error
 */
protected final Map<String, ModelProperties> reverseEngineerTable(Model aModel,
        ReverseEngineeringOptions aOptions, ReverseEngineeringNotifier aNotifier, TableEntry aTableEntry,
        Connection aConnection) throws SQLException, ReverseEngineeringException {
    Map<String, ModelProperties> theExistingModelItemProperties = null;
    aNotifier.notifyMessage(ERDesignerBundle.ENGINEERINGTABLE, aTableEntry.getTableName());

    DatabaseMetaData theMetaData = aConnection.getMetaData();

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

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

    while (theTablesResultSet.next()) {

        String theTableRemarks = theTablesResultSet.getString("REMARKS");

        Table theNewTable = new Table();

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

        if (!StringUtils.isEmpty(theTableRemarks)) {
            theNewTable.setComment(theTableRemarks);
        }

        // Reverse engineer attributes
        ResultSet theColumnsResultSet = theMetaData.getColumns(aTableEntry.getCatalogName(), theSchemaPattern,
                theTablePattern, null);
        while (theColumnsResultSet.next()) {

            String theColumnName = null;
            String theTypeName = null;
            Integer theSize = null;
            Integer theFraction = null;
            int theRadix = 0;
            int theNullable = 0;
            String theDefaultValue = null;
            String theColumnRemarks = null;

            try {
                theColumnName = theColumnsResultSet.getString("COLUMN_NAME");
            } catch (SQLException e) {
            }

            try {
                theTypeName = theColumnsResultSet.getString("TYPE_NAME");
            } catch (SQLException e) {
            }

            try {
                theSize = theColumnsResultSet.getInt("COLUMN_SIZE");
            } catch (SQLException e) {
            }

            try {
                theFraction = theColumnsResultSet.getInt("DECIMAL_DIGITS");
            } catch (SQLException e) {
            }

            try {
                theRadix = theColumnsResultSet.getInt("NUM_PREC_RADIX");
            } catch (SQLException e) {
            }

            try {
                theNullable = theColumnsResultSet.getInt("NULLABLE");
            } catch (SQLException e) {
            }

            try {
                theDefaultValue = theColumnsResultSet.getString("COLUMN_DEF");
                if (!StringUtils.isEmpty(theDefaultValue)) {
                    theDefaultValue = theDefaultValue.trim();
                }
            } catch (SQLException e) {
            }

            try {
                theColumnRemarks = theColumnsResultSet.getString("REMARKS");
            } catch (SQLException e) {
            }

            Attribute<Table> theAttribute = new Attribute<>();

            theAttribute.setName(dialect.getCastType().cast(theColumnName));
            if (!StringUtils.isEmpty(theColumnRemarks)) {
                theAttribute.setComment(theColumnRemarks);
            }

            // Search for the datatype in the domains, the dialect specific and the user defined datatypes
            DataType theDataType = aModel.getAvailableDataTypes()
                    .findByName(dialect.convertTypeNameToRealTypeName(theTypeName));

            if (theDataType == null) {
                throw new ReverseEngineeringException("Unknown data type " + theTypeName + " for "
                        + aTableEntry.getTableName() + "." + theColumnName);
            }

            boolean isNullable = true;

            switch (theNullable) {
            case DatabaseMetaData.columnNoNulls:
                isNullable = false;
                break;
            case DatabaseMetaData.columnNullable:
                isNullable = true;
                break;
            default:
                LOGGER.warn("Unknown nullability : " + theNullable + " for " + theColumnName + " of table "
                        + theNewTable.getName());
            }

            theAttribute.setDatatype(theDataType);
            theAttribute.setSize(theSize);
            theAttribute.setFraction(theFraction);
            theAttribute.setScale(theRadix);
            theAttribute.setDefaultValue(theDefaultValue);
            theAttribute.setNullable(isNullable);

            reverseEngineerAttribute(theAttribute, aTableEntry, aConnection);

            try {
                theNewTable.addAttribute(aModel, theAttribute);
            } catch (ElementAlreadyExistsException | ElementInvalidNameException e) {
                throw new ReverseEngineeringException(e.getMessage(), e);
            }
        }
        theColumnsResultSet.close();

        // Reverse engineer primary keys
        reverseEngineerPrimaryKey(aModel, aTableEntry, theMetaData, theNewTable);

        // Reverse engineer indexes
        try {
            reverseEngineerIndexes(aModel, aTableEntry, theMetaData, theNewTable, aNotifier);
        } catch (SQLException e) {
            // if there is an sql exception, just ignore it
        }

        // We are done here
        try {
            aModel.addTable(theNewTable);
        } catch (ElementAlreadyExistsException e1) {
            //this manages the reverse engineering into an existing model and cares only about the table names of the model that conflict with the new table names from the connection
            //TODO: also care about tables that are no longer part of the connection, but still exist in the local model. E.g. show a dialog and ask the user what to do (delete/keep)
            try {
                //buffer the properties (e.g. position in model) of the existing table and its relations (e.g. the offset of the title) that are going to be replaced
                Table theExistingTable = aModel.getTables().findByName(theNewTable.getName());
                RelationList theExistingRelations = aModel.getRelations().getAllRelataionsOf(theExistingTable);

                if (theExistingModelItemProperties == null) {
                    theExistingModelItemProperties = new HashMap<>();
                }

                //store former layouting data for the table and its relations in the old graph
                theExistingModelItemProperties.put(theExistingTable.getName(),
                        theExistingTable.getProperties());
                for (Relation anExistingRelation : theExistingRelations) {
                    theExistingModelItemProperties.put(anExistingRelation.getName(),
                            anExistingRelation.getProperties());
                }

                //remove old table and its relations
                aModel.removeTable(theExistingTable);

                //add the new table without relations
                aModel.addTable(theNewTable);
            } catch (ElementAlreadyExistsException | ElementInvalidNameException | VetoException e2) {
                throw new ReverseEngineeringException(e2.getMessage());
            }
        } catch (ElementInvalidNameException | VetoException e3) {
            throw new ReverseEngineeringException(e3.getMessage());
        }
    }

    theTablesResultSet.close();

    return theExistingModelItemProperties;
}

From source file:org.kuali.test.ui.components.sqlquerypanel.DatabasePanel.java

private void loadAvailableDatabaseTables() {
    List<TableData> tables = new ArrayList<TableData>();

    Connection conn = null;/*from  w  w w .j av  a2 s.  c om*/
    ResultSet res = null;

    try {
        // load any additional database info - this will give us user-friendly names
        loadAdditionalDbInfo();

        // this is the empty place holder for base table selection
        tables.add(new TableData());

        DatabaseConnection dbconn = Utils.findDatabaseConnectionByName(getMainframe().getConfiguration(),
                getPlatform().getDatabaseConnectionName());

        if (dbconn != null) {
            conn = Utils.getDatabaseConnection(getMainframe().getEncryptionPassword(), dbconn);
            DatabaseMetaData dmd = conn.getMetaData();
            res = dmd.getTables(null, dbconn.getSchema(), null, new String[] { "TABLE", "VIEW" });

            while (res.next()) {
                String tableName = res.getString(3);

                Table t = additionalDbInfo.get(tableName);

                if ((t != null) || !dbconn.getConfiguredTablesOnly()) {
                    if ((t != null) && dbconn.getConfiguredTablesOnly()) {
                        tables.add(new TableData(dbconn.getSchema(), tableName, t.getDisplayName()));
                    } else {
                        tables.add(new TableData(dbconn.getSchema(), tableName, tableName));
                    }
                }
            }
        }
    }

    catch (Exception ex) {
        UIUtils.showError(getMainframe(), "Database Connection Error",
                "An error occurred while attemption to connect to database - " + ex.toString());
    }

    finally {
        Utils.closeDatabaseResources(conn, null, res);
    }

    Collections.sort(tables);

    for (TableData td : tables) {
        tableDropdown.addItem(td);
    }
}

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

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

        List<String> tablesOnDatabase = 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();
        while (tablesIterator.hasNext()) {
            String tableName = tablesIterator.next();
            if (findTable(tableName, originalTables) != null) {
                // already present, remove table name from the list of tables that can be imported
                tablesIterator.remove();
            }
        }
        return tablesOnDatabase;

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

    }

}

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

public IDataSourceProxy loadSchemaForWeb(int scenarioNo, AccessConfiguration accessConfiguration,
        DBFragmentDescription dataDescription, IConnectionFactory dataSourceDB, boolean source)
        throws DAOException {
    this.dataDescription = dataDescription;
    INode root = null;//from  w ww .j  a v a  2  s  . c  o  m
    String catalog = null;
    String schemaName = accessConfiguration.getSchemaName();
    DatabaseMetaData databaseMetaData = null;
    Connection connection = dataSourceDB.getConnection(accessConfiguration);
    IDataSourceProxy dataSource = null;
    try {
        databaseMetaData = connection.getMetaData();
        catalog = connection.getCatalog();
        if (catalog == null) {
            catalog = accessConfiguration.getUri();
            if (logger.isDebugEnabled())
                logger.debug("Catalog is null. Catalog name will be: " + catalog);
        }
        root = this.createRootNode(catalog);

        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;
            }
            INode setTable = new SetNode(tableName);
            setTable.addChild(getTuple(databaseMetaData, catalog, schemaName, tableName));
            setTable.setRequired(false);
            setTable.setNotNull(true);
            root.addChild(setTable);
            addNode(tableName, setTable);
        }
        dataSource = new ConstantDataSourceProxy(new DataSource(SpicyEngineConstants.TYPE_RELATIONAL, root));
        dataSource.addAnnotation(SpicyEngineConstants.ACCESS_CONFIGURATION, accessConfiguration);
        for (Map.Entry<String, String> entry : changedColumnNames.entrySet()) {
            dataSource.putChangedValue(entry.getKey(), entry.getValue());
        }
        loadPrimaryKeys(dataSource, databaseMetaData, catalog, schemaName, source, null, scenarioNo, true);
        loadForeignKeys(dataSource, databaseMetaData, catalog, schemaName, source, scenarioNo);
    } catch (Throwable ex) {
        logger.error(ex);
        throw new DAOException(ex.getMessage());
    } finally {
        if (connection != null)
            dataSourceDB.close(connection);
    }
    return dataSource;
}