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:org.pentaho.aggdes.model.mondrian.validate.CubePkValidator.java

public List<ValidationMessage> validateCube(Schema schema, Cube cube, Connection conn) {
    List<ValidationMessage> messages = new ArrayList<ValidationMessage>();

    Map<String, Boolean> checkedRelations = new HashMap<String, Boolean>();

    // ~ Get DatabaseMetaData ==========================================================================================
    DatabaseMetaData meta = null;
    try {//  w  w  w . j a v a  2s  .com
        meta = conn.getMetaData();
    } catch (SQLException e) {
        if (logger.isErrorEnabled()) {
            logger.error("an exception occurred", e); //$NON-NLS-1$
        }
        return fatal(e, messages);
    }

    if (logger.isDebugEnabled()) {
        logger.debug("processing cube \"" + cube.name + "\""); //$NON-NLS-1$ //$NON-NLS-2$
    }

    // TODO: include validation support for mondrian views
    if (!(cube.fact instanceof Table)) {
        if (logger.isDebugEnabled()) {
            logger.debug("cube \"" + cube.name + "\" contains unsupported fact type, " + cube.fact); //$NON-NLS-1$ //$NON-NLS-2$
        }
        return messages;
    }

    // ~ Check: Primary key on cube's fact table========================================================================

    String relationName = ((Table) cube.fact).name;
    String schemaName = ((Table) cube.fact).schema;

    if (logger.isDebugEnabled()) {
        logger.debug(
                "checking that primary key exists on relation \"" + (null == schemaName ? "" : schemaName + ".") //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
                        + relationName + "\""); //$NON-NLS-1$
    }

    ResultSet rs = null;
    try {
        rs = meta.getPrimaryKeys(null, schemaName, relationName);
    } catch (SQLException e) {
        if (logger.isErrorEnabled()) {
            logger.error("an exception occurred", e); //$NON-NLS-1$
        }
        return fatal(e, messages);
    }
    boolean pkFound = false;
    try {
        while (rs.next()) {
            pkFound = true;
            break;
        }
    } catch (SQLException e) {
        if (logger.isErrorEnabled()) {
            logger.error("an exception occurred", e); //$NON-NLS-1$
        }
        return fatal(e, messages);
    }
    if (!pkFound) {
        append(messages, ERROR, "ERROR_FACT_TABLE_PK_CHECK", cube.name, relationName); //$NON-NLS-1$
    } else {
        append(messages, OK, "OK_FACT_TABLE_PK_CHECK", cube.name, relationName); //$NON-NLS-1$
    }

    return messages;
}

From source file:it.eng.spagobi.meta.initializer.PhysicalModelInitializer.java

private void addPrimaryKey(DatabaseMetaData dbMeta, PhysicalModel model, PhysicalTable table) {
    PhysicalColumn column;// ww w  .  j  av a  2  s .  c  om
    PhysicalPrimaryKey primaryKey;
    ResultSet rs;

    primaryKey = null;

    try {
        rs = dbMeta.getPrimaryKeys(model.getCatalog(), model.getSchema(), table.getName());
        /*
         * 1. TABLE_CAT String => table catalog (may be null) 2. TABLE_SCHEM String => table schema (may be null) 3. TABLE_NAME String => table name 4.
         * COLUMN_NAME String => column name 5. KEY_SEQ short => sequence number within primary key 6. PK_NAME String => primary key name (may be null)
         */

        while (rs.next()) {
            if (primaryKey == null) {
                primaryKey = FACTORY.createPhysicalPrimaryKey();
                primaryKey.setName(rs.getString("PK_NAME"));

                primaryKey.setTable(table);
                model.getPrimaryKeys().add(primaryKey);

                getPropertiesInitializer().addProperties(primaryKey);
            }

            column = table.getColumn(rs.getString("COLUMN_NAME"));
            if (column != null) {
                primaryKey.getColumns().add(column);
            }

        }
        rs.close();

    } catch (Throwable t) {
        throw new RuntimeException("Impossible to retrive primaryKeys metadata", t);
    }
}

From source file:org.apache.ddlutils.task.DumpMetadataTask.java

