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:it.unibas.spicy.persistence.relational.DAORelational.java

private TupleNode getTuple(DatabaseMetaData databaseMetaData, String catalog, String schemaName,
        String tableName) throws SQLException {
    if (logger.isDebugEnabled())
        logger.debug("\nTable: " + tableName);
    TupleNode tupleNode = new TupleNode(tableName + TUPLE_SUFFIX);
    tupleNode.setRequired(false);//from   w  w  w  . j a v  a 2s  . c  o m
    tupleNode.setNotNull(true);
    tupleNode.setVirtual(true);
    addNode(tableName + TUPLE_SUFFIX, tupleNode);
    ResultSet columnsResultSet = databaseMetaData.getColumns(catalog, schemaName, tableName, null);
    String columns = "";
    while (columnsResultSet.next()) {
        String columnName = columnsResultSet.getString("COLUMN_NAME");
        //the "-" character is replaced since it cannot be accepted by JEP and MIMap
        if (columnName.contains("-")) {
            String oldColumnName = columnName;
            columnName = oldColumnName.replace("-", "_");
            changedColumnNames.put(tableName + "." + columnName.replaceAll("\"", ""),
                    oldColumnName.replaceAll("\"", ""));
        }
        columns += "\"" + columnName + "\"";
        String keyColumn = tableName + "." + columnName;
        String columnType = columnsResultSet.getString("TYPE_NAME");
        /////String typeOfColumn = Types.POSTGRES_STRING;
        columns += " " + columnType + ",";
        String isNullable = columnsResultSet.getString("IS_NULLABLE");
        if (!this.dataDescription.checkLoadAttribute(tableName, columnName)) {
            continue;
        }
        boolean isNull = false;
        if (isNullable.equalsIgnoreCase("YES")) {
            isNull = true;
        } else {
            //take out the last ',' character
            columns = columns.substring(0, columns.length() - 1);
            columns += " NOT NULL,";
        }
        INode columnNode = new AttributeNode(columnName);
        addNode(keyColumn, columnNode);
        columnNode.setNotNull(!isNull);
        String typeOfColumn = DAORelationalUtility.convertDBTypeToDataSourceType(columnType);
        columnNode.addChild(new LeafNode(typeOfColumn));
        tupleNode.addChild(columnNode);
        if (logger.isDebugEnabled())
            logger.debug("\n\tColumn Name: " + columnName + "(" + columnType + ") " + " type of column= "
                    + typeOfColumn + "[IS_Nullable: " + isNullable + "]");
    }
    //take out the last ',' character
    columns = columns.substring(0, columns.length() - 1);
    return tupleNode;
}

From source file:ca.sqlpower.matchmaker.address.AddressPool.java

/**
 * Build a Map of column meta data for the given table.
 * The Map's keys are the column names, in lower case, to match the constants in this class.
 * The Map's values are the column widths (as Integers).
 * //w  ww.  j a  va2 s  . c om
 * SQLExceptions are not propogated to the caller. However, if no column data
 * can be retreived, an empty Map is returned.
 * 
 */
private Map<String, Integer> getColumnMetaData(Logger engineLogger, final SQLTable table) {
    Map<String, Integer> columnMetaData = new HashMap<String, Integer>();
    Connection con = null;
    ResultSet columns = null;
    try {
        con = project.createResultTableConnection();
        DatabaseMetaData meta = con.getMetaData();
        columns = meta.getColumns(table.getCatalogName(), table.getSchemaName(), table.getName(), null);

        while (columns.next()) {
            String col = StringUtils.lowerCase(columns.getString("COLUMN_NAME"));
            int size = columns.getInt("COLUMN_SIZE");

            engineLogger.debug("Column: " + col + " Size: " + size);

            columnMetaData.put(col.toLowerCase(), Integer.valueOf(size));
        }
    } catch (SQLException e) {
        // Don't propogate exception, just log and keep rolling on.
        engineLogger.error("Error while retrieving column data", e);
    } finally {
        if (columns != null)
            try {
                columns.close();
            } catch (SQLException e) {
                engineLogger.error("Error while closing ResultSet", e);
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                engineLogger.error("Error while closing Connection", e);
            }
    }

    return Collections.unmodifiableMap(columnMetaData);
}

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

