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.diffkit.db.DKDBTableDataAccess.java

private List<Map<String, ?>> getColumnMaps(Map<String, ?> tableMap_, DatabaseMetaData dbMeta_)
        throws SQLException {
    String catalogName = (String) DKMapUtil.getValueForKeyPrefix(tableMap_, TABLE_CATALOG_KEY);
    String schemaName = (String) DKMapUtil.getValueForKeyPrefix(tableMap_, TABLE_SCHEMA_KEY);
    String tableName = (String) tableMap_.get(TABLE_NAME_KEY);
    _log.debug("catalogName->{}", catalogName);
    _log.debug("schemaName->{}", schemaName);
    _log.debug("tableName->{}", tableName);

    ResultSet columnsRS = dbMeta_.getColumns(catalogName, schemaName, tableName, null);
    List<Map<String, ?>> columnMaps = DKSqlUtil.readRows(columnsRS);
    _log.debug("columnMaps->{}", columnMaps);
    DKSqlUtil.close(columnsRS);/*w  w  w  .  j  a  v  a2 s  .co  m*/
    return columnMaps;
}

From source file:de.griffel.confluence.plugins.plantuml.AbstractDatabaseStructureMacroImpl.java

private List<ColumnDef> getColumns(DatabaseMetaData dbmd) {
    final List<ColumnDef> result = new LinkedList<ColumnDef>();

    if (_errorMessage == null && _macroParams.isShowColumns()) {
        ResultSet rs = null;//from   w  w  w.j a  v a  2 s  .  com
        try {
            rs = dbmd.getColumns(null, _macroParams.getSchemaName(), _macroParams.getTableNameFilter(),
                    _macroParams.getColumnNameFilter());
            while (rs.next()) {
                final String comment = _macroParams.isShowComments() ? rs.getString(12) : null;
                final String defaultValue = _macroParams.isShowDefaults() ? rs.getString(13) : null;

                final ColumnDef tmp = new ColumnDef(rs.getString(1), rs.getString(2), rs.getString(3),
                        rs.getString(4), rs.getString(6), rs.getInt(7), rs.getInt(9), rs.getInt(11), comment,
                        defaultValue);
                result.add(tmp);
                if (log.isDebugEnabled()) {
                    log.debug(tmp.getColumnId());
                }
            }
        } catch (SQLException e) {
            sqlException(_macroParams.getDatasource(), e);
        } finally {
            closeResource(rs);
        }
    }
    return result;
}

From source file:org.jasig.ssp.util.importer.job.validation.map.metadata.database.JdbcTableColumnMetadataRepository.java

@Override
public MapColumnMetadata getColumnMetadata(final ColumnReference columnReference) {
    return JdbcUtils.doWithConnection(dataSource, new JdbcConnectionCallback<MapColumnMetadata>() {

        @Override//from   w  w w  .  ja  v  a2  s.  c  om
        public MapColumnMetadata doWork(Connection connection) throws SQLException {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            if (identifierCaser == null) {
                identifierCaser = new DatabaseIdentifierCaser(databaseMetaData);
            }

            String tableName = identifierCaser.apply(columnReference.getTableName());
            String columnName = identifierCaser.apply(columnReference.getColumnName());

            logger.debug("Querying column metadata for table: {}, column: {}.", tableName, columnName);
            ResultSet resultSet = databaseMetaData.getColumns(catalog, schema, tableName, columnName);

            final MapColumnMetadata mapColumnMetadata = mapToColumnMetadata(columnReference, resultSet);
            logger.debug("Found column metadata for table: {}, column: {}: {}",
                    new Object[] { tableName, columnName, mapColumnMetadata });

            return mapColumnMetadata;
        }

    });
}

From source file:org.kuali.core.db.torque.KualiTorqueJDBCTransformTask.java

/**
 * Retrieves all the column names and types for a given table from
 * JDBC metadata.  It returns a List of Lists.  Each element
 * of the returned List is a List with:/*w w  w.j  a  va2s .c o m*/
 *
 * element 0 => a String object for the column name.
 * element 1 => an Integer object for the column type.
 * element 2 => size of the column.
 * element 3 => null type.
 *
 * @param dbMeta JDBC metadata.
 * @param tableName Table from which to retrieve column information.
 * @return The list of columns in <code>tableName</code>.
 * @throws SQLException
 */
