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:org.apache.bigtop.itest.hive.TestJdbc.java

@Test
public void setSchema() throws SQLException {
    try (Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {

        final String dbName = "bigtop_jdbc_test_db";

        final String tableName = "bigtop_jdbc_test_table";
        stmt.execute("drop table if exists " + tableName);

        stmt.execute("drop database if exists " + dbName + " cascade");
        stmt.execute("create database " + dbName);

        conn.setSchema(dbName);//from   www  .  j  a  v a2 s.  co  m

        DatabaseMetaData md = conn.getMetaData();

        ResultSet rs = md.getSchemas(null, dbName);

        while (rs.next()) {
            String schemaName = rs.getString(2);
            LOG.debug("Schema name is " + schemaName);
        }

        stmt.execute("create table " + tableName + " (i int, s varchar(32))");

        rs = md.getTables(null, dbName, tableName, null);
        while (rs.next()) {
            String tName = rs.getString(3);
            LOG.debug("Schema name is " + tName);
        }

        rs = md.getColumns(null, dbName, tableName, "i");
        while (rs.next()) {
            String colName = rs.getString(4);
            LOG.debug("Schema name is " + colName);
        }

        rs = md.getFunctions(null, dbName, "foo");
        while (rs.next()) {
            String funcName = rs.getString(3);
            LOG.debug("Schema name is " + funcName);
        }
    }
}

From source file:org.executequery.gui.editor.autocomplete.AutoCompleteSelectionsFactory.java

private void databaseColumnsForTables(DatabaseHost databaseHost, List<AutoCompleteListItem> tables) {

    trace("Retrieving column names for tables for host [ " + databaseHost.getName() + " ]");

    ResultSet rs = null;/*ww w. j av  a 2  s .  c o  m*/
    List<ColumnInformation> columns = new ArrayList<ColumnInformation>();
    List<AutoCompleteListItem> list = new ArrayList<AutoCompleteListItem>();

    String catalog = databaseHost.getCatalogNameForQueries(defaultCatalogForHost(databaseHost));
    String schema = databaseHost.getSchemaNameForQueries(defaultSchemaForHost(databaseHost));
    DatabaseMetaData dmd = databaseHost.getDatabaseMetaData();

    for (int i = 0, n = tables.size(); i < n; i++) {

        try {
            if (Thread.interrupted() || dmd.getConnection().isClosed()) {

                return;
            }
        } catch (SQLException e) {
        }

        AutoCompleteListItem table = tables.get(i);
        if (table == null) {

            continue;
        }

        trace("Retrieving column names for table [ " + table.getValue() + " ]");

        try {

            rs = dmd.getColumns(catalog, schema, table.getValue(), null);
            while (rs.next()) {

                String name = rs.getString(4);
                columns.add(
                        columnInformationFactory.build(table.getValue(), name, rs.getString(6), rs.getInt(5),
                                rs.getInt(7), rs.getInt(9), rs.getInt(11) == DatabaseMetaData.columnNoNulls));
            }

            for (ColumnInformation column : columns) {

                list.add(new AutoCompleteListItem(column.getName(), table.getValue(), column.getDescription(),
                        DATABASE_COLUMN_DESCRIPTION, AutoCompleteListItemType.DATABASE_TABLE_COLUMN));
            }

            provider.addListItems(list);
            releaseResources(rs);
            columns.clear();
            list.clear();

        } catch (Throwable e) {

            // don't want to break the editor here so just log and bail...

            error("Error retrieving column data for table " + table.getDisplayValue() + " - driver returned: "
                    + e.getMessage());

        } finally {

            releaseResources(rs);
        }

    }

    trace("Finished retrieving column names for tables for host [ " + databaseHost.getName() + " ]");
}

From source file:org.dbmfs.DatabaseAccessor.java

private Map<String, Map<String, Object>> getAllColumnMeta(String tableName) throws Exception {
    Map<String, Map<String, Object>> allColumnMeta = null;
    try {//from w ww  .jav  a 2s.  c  o m
        if (allColumnMetaCacheFolder.containsKey(tableName))
            return (Map<String, Map<String, Object>>) allColumnMetaCacheFolder.get(tableName);

        allColumnMeta = new LinkedHashMap();

        DatabaseMetaData dbmd = injectConn.getMetaData();

        // ?
        ResultSet rs = dbmd.getColumns(null, null, tableName, "%");
        while (rs.next()) {

            Map<String, Object> columMeta = new LinkedHashMap();
            columMeta.put("name", rs.getString("COLUMN_NAME"));
            columMeta.put("type", rs.getInt("DATA_TYPE"));
            columMeta.put("type_name", rs.getString("TYPE_NAME"));
            columMeta.put("column_size", rs.getInt("COLUMN_SIZE"));
            columMeta.put("null_type", rs.getString("IS_NULLABLE"));
            columMeta.put("seq_type", rs.getString("IS_AUTOINCREMENT"));
            columMeta.put("javaTypeName", getJavaTypeName(rs.getString("TYPE_NAME")));
            allColumnMeta.put((String) columMeta.get("name"), columMeta);
        }

        Map<String, Object> pKeyNameMap = new LinkedHashMap();
        try {
            List<String> pkeyList = getPrimaryKeyColumnNames(tableName);

            int no = 1;
            for (String pkeyName : pkeyList) {
                pKeyNameMap.put(pkeyName, no);
                no++;
            }
        } catch (SQLException se) {
        }
        allColumnMeta.put(DatabaseAccessor.tableMetaInfoPKeyKey, pKeyNameMap);
        allColumnMetaCacheFolder.put(tableName, allColumnMeta);

        rs.close();
    } catch (Exception e) {
        e.printStackTrace();
        throw e;
    }
    return allColumnMeta;
}

From source file:madgik.exareme.master.queryProcessor.analyzer.stat.Stat.java

@Override
public Map<String, Table> extractStats() throws Exception {

    DatabaseMetaData dbmd = con.getMetaData(); // dtabase metadata object

    // listing tables and columns
    String catalog = null;/*from   w w w .ja  va 2s  . c  o  m*/
    String schemaPattern = null;
    String tableNamePattern = null;
    String[] types = null;
    String columnNamePattern = null;

    ResultSet resultTables = dbmd.getTables(catalog, schemaPattern, tableNamePattern, types);
    log.debug("Starting extracting stats");
    while (resultTables.next()) {
        Map<String, Column> columnMap = new HashMap<String, Column>();
        String tableName = StringEscapeUtils.escapeJava(resultTables.getString(3));
        log.debug("Analyzing table " + tableName);

        int columnCount = resultTables.getMetaData().getColumnCount();
        int toupleSize = 0; // in bytes

        tableNamePattern = tableName;
        ResultSet resultColumns = dbmd.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);

        int count = OptiqueAnalyzer.getCountFor(tableName, sch);

        if (count == 0) {
            log.debug("Empty table");
            continue;
        }

        while (resultColumns.next()) {

            String columnName = StringEscapeUtils.escapeJava(resultColumns.getString(4));
            int columnType = resultColumns.getInt(5);

            // computing column's size in bytes
            int columnSize = computeColumnSize(columnName, columnType, tableName);
            toupleSize += columnSize;

            // execute queries for numberOfDiffValues, minVal, maxVal
            Map<String, Integer> diffValFreqMap = new HashMap<String, Integer>();

            // computing column's min and max values
            MinMax mm = computeMinMax(tableName, columnName);
            String minVal = mm.getMin();
            String maxVal = mm.getMax();

            // /
            List<ValFreq> freqs = computeDistinctValuesFrequency(tableName, columnName);

            for (ValFreq k : freqs) {
                diffValFreqMap.put(k.getVal(), k.getFreq());

            }

            // /add min max diff vals in the sampling values
            int minOcc = computeValOccurences(tableName, columnName, minVal);
            if (!diffValFreqMap.containsKey(minVal))
                diffValFreqMap.put(minVal, minOcc);
            int maxOcc = computeValOccurences(tableName, columnName, maxVal);
            if (!diffValFreqMap.containsKey(maxVal))
                diffValFreqMap.put(maxVal, maxOcc);

            int diffVals = diffValFreqMap.size();

            Column c = new Column(columnName, columnType, columnSize, diffVals, minVal, maxVal, diffValFreqMap);
            columnMap.put(columnName, c);

        }

        ResultSet pkrs = dbmd.getExportedKeys("", "", tableName);
        String pkey = "DEFAULT_KEY";

        while (pkrs.next()) {
            pkey = pkrs.getString("PKCOLUMN_NAME");
            break;
        }

        Table t = new Table(tableName, columnCount, toupleSize, columnMap, count, pkey);
        schema.put(tableName, t);

    }

    return schema;

}