private void setupDataTableColumns(DatabaseMetaData metaData, DataTable dataTable, String tableName)
        throws SQLException {
    ResultSet trs = metaData.getColumns(null, null, tableName, null);
    while (trs.next()) {
        String columnName = trs.getString("COLUMN_NAME");
        String columnType = trs.getString("TYPE_NAME");

        DataField fld = new DataField();
        fld.setName(columnName);/* ww  w. ja  v a  2 s.com*/

        // setType
        if (columnType.toUpperCase().equals("INTEGER")) {
            fld.setType(DataField.TYPE_INTEGER);
        } else if (columnType.toUpperCase().equals("DOUBLE")) {
            fld.setType(DataField.TYPE_DOUBLE);
        } else {
            fld.setType(DataField.TYPE_STRING);
        }

        dataTable.addField(fld);
    }

}

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

/**
 * Read metadata about a table from the database.
 *
 * @param table/*from  w  ww  .  ja  v a2s  .  c  o  m*/
 *            The RDBMS table.
 * @return A map of information about the columns. The key is the name of
 *         the column, a String; the value is a ColumnInfo object.
 * @exception SQLException
 *                If there is a problem retrieving information from the
 *                RDBMS.
 */
@Mock
private static Map<String, ColumnInfo> retrieveColumnInfo(String table) throws SQLException {
    Connection connection = null;
    ResultSet pkcolumns = null;
    ResultSet columns = null;

    try {
        String schema = ConfigurationManager.getProperty("db.schema");
        String catalog = null;

        int dotIndex = table.indexOf(".");
        if (dotIndex > 0) {
            catalog = table.substring(0, dotIndex);
            table = table.substring(dotIndex + 1, table.length());
            log.warn("catalog: " + catalog);
            log.warn("table: " + table);
        }

        connection = getConnection();

        DatabaseMetaData metadata = connection.getMetaData();
        Map<String, ColumnInfo> results = new HashMap<String, ColumnInfo>();

        //H2 database has no limit or is unknown, so the result is 0. We
        //have to comment to avoid errors
        //int max = metadata.getMaxTableNameLength();
        //String tname = (table.length() >= max) ? table
        //        .substring(0, max - 1) : table;
        pkcolumns = metadata.getPrimaryKeys(catalog, schema, table);

        Set<String> pks = new HashSet<String>();

        while (pkcolumns.next())
            pks.add(pkcolumns.getString(4));

        columns = metadata.getColumns(catalog, schema, table, null);

        while (columns.next()) {
            String column = columns.getString(4);
            ColumnInfo cinfo = new ColumnInfo();
            cinfo.setName(column);
            cinfo.setType((int) columns.getShort(5));

            if (pks.contains(column)) {
                cinfo.setIsPrimaryKey(true);
            }

            results.put(column, cinfo);
        }

        return results;
    } finally {
        if (pkcolumns != null) {
            try {
                pkcolumns.close();
            } catch (SQLException sqle) {
            }
        }

        if (columns != null) {
            try {
                columns.close();
            } catch (SQLException sqle) {
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException sqle) {
            }
        }
    }
}

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

private void setupDataTableColumns(DatabaseMetaData metaData, DataTable dataTable, String tableName)
        throws SQLException {
    ResultSet trs = metaData.getColumns(null, null, tableName, null);
    while (trs.next()) {
        String columnName = trs.getString("COLUMN_NAME");
        String columnType = trs.getString("TYPE_NAME");

        DataField fld = new DataField();
        fld.setName(columnName);//www .  j  a  v  a 2 s . co m

        // setType for MYSQL
        if (columnType.toUpperCase().equals("VARCHAR") || columnType.toUpperCase().equals("CHAR")) {
            fld.setType(DataField.TYPE_STRING);
        } else if (columnType.toUpperCase().equals("TEXT")) {
            fld.setType(DataField.TYPE_TEXT_STRING);
        } else if (columnType.toUpperCase().equals("TINYINT") || columnType.toUpperCase().equals("INTEGER")
                || columnType.toUpperCase().equals("BIGINT")) {
            fld.setType(DataField.TYPE_INTEGER);
        } else if (columnType.toUpperCase().equals("TIMESTAMP")
                || columnType.toUpperCase().equals("DATETIME")) {
            fld.setType(DataField.TYPE_TIMESTAMP);
        } else if (columnType.toUpperCase().equals("DATE")) {
            fld.setType(DataField.TYPE_DATE);
        } else if (columnType.toUpperCase().equals("FLOAT") || columnType.toUpperCase().equals("DOUBLE")) {
            fld.setType(DataField.TYPE_DOUBLE);
        } else {
            fld.setType(DataField.TYPE_STRING);
        }

        dataTable.addField(fld);
    }

    // PrimaryKeys
    ResultSet primarysRs = metaData.getPrimaryKeys(null, null, tableName);
    while (primarysRs.next()) {
        String columnName = primarysRs.getString("COLUMN_NAME");

        DataField fld = dataTable.getDataField(columnName);
        fld.setPrimary(true);
        dataTable.addPrimaryKey(fld.getName());
    }
    primarysRs.close();
}

