Example usage for java.sql DatabaseMetaData getColumns

List of usage examples for java.sql DatabaseMetaData 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:org.nuxeo.ecm.core.storage.sql.db.H2Fulltext.java

private static int getPrimaryKeyType(DatabaseMetaData meta, String schema, String table) throws SQLException {
    // find primary key name
    String primaryKeyName = null;
    try (ResultSet rs = meta.getPrimaryKeys(null, schema, table)) {
        while (rs.next()) {
            if (primaryKeyName != null) {
                throw new SQLException("Can only index primary keys on one column for " + schema + '.' + table);
            }/*from  w ww .  j  a va  2s . c  om*/
            primaryKeyName = rs.getString("COLUMN_NAME");
        }
        if (primaryKeyName == null) {
            throw new SQLException("No primary key for " + schema + '.' + table);
        }
    }
    // find primary key type
    try (ResultSet rs = meta.getColumns(null, schema, table, primaryKeyName)) {
        if (!rs.next()) {
            throw new SQLException("Could not find primary key");
        }
        return rs.getInt("DATA_TYPE");
    }
}

From source file:databaseadapter.GenerateMojo.java

protected Map<Table, List<Column>> collectColumns(Collection<Table> tables) throws MojoExecutionException {
    ResultSet rscolumns = null;//from ww  w  .  jav a2 s  . c  o  m
    try {
        Map<Table, List<Column>> map = new LinkedHashMap<Table, List<Column>>();
        DatabaseMetaData metaData = connection.getMetaData();
        rscolumns = metaData.getColumns(null, schemaPattern, "%", "%");
        while (rscolumns.next()) {
            String tableName = rscolumns.getString("TABLE_NAME");
            Table table = find(tables, tableName);
            if (table == null)
                continue;

            List<Column> columns = map.get(table);
            if (columns == null) {
                columns = new LinkedList<Column>();
                map.put(table, columns);
            }

            String columnName = rscolumns.getString("COLUMN_NAME");
            int dataType = rscolumns.getInt("DATA_TYPE");
            int columnSize = rscolumns.getInt("COLUMN_SIZE");
            int decimalDigits = rscolumns.getInt("DECIMAL_DIGITS");
            int nullable = rscolumns.getInt("NULLABLE");
            String remarks = rscolumns.getString("REMARKS");

            Column column = new Column(columnName, dataType, columnSize, decimalDigits,
                    nullable == DatabaseMetaData.columnNullable, remarks);
            columns.add(column);
        }

        return map;
    } catch (SQLException e) {
        throw new MojoExecutionException("Unable to generate database adapter due to a '"
                + e.getClass().getName() + "' with message '" + e.getMessage() + "'", e);
    } finally {
        if (rscolumns != null) {
            try {
                rscolumns.close();
            } catch (SQLException ignore) {
            }
        }
    }
}

From source file:org.rhq.plugins.postgres.PostgresTableComponent.java

public Configuration loadResourceConfiguration() throws Exception {
    Configuration config = new Configuration();
    config.put(new PropertySimple("schemaName",
            resourceContext.getPluginConfiguration().getSimple("schemaName").getStringValue()));
    config.put(new PropertySimple("tableName",
            resourceContext.getPluginConfiguration().getSimple("tableName").getStringValue()));

    Connection connection = null;
    ResultSet columns;//from  ww w. j av a 2s . c om
    try {
        connection = this.resourceContext.getParentResourceComponent().getConnection();
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        columns = databaseMetaData.getColumns("", getSchemaNameFromContext(resourceContext),
                getTableNameFromContext(resourceContext), "");
        PropertyList columnList = new PropertyList("columns");

        while (columns.next()) {
            PropertyMap col = new PropertyMap("columnDefinition");

            col.put(new PropertySimple("columnName", columns.getString("COLUMN_NAME")));
            col.put(new PropertySimple("columnType", columns.getString("TYPE_NAME")));
            col.put(new PropertySimple("columnLength", columns.getInt("COLUMN_SIZE")));
            col.put(new PropertySimple("columnPrecision", columns.getInt("DECIMAL_DIGITS")));
            col.put(new PropertySimple("columnDefault", columns.getString("COLUMN_DEF")));
            col.put(new PropertySimple("columnNullable",
                    Boolean.valueOf(isNullableToBoolean(columns.getInt("NULLABLE")))));

            columnList.add(col);
        }

        config.put(columnList);
    } finally {
        safeClose(connection);
    }

    return config;
}

From source file:org.apache.hadoop.chukwa.database.Macro.java

