Example usage for java.sql DatabaseMetaData getColumns

List of usage examples for java.sql DatabaseMetaData getColumns

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. getColumns

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getColumns.

Prototype

ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
        throws SQLException;

Source Link

Document

Retrieves a description of table columns available in the specified catalog.

Usage

From source file:com.idega.block.article.data.CategoryBugRemover.java

public boolean isBadColunmsExist() throws SQLException {
    Boolean isCategoriesImported = getApplication().getSettings()
            .getBoolean(ArticlesImporter.CATEGORIES_IMPORTED_APP_PROP, false);
    Boolean isArticlesImported = getApplication().getSettings()
            .getBoolean(ArticlesImporter.ARTICLES_IMPORTED_APP_PROP, false);

    if (isArticlesImported || isCategoriesImported) {
        return Boolean.TRUE;
    }//from  w w w.j a  v a  2s. c o m

    Connection conn = null;
    try {
        conn = SimpleQuerier.getConnection();
        DatabaseMetaData meta = conn.getMetaData();
        ResultSet columnsInfo = meta.getColumns(null, null, "IC_CATEGORY", null);
        while (columnsInfo.next()) {
            String columnName = columnsInfo.getString("COLUMN_NAME");
            if (columnName.equalsIgnoreCase("ID")) {
                return Boolean.TRUE;
            }

            if (columnName.equalsIgnoreCase("CATEGORY")) {
                return Boolean.TRUE;
            }

            if (columnName.equalsIgnoreCase("HASHCODE")) {
                return Boolean.TRUE;
            }
        }
    } finally {
        if (conn != null)
            conn.close();
    }

    return Boolean.FALSE;
}

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;/*from ww  w .  j a  va  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:com.bluexml.side.Integration.alfresco.sql.synchronization.dialects.CreateTableStatement.java

public TableStatus checkStatus(Connection connection) {

    TableStatus status = TableStatus.EXISTS_MATCHED;
    ResultSet rs = null;//from   ww w.j a  v a 2  s.  c  o m

    try {
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        rs = databaseMetaData.getColumns(null, null, tableName, "%");

        if (!rs.next()) {
            status = TableStatus.NOT_EXISTS;
        } else {
            if (logger.isDebugEnabled())
                logger.debug("Checking table '" + tableName + "'");

            Map<String, Integer> tableColumns = new LinkedHashMap<String, Integer>();
            do {
                String columnName = rs.getString("COLUMN_NAME");
                Integer dataType = rs.getInt("DATA_TYPE");
                if (logger.isDebugEnabled()) {
                    String dataTypeDepName = rs.getString("TYPE_NAME");
                    logger.debug("Column '" + columnName + "' with type '" + dataTypeDepName + "'(" + dataType
                            + ")");
                }
                tableColumns.put(columnName, dataType);
            } while (rs.next());
            rs.close();
            // TODO : Implement type checking to return EXIST_SIMILAR if types are compatible

            Set<String> propertySet = columns.keySet();
            propertySet.removeAll(tableColumns.keySet());

            if (!propertySet.isEmpty()) {
                status = TableStatus.EXISTS_UNMATCHED;
            }

            if (customActionManager != null) {
                status = customActionManager.doInSchemaChecking(tableColumns, status, tableType);
            } else {
                if (logger.isDebugEnabled())
                    logger.debug(
                            "Cannot execute any custom checking since no custom action manager has been defined on create statement for table '"
                                    + tableName + "'");
            }
        }

    } catch (SQLException e) {
        logger.error("Cannot get meta-data for checking table status");
        logger.debug(e);
        return TableStatus.NOT_CHECKABLE;
    }

    if (logger.isDebugEnabled())
        logger.debug("Checking table output status '" + tableName + "': " + status.name());

    return status;

}

From source file:org.talend.components.snowflake.runtime.SnowflakeSourceOrSink.java

protected Schema getSchema(RuntimeContainer container, Connection connection, String tableName)
        throws IOException {
    Schema tableSchema = null;// ww  w  .  j a  v a2 s  .c o  m

    SnowflakeConnectionProperties connProps = getEffectiveConnectionProperties(container);
    try {
        DatabaseMetaData metaData = connection.getMetaData();

        ResultSet resultSet = metaData.getColumns(getCatalog(connProps), getDbSchema(connProps), tableName,
                null);
        tableSchema = getSnowflakeAvroRegistry().inferSchema(resultSet);
        if (tableSchema == null) {
            throw new IOException(i18nMessages.getMessage("error.tableNotFound", tableName));
        }

        // Update the schema with Primary Key details
        // FIXME - move this into the inferSchema stuff
        ResultSet keysIter = metaData.getPrimaryKeys(getCatalog(connProps), getDbSchema(connProps), tableName);

        List<String> pkColumns = new ArrayList<>(); // List of Primary Key columns for this table
        while (keysIter.next()) {
            pkColumns.add(keysIter.getString("COLUMN_NAME"));
        }

        for (Field f : tableSchema.getFields()) {
            if (pkColumns.contains(f.name())) {
                f.addProp(SchemaConstants.TALEND_COLUMN_IS_KEY, "true");
            }
        }

    } catch (SQLException se) {
        throw new IOException(se);
    }

    return tableSchema;

}

From source file:org.unitils.core.dbsupport.DerbyDbSupport.java

/**
 * Returns the names of all columns that have a 'not-null' constraint on them.
 * <p/>/* w  ww . j a v a 2s  . co  m*/
 * This info is not available in the Derby sys tables. The database meta data is used instead to retrieve it.
 *
 * @param tableName The table, not null
 * @return The set of column names, not null
 */