From source file:com.couchbase.devex.JDBCConfig.java

@Override
public Observable<Document> startImport() throws Exception {
    // get Database Medatadata objects to retrieve Tables schema
    DatabaseMetaData databaseMetadata = jdbcTemplate.getDataSource().getConnection().getMetaData();
    List<String> tableNames = new ArrayList<String>();
    // Get tables names
    ResultSet result = databaseMetadata.getTables(catalog, schemaPattern, tableNamePattern, types);
    while (result.next()) {
        String tablename = result.getString(3);
        String tableType = result.getString(4);
        // make sure we only import table(as oppose to Views, counter etc...)
        if (!tablename.isEmpty() && "TABLE".equals(tableType)) {
            tableNames.add(tablename);/*from   w w w.  j av a  2s. c o m*/
            log.debug("Will import table " + tablename);
        }
    }
    // Map the tables schema to Table objects
    Map<String, Table> tables = new HashMap<String, Table>();
    JsonObject tablesSchema = JsonObject.create();
    for (String tableName : tableNames) {
        result = databaseMetadata.getColumns(catalog, schemaPattern, tableName, columnNamePattern);
        Table table = new Table(tableName);
        while (result.next()) {
            String columnName = result.getString(4);
            // Maps to JDBCType enum
            int columnType = result.getInt(5);
            table.addColumn(columnName, columnType);
        }
        result = databaseMetadata.getPrimaryKeys(catalog, schemaPattern, tableName);
        while (result.next()) {
            String columnName = result.getString(4);
            table.setPrimaryKey(columnName);
        }
        tables.put(tableName, table);
        tablesSchema.put(tableName, table.toJsonObject());
    }
    JsonDocument schemaDoc = JsonDocument.create(tablesSchemaId, tablesSchema);
    log.debug(tablesSchema);
    // FlatMap each table to an Observable of JsonDocument, one
    // JsonDocument per table row.
    return Observable.from(tableNames).flatMap(s -> {
        String sql = String.format(SELECT_EVERYTHING_FROM_TABLE_QUERY, s);
        return Observable.from(jdbcTemplate.query(sql, new JSONRowMapper(tables.get(s))));
    })
            // start by a jsonDocument containing the tables to be imported.
            .startWith(schemaDoc);
}

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