public String computeMacro(String macro) throws SQLException {
    Pattern p = Pattern.compile("past_(.*)_minutes");
    Matcher matcher = p.matcher(macro);
    if (macro.indexOf("avg(") == 0 || macro.indexOf("group_avg(") == 0 || macro.indexOf("sum(") == 0) {
        String meta = "";
        String[] table = null;/*  w w w.  j a v a  2  s .c o m*/
        if (forCharting) {
            table = dbc.findTableNameForCharts(macro.substring(macro.indexOf("(") + 1, macro.indexOf(")")),
                    start, end);
        } else {
            table = dbc.findTableName(macro.substring(macro.indexOf("(") + 1, macro.indexOf(")")), start, end);
        }
        try {
            String cluster = System.getProperty("CLUSTER");
            if (cluster == null) {
                cluster = "unknown";
            }
            db = new DatabaseWriter(cluster);
            DatabaseMetaData dbMetaData = db.getConnection().getMetaData();
            ResultSet rs = dbMetaData.getColumns(null, null, table[0], null);
            boolean first = true;
            while (rs.next()) {
                if (!first) {
                    meta = meta + ",";
                }
                String name = rs.getString(4);
                int type = rs.getInt(5);
                if (type == java.sql.Types.VARCHAR) {
                    if (macro.indexOf("group_avg(") < 0) {
                        meta = meta + "count(" + name + ") as " + name;
                    } else {
                        meta = meta + name;
                    }
                    first = false;
                } else if (type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT
                        || type == java.sql.Types.INTEGER) {
                    if (macro.indexOf("sum(") == 0) {
                        meta = meta + "sum(" + name + ")";
                    } else {
                        meta = meta + "avg(" + name + ")";
                    }
                    first = false;
                } else if (type == java.sql.Types.TIMESTAMP) {
                    meta = meta + name;
                    first = false;
                } else {
                    if (macro.indexOf("sum(") == 0) {
                        meta = meta + "SUM(" + name + ")";
                    } else {
                        meta = meta + "AVG(" + name + ")";
                    }
                    first = false;
                }
            }
            db.close();
            if (first) {
                throw new SQLException("Table is undefined.");
            }
        } catch (SQLException ex) {
            throw new SQLException("Table does not exist:" + table[0]);
        }
        return meta;
    } else if (macro.indexOf("now") == 0) {
        SimpleDateFormat sdf = new SimpleDateFormat();
        return DatabaseWriter.formatTimeStamp(current);
    } else if (macro.intern() == "start".intern()) {
        return DatabaseWriter.formatTimeStamp(start);
    } else if (macro.intern() == "end".intern()) {
        return DatabaseWriter.formatTimeStamp(end);
    } else if (matcher.find()) {
        int period = Integer.parseInt(matcher.group(1));
        long timestamp = current - (current % (period * 60 * 1000L)) - (period * 60 * 1000L);
        return DatabaseWriter.formatTimeStamp(timestamp);
    } else if (macro.indexOf("past_hour") == 0) {
        return DatabaseWriter.formatTimeStamp(current - 3600 * 1000L);
    } else if (macro.endsWith("_week")) {
        long partition = current / DatabaseConfig.WEEK;
        if (partition <= 0) {
            partition = 1;
        }
        String[] buffers = macro.split("_");
        StringBuffer tableName = new StringBuffer();
        for (int i = 0; i < buffers.length - 1; i++) {
            tableName.append(buffers[i]);
            tableName.append("_");
        }
        tableName.append(partition);
        tableName.append("_week");
        return tableName.toString();
    } else if (macro.endsWith("_month")) {
        long partition = current / DatabaseConfig.MONTH;
        if (partition <= 0) {
            partition = 1;
        }
        String[] buffers = macro.split("_");
        StringBuffer tableName = new StringBuffer();
        for (int i = 0; i < buffers.length - 1; i++) {
            tableName.append(buffers[i]);
            tableName.append("_");
        }
        tableName.append(partition);
        tableName.append("_month");
        return tableName.toString();
    } else if (macro.endsWith("_quarter")) {
        long partition = current / DatabaseConfig.QUARTER;
        if (partition <= 0) {
            partition = 1;
        }
        String[] buffers = macro.split("_");
        StringBuffer tableName = new StringBuffer();
        for (int i = 0; i < buffers.length - 1; i++) {
            tableName.append(buffers[i]);
            tableName.append("_");
        }
        tableName.append(partition);
        tableName.append("_quarter");
        return tableName.toString();
    } else if (macro.endsWith("_year")) {
        long partition = current / DatabaseConfig.YEAR;
        if (partition <= 0) {
            partition = 1;
        }
        String[] buffers = macro.split("_");
        StringBuffer tableName = new StringBuffer();
        for (int i = 0; i < buffers.length - 1; i++) {
            tableName.append(buffers[i]);
            tableName.append("_");
        }
        tableName.append(partition);
        tableName.append("_year");
        return tableName.toString();
    } else if (macro.endsWith("_decade")) {
        long partition = current / DatabaseConfig.DECADE;
        if (partition <= 0) {
            partition = 1;
        }
        String[] buffers = macro.split("_");
        StringBuffer tableName = new StringBuffer();
        for (int i = 0; i < buffers.length - 1; i++) {
            tableName.append(buffers[i]);
            tableName.append("_");
        }
        tableName.append(partition);
        tableName.append("_decade");
        return tableName.toString();
    }
    if (forCharting) {
        if (macro.startsWith("session(") && request != null) {
            String keyword = macro.substring(macro.indexOf("(") + 1, macro.indexOf(")"));
            String[] objects = null;
            if (request.getSession().getAttribute(keyword) != null) {
                objects = ((String) request.getSession().getAttribute(keyword)).split(",");
            }
            StringBuffer buf = new StringBuffer();
            boolean first = true;
            if (objects != null) {
                for (String object : objects) {
                    if (!first) {
                        buf.append(" or ");
                    }
                    first = false;
                    buf.append(
                            macro.substring(macro.indexOf("(") + 1, macro.indexOf(")")) + "='" + object + "'");
                }
                return buf.toString();
            }
            return "";
        } else {
            String[] tableList = dbc.findTableNameForCharts(macro, start, end);
            StringBuffer buf = new StringBuffer();
            boolean first = true;
            for (String table : tableList) {
                if (!first) {
                    buf.append("|");
                }
                first = false;
                buf.append(table);
            }
            return buf.toString();
        }
    }
    String[] tableList = dbc.findTableName(macro, current, current);
    return tableList[0];
}