From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectPostgreSQL.java

protected boolean getCompatibilityFulltextTable(DatabaseMetaData metadata) throws SQLException {
    ResultSet rs = metadata.getColumns(null, null, Model.FULLTEXT_TABLE_NAME, "%");
    while (rs.next()) {
        // COLUMN_NAME=fulltext DATA_TYPE=1111 TYPE_NAME=tsvector
        String columnName = rs.getString("COLUMN_NAME");
        if (Model.FULLTEXT_FULLTEXT_KEY.equals(columnName)) {
            String typeName = rs.getString("TYPE_NAME");
            return "tsvector".equals(typeName);
        }/*from w w w  .  j av a  2s . com*/
    }
    return false;
}

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

private TupleNode getTuple(DatabaseMetaData databaseMetaData, String catalog, String schemaName,
        String tableName, boolean source, Statement statement, int scenarioNo) throws SQLException {
    if (logger.isDebugEnabled())
        logger.debug("\nTable: " + tableName);
    TupleNode tupleNode = new TupleNode(tableName + TUPLE_SUFFIX);
    tupleNode.setRequired(false);/*from   w  w w.  j  a  v  a 2 s. c  o  m*/
    tupleNode.setNotNull(true);
    tupleNode.setVirtual(true);
    addNode(tableName + TUPLE_SUFFIX, tupleNode);
    ResultSet columnsResultSet = databaseMetaData.getColumns(catalog, schemaName, tableName, null);
    String columns = "";
    while (columnsResultSet.next()) {
        String columnName = columnsResultSet.getString("COLUMN_NAME");
        //the "-" character is replaced since it cannot be accepted by JEP and MIMap
        if (columnName.contains("-")) {
            String oldColumnName = columnName;
            columnName = oldColumnName.replace("-", "_");
            changedColumnNames.put(tableName + "." + columnName.replaceAll("\"", ""),
                    oldColumnName.replaceAll("\"", ""));
        }
        columns += "\"" + columnName + "\"";
        String keyColumn = tableName + "." + columnName;
        String columnType = columnsResultSet.getString("TYPE_NAME");
        /////String typeOfColumn = Types.POSTGRES_STRING;
        columns += " " + columnType + ",";
        String isNullable = columnsResultSet.getString("IS_NULLABLE");
        if (!this.dataDescription.checkLoadAttribute(tableName, columnName)) {
            continue;
        }
        boolean isNull = false;
        if (isNullable.equalsIgnoreCase("YES")) {
            isNull = true;
        } else {
            //take out the last ',' character
            columns = columns.substring(0, columns.length() - 1);
            columns += " NOT NULL,";
        }
        INode columnNode = new AttributeNode(columnName);
        addNode(keyColumn, columnNode);
        columnNode.setNotNull(!isNull);
        String typeOfColumn = DAORelationalUtility.convertDBTypeToDataSourceType(columnType);
        columnNode.addChild(new LeafNode(typeOfColumn));
        tupleNode.addChild(columnNode);
        //            if(tableName.contains("encounter_mapping")) System.out.println("\n\tColumn Name: " + columnName + "(" + columnType + ") " + " type of column= " + typeOfColumn + "[IS_Nullable: " + isNullable + "]");
        if (logger.isDebugEnabled())
            logger.debug("\n\tColumn Name: " + columnName + "(" + columnType + ") " + " type of column= "
                    + typeOfColumn + "[IS_Nullable: " + isNullable + "]");
    }
    //take out the last ',' character
    columns = columns.substring(0, columns.length() - 1);
    //postgres create table
    String table;
    if (source) {
        table = SpicyEngineConstants.SOURCE_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\"";
    } else {
        table = SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\"";
    }
    statement.executeUpdate("drop table if exists " + table);
    statement.executeUpdate("create table " + table + " (" + columns + ")");

    //        if(tableName.contains("encounter_mapping")) {
    //            System.out.println("-----------------------------------------------\n"
    //                + "create table "+ table +" ("+ columns+ ")"
    //                + "\n-----------------------------------------------");
    //            
    //            System.out.println("Running query");
    //        
    //            ResultSet queryResult = statement.executeQuery("SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE " 
    //                + "table_name = '"+ tableName  + "' ORDER BY ordinal_position;");
    //
    //            System.out.println("Query run, waiting for results");
    //            while (queryResult.next()) {
    //                System.out.println(queryResult.getString("column_name") + "\t" + 
    //                        queryResult.getString("data_type") + "\t" + queryResult.getString("is_nullable"));
    //            }
    //            
    //            System.out.println("END of results");
    //        }
    //        

    return tupleNode;
}

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

