Example usage for java.sql DatabaseMetaData getPrimaryKeys

List of usage examples for java.sql DatabaseMetaData getPrimaryKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. getPrimaryKeys

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getPrimaryKeys.

Prototype

ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;

Source Link

Document

Retrieves a description of the given table's primary key columns.

Usage

From source file:com.jaxio.celerio.configuration.database.support.MetadataExtractor.java

private void loadPrimaryKeys(JdbcConnectivity configuration, DatabaseMetaData databaseMetaData, Table table)
        throws SQLException {
    log.info("Extracting primary key for table: " + table.getName());

    ResultSet resultSet = databaseMetaData.getPrimaryKeys(configuration.getCatalog(),
            configuration.getSchemaName(), table.getName());
    ResultSetWrapper rsw = new ResultSetPrimaryKeys(resultSet, useLabel);

    while (resultSet.next()) {
        table.addPrimaryKey(getString(rsw, "COLUMN_NAME"));
    }/*from w ww.  j  a v  a 2  s  .  co  m*/

    resultSet.close();
}

From source file:org.apache.syncope.core.util.ImportExport.java

private void doExportTable(final TransformerHandler handler, final Connection conn, final String tableName)
        throws SQLException, SAXException {

    AttributesImpl attrs = new AttributesImpl();

    PreparedStatement stmt = null;
    ResultSet rs = null;/*  ww w . j  av a  2  s .c  om*/
    ResultSet pkeyRS = null;

    try {
        // ------------------------------------
        // retrieve primary keys to perform an ordered select

        final DatabaseMetaData meta = conn.getMetaData();
        pkeyRS = meta.getPrimaryKeys(null, null, tableName);

        final StringBuilder orderBy = new StringBuilder();

        while (pkeyRS.next()) {
            final String columnName = pkeyRS.getString("COLUMN_NAME");

            if (columnName != null) {
                if (orderBy.length() > 0) {
                    orderBy.append(",");
                }

                orderBy.append(columnName);
            }
        }

        // ------------------------------------
        stmt = conn.prepareStatement(
                "SELECT * FROM " + tableName + " a" + (orderBy.length() > 0 ? " ORDER BY " + orderBy : ""));

        rs = stmt.executeQuery();
        for (int rowNo = 0; rs.next(); rowNo++) {
            attrs.clear();

            final ResultSetMetaData rsMeta = rs.getMetaData();

            for (int i = 0; i < rsMeta.getColumnCount(); i++) {
                final String columnName = rsMeta.getColumnName(i + 1);
                final Integer columnType = rsMeta.getColumnType(i + 1);

                // Retrieve value taking care of binary values.
                String value = getValues(rs, columnName, columnType);

                if (value != null) {
                    attrs.addAttribute("", "", columnName, "CDATA", value);
                }
            }

            handler.startElement("", "", tableName, attrs);
            handler.endElement("", "", tableName);
        }
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                LOG.error("While closing result set", e);
            }
        }
        if (pkeyRS != null) {
            try {
                pkeyRS.close();
            } catch (SQLException e) {
                LOG.error("While closing result set", e);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                LOG.error("While closing result set", e);
            }
        }
    }
}

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

@Override
public TableMetadata getTableMetadata(final TableReference tableReference) {
    return JdbcUtils.doWithConnection(dataSource, new JdbcConnectionCallback<TableMetadata>() {

        @Override//from  ww w .j a  va 2s.  co  m
        public TableMetadata doWork(Connection connection) throws SQLException {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            if (identifierCaser == null) {
                identifierCaser = new DatabaseIdentifierCaser(databaseMetaData);
            }

            String tableName = identifierCaser.apply(tableReference.getTableName());

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

            return mapToTableMetadata(tableReference, resultSet);
        }

    });
}

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

/**
 * Retrieves primary keys of a table/*from   w  w w  .  ja v  a2  s. com*/
 * 
 * @param table
 *          Table object where name and schema are known
 * @return primary keys of the table as a list
 */