public List<List<Object>> getColumns(DatabaseMetaData dbMeta, String tableName) throws SQLException {
    List<List<Object>> columns = new ArrayList<List<Object>>();
    ResultSet columnSet = null;
    try {
        columnSet = dbMeta.getColumns(null, dbSchema, tableName, null);
        while (columnSet.next()) {
            String name = columnSet.getString(4);
            Integer sqlType = new Integer(columnSet.getString(5));
            Integer size = new Integer(columnSet.getInt(7));
            Integer decimalDigits = new Integer(columnSet.getInt(9));
            Integer nullType = new Integer(columnSet.getInt(11));
            String defValue = columnSet.getString(13);

            List<Object> col = new ArrayList<Object>(6);
            col.add(name);
            col.add(sqlType);
            col.add(size);
            col.add(nullType);
            col.add(defValue);
            col.add(decimalDigits);
            columns.add(col);
        }
    } finally {
        if (columnSet != null) {
            columnSet.close();
        }
    }
    return columns;
}

From source file:org.fao.geonet.services.dataprep.geocoding.GeoCoding.java

private JSONArray getColumnNamesInJSON(String tableName, JSONObject dbInfoJSONObj, Connection conn)
        throws SQLException, JSONException {
    DatabaseMetaData candidatedb_Metadata = conn.getMetaData();

    ResultSet rs = candidatedb_Metadata.getColumns(dbInfoJSONObj.getString("database_name"), "public",
            tableName, null);//www  .  j a  v a2 s. c  o  m
    JSONArray resultsJSONArray = new JSONArray();
    while (rs.next()) {
        JSONObject resultJSONObj = new JSONObject();
        System.out.println(rs.getObject("COLUMN_NAME"));
        resultJSONObj.put("name", rs.getObject("COLUMN_NAME"));
        resultJSONObj.put("type", rs.getObject("TYPE_NAME"));
        resultsJSONArray.put(resultJSONObj);
    }
    return resultsJSONArray;
}

From source file:cz.lbenda.dataman.db.DbStructureFactory.java

private void generateStructureColumns(Map<String, CatalogDesc> catalogs, DatabaseMetaData dmd)
        throws SQLException {
    SQLDialect dialect = dbConfig.getJdbcConfiguration().getDialect();
    for (Map.Entry<String, CatalogDesc> entry : catalogs.entrySet()) {
        try (ResultSet rsColumn = dmd.getColumns(entry.getKey(), null, null, null)) {
            rsColumn.last();/*from   w w w . ja  va  2s  .  co m*/
            StatusHelper.getInstance().progressNextStep(this, STEP_READ_COLUMNS, rsColumn.getRow());
        } catch (SQLException e) {
            StatusHelper.getInstance().progressNextStep(this, STEP_READ_COLUMNS, 1000);
        }
        try (ResultSet rsColumn = dmd.getColumns(entry.getKey(), null, null, null)) {
            writeColumnNames("generateStructureColumns", rsColumn.getMetaData());
            while (rsColumn.next()) {
                StatusHelper.getInstance().progress(this);
                String schema = rsColumn.getString(dialect.columnTableSchema());
                String table = rsColumn.getString(dialect.columnTableName());
                TableDesc td = entry.getValue().getSchema(schema).getTable(table);
                if (td != null) {
                    ColumnDesc column = new ColumnDesc(td, rsColumn, dialect);
                    td.addColumn(column);
                }
            }
        }
    }
}

From source file:org.apache.oozie.command.SchemaCheckXCommand.java