From source file:org.openconcerto.sql.model.JDBCStructureSource.java

protected void _fillTables(final Set<String> newSchemas, final Connection conn) throws SQLException {
    // for new tables, add ; for existing, refresh
    final DatabaseMetaData metaData = conn.getMetaData();
    // getColumns() only supports pattern (eg LIKE) so we must make multiple calls
    for (final String s : newSchemas) {
        final ResultSet rs = metaData.getColumns(this.getBase().getMDName(), s, "%", null);

        // handle tables becoming empty (possible in pg)
        final Set<SQLName> tablesWithColumns = new HashSet<SQLName>();

        boolean hasNext = rs.next();
        while (hasNext) {
            final String schemaName = rs.getString("TABLE_SCHEM");
            final String tableName = rs.getString("TABLE_NAME");
            tablesWithColumns.add(new SQLName(schemaName, tableName));

            final Boolean moved;
            if (newSchemas.contains(schemaName)) {
                final SQLSchema schema = getNewSchema(schemaName);
                moved = schema.refreshTable(metaData, rs);
            } else {
                moved = null;//from   w w w .  java  2 s.  c om
            }
            hasNext = moved == null ? rs.next() : moved;
        }

        // tables with no column = all tables - tables with column
        final SQLSchema schema = getNewSchema(s);
        for (final String t : schema.getTableNames()) {
            if (!tablesWithColumns.contains(new SQLName(s, t))) {
                // empty table with no db access
                schema.getTable(t).emptyFields();
            }
        }
    }

    // type & comment
    for (final SQLName tName : getTablesNames()) {
        final SQLTable t = getNewSchema(tName.getItemLenient(-2)).getTable(tName.getName());
        final List<String> l = (List<String>) this.tableNames.getNonNull(tName);
        t.setType(l.get(0));
        t.setComment(l.get(1));
    }

    final SQLSystem system = getBase().getServer().getSQLSystem();
    // procedures
    final CollectionMap<String, String> proceduresBySchema = new CollectionMap<String, String>();
    for (final String s : newSchemas) {
        final ResultSet rsProc = metaData.getProcedures(this.getBase().getMDName(), s, "%");
        while (rsProc.next()) {
            // to ignore case : pg.AbstractJdbc2DatabaseMetaData doesn't quote aliases
            final Map map = BasicRowProcessor.instance().toMap(rsProc);
            final String schemaName = (String) map.get("PROCEDURE_SCHEM");
            if (newSchemas.contains(schemaName)) {
                final String procName = (String) map.get("PROCEDURE_NAME");
                proceduresBySchema.put(schemaName, procName);
                getNewSchema(schemaName).addProcedure(procName);
            }
        }
    }
    // try to find out more about those procedures
    if (proceduresBySchema.size() > 0) {
        final String sel = system.getSyntax().getFunctionQuery(getBase(), proceduresBySchema.keySet());
        if (sel != null) {
            // don't cache since we don't listen on system tables
            for (final Object o : (List) getBase().getDataSource().execute(sel,
                    new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false))) {
                final Map m = (Map) o;
                final SQLSchema newSchema = getNewSchema((String) m.get("schema"));
                if (newSchema != null)
                    newSchema.setProcedureSource((String) m.get("name"), (String) m.get("src"));
            }
        }
    }

    // if no schemas exist, there can be no triggers
    // (avoid a query and a special case since "in ()" is not valid)
    if (newSchemas.size() > 0) {
        final ITransformer<Tuple2<String, String>, SQLTable> tableFinder = new ITransformer<Tuple2<String, String>, SQLTable>() {
            @Override
            public SQLTable transformChecked(Tuple2<String, String> input) {
                return getNewTable(input.get0(), input.get1());
            }
        };
        new JDBCStructureSource.TriggerQueryExecutor(tableFinder).apply(getBase(), newSchemas);
        new JDBCStructureSource.ColumnsQueryExecutor(tableFinder).apply(getBase(), newSchemas);
        try {
            new JDBCStructureSource.ConstraintsExecutor(tableFinder).apply(getBase(), newSchemas);
        } catch (QueryExn e1) {
            // constraints are not essentials, continue
            e1.printStackTrace();
            for (final SQLName tName : getTablesNames()) {
                final SQLTable t = getNewSchema(tName.getItemLenient(-2)).getTable(tName.getName());
                t.addConstraint(null);
            }
        }
    }
}