/**
 * Dumps the primary key columns of the indicated table.
 * /*from ww w.  j a  v a2 s .  c om*/
 * @param xmlWriter   The xml writer to write to
 * @param metaData    The database metadata
 * @param catalogName The catalog name
 * @param schemaName  The schema name
 * @param tableName   The table name
 */
private void dumpPKs(PrettyPrintingXmlWriter xmlWriter, final DatabaseMetaData metaData,
        final String catalogName, final String schemaName, final String tableName) throws SQLException {
    performResultSetXmlOperation(xmlWriter, null, new ResultSetXmlOperation() {
        public ResultSet getResultSet() throws SQLException {
            return metaData.getPrimaryKeys(catalogName, schemaName, tableName);
        }

        public void handleRow(PrettyPrintingXmlWriter xmlWriter, ResultSet result) throws SQLException {
            Set columns = getColumnsInResultSet(result);
            String columnName = result.getString("COLUMN_NAME");

            if ((columnName != null) && (columnName.length() > 0)) {
                xmlWriter.writeElementStart(null, "primaryKey");
                xmlWriter.writeAttribute(null, "column", columnName);

                addStringAttribute(xmlWriter, "name", result, columns, "PK_NAME");
                addShortAttribute(xmlWriter, "sequenceNumberInPK", result, columns, "KEY_SEQ");

                xmlWriter.writeElementEnd();
            }
        }

        public void handleError(SQLException ex) {
            log("Could not read the primary keys for table '" + tableName + "' from the result set: "
                    + ex.getStackTrace(), Project.MSG_ERR);
        }
    });
}

From source file:it.unibas.spicy.persistence.relational.DAORelational.java

private void loadPrimaryKeys(IDataSourceProxy dataSource, DatabaseMetaData databaseMetaData, String catalog,
        String schemaName, boolean source, Statement statement, int scenarioNo, boolean web)
        throws SQLException {

    String[] tableTypes = new String[] { "TABLE" };
    ResultSet tableResultSet = databaseMetaData.getTables(catalog, schemaName, null, tableTypes);
    while (tableResultSet.next()) {
        String tableName = tableResultSet.getString("TABLE_NAME");
        if (!this.dataDescription.checkLoadTable(tableName)) {
            logger.debug("Excluding table: " + tableName);
            continue;
        }/*from  w  w w. j  a  va  2s  . c  o  m*/
        if (logger.isDebugEnabled())
            logger.debug("Searching primary keys. ANALYZING TABLE  = " + tableName);
        ResultSet resultSet = databaseMetaData.getPrimaryKeys(catalog, null, tableName);
        List<PathExpression> listOfPath = new ArrayList<PathExpression>();
        List<String> PKcolumnNames = new ArrayList<String>();
        while (resultSet.next()) {
            String columnName = resultSet.getString("COLUMN_NAME");
            if (logger.isDebugEnabled())
                logger.debug("Analyzing primary key: " + columnName);
            if (!this.dataDescription.checkLoadAttribute(tableName, columnName)) {
                continue;
            }
            if (logger.isDebugEnabled())
                logger.debug("Found a Primary Key: " + columnName);
            String keyPrimary = tableName + "." + columnName;
            listOfPath.add(DAORelationalUtility.generatePath(keyPrimary));

            //giannisk alter table, add primary key
            ////un-comment the following if Primary Key Constraints are to be considered
            PKcolumnNames.add("\"" + columnName + "\"");
        }
        if (!web && !PKcolumnNames.isEmpty()) {
            String table;
            if (source) {
                table = SpicyEngineConstants.SOURCE_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\"";
            } else {
                String newSchemaName = SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo;
                table = newSchemaName + ".\"" + tableName + "\"";
                statement.execute(
                        GenerateSQL.createTriggerFunction(table, newSchemaName, tableName, PKcolumnNames));
                statement.execute(GenerateSQL.createTriggerBeforeInsert(table, newSchemaName, tableName));
            }

            String primaryKeys = String.join(",", PKcolumnNames);
            statement.executeUpdate("ALTER TABLE " + table + " ADD PRIMARY KEY (" + primaryKeys + ");");
        }
        ////
        //}                 
        if (!listOfPath.isEmpty()) {
            KeyConstraint keyConstraint = new KeyConstraint(listOfPath, true);
            dataSource.addKeyConstraint(keyConstraint);
        }
    }
}

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  ww  .j a v a 2  s  . c om*/
            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.openmrs.module.spreadsheetimport.DatabaseBackend.java