/**
 * Read metadata about a table from the database.
 * //  w  ww.  j  a  va  2s. co  m
 * @param table
 *            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.
 */
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");
        if (StringUtils.isBlank(schema)) {
            schema = null;
        }
        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>();

        int max = metadata.getMaxTableNameLength();
        String tname = ((max > 0) && (table.length() >= max)) ? table.substring(0, max - 1) : table;

        pkcolumns = metadata.getPrimaryKeys(catalog, schema, tname);

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

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

        columns = metadata.getColumns(catalog, schema, tname, 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 Collections.unmodifiableMap(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:net.certifi.audittablegen.GenericDMR.java

/**
 * Get List of ColumnDef objects for all tables
 * in the targeted database/schema//ww w  .j a  v a 2  s  .co m
 * 
 * @param tableName
 * @return ArrayList of ColumnDef objects or an empty list if none are found.
 */
@Override
public List getColumns(String tableName) {

    //getDataTypes will initialize the map if it isn't already loaded
    Map<String, DataTypeDef> dtds = getDataTypes();

    List columns = new ArrayList<>();

    try {
        Connection conn = dataSource.getConnection();
        DatabaseMetaData dmd = conn.getMetaData();
        ResultSet rs = dmd.getColumns(null, verifiedSchema, tableName, null);

        //load all of the metadata in the result set into a map for each column

        ResultSetMetaData rsmd = rs.getMetaData();
        int metaDataColumnCount = rsmd.getColumnCount();
        if (!rs.isBeforeFirst()) {
            throw new RuntimeException(
                    "No results for DatabaseMetaData.getColumns(" + verifiedSchema + "." + tableName + ")");
        }
        while (rs.next()) {
            ColumnDef columnDef = new ColumnDef();
            Map columnMetaData = new CaseInsensitiveMap();
            for (int i = 1; i <= metaDataColumnCount; i++) {
                columnMetaData.put(rsmd.getColumnName(i), rs.getString(i));
            }
            columnDef.setName(rs.getString("COLUMN_NAME"));
            columnDef.setTypeName(rs.getString("TYPE_NAME"));
            columnDef.setSqlType(rs.getInt("DATA_TYPE"));
            columnDef.setSize(rs.getInt("COLUMN_SIZE"));
            columnDef.setDecimalSize(rs.getInt("DECIMAL_DIGITS"));
            columnDef.setSourceMeta(columnMetaData);

            if (dtds.containsKey(columnDef.getTypeName())) {
                columnDef.setDataTypeDef(dtds.get(columnDef.getTypeName()));
            } else {
                throw new RuntimeException(
                        "Missing DATA_TYPE definition for data type " + columnDef.getTypeName());
            }
            columns.add(columnDef);
        }

    } catch (SQLException e) {
        throw Throwables.propagate(e);
    }

    return columns;

}

From source file:com.adito.jdbc.DBDumper.java

/**
 * Dump table creation SQL. It is up to the caller to close the stream and connections when
 * finished with./*from w  w w  .j av a  2s. c om*/
 * 
 * @param writer write SQL to this writer.
 * @param conx connection to get data from
 * @param quoteChar character to use to quote strings
 * @param tables array of table names or <code>null</code> to dump all in
 *        database
 * @throws Exception on any error
 */
public void dumpTable(PrintWriter writer, JDBCConnectionImpl conx, char quoteChar, String[] tables)
        throws Exception {
    Connection jdbcConnection = conx.getConnection();
    DatabaseMetaData dbMetaData = jdbcConnection.getMetaData();

    if (tables == null) {
        ResultSet rs = dbMetaData.getTables(null, null, null, null);
        try {
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                String tableType = rs.getString("TABLE_TYPE");
                if (tableType.equalsIgnoreCase("TABLE")) {
                    dumpTable(writer, conx, quoteChar, new String[] { tableName });
                }
            }
        } finally {
            rs.close();
        }
    } else {
        for (int i = 0; i < tables.length; i++) {
            String tableName = tables[i];
            log.info("Dumping table creation for " + tableName);
            writer.println("CREATE TABLE " + tableName + " (");
            boolean first = true;

            // Columns
            ResultSet rs2 = dbMetaData.getColumns(null, null, tableName, "%");
            try {
                while (rs2.next()) {
                    if (first) {
                        first = false;
                    } else {
                        writer.println(",");
                    }
                    String columnName = rs2.getString("COLUMN_NAME");
                    String columnType = rs2.getString("TYPE_NAME");
                    int columnSize = rs2.getInt("COLUMN_SIZE");
                    String nullable = rs2.getString("IS_NULLABLE");
                    String nullString = "NULL";
                    if ("NO".equalsIgnoreCase(nullable)) {
                        nullString = "NOT NULL";
                    }
                    writer.print("    " + columnName + " " + columnType);
                    if (columnSize != 0) {
                        if (columnType.equalsIgnoreCase("varchar") && columnSize > 255) {
                            columnSize = 255;
                        }
                        writer.print(" (" + columnSize + ")");
                    }
                    writer.print(" " + nullString);

                }
            } finally {
                rs2.close();
            }

            // Keys
            try {
                rs2 = dbMetaData.getPrimaryKeys(null, null, tableName);
                String primaryKeyName = null;
                StringBuffer primaryKeyColumns = new StringBuffer();
                while (rs2.next()) {
                    String thisKeyName = rs2.getString("PK_NAME");
                    if ((thisKeyName != null && primaryKeyName == null)
                            || (thisKeyName == null && primaryKeyName != null)
                            || (thisKeyName != null && !thisKeyName.equals(primaryKeyName))
                            || (primaryKeyName != null && !primaryKeyName.equals(thisKeyName))) {
                        if (primaryKeyColumns.length() > 0) {
                            writer.print(",\n    PRIMARY KEY ");
                            if (primaryKeyName != null) {
                                writer.print(primaryKeyName);
                            }
                            writer.print("(" + primaryKeyColumns.toString() + ")");
                        }
                        primaryKeyColumns = new StringBuffer();
                        primaryKeyName = thisKeyName;
                    }
                    if (primaryKeyColumns.length() > 0) {
                        primaryKeyColumns.append(", ");
                    }
                    primaryKeyColumns.append(rs2.getString("COLUMN_NAME"));
                }
                if (primaryKeyColumns.length() > 0) {
                    writer.print(",\n    PRIMARY KEY ");
                    if (primaryKeyName != null) {
                        writer.print(primaryKeyName);
                    }
                    writer.print(" (" + primaryKeyColumns.toString() + ")");
                }
            } finally {
                rs2.close();
            }
            writer.println("\n);");
            writer.println();
        }
    }
}

From source file:net.hydromatic.optiq.test.JdbcTest.java

/** Tests driver's implementation of {@link DatabaseMetaData#getColumns}. */
@Test// w  ww. j  a  va 2s  . c om
public void testMetaDataColumns() throws ClassNotFoundException, SQLException {
    Connection connection = getConnection("hr", "foodmart");
    DatabaseMetaData metaData = connection.getMetaData();
    ResultSet resultSet = metaData.getColumns(null, null, null, null);
    assertTrue(resultSet.next()); // there's something
    resultSet.close();
    connection.close();
}

From source file:nl.b3p.gis.writers.B3pOgcSqlWriter.java

private void checkcolumns(FeatureSchema fs, String geomColumn, boolean attributeNamesToLowerCase,
        DatabaseMetaData dbmd, String tablename) throws Exception {
    for (int i = 0; i < fs.getAttributeCount(); i++) {
        if (i != fs.getGeometryIndex() || geomColumn == null) {
            boolean columFound = false;
            String attributeName = fs.getAttributeName(i);
            if (attributeNamesToLowerCase) {
                attributeName = attributeName.toLowerCase();
            }/*from  w  w w .j av  a2  s .  c o  m*/
            AttributeType attributeType = fs.getAttributeType(i);
            //get the column by attribute name.
            ResultSet rs = dbmd.getColumns(null, null, tablename, attributeName);
            //if the column exists check the compatibility of the data_type
            if (rs.next()) {
                int type = rs.getInt("DATA_TYPE");
                if (isCompatibleType(attributeType, type)) {
                    columFound = true;
                }
            }
            //if there is no correct columFound it is a 'wrong' table
            if (!columFound) {
                log.error("Table already exists but has wrong columns.");
                throw new Exception("Table already exists but has wrong columns.");
            }
        } else {
            ResultSet rs = dbmd.getColumns(null, null, tablename, geomColumn);
            if (!rs.next()) {
                log.error("Table already exists but has wrong columns.");
                throw new Exception("Table already exists but has wrong columns.");
            }
        }
    }
}