From source file:com.wipro.ats.bdre.clustermigration.MigrationPreprocessor.java

private List<String> getCurrentSourceColumnList(String sourceDb, String table, String sourceHiveConnection)
        throws SQLException {
    List<String> sourceColumnList = new ArrayList<>();
    DatabaseMetaData metaData = getHiveJDBCConnection(sourceDb, sourceHiveConnection).getMetaData();
    ResultSet rsColumns = metaData.getColumns(null, sourceDb, table, null);
    while (rsColumns.next()) {
        String columnName = rsColumns.getString("COLUMN_NAME");
        String dataType = rsColumns.getString("TYPE_NAME");
        sourceColumnList.add(columnName + " " + dataType);
    }/*from w ww . ja  va  2s  . c om*/
    rsColumns.close();
    return sourceColumnList;
}

From source file:org.ensembl.healthcheck.testcase.eg_compara.AbstractControlledRows.java

/**
 * //  www.  ja v a2 s  .  c  o  m
 * Returns the names of all columns for a given table.
 * 
 * @param conn
 * @param table
 * @return names of columns
 */
protected List<String> getColumnsOfTable(Connection conn, String table) {

    List<String> columnsOfTable;

    try {
        DatabaseMetaData md = conn.getMetaData();
        columnsOfTable = new ArrayList<String>();
        ResultSet rs = md.getColumns(null, null, table, null);

        while (rs.next()) {
            columnsOfTable.add(rs.getString(4));
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    if (columnsOfTable.size() == 0) {
        throw new RuntimeException("Got no columns for table " + table);
    }
    return columnsOfTable;
}

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

public ResultSet getMetaData() throws DataSourceException {
    try {/*from  w w  w . j  a  v a  2s  .  c o  m*/

        DatabaseHost databaseHost = getHost();
        String _catalog = databaseHost.getCatalogNameForQueries(getCatalogName());
        String _schema = databaseHost.getSchemaNameForQueries(getSchemaName());

        DatabaseMetaData dmd = databaseHost.getDatabaseMetaData();
        return dmd.getColumns(_catalog, _schema, getName(), null);

    } catch (SQLException e) {
        throw new DataSourceException(e);
    }
}

From source file:com.splicemachine.mrio.api.core.SMSQLUtil.java

/**
 *
 * Get table structure of 'tableName'/*from   ww  w . j  a  va  2 s  .c om*/
 * Return Column Name list : Column Type list
 * @throws SQLException
 *
 * */
public List<NameType> getTableStructure(String tableName) throws SQLException {
    if (LOG.isTraceEnabled())
        SpliceLogUtils.trace(LOG, "getTableStructure tableName=%s", tableName);
    List<NameType> colType = new ArrayList<NameType>();
    ResultSet result = null;
    try {
        String[] schemaTableName = parseTableName(tableName);
        DatabaseMetaData databaseMetaData = connect.getMetaData();
        result = databaseMetaData.getColumns(null, schemaTableName[0], schemaTableName[1], null);
        while (result.next()) {
            colType.add(new NameType(result.getString(4), result.getInt(5)));
        }
    } finally {
        if (result != null)
            result.close();
    }
    if (LOG.isTraceEnabled())
        SpliceLogUtils.trace(LOG, "getTableStructure returns=%s", Arrays.toString(colType.toArray()));
    return colType;
}

From source file:org.rhq.plugins.postgres.PostgresTableComponent.java

public void updateResourceConfiguration(ConfigurationUpdateReport report) {
    try {/*from   ww  w .  j a  v a 2  s.co m*/
        Configuration updatedConfiguration = report.getConfiguration();
        PropertyList updatedColumns = updatedConfiguration.getList("columns");

        Connection connection = this.resourceContext.getParentResourceComponent().getConnection();

        DatabaseMetaData dmd = connection.getMetaData();
        ResultSet rs = dmd.getColumns("", "", getTableNameFromContext(resourceContext), "");
        Map<String, ColumnDefinition> existingDefs = new HashMap<String, ColumnDefinition>();
        try {
            while (rs.next()) {
                ColumnDefinition def = new ColumnDefinition(rs);
                existingDefs.put(def.columnName, def);
            }
        } finally {
            rs.close();
        }

        for (Property newColumnDefinition : updatedColumns.getList()) {
            PropertyMap colDef = (PropertyMap) newColumnDefinition;

            ColumnDefinition existingDef = existingDefs.get(colDef.getSimple("columnName").getStringValue());
            ColumnDefinition newDef = new ColumnDefinition(colDef);
            if (existingDef == null) {
                // This is a new column to add
                String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext))
                        + " ADD COLUMN " + newDef.getColumnSql();
                if (DatabaseQueryUtility.executeUpdate(this, sql) != 0) {
                    throw new RuntimeException("Couldn't add column using SQL: " + sql);
                }
            } else {
                existingDefs.remove(existingDef.columnName);
                boolean columnLengthChanged = ((existingDef.columnLength != null
                        && !existingDef.columnLength.equals(newDef.columnLength))
                        || (existingDef.columnLength == null && existingDef.columnLength != null));
                boolean columnPrecisionChanged = ((existingDef.columnPrecision != null
                        && !existingDef.columnPrecision.equals(newDef.columnPrecision))
                        || (existingDef.columnPrecision == null && existingDef.columnPrecision != null));
                if (!existingDef.columnType.equals(newDef.columnType) || columnLengthChanged
                        || columnPrecisionChanged) {
                    String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext))
                            + " ALTER COLUMN " + getQuoted(newDef.columnName) + " TYPE " + newDef.columnType;
                    if (newDef.columnLength != null) {
                        sql += " ( " + newDef.columnLength;
                        // TODO: Implement a more robust check to figure out if this column has a numeric type.
                        if (newDef.columnPrecision != null && !newDef.columnType.startsWith("varchar"))
                            sql += ", " + newDef.columnPrecision;
                        sql += " ) ";
                    }

                    if (DatabaseQueryUtility.executeUpdate(this, sql) != 1) {
                        throw new RuntimeException("Couldn't alter column type using SQL: " + sql);
                    }
                }

                // Set default separately.
                boolean columnDefaultChanged = ((existingDef.columnDefault != null
                        && !existingDef.columnDefault.equals(newDef.columnDefault))
                        || (existingDef.columnDefault == null && newDef.columnDefault != null));
                if (columnDefaultChanged) {
                    String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext))
                            + " ALTER COLUMN " + getQuoted(newDef.columnName);
                    if (newDef.columnDefault == null) {
                        sql += " DROP DEFAULT";
                    } else {
                        sql += " SET DEFAULT " + newDef.columnDefault;
                    }

                    if (DatabaseQueryUtility.executeUpdate(this, sql) != 1) {
                        throw new RuntimeException("Couldn't update column default using SQL: " + sql);
                    }
                }
            }
        }

        // Cols left in existdef map have been removed and need to be dropped
        for (ColumnDefinition def : existingDefs.values()) {
            DatabaseQueryUtility.executeUpdate(this,
                    "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext)) + " DROP COLUMN "
                            + getQuoted(def.columnName));
        }

        report.setStatus(ConfigurationUpdateStatus.SUCCESS);
    } catch (SQLException e) {
        report.setErrorMessageFromThrowable(e);
        report.setStatus(ConfigurationUpdateStatus.FAILURE);
    }
}