private static void reverseEngineerDatabaseTable() throws Exception {
    tableColumnMap = new TreeMap<String, String>();
    tableColumnListMap = new TreeMap<String, List<String>>();
    Connection conn = null;//ww  w. j a va  2s .  c om
    Exception exception = null;
    try {
        // Connect to db
        Class.forName("com.mysql.jdbc.Driver").newInstance();

        Properties p = Context.getRuntimeProperties();
        String url = p.getProperty("connection.url");

        conn = DriverManager.getConnection(url, p.getProperty("connection.username"),
                p.getProperty("connection.password"));

        // All tables
        DatabaseMetaData dmd = conn.getMetaData();
        ResultSet rs = dmd.getTables(null, null, "", null);
        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");

            // All columns
            List<String> columnNames = new ArrayList<String>();
            ResultSet rsColumns = dmd.getColumns(null, null, tableName, "");
            while (rsColumns.next()) {
                columnNames.add(rsColumns.getString("COLUMN_NAME"));
            }
            rsColumns.close();

            //            // Remove imported keys
            ResultSet rsImportedKeys = dmd.getImportedKeys(null, null, tableName);
            while (rsImportedKeys.next()) {
                String columnName = rsImportedKeys.getString("FKCOLUMN_NAME");
                if (columnNames.contains(columnName) && "obs".equalsIgnoreCase(tableName)
                        && !"value_coded".equalsIgnoreCase(columnName)) { // hack: only allow obs.value_coded to go through
                    columnNames.remove(columnName);
                }
            }
            rsImportedKeys.close();

            List<String> clonedColumns = new ArrayList<String>();
            clonedColumns.addAll(columnNames);

            // Add to map
            for (String columnName : clonedColumns) {
                String tableDotColumn = tableName + "." + columnName;
                tableColumnMap.put(tableDotColumn, makePrettyTableDotColumn(tableDotColumn));
            }

            // Remove primary key
            ResultSet rsPrimaryKeys = dmd.getPrimaryKeys(null, null, tableName);
            while (rsPrimaryKeys.next()) {
                String columnName = rsPrimaryKeys.getString("COLUMN_NAME");
                if (columnNames.contains(columnName)) {
                    columnNames.remove(columnName);
                }
            }
            rsPrimaryKeys.close();

            tableColumnListMap.put(tableName, columnNames);

        }
    } catch (Exception e) {
        log.debug(e.toString());
        exception = e;
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
    }

    if (exception != null) {
        throw exception;
    }
}