/**
 * Returns the column names of the specified database object.
 *
 * @param catalog the table catalog name
 * @param schema the table schema name//  w w w.  j a  v a 2 s  .co  m
 * @param table the database object name
 * @return the column names
 */
public List<String> getColumnNames(String catalog, String schema, String table) throws DataSourceException {

    ResultSet rs = null;
    List<String> columns = new ArrayList<String>();

    try {
        String _catalog = getCatalogNameForQueries(catalog);
        String _schema = getSchemaNameForQueries(schema);
        DatabaseMetaData dmd = getDatabaseMetaData();

        // retrieve the base column info
        rs = dmd.getColumns(_catalog, _schema, table, null);
        while (rs.next()) {

            columns.add(rs.getString(4));
        }

        return columns;

    } catch (SQLException e) {

        if (Log.isDebugEnabled()) {

            Log.error("Error retrieving column data for table " + table + " using connection "
                    + getDatabaseConnection(), e);
        }

        return columns;

    } finally {

        releaseResources(rs);
    }

}

From source file:edu.ku.brc.dbsupport.MySQLDMBSUserMgr.java

@Override
public boolean doesFieldExistInTable(final String tableName, final String fieldName) {
    try {/* ww  w.  ja v a2s  .c o m*/
        DatabaseMetaData mdm = connection.getMetaData();
        ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
        while (rs.next()) {
            String dbFieldName = rs.getString("COLUMN_NAME");
            if (dbFieldName.equals(fieldName)) {
                rs.close();
                return true;
            }
        }
        rs.close();

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return false;
}

From source file:com.oltpbenchmark.catalog.Catalog.java

/**
 * Construct the set of Table objects from a given Connection handle
 * @param conn//from  w w w .  j  av a2  s.  c o m
 * @return
 * @throws SQLException
 * @see http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html
 */
protected void init() throws SQLException {
    // Load the database's DDL
    this.benchmark.createDatabase(DB_TYPE, this.conn);

    // TableName -> ColumnName -> <FkeyTable, FKeyColumn>
    Map<String, Map<String, Pair<String, String>>> foreignKeys = new HashMap<String, Map<String, Pair<String, String>>>();

    DatabaseMetaData md = conn.getMetaData();
    ResultSet table_rs = md.getTables(null, null, null, new String[] { "TABLE" });
    while (table_rs.next()) {
        if (LOG.isDebugEnabled())
            LOG.debug(SQLUtil.debug(table_rs));
        String internal_table_name = table_rs.getString(3);
        String table_name = origTableNames.get(table_rs.getString(3).toUpperCase());
        assert (table_name != null) : "Unexpected table '" + table_rs.getString(3) + "' from catalog";
        LOG.debug(String.format("ORIG:%s -> CATALOG:%s", internal_table_name, table_name));

        String table_type = table_rs.getString(4);
        if (table_type.equalsIgnoreCase("TABLE") == false)
            continue;
        Table catalog_tbl = new Table(table_name);

        // COLUMNS
        if (LOG.isDebugEnabled())
            LOG.debug("Retrieving COLUMN information for " + table_name);
        ResultSet col_rs = md.getColumns(null, null, internal_table_name, null);
        while (col_rs.next()) {
            if (LOG.isTraceEnabled())
                LOG.trace(SQLUtil.debug(col_rs));
            String col_name = col_rs.getString(4);
            int col_type = col_rs.getInt(5);
            String col_typename = col_rs.getString(6);
            Integer col_size = col_rs.getInt(7);
            String col_defaultValue = col_rs.getString(13);
            boolean col_nullable = col_rs.getString(18).equalsIgnoreCase("YES");
            boolean col_autoinc = false; // FIXME col_rs.getString(22).toUpperCase().equals("YES");

            Column catalog_col = new Column(catalog_tbl, col_name, col_type, col_typename, col_size);
            catalog_col.setDefaultValue(col_defaultValue);
            catalog_col.setAutoincrement(col_autoinc);
            catalog_col.setNullable(col_nullable);
            // FIXME col_catalog.setSigned();

            if (LOG.isDebugEnabled())
                LOG.debug(
                        String.format("Adding %s.%s [%s / %d]", table_name, col_name, col_typename, col_type));
            catalog_tbl.addColumn(catalog_col);
        } // WHILE
        col_rs.close();

        // PRIMARY KEYS
        if (LOG.isDebugEnabled())
            LOG.debug("Retrieving PRIMARY KEY information for " + table_name);
        ResultSet pkey_rs = md.getPrimaryKeys(null, null, internal_table_name);
        SortedMap<Integer, String> pkey_cols = new TreeMap<Integer, String>();
        while (pkey_rs.next()) {
            String col_name = pkey_rs.getString(4);
            assert (catalog_tbl.getColumnByName(col_name) != null) : String
                    .format("Unexpected primary key column %s.%s", table_name, col_name);
            int col_idx = pkey_rs.getShort(5);
            // HACK: SQLite doesn't return the KEY_SEQ, so if we get back
            //       a zero for this value, then we'll just length of the pkey_cols map
            if (col_idx == 0)
                col_idx = pkey_cols.size();
            LOG.debug(String.format("PKEY[%02d]: %s.%s", col_idx, table_name, col_name));
            assert (pkey_cols.containsKey(col_idx) == false);
            pkey_cols.put(col_idx, col_name);
        } // WHILE
        pkey_rs.close();
        catalog_tbl.setPrimaryKeyColumns(pkey_cols.values());

        // INDEXES
        if (LOG.isDebugEnabled())
            LOG.debug("Retrieving INDEX information for " + table_name);
        ResultSet idx_rs = md.getIndexInfo(null, null, internal_table_name, false, false);
        while (idx_rs.next()) {
            if (LOG.isDebugEnabled())
                LOG.debug(SQLUtil.debug(idx_rs));
            boolean idx_unique = (idx_rs.getBoolean(4) == false);
            String idx_name = idx_rs.getString(6);
            int idx_type = idx_rs.getShort(7);
            int idx_col_pos = idx_rs.getInt(8) - 1;
            String idx_col_name = idx_rs.getString(9);
            String sort = idx_rs.getString(10);
            SortDirectionType idx_direction;
            if (sort != null) {
                idx_direction = sort.equalsIgnoreCase("A") ? SortDirectionType.ASC : SortDirectionType.DESC;
            } else
                idx_direction = null;

            Index catalog_idx = catalog_tbl.getIndex(idx_name);
            if (catalog_idx == null) {
                catalog_idx = new Index(catalog_tbl, idx_name, idx_type, idx_unique);
                catalog_tbl.addIndex(catalog_idx);
            }
            assert (catalog_idx != null);
            catalog_idx.addColumn(idx_col_name, idx_direction, idx_col_pos);
        } // WHILE
        idx_rs.close();

        // FOREIGN KEYS
        if (LOG.isDebugEnabled())
            LOG.debug("Retrieving FOREIGN KEY information for " + table_name);
        ResultSet fk_rs = md.getImportedKeys(null, null, internal_table_name);
        foreignKeys.put(table_name, new HashMap<String, Pair<String, String>>());
        while (fk_rs.next()) {
            if (LOG.isDebugEnabled())
                LOG.debug(table_name + " => " + SQLUtil.debug(fk_rs));
            assert (fk_rs.getString(7).equalsIgnoreCase(table_name));

            String colName = fk_rs.getString(8);
            String fk_tableName = origTableNames.get(fk_rs.getString(3).toUpperCase());
            String fk_colName = fk_rs.getString(4);

            foreignKeys.get(table_name).put(colName, Pair.of(fk_tableName, fk_colName));
        } // WHILE
        fk_rs.close();

        tables.put(table_name, catalog_tbl);
    } // WHILE
    table_rs.close();

    // FOREIGN KEYS
    if (LOG.isDebugEnabled())
        LOG.debug("Foreign Key Mappings:\n" + StringUtil.formatMaps(foreignKeys));
    for (Table catalog_tbl : tables.values()) {
        Map<String, Pair<String, String>> fk = foreignKeys.get(catalog_tbl.getName());
        for (Entry<String, Pair<String, String>> e : fk.entrySet()) {
            String colName = e.getKey();
            Column catalog_col = catalog_tbl.getColumnByName(colName);
            assert (catalog_col != null);

            Pair<String, String> fkey = e.getValue();
            assert (fkey != null);

            Table fkey_tbl = tables.get(fkey.first);
            if (fkey_tbl == null) {
                throw new RuntimeException("Unexpected foreign key parent table " + fkey);
            }
            Column fkey_col = fkey_tbl.getColumnByName(fkey.second);
            if (fkey_col == null) {
                throw new RuntimeException("Unexpected foreign key parent column " + fkey);
            }

            if (LOG.isDebugEnabled())
                LOG.debug(catalog_col.fullName() + " -> " + fkey_col.fullName());
            catalog_col.setForeignKey(fkey_col);
        } // FOR
    } // FOR

    return;
}