protected Set<String> getNotNullColummnNames(String tableName) {
    Connection connection = null;
    ResultSet resultSet = null;
    try {
        connection = getSQLHandler().getDataSource().getConnection();
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        resultSet = databaseMetaData.getColumns(null, getSchemaName(), tableName, "%");
        Set<String> result = new HashSet<String>();
        while (resultSet.next()) {
            if (resultSet.getInt(11) == DatabaseMetaData.columnNoNulls) { // NULLABLE
                result.add(resultSet.getString(4)); //COLUMN_NAME
            }
        }
        return result;
    } catch (SQLException e) {
        throw new UnitilsException("Error while querying for Derby primary keys for table name: " + tableName,
                e);
    } finally {
        closeQuietly(connection, null, resultSet);
    }
}

From source file:org.apache.kylin.source.jdbc.JdbcExplorer.java

@Override
public ColumnDesc[] evalQueryMetadata(String query) {
    if (StringUtils.isEmpty(query)) {
        throw new RuntimeException("Evaluate query shall not be empty.");
    }//from  w w  w  .j  a  va2s .  c  o m

    KylinConfig config = KylinConfig.getInstanceFromEnv();
    String tmpDatabase = config.getHiveDatabaseForIntermediateTable();
    String tmpView = tmpDatabase + ".kylin_eval_query_"
            + UUID.nameUUIDFromBytes(query.getBytes(StandardCharsets.UTF_8)).toString().replaceAll("-", "");

    String dropViewSql = "DROP VIEW IF EXISTS " + tmpView;
    String evalViewSql = "CREATE VIEW " + tmpView + " as " + query;

    Connection con = null;
    ResultSet rs = null;
    try {
        logger.debug("Removing duplicate view {}", tmpView);
        executeSQL(dropViewSql);
        logger.debug("Creating view {} for query: {}", tmpView, query);
        executeSQL(evalViewSql);
        logger.debug("Evaluating query columns' metadata");
        con = SqlUtil.getConnection(dbconf);
        DatabaseMetaData dbmd = con.getMetaData();
        rs = dbmd.getColumns(null, tmpDatabase, tmpView, null);
        ColumnDesc[] result = extractColumnFromMeta(rs);
        return result;
    } catch (SQLException e) {
        throw new RuntimeException("Cannot evaluate metadata of query: " + query, e);
    } finally {
        DBUtils.closeQuietly(con);
        DBUtils.closeQuietly(rs);
        try {
            logger.debug("Cleaning up temp view.");
            executeSQL(dropViewSql);
        } catch (SQLException e) {
            logger.warn("Failed to clean up temp view of query: {}", query, e);
        }
    }
}