private boolean checkColumns(DatabaseMetaData metaData, String catalog, String table,
        Map<String, Integer> expectedColumnTypes) throws SQLException {
    boolean problem = false;
    Map<String, Pair<Integer, String>> foundColumns = new HashMap<String, Pair<Integer, String>>();
    ResultSet rs = metaData.getColumns(catalog, null, table, null);
    while (rs.next()) {
        String colName = rs.getString("COLUMN_NAME");
        Integer dataType = rs.getInt("DATA_TYPE");
        String colDef = rs.getString("COLUMN_DEF");
        if (colName != null) {
            foundColumns.put(colName, new Pair<Integer, String>(dataType, colDef));
        }//from ww  w .j av  a 2  s  .  co m
    }
    Collection missingColumns = CollectionUtils.subtract(expectedColumnTypes.keySet(), foundColumns.keySet());
    if (!missingColumns.isEmpty()) {
        LOG.error("Found [{0}] missing columns in table [{1}]: {2}", missingColumns.size(), table,
                Arrays.toString(missingColumns.toArray()));
        problem = true;
    } else {
        for (Map.Entry<String, Integer> ent : expectedColumnTypes.entrySet()) {
            if (!foundColumns.get(ent.getKey()).getFirst().equals(ent.getValue())) {
                LOG.error("Expected column [{0}] in table [{1}] to have type [{2}], but found type [{3}]",
                        ent.getKey(), table, getSQLTypeFromInt(ent.getValue()),
                        getSQLTypeFromInt(foundColumns.get(ent.getKey()).getFirst()));
                problem = true;
            } else if (foundColumns.get(ent.getKey()).getSecond() != null) {
                LOG.error(
                        "Expected column [{0}] in table [{1}] to have default value [NULL], but found default vale [{2}]",
                        ent.getKey(), table, foundColumns.get(ent.getKey()).getSecond());
                problem = true;
            } else {
                LOG.debug("Found column [{0}] in table [{1}] with type [{2}] and default value [NULL]",
                        ent.getKey(), table, getSQLTypeFromInt(ent.getValue()));
            }
        }
    }
    if (!ignoreExtras) {
        Collection extraColumns = CollectionUtils.subtract(foundColumns.keySet(), expectedColumnTypes.keySet());
        if (!extraColumns.isEmpty()) {
            LOG.error("Found [{0}] extra columns in table [{1}]: {2}", extraColumns.size(), table,
                    Arrays.toString(extraColumns.toArray()));
            problem = true;
        } else {
            LOG.debug("No extra columns found in table [{0}]", table);
        }
    }
    return problem;
}

From source file:eionet.cr.dao.virtuoso.VirtuosoStagingDatabaseDAO.java

@Override
public List<StagingDatabaseTableColumnDTO> getTablesColumns(String dbName) throws DAOException {

    ArrayList<StagingDatabaseTableColumnDTO> result = new ArrayList<StagingDatabaseTableColumnDTO>();

    ResultSet rs = null;//  ww  w  . ja  va  2  s .c  om
    Connection conn = null;
    try {
        conn = getSQLConnection();
        DatabaseMetaData metaData = conn.getMetaData();
        rs = metaData.getColumns(dbName, null, null, null);
        while (rs.next()) {
            String table = rs.getString("TABLE_NAME");
            String column = rs.getString("COLUMN_NAME");
            String dataType = rs.getString("TYPE_NAME");
            StagingDatabaseTableColumnDTO dto = new StagingDatabaseTableColumnDTO(table, column, dataType);
            result.add(dto);
        }
    } catch (SQLException e) {
        throw new DAOException("Failure getting the tables and columns of database: " + dbName, e);
    } finally {
        SQLUtil.close(rs);
        SQLUtil.close(conn);
    }

    return result;
}

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

@Test
public void testMetadata2() throws Exception {
    Connection conn = getConnection();

    List<String> tableList = Lists.newArrayList();
    DatabaseMetaData dbMetadata = conn.getMetaData();
    ResultSet resultSet = dbMetadata.getTables(null, "%", "%", new String[] { "TABLE" });
    while (resultSet.next()) {
        String schema = resultSet.getString("TABLE_SCHEM");
        String name = resultSet.getString("TABLE_NAME");

        System.out.println("Get table: schema=" + schema + ", name=" + name);
        tableList.add(schema + "." + name);

    }//from ww  w  .  j ava  2  s .  c om

    resultSet.close();
    Assert.assertTrue(tableList.contains("DEFAULT.TEST_KYLIN_FACT"));

    resultSet = dbMetadata.getColumns(null, "%", "TEST_KYLIN_FACT", "%");

    List<String> columns = Lists.newArrayList();
    while (resultSet.next()) {
        String name = resultSet.getString("COLUMN_NAME");
        String type = resultSet.getString("TYPE_NAME");

        System.out.println("Get column: name=" + name + ", data_type=" + type);
        columns.add(name);

    }

    Assert.assertTrue(columns.size() > 0 && columns.contains("CAL_DT"));
    resultSet.close();
    conn.close();
}

From source file:org.ensembl.healthcheck.util.DBUtils.java

public static boolean columnExists(Connection con, String table, String column) {

    boolean result = false;
    ResultSet rs = null;//from  ww w. j  a  v a  2  s  . co m
    try {

        DatabaseMetaData dbm = con.getMetaData();
        rs = dbm.getColumns(null, null, table, column);

        if (rs.next()) {
            result = true;
        }

    } catch (SQLException e) {
        throw new SqlUncheckedException("Could not check for table " + table, e);
    } finally {
        closeQuietly(rs);
    }

    return result;

}