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:weave.utils.SQLUtils.java

/**
 * @param conn An existing SQL Connection
 * @param schemaName A schema name accessible through the given connection
 * @param tableName A table name existing in the given schema
 * @return A List of column names in the given table
 * @throws SQLException If the query fails.
 *//*from w  w  w .  j a v a2 s . c om*/
public static List<String> getColumns(Connection conn, String schemaName, String tableName)
        throws SQLException {
    List<String> columns = new Vector<String>();
    CallableStatement cstmt = null;
    ResultSet rs = null;
    try {
        DatabaseMetaData md = conn.getMetaData();

        tableName = escapeSearchString(conn, tableName);

        // MySQL uses "catalogs" instead of "schemas"
        if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase(MYSQL))
            rs = md.getColumns(schemaName, null, tableName, null);
        else if (isOracleServer(conn))
            rs = md.getColumns(null, schemaName.toUpperCase(), tableName, null);
        else
            rs = md.getColumns(null, schemaName, tableName, null);

        // use column index instead of name because sometimes the names are lower case, sometimes upper.
        while (rs.next())
            columns.add(rs.getString(4)); // column_name
    } finally {
        // close everything in reverse order
        SQLUtils.cleanup(rs);
        SQLUtils.cleanup(cstmt);
    }
    return columns;
}

From source file:org.sonar.core.persistence.InMemoryDatabase.java

private void truncateTables() {
    Connection connection = null;
    try {//from  w  w  w.ja va2s .c  o m
        connection = datasource.getConnection();

        DatabaseMetaData meta = connection.getMetaData();
        Statement statement = connection.createStatement();

        ResultSet res = meta.getTables(null, null, null, new String[] { "TABLE" });
        while (res.next()) {
            String tableName = res.getString("TABLE_NAME");
            statement.executeUpdate("TRUNCATE TABLE " + tableName);
        }
        res.close();

        // See https://issues.apache.org/jira/browse/DERBY-5403
        res = meta.getColumns(null, null, null, "ID");
        while (res.next()) {
            String tableName = res.getString("TABLE_NAME");
            statement.executeUpdate("ALTER TABLE " + tableName + " ALTER COLUMN ID RESTART WITH 1");
        }
        res.close();

        statement.close();
    } catch (SQLException e) {
        throw new IllegalStateException("Fail to truncate tables", e);

    } finally {
        closeQuietly(connection); // Important, otherwise tests can stuck
    }
}

From source file:fr.cnes.sitools.datasource.jdbc.business.SitoolsDataSource.java

/**
 * Get the list of columns of a table/*  ww w. ja v  a  2 s  . c  om*/
 * 
 * @param table
 *          Table name
 * @return name of related columns of the table TODO evolution return List<Column>
 */
public List<String> getMetadata(Structure table) {
    List<String> columnNameList = null;
    Connection conn = null;
    ResultSet rs = null;
    try {
        conn = getConnection();

        columnNameList = new ArrayList<String>();
        DatabaseMetaData metaData = conn.getMetaData();
        rs = metaData.getColumns(null, null, Wrapper.getReference(table), null);
        while (rs.next()) {
            columnNameList.add(rs.getString("COLUMN_NAME"));
        }
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, null, ex);
    } finally {
        closeConnection(conn);
        closeResultSet(rs);
    }
    return columnNameList;
}

From source file:com.extrahardmode.module.MsgPersistModule.java