public List<String> getPrimaryKey(Structure table) {

    List<String> columnNameList = new ArrayList<String>();
    Connection conn = null;
    ResultSet rs = null;
    try {
        conn = getConnection();

        DatabaseMetaData metaData = conn.getMetaData();
        rs = metaData.getPrimaryKeys(null, table.getSchemaName(), table.getName());
        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:net.ymate.platform.persistence.jdbc.scaffold.JdbcScaffold.java

/**
 * @param dbName ???/*  w ww .  ja  v  a  2  s.  co  m*/
 * @param dbUserName ??
 * @param tableName ??
 * @return ?????
 */
private TableMeta getTableMeta(String dbName, String dbUserName, String tableName) {
    IConnectionHolder _connHolder = null;
    Statement _statement = null;
    ResultSet _resultSet = null;
    Map<String, ColumnInfo> _tableFields = new LinkedHashMap<String, ColumnInfo>();
    List<String> _pkFields = new LinkedList<String>();
    TableMeta _meta = new TableMeta(_pkFields, _tableFields);
    try {
        _connHolder = JDBC.getConnectionHolder();
        String _dbType = JDBC_SCAFFOLD_CONF.getProperty("ymp.scaffold.jbdc.db_type", "unknow");
        DatabaseMetaData _dbMetaData = _connHolder.getConnection().getMetaData();
        _resultSet = _dbMetaData.getPrimaryKeys(dbName,
                _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName, tableName);
        if (_resultSet == null) {
            _meta = null;
            System.err.println("Database table \"" + tableName + "\" primaryKey resultSet is null, ignored");
        } else {
            while (_resultSet.next()) {
                _pkFields.add(_resultSet.getString(4).toLowerCase());
            }
            if (_pkFields.isEmpty()) {
                _meta = null;
                System.err
                        .println("Database table \"" + tableName + "\" does not set the primary key, ignored");
            } else {
                _statement = _connHolder.getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
                _resultSet = _statement
                        .executeQuery("select * from " + _connHolder.getDialect().wapperQuotedIdent(tableName));
                ResultSetMetaData _rsMetaData = _resultSet.getMetaData();
                //
                for (int _idx = 1; _idx <= _rsMetaData.getColumnCount(); _idx++) {
                    // ??
                    ResultSet _column = _dbMetaData.getColumns(dbName,
                            _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName,
                            tableName, _rsMetaData.getColumnName(_idx));
                    if (_column.next()) {
                        // ????
                        _tableFields.put(_rsMetaData.getColumnName(_idx).toLowerCase(),
                                new ColumnInfo(_rsMetaData.getColumnName(_idx).toLowerCase(),
                                        compressType(_rsMetaData.getColumnClassName(_idx)),
                                        _rsMetaData.isAutoIncrement(_idx), _rsMetaData.isNullable(_idx),
                                        _column.getString("COLUMN_DEF")));
                    }
                }
                //
                System.err.println("TABLE_NAME: " + tableName + " ---------------->>");
                System.err.println("COLUMN_NAME\tPK\tCOLUMN_TYPE\tIS_AUTOINCREMENT\tIS_NULLABLE\tCOLUMN_DEF");
                for (ColumnInfo _cInfo : _tableFields.values()) {
                    System.err
                            .println(_cInfo.getColumnName() + "\t" + _pkFields.contains(_cInfo.getColumnName())
                                    + "\t" + _cInfo.getColumnType() + "\t" + _cInfo.isAutoIncrement() + "\t"
                                    + _cInfo.getNullable() + "\t" + _cInfo.getDefaultValue());
                }
            }
        }
    } catch (Throwable e) {
        if (e instanceof Error) {
            throw (Error) e;
        }
        throw new Error(RuntimeUtils.unwrapThrow(e));
    } finally {
        _connHolder.release();
        _statement = null;
        _resultSet = null;
    }
    return _meta;
}

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

private boolean checkPrimaryKey(DatabaseMetaData metaData, String catalog, String table,
        String expectedPrimaryKeyColumn) throws SQLException {
    boolean problem = false;
    ResultSet rs = metaData.getPrimaryKeys(catalog, null, table);
    if (!rs.next()) {
        LOG.error("Expected column [{0}] to be the primary key in table [{1}], but none were found",
                expectedPrimaryKeyColumn, table);
        problem = true;/*  w  ww . j  a  v a 2s  . c  o m*/
    } else {
        String foundPrimaryKeyColumn = rs.getString("COLUMN_NAME");
        if (!foundPrimaryKeyColumn.equals(expectedPrimaryKeyColumn)) {
            LOG.error(
                    "Expected column [{0}] to be the primary key in table [{1}], but found column [{2}] instead",
                    expectedPrimaryKeyColumn, table, foundPrimaryKeyColumn);
            problem = true;
        } else {
            LOG.debug("Found column [{0}] to be the primary key in table [{1}]", expectedPrimaryKeyColumn,
                    table);
        }
    }
    return problem;
}

From source file:org.diffkit.db.DKDBTableDataAccess.java

private List<Map<String, ?>> getPKMaps(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 primaryKeyRS = dbMeta_.getPrimaryKeys(catalogName, schemaName, tableName);
    _log.debug("primaryKeyRS->{}", primaryKeyRS);
    if (primaryKeyRS == null) {
        _log.warn("no primaryKeyRS for catalog_->{} schema_->{} tableName_->{}");
        return null;
    }/*w  ww .j a  v a 2s  .c om*/
    List<Map<String, ?>> pkMaps = DKSqlUtil.readRows(primaryKeyRS, true);
    _log.debug("pkMaps->{}", pkMaps);
    DKSqlUtil.close(primaryKeyRS);
    return pkMaps;
}

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

@Test
public void testIssue77() throws Exception {
    DatabaseMetaData md = con.getMetaData();
    System.out.println();/*from  w w  w  .  j  a v  a  2  s  .  c o  m*/
    System.out.println("Test Issue #77");
    System.out.println("--------------");

    // test various retrieval methods
    ResultSet result = md.getPrimaryKeys(con.getCatalog(), KEYSPACE, TABLE);
    assertTrue("Make sure we have found an pk", result.next());

    // check the column name from index
    String cn = result.getString("COLUMN_NAME");
    assertEquals("Column name match for pk", "keyname", cn);
    System.out.println("Found pk via dmd :   " + cn);
}

From source file:org.talend.components.snowflake.runtime.SnowflakeSourceOrSink.java

protected Schema getSchema(RuntimeContainer container, Connection connection, String tableName)
        throws IOException {
    Schema tableSchema = null;//from   w ww .ja v  a2  s  .c om

    SnowflakeConnectionProperties connProps = getEffectiveConnectionProperties(container);
    try {
        DatabaseMetaData metaData = connection.getMetaData();

        ResultSet resultSet = metaData.getColumns(getCatalog(connProps), getDbSchema(connProps), tableName,
                null);
        tableSchema = getSnowflakeAvroRegistry().inferSchema(resultSet);
        if (tableSchema == null) {
            throw new IOException(i18nMessages.getMessage("error.tableNotFound", tableName));
        }

        // Update the schema with Primary Key details
        // FIXME - move this into the inferSchema stuff
        ResultSet keysIter = metaData.getPrimaryKeys(getCatalog(connProps), getDbSchema(connProps), tableName);

        List<String> pkColumns = new ArrayList<>(); // List of Primary Key columns for this table
        while (keysIter.next()) {
            pkColumns.add(keysIter.getString("COLUMN_NAME"));
        }

        for (Field f : tableSchema.getFields()) {
            if (pkColumns.contains(f.name())) {
                f.addProp(SchemaConstants.TALEND_COLUMN_IS_KEY, "true");
            }
        }

    } catch (SQLException se) {
        throw new IOException(se);
    }

    return tableSchema;

}

From source file:net.ymate.platform.persistence.jdbc.scaffold.EntityGenerator.java

/**
 * @param dbName     ???/*from  ww w.j  ava2  s .  com*/
 * @param dbUserName ??
 * @param tableName  ??
 * @return ?????
 */
private TableMeta getTableMeta(String dbName, String dbUserName, String tableName) {
    IConnectionHolder _connHolder = null;
    Statement _statement = null;
    ResultSet _resultSet = null;
    Map<String, ColumnInfo> _tableFields = new LinkedHashMap<String, ColumnInfo>();
    List<String> _pkFields = new LinkedList<String>();
    TableMeta _meta = new TableMeta(_pkFields, _tableFields);
    try {
        _connHolder = __jdbc.getDefaultConnectionHolder();
        String _dbType = _connHolder.getDialect().getName();
        DatabaseMetaData _dbMetaData = _connHolder.getConnection().getMetaData();
        System.out.println(">>> Catalog: " + dbName);
        System.out.println(">>> Schema: " + dbUserName);
        System.out.println(">>> Table: " + tableName);
        _resultSet = _dbMetaData.getPrimaryKeys(dbName,
                _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName, tableName);
        if (_resultSet == null) {
            System.err.println("Database table \"" + tableName + "\" primaryKey resultSet is null, ignored");
            return null;
        } else {
            while (_resultSet.next()) {
                _pkFields.add(_resultSet.getString(4).toLowerCase());
            }
            if (_pkFields.isEmpty()) {
                System.err
                        .println("Database table \"" + tableName + "\" does not set the primary key, ignored");
                return null;
            } else {
                //
                System.out.println(">>> " + "COLUMN_NAME / " + "COLUMN_CLASS_NAME / " + "PRIMARY_KEY / "
                        + "AUTO_INCREMENT / " + "SIGNED / " + "PRECISION / " + "SCALE / " + "NULLABLE / "
                        + "DEFAULT / " + "REMARKS");
                //
                _statement = _connHolder.getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
                _resultSet = _statement.executeQuery(
                        "SELECT * FROM ".concat(_connHolder.getDialect().wrapIdentifierQuote(tableName)));
                ResultSetMetaData _rsMetaData = _resultSet.getMetaData();
                //
                for (int _idx = 1; _idx <= _rsMetaData.getColumnCount(); _idx++) {
                    // ??
                    ResultSet _column = _dbMetaData.getColumns(dbName,
                            _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName,
                            tableName, _rsMetaData.getColumnName(_idx));
                    if (_column.next()) {
                        // ????
                        _tableFields.put(_rsMetaData.getColumnName(_idx).toLowerCase(),
                                new ColumnInfo(_rsMetaData.getColumnName(_idx).toLowerCase(),
                                        _rsMetaData.getColumnClassName(_idx), _rsMetaData.isAutoIncrement(_idx),
                                        _rsMetaData.isSigned(_idx), _rsMetaData.getPrecision(_idx),
                                        _rsMetaData.getScale(_idx), _rsMetaData.isNullable(_idx),
                                        _column.getString("COLUMN_DEF"), _column.getString("REMARKS")));
                        System.out.println("--> " + _rsMetaData.getColumnName(_idx).toLowerCase() + "\t"
                                + _rsMetaData.getColumnClassName(_idx) + "\t"
                                + _pkFields.contains(_rsMetaData.getColumnName(_idx).toLowerCase()) + "\t"
                                + _rsMetaData.isAutoIncrement(_idx) + "\t" + _rsMetaData.isSigned(_idx) + "\t"
                                + _rsMetaData.getPrecision(_idx) + "\t" + _rsMetaData.getScale(_idx) + "\t"
                                + _rsMetaData.isNullable(_idx) + "\t" + _column.getString("COLUMN_DEF") + "\t"
                                + _column.getString("REMARKS"));
                    }
                    _column.close();
                }
            }
        }
    } catch (Exception e) {
        if (e instanceof RuntimeException) {
            throw (RuntimeException) e;
        }
        throw new RuntimeException(e);
    } finally {
        if (_statement != null) {
            try {
                _statement.close();
            } catch (SQLException e) {
                _LOG.warn("", e);
            }
        }
        if (_resultSet != null) {
            try {
                _resultSet.close();
            } catch (SQLException e) {
                _LOG.warn("", e);
            }
        }
        if (_connHolder != null) {
            _connHolder.release();
        }
    }
    return _meta;
}