From source file:edu.umass.cs.gnsclient.client.util.keystorage.SimpleKeyStore.java

private void showAllTables() {
    try {// ww  w  . j a  va 2 s  .c om
        DatabaseMetaData meta = conn.getMetaData();
        ResultSet resultSet = meta.getColumns(null, null, null, null);
        while (resultSet.next()) {
            if (!resultSet.getString("TABLE_NAME").startsWith("SYS")) {
                GNSClientConfig.getLogger().log(Level.FINE, "TABLE: {0}", resultSet.getString("TABLE_NAME"));
            }
        }
    } catch (SQLException e) {
        DerbyControl.printSQLException(e);
    }
}

From source file:org.jtester.module.database.support.DerbyDbSupport.java

/**
 * Returns the names of all columns that have a 'not-null' constraint on
 * them./*from   www  .  j  a  va  2 s.  c  o  m*/
 * <p/>
 * This info is not available in the Derby sys tables. The database meta
 * data is used instead to retrieve it.
 * 
 * @param tableName
 *            The table, not null
 * @return The set of column names, not null
 */
protected Set<String> getNotNullColummnNames(String tableName) {
    Connection connection = null;
    ResultSet resultSet = null;
    try {
        connection = getSQLHandler().getDataSource().getConnection();
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        resultSet = databaseMetaData.getColumns(null, getSchemaName(), tableName, "%");
        Set<String> result = new HashSet<String>();
        while (resultSet.next()) {
            if (resultSet.getInt(11) == DatabaseMetaData.columnNoNulls) { // NULLABLE
                result.add(resultSet.getString(4)); // COLUMN_NAME
            }
        }
        return result;
    } catch (SQLException e) {
        throw new JTesterException("Error while querying for Derby primary keys for table name: " + tableName,
                e);
    } finally {
        closeQuietly(connection, null, resultSet);
    }
}

From source file:com.emr.schemas.ForeignDataMover.java

private List getTableColumns(String tableName, Connection con) {
    List columns = new ArrayList();
    try {// w w w.jav a 2  s  .  c o m
        DatabaseMetaData dbmd = con.getMetaData();
        ResultSet rs = dbmd.getColumns(null, null, tableName, "%");
        while (rs.next()) {
            String colName = rs.getString(4);
            columns.add(colName);
        }
    } catch (SQLException e) {
        String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e);
        JOptionPane.showMessageDialog(this,
                "Could not fetch Tables for the Database. Error Details: " + stacktrace, "Table Names Error",
                JOptionPane.ERROR_MESSAGE);
    }
    return columns;
}

From source file:org.dspace.storage.rdbms.DatabaseUtils.java

/**
 * Determine if a particular database column exists in our database
 *
 * @param connection//w ww  .j ava2s .  c  o m
 *          Current Database Connection
 * @param tableName
 *          The name of the table
 * @param columnName
 *          The name of the column in the table
 * @return true if column of that name exists, false otherwise
 */
public static boolean tableColumnExists(Connection connection, String tableName, String columnName) {
    boolean exists = false;
    ResultSet results = null;

    try {
        // Get the name of the Schema that the DSpace Database is using
        // (That way we can search the right schema)
        String schema = getSchemaName(connection);

        // Canonicalize everything to the proper case based on DB type
        schema = canonicalize(connection, schema);
        tableName = canonicalize(connection, tableName);
        columnName = canonicalize(connection, columnName);

        // Get information about our database.
        DatabaseMetaData meta = connection.getMetaData();

        // Search for a column of that name in the specified table & schema
        results = meta.getColumns(null, schema, tableName, columnName);
        if (results != null && results.next()) {
            exists = true;
        }
    } catch (SQLException e) {
        log.error("Error attempting to determine if column " + columnName + " exists", e);
    } finally {
        try {
            // ensure the ResultSet gets closed
            if (results != null && !results.isClosed())
                results.close();
        } catch (SQLException e) {
            // ignore it
        }
    }

    return exists;
}