/** Creates tables if they do not exist. */
private void initializeTables() {
    Connection conn = null;// ww  w .  j  av a 2 s. c  om
    Statement statement = null;
    try {
        conn = retrieveConnection();
        statement = conn.createStatement();
        statement.setQueryTimeout(30);

        //One table holding the playername id relation
        String playerQuery = String.format(
                "CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT, %s STRING)", playerTable,
                "name");
        statement.executeUpdate(playerQuery);

        //One column for every message
        StringBuilder columns = new StringBuilder();
        for (MessageNode node : MessageNode.getMessageNodes()) {
            MsgCategory cat = messages.getCat(node);
            if (node.getColumnName() != null && (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME)) {
                columns.append(',');
                columns.append(node.getColumnName());
            }
        }

        String msgQuery = String.format("CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY UNIQUE %s)",
                msgTable, columns);
        statement.executeUpdate(msgQuery);

        //Check if all columns are present
        DatabaseMetaData dmd = conn.getMetaData();
        //Add missing columns
        for (MessageNode node : MessageNode.getMessageNodes()) {
            MsgCategory cat = messages.getCat(node);
            if (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME) {
                ResultSet set = dmd.getColumns(null, null, msgTable, node.getColumnName());
                if (!set.next()) {
                    String updateQuery = String.format("ALTER TABLE %s ADD COLUMN %s", msgTable,
                            node.getColumnName());
                    statement.executeUpdate(updateQuery);
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (conn != null)
                conn.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:org.msec.LogQuery.java

private List<LogField> getColumnList() throws SQLException {
    String tableName = "t_dailylog_" + dayFormatter.format(new Date());
    DatabaseMetaData md = conn.getMetaData();
    ResultSet rs = md.getColumns(null, null, tableName, null);

    List<LogField> columnList = new ArrayList<LogField>();
    String columnName;/*ww w .  j a  v a  2s .  c  o m*/
    String columnType;
    String transColumnType = "";
    while (rs.next()) {
        columnName = rs.getString("COLUMN_NAME");
        columnType = rs.getString("TYPE_NAME").toLowerCase();

        if (builtinColumns.contains(columnName.toLowerCase())) {
            continue;
        }

        //
        if (delFields.contains(columnName.toLowerCase())) {
            continue;
        }
        if (columnType.contains("int")) {
            transColumnType = "Integer";
        } else if (columnType.contains("char") || columnType.contains("text")) {
            transColumnType = "String";
        } else if (columnType.contains("date") || columnType.contains("time")) {
            transColumnType = "Date";
        } else {
            transColumnType = columnType;
        }
        columnList.add(new LogField(columnName, transColumnType, ""));
    }
    return columnList;
}

From source file:org.apache.ambari.server.orm.DBAccessorImpl.java

@Override
public boolean tableHasColumn(String tableName, String columnName) throws SQLException {
    boolean result = false;
    DatabaseMetaData metaData = getDatabaseMetaData();

    ResultSet rs = metaData.getColumns(null, null, convertObjectName(tableName), convertObjectName(columnName));

    if (rs != null) {
        try {//from   w  ww  .  j  av a2 s  .  c  o m
            if (rs.next()) {
                return rs.getString("COLUMN_NAME") != null
                        && rs.getString("COLUMN_NAME").equalsIgnoreCase(columnName);
            }
        } finally {
            rs.close();
        }
    }

    return result;
}

From source file:com.feedzai.commons.sql.abstraction.engine.impl.PostgreSqlEngine.java

@Override
public synchronized Map<String, DbColumnType> getMetadata(final String name) throws DatabaseEngineException {
    final Map<String, DbColumnType> metaMap = new LinkedHashMap<String, DbColumnType>();

    ResultSet rsColumns = null;/*from  w ww .j a va2 s  .  c  o m*/
    try {
        getConnection();

        DatabaseMetaData meta = conn.getMetaData();
        rsColumns = meta.getColumns(null, "public", name, null);
        while (rsColumns.next()) {
            metaMap.put(rsColumns.getString("COLUMN_NAME"), toPdbType(rsColumns.getInt("DATA_TYPE")));
        }

        return metaMap;
    } catch (Exception e) {
        throw new DatabaseEngineException("Could not get metadata", e);
    } finally {
        try {
            if (rsColumns != null) {
                rsColumns.close();
            }

        } catch (Exception a) {
            logger.trace("Error closing result set.", a);
        }
    }
}

From source file:com.micromux.cassandra.jdbc.JdbcRegressionTest.java

@Test
public void testIssue78() throws Exception {
    DatabaseMetaData md = con.getMetaData();

    // load the columns, with no catalog and schema
    ResultSet result = md.getColumns(null, "%", TABLE, "ivalue");
    assertTrue("Make sure we have found an column", result.next());
}

From source file:org.wso2.carbon.event.output.adaptor.mysql.MysqlEventAdaptorType.java

private TableInfo initializeDatabaseTableInfo(String tableName, String executionMode, String updateColumnKeys,
        Object message, OutputEventAdaptorConfiguration adaptorConfig) throws SQLException {
    TableInfo tableInfo = new TableInfo();
    tableInfo.setTableName(tableName);/*from w  ww  .j  av  a2  s . c om*/
    if (resourceBundle.getString(MysqlEventAdaptorConstants.ADAPTOR_MYSQL_EXECUTION_MODE_UPDATE)
            .equalsIgnoreCase(executionMode)) {
        tableInfo.setUpdateMode(true);
    }
    Connection con = null;
    Statement stmt = null;

    try {
        DataSource dataSource = pooledDataSources.get(adaptorConfig.getOutputProperties()
                .get(MysqlEventAdaptorConstants.ADAPTOR_MYSQL_DATASOURCE_NAME));
        if (dataSource == null) {
            CarbonDataSource carbonDataSource = EventAdaptorValueHolder.getDataSourceService()
                    .getDataSource(adaptorConfig.getOutputProperties()
                            .get(MysqlEventAdaptorConstants.ADAPTOR_MYSQL_DATASOURCE_NAME));
            if (carbonDataSource == null) {
                throw new OutputEventAdaptorEventProcessingException(
                        "There is no any data-source found called : " + adaptorConfig.getOutputProperties()
                                .get(MysqlEventAdaptorConstants.ADAPTOR_MYSQL_DATASOURCE_NAME));
            } else {
                dataSource = (DataSource) carbonDataSource.getDSObject();
                pooledDataSources.put(adaptorConfig, dataSource);
            }
        }

        con = dataSource.getConnection();
        String databaseName = con.getCatalog();
        tableInfo.setDatabaseName(databaseName);

        // create the table.
        StringBuilder statementBuilder = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
        statementBuilder.append(databaseName + "." + tableName);
        statementBuilder.append(" (");
        boolean appendComma = false;
        for (Map.Entry<String, Object> entry : (((Map<String, Object>) message).entrySet())) {
            if (appendComma) {
                statementBuilder.append(",");
            } else {
                appendComma = true;
            }
            statementBuilder.append(entry.getKey()).append("  ");
            if (entry.getValue() instanceof Integer) {
                statementBuilder.append("INT");
            } else if (entry.getValue() instanceof Long) {
                statementBuilder.append("BIGINT");
            } else if (entry.getValue() instanceof Float) {
                statementBuilder.append("FLOAT");
            } else if (entry.getValue() instanceof Double) {
                statementBuilder.append("DOUBLE");
            } else if (entry.getValue() instanceof String) {
                statementBuilder.append("VARCHAR(255)");
            } else if (entry.getValue() instanceof Boolean) {
                statementBuilder.append("BOOL");
            }
        }
        statementBuilder.append(")");

        stmt = con.createStatement();
        stmt.execute("CREATE SCHEMA IF NOT EXISTS " + databaseName);
        stmt.executeUpdate(statementBuilder.toString());

        ArrayList<Attribute> tableInsertColumnList = new ArrayList<Attribute>();
        statementBuilder = new StringBuilder("INSERT INTO ");
        statementBuilder.append(databaseName + "." + tableName);
        statementBuilder.append(" ( ");

        StringBuilder valuePositionsBuilder = new StringBuilder("");

        appendComma = false;
        DatabaseMetaData databaseMetaData = con.getMetaData();
        ResultSet rs = databaseMetaData.getColumns(databaseName, null, tableName, null);
        while (rs.next()) {
            AttributeType type = null;
            int colType = rs.getInt("DATA_TYPE");
            switch (colType) {
            case Types.VARCHAR:
                type = AttributeType.STRING;
                break;
            case Types.INTEGER:
                type = AttributeType.INT;
                break;
            case Types.BIGINT:
                type = AttributeType.LONG;
                break;
            case Types.DOUBLE:
                type = AttributeType.DOUBLE;
                break;
            case Types.FLOAT:
                type = AttributeType.FLOAT;
                break;
            case Types.TINYINT:
            case Types.BOOLEAN:
            case MysqlEventAdaptorConstants.TINYINT_MYSQL_VALUE:
                type = AttributeType.BOOL;
                break;

            }
            Attribute attribute = new Attribute(rs.getString("COLUMN_NAME"), type);
            tableInsertColumnList.add(attribute);

            if (appendComma) {
                statementBuilder.append(",");
                valuePositionsBuilder.append(",");
            } else {
                appendComma = true;
            }
            statementBuilder.append(attribute.getName());
            valuePositionsBuilder.append("?");
        }
        statementBuilder.append(") VALUES (");
        statementBuilder.append(valuePositionsBuilder.toString());
        statementBuilder.append(")");
        tableInfo.setPreparedInsertStatement(statementBuilder.toString());
        tableInfo.setInsertColumnOrder(tableInsertColumnList);

        if (executionMode.equalsIgnoreCase(
                resourceBundle.getString(MysqlEventAdaptorConstants.ADAPTOR_MYSQL_EXECUTION_MODE_UPDATE))) {

            // existence check query.
            StringBuilder existenceQueryBuilder = new StringBuilder("SELECT 1 FROM ");
            existenceQueryBuilder.append(databaseName + "." + tableName);
            existenceQueryBuilder.append(" WHERE ");
            String[] queryAttributes = updateColumnKeys.trim().split(",");
            ArrayList<Attribute> queryAttributeList = new ArrayList<Attribute>(queryAttributes.length);

            for (int i = 0; i < queryAttributes.length; i++) {
                if (i > 0) {
                    existenceQueryBuilder.append(" AND ");
                }
                existenceQueryBuilder.append(queryAttributes[i]);
                existenceQueryBuilder.append(" = ? ");
                for (Attribute at : tableInfo.getInsertColumnOrder()) {
                    if (queryAttributes[i].equalsIgnoreCase(at.getName())) {
                        queryAttributeList.add(at);
                        break;
                    }
                }
            }
            tableInfo.setExistenceCheckColumnOrder(queryAttributeList);
            tableInfo.setPreparedExistenceCheckStatement(existenceQueryBuilder.toString());

            // update query.
            StringBuilder updateQueryBuilder = new StringBuilder("UPDATE  ");
            ArrayList<Attribute> updateAttributes = new ArrayList<Attribute>();
            updateQueryBuilder.append(tableInfo.getDatabaseName() + "." + tableInfo.getTableName());
            updateQueryBuilder.append(" SET ");
            appendComma = false;
            for (Attribute at : tableInfo.getInsertColumnOrder()) {
                if (!tableInfo.getExistenceCheckColumnOrder().contains(at)) {
                    if (appendComma) {
                        updateQueryBuilder.append(" , ");
                    }
                    updateQueryBuilder.append(at.getName());
                    updateQueryBuilder.append(" = ? ");
                    updateAttributes.add(at);
                    appendComma = true;
                }
            }
            updateQueryBuilder.append(" WHERE ");
            boolean appendAnd = false;
            for (Attribute at : tableInfo.getExistenceCheckColumnOrder()) {
                if (appendAnd) {
                    updateQueryBuilder.append(" AND ");
                }
                updateQueryBuilder.append(at.getName());
                updateQueryBuilder.append(" = ? ");
                updateAttributes.add(at);
                appendAnd = true;
            }
            tableInfo.setUpdateColumnOrder(updateAttributes);
            tableInfo.setPreparedUpdateStatement(updateQueryBuilder.toString());

        }
        return tableInfo;
    } catch (SQLException e) {
        pooledDataSources.remove(adaptorConfig.getOutputProperties()
                .get(MysqlEventAdaptorConstants.ADAPTOR_MYSQL_DATASOURCE_NAME));
        log.error("error while initializing the table", e);
    } catch (DataSourceException e) {
        pooledDataSources.remove(adaptorConfig.getOutputProperties()
                .get(MysqlEventAdaptorConstants.ADAPTOR_MYSQL_DATASOURCE_NAME));
        log.error("error while accessing the datasource:", e);
    } finally {
        cleanupConnections(stmt, con);
    }
    return null;
}

From source file:madgik.exareme.master.queryProcessor.analyzer.fanalyzer.FederatedAnalyzer.java

private Map<String, Set<String>> specifyColumns(String tableName) throws Exception {
    Map<String, Set<String>> info = new HashMap<String, Set<String>>();
    DatabaseMetaData dbmd = con.getMetaData(); // dtabase metadata object

    // listing tables and columns
    String catalog = null;/* www .j  a  v  a 2s  .c  o m*/
    String schemaPattern = null;
    String columnNamePattern = null;
    String tname = tableName;
    if (tname.contains(".")) {
        schemaPattern = tname.split("\\.")[0];
        tname = tname.split("\\.")[1];
    }

    ResultSet resultColumns = dbmd.getColumns(catalog, schemaPattern, tname, columnNamePattern);
    Set<String> columns = new HashSet<String>();
    while (resultColumns.next()) {
        String columnName = StringEscapeUtils.escapeJava(resultColumns.getString(4));
        columns.add(columnName);
    }
    info.put(tableName, columns);
    resultColumns.close();
    return info;

}