From source file:org.apache.syncope.core.persistence.jpa.content.XMLContentExporter.java

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

    LOG.debug("Export table {}", tableName);

    AttributesImpl attrs = new AttributesImpl();

    PreparedStatement stmt = null;
    ResultSet rs = null;//from w w w  . j a va  2  s.c  o  m
    try {
        StringBuilder orderBy = new StringBuilder();

        DatabaseMetaData meta = conn.getMetaData();

        // ------------------------------------
        // retrieve foreign keys (linked to the same table) to perform an ordered select
        ResultSet pkeyRS = null;
        try {
            pkeyRS = meta.getImportedKeys(conn.getCatalog(), dbSchema, tableName);
            while (pkeyRS.next()) {
                if (tableName.equals(pkeyRS.getString("PKTABLE_NAME"))) {
                    String columnName = pkeyRS.getString("FKCOLUMN_NAME");
                    if (columnName != null) {
                        if (orderBy.length() > 0) {
                            orderBy.append(",");
                        }

                        orderBy.append(columnName);
                    }
                }
            }
        } finally {
            if (pkeyRS != null) {
                try {
                    pkeyRS.close();
                } catch (SQLException e) {
                    LOG.error("While closing result set", e);
                }
            }
        }

        // retrieve primary keys to perform an ordered select
        try {
            pkeyRS = meta.getPrimaryKeys(null, null, tableName);
            while (pkeyRS.next()) {
                String columnName = pkeyRS.getString("COLUMN_NAME");
                if (columnName != null) {
                    if (orderBy.length() > 0) {
                        orderBy.append(",");
                    }

                    orderBy.append(columnName);
                }
            }
        } finally {
            if (pkeyRS != null) {
                try {
                    pkeyRS.close();
                } catch (SQLException e) {
                    LOG.error("While closing result set", e);
                }
            }
        }

        // ------------------------------------
        StringBuilder query = new StringBuilder();
        query.append("SELECT * FROM ").append(tableName).append(" a");
        if (StringUtils.isNotBlank(whereClause)) {
            query.append(" WHERE ").append(whereClause);
        }
        if (orderBy.length() > 0) {
            query.append(" ORDER BY ").append(orderBy);
        }
        stmt = conn.prepareStatement(query.toString());

        rs = stmt.executeQuery();
        while (rs.next()) {
            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 && (!COLUMNS_TO_BE_NULLIFIED.containsKey(tableName)
                        || !COLUMNS_TO_BE_NULLIFIED.get(tableName).contains(columnName))) {

                    attrs.addAttribute("", "", columnName, "CDATA", value);
                }
            }

            handler.startElement("", "", tableName, attrs);
            handler.endElement("", "", tableName);

            LOG.debug("Add record {}", attrs);
        }
    } finally {
        if (rs != null) {
            try {
                rs.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:com.glaf.core.util.DBUtils.java

public static List<String> getPrimaryKeys(Connection connection, String tableName) {
    ResultSet rs = null;// w ww.j a va 2s.c o m
    List<String> primaryKeys = new java.util.ArrayList<String>();
    try {
        String dbType = DBConnectionFactory.getDatabaseType(connection);
        DatabaseMetaData metaData = connection.getMetaData();

        if ("h2".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("oracle".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("db2".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("mysql".equals(dbType)) {
            tableName = tableName.toLowerCase();
        } else if (POSTGRESQL.equals(dbType)) {
            tableName = tableName.toLowerCase();
        }

        rs = metaData.getPrimaryKeys(null, null, tableName);
        while (rs.next()) {
            primaryKeys.add(rs.getString("column_name").toLowerCase());
        }

        // logger.debug(tableName + " primaryKeys:" + primaryKeys);
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
    }
    return primaryKeys;
}

From source file:com.glaf.core.util.DBUtils.java

public static List<String> getPrimaryKeys(String systemName, String tableName) {
    List<String> primaryKeys = new java.util.ArrayList<String>();
    Connection connection = null;
    ResultSet rs = null;/*from   ww  w  .j a v a2  s  .  c  om*/
    try {
        connection = DBConnectionFactory.getConnection(systemName);
        String dbType = DBConnectionFactory.getDatabaseType(connection);
        DatabaseMetaData metaData = connection.getMetaData();

        if ("h2".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("oracle".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("db2".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("mysql".equals(dbType)) {
            tableName = tableName.toLowerCase();
        } else if (POSTGRESQL.equals(dbType)) {
            tableName = tableName.toLowerCase();
        }

        rs = metaData.getPrimaryKeys(null, null, tableName);
        while (rs.next()) {
            primaryKeys.add(rs.getString("column_name"));
        }

    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(connection);
    }
    return primaryKeys;
}

From source file:com.glaf.core.util.DBUtils.java

public static List<String> getPrimaryKeys(String tableName) {
    List<String> primaryKeys = new java.util.ArrayList<String>();
    Connection connection = null;
    ResultSet rs = null;//w  w w  .  ja  va 2  s  . c  o m
    try {
        connection = DBConnectionFactory.getConnection();
        String dbType = DBConnectionFactory.getDatabaseType(connection);
        DatabaseMetaData metaData = connection.getMetaData();

        if ("h2".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("oracle".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("db2".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("mysql".equals(dbType)) {
            tableName = tableName.toLowerCase();
        } else if (POSTGRESQL.equals(dbType)) {
            tableName = tableName.toLowerCase();
        }

        rs = metaData.getPrimaryKeys(null, null, tableName);
        while (rs.next()) {
            primaryKeys.add(rs.getString("column_name"));
        }

        // logger.debug(tableName + " primaryKeys:" + primaryKeys);
    } catch (Exception ex) {
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(connection);
    }
    return primaryKeys;
}