Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.PolicyDAOImpl.java

private Policy persistPolicy(Policy policy) throws PolicyManagerDAOException {
    Connection conn;/*from  ww  w.j  ava  2  s .c  om*/
    PreparedStatement stmt = null;
    ResultSet generatedKeys = null;
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();

    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_POLICY (NAME, PROFILE_ID, TENANT_ID, PRIORITY, COMPLIANCE, OWNERSHIP_TYPE,"
                + "UPDATED, ACTIVE, DESCRIPTION) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(query, new String[] { "id" });

        stmt.setString(1, policy.getPolicyName());
        stmt.setInt(2, policy.getProfile().getProfileId());
        stmt.setInt(3, tenantId);
        stmt.setInt(4, readHighestPriorityOfPolicies());
        stmt.setString(5, policy.getCompliance());
        stmt.setString(6, policy.getOwnershipType());
        stmt.setInt(7, 0);
        stmt.setInt(8, 0);
        stmt.setString(9, policy.getDescription());

        int affectedRows = stmt.executeUpdate();

        if (affectedRows == 0 && log.isDebugEnabled()) {
            String msg = "No rows are updated on the policy table.";
            log.debug(msg);
        }
        generatedKeys = stmt.getGeneratedKeys();

        if (generatedKeys.next()) {
            policy.setId(generatedKeys.getInt(1));
        }
        // checking policy id here, because it object could have passed with id from the calling method.
        if (policy.getId() == 0) {
            throw new RuntimeException("No rows were inserted, policy id cannot be null.");
        }

    } catch (SQLException e) {
        throw new PolicyManagerDAOException("Error occurred while adding policy to the database", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, generatedKeys);
    }
    return policy;
}

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

public int addTextDataContent(String content) throws ApolloDatabaseException, Md5UtilsException {

    String md5 = "";
    try {//w  w w .  j av  a 2 s  .  c o  m
        md5 = DigestUtils.md5Hex(content);
    } catch (NullPointerException npe) {
        System.out.println("?");
    }

    String query = "INSERT IGNORE INTO run_data_content (text_content, md5_hash_of_content, md5_collision_id) values (?,?,?)";

    try (Connection conn = datasource.getConnection()) {
        PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        int highestMD5CollisionId = getHighestMD5CollisionIdForRunDataContent(conn, content);

        pstmt.setString(1, content);
        pstmt.setString(2, md5);
        pstmt.setInt(3, highestMD5CollisionId);
        int rowsAffected = pstmt.executeUpdate();
        if (rowsAffected > 0) {
            ResultSet rs = pstmt.getGeneratedKeys();
            rs.next();
            return rs.getInt(1);
        } else {
            //should check to see if we have a real collision
            query = "SELECT id, text_content FROM run_data_content where md5_hash_of_content = ?";

            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, md5);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                // no need to store the data twice

                // let's not be too hasty, we need to see if it's a TRUE cache hit,
                // so compare the "content" parameter, with rs.getString(2)
                // if it's a FALSE cache hit..we need to insert the new content,
                // with an incremented md5_collision_id
                String existingContent = rs.getString(2);
                if (existingContent.equals(content)) {
                    // this is a true cache hit, so return the ID
                    return rs.getInt(1);
                } else {
                    throw new ApolloDatabaseException("MD5 collision detected (" + md5 + ")!\n\n "
                            + existingContent + " not equal to\n\n " + content);
                }
            } else {
                throw new ApolloDatabaseException("Unable to retrieve data for hash: " + md5 + "!\n\n");
            }
        }
    } catch (SQLException ex) {
        throw new ApolloDatabaseException(
                "SQLException adding text data content with hash \"" + md5 + "\": " + ex.getMessage());
    }

}

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

public int associateContentWithRunId(BigInteger runKey, int dataContentKey, int runDataDescriptionId)
        throws ApolloDatabaseException, ApolloDatabaseKeyNotFoundException {

    if (runDataDescriptionId >= 0) {
        String query = "INSERT IGNORE INTO run_data (run_id, description_id, content_id) values (?,?,?)";

        PreparedStatement pstmt;
        try (Connection conn = datasource.getConnection()) {

            pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            pstmt.setInt(1, runKey.intValue());

            pstmt.setInt(2, runDataDescriptionId);
            pstmt.setInt(3, dataContentKey);
            int rowsAffected = pstmt.executeUpdate();
            if (rowsAffected > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                rs.next();//ww w . ja  v  a2 s . c  o  m
                return rs.getInt(1);
            } else {
                pstmt.close();
                query = "SELECT id FROM run_data WHERE run_id = ? AND description_id = ? and content_id = ?";
                try {
                    pstmt = conn.prepareStatement(query);
                    pstmt.setInt(1, runKey.intValue());
                    pstmt.setInt(2, runDataDescriptionId);
                    pstmt.setInt(3, dataContentKey);
                    ResultSet rs = pstmt.executeQuery();
                    if (rs.next()) {
                        return rs.getInt(1);
                    } else {
                        throw new ApolloDatabaseException("Could not get id for apparently existing run_data.");
                    }
                } finally {
                    pstmt.close();
                }
            }
        } catch (SQLException ex) {
            throw new ApolloDatabaseException(
                    "SQLException associating content with run ID " + runKey + ": " + ex.getMessage());
        }
    } else {
        throw new ApolloDatabaseKeyNotFoundException(
                "associateContentWithRunId() called with an invalid key: " + runKey);
    }

}

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

public BigInteger[] addVisualizationRun(RunVisualizationMessage runVisualizationMessage, int md5CollisionId,
        Authentication authentication)//from w  ww.  j a  v a2  s.  co  m
        throws ApolloDatabaseException, ApolloDatabaseRecordNotInsertedException, Md5UtilsException {

    String userName = authentication.getRequesterId();
    String password = authentication.getRequesterPassword();

    String[] userIdTokens = parseUserId(userName);
    userName = userIdTokens[0];

    int userKey = getUserKey(userName, password);

    int softwareKey = getSoftwareIdentificationKey(runVisualizationMessage.getSoftwareIdentification());

    try (Connection conn = datasource.getConnection()) {
        //conn = getConn();
        List<BigInteger> runIds = new ArrayList<>();
        for (RunIdentificationAndLabel runIdentificationAndLabel : runVisualizationMessage
                .getSimulationRunIds()) {
            runIds.add(runIdentificationAndLabel.getRunIdentification());
        }
        BigInteger simulationGroupId = getNewSimulationGroupId();
        addRunIdsToSimulationGroup(simulationGroupId, runIds);

        String query = "INSERT INTO run (md5_hash_of_run_message, software_id, requester_id, last_service_to_be_called, simulation_group_id, md5_collision_id) VALUES (?, ?, ?, ?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, md5Utils.getMd5(runVisualizationMessage));
        pstmt.setInt(2, softwareKey);
        pstmt.setInt(3, userKey);
        pstmt.setInt(4, 4); // 4 is translator
        pstmt.setLong(5, simulationGroupId.longValue());
        pstmt.setInt(6, md5CollisionId);
        pstmt.execute();

        BigInteger runId = null;
        ResultSet rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            runId = new BigInteger(rs.getString(1));
        } else {
            throw new ApolloDatabaseRecordNotInsertedException("Record not inserted!");
        }

        // ALSO NEED TO ADD serialized runVisualizationMessage(JSON) to
        // run_data_content table...
        // use insertDataContentForRun for this
        int dataContentKey = addTextDataContent(jsonUtils.getJSONString(runVisualizationMessage));
        int runDataDescriptionId = getRunDataDescriptionId(ContentDataFormatEnum.TEXT, "run_message.json",
                ContentDataTypeEnum.RUN_MESSAGE, 0,
                getSoftwareIdentificationKey(runVisualizationMessage.getSoftwareIdentification()));
        // int runDataId = the following line returns the runDataId, but
        // it's not used at this point.
        associateContentWithRunId(new BigInteger(String.valueOf(runId)), dataContentKey, runDataDescriptionId);
        BigInteger[] runIdSimulationGroupId = new BigInteger[2];
        runIdSimulationGroupId[0] = runId;
        runIdSimulationGroupId[1] = simulationGroupId;
        return runIdSimulationGroupId;
        //  } catch (ClassNotFoundException ex) {
        //     throw new ApolloDatabaseException(
        //            "ClassNotFoundException attempting to add visualization run: "
        //                   + ex.getMessage());
    } catch (SQLException ex) {
        throw new ApolloDatabaseException(
                "SQLException attempting to add visualization run: " + ex.getMessage());
    }
}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCResourceDAO.java

public int addContentBytes(InputStream contentStream) throws RegistryException {

    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    int contentID = -1;

    try {/*from   www.ja v  a 2 s.co m*/
        String sql = "INSERT INTO REG_CONTENT (REG_CONTENT_DATA, REG_TENANT_ID) VALUES (?, ?)";
        String sql1 = "SELECT MAX(REG_CONTENT_ID) FROM REG_CONTENT";

        int size = contentStream.available();
        PreparedStatement ps, ps1 = null;
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_CONTENT_ID") });
        } else {
            ps = conn.prepareStatement(sql);
        }
        try {
            ps.setBinaryStream(1, contentStream, size);
            ps.setInt(2, CurrentSession.getTenantId());
            ResultSet result;
            if (returnsGeneratedKeys) {
                ps.executeUpdate();
                result = ps.getGeneratedKeys();
            } else {
                synchronized (ADD_CONTENT_LOCK) {
                    ps.executeUpdate();
                    if (dbProductName.equals("OpenEdge RDBMS")) {
                        String sql2 = "UPDATE REG_CONTENT SET REG_CONTENT_ID = "
                                + "PUB.REG_CONTENT_SEQUENCE.NEXTVAL WHERE REG_CONTENT_ID = 0";
                        PreparedStatement ps2 = null;
                        try {
                            ps2 = conn.prepareStatement(sql2);
                            ps2.executeUpdate();
                        } finally {
                            if (ps2 != null) {
                                ps2.close();
                            }
                        }
                    }
                    ps1 = conn.prepareStatement(sql1);
                    result = ps1.executeQuery();
                }
            }
            try {
                if (result.next()) {
                    contentID = result.getInt(1);
                }
            } finally {
                if (result != null) {
                    result.close();
                }
            }
        } finally {
            try {
                if (ps1 != null) {
                    ps1.close();
                }
            } finally {
                if (ps != null) {
                    ps.close();
                }
            }
        }
    } catch (IOException e) {
        String msg = "An error occurred while processing content stream.";
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } catch (SQLException e) {
        String msg = "Failed to write resource content to the database.";
        log.error(msg, e);
        throw new RegistryException(msg, e);
    }
    return contentID;
}

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

public int getRunKey(RunSimulationMessage runSimulationMessage)
        throws ApolloDatabaseException, Md5UtilsException {
    Authentication auth = runSimulationMessage.getAuthentication();

    int userKey = getUserKey(auth.getRequesterId(), auth.getRequesterPassword());
    int softwareKey = getSoftwareIdentificationKey(runSimulationMessage.getSoftwareIdentification());

    String hash = md5Utils.getMd5(runSimulationMessage);

    String query = "SELECT id FROM run WHERE md5_hash_of_run_message = ?";
    try (Connection conn = datasource.getConnection()) {
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, hash);// www .java 2s .c o m
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            return rs.getInt(1);
        } else {
            query = "INSERT INTO run (requester_id, software_id, md5_hash_of_run_message) VALUES (?,?,?)";
            pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            pstmt.setInt(1, userKey);
            pstmt.setInt(2, softwareKey);
            pstmt.setString(3, hash);
            pstmt.execute();
            rs = pstmt.getGeneratedKeys();
            rs.next();
            return rs.getInt(1);
        }
    } catch (SQLException ex) {
        throw new ApolloDatabaseException("SQLException getting run key: " + ex.getMessage());
    }
}

From source file:org.wso2.carbon.repository.core.jdbc.dao.JDBCResourceDAO.java

public int addContentBytes(InputStream contentStream) throws RepositoryException {
    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    int contentID = -1;

    try {/*  w ww.  j  av a2s. c  o  m*/
        String sql = "INSERT INTO REG_CONTENT (REG_CONTENT_DATA, REG_TENANT_ID) VALUES (?, ?)";
        String sql1 = "SELECT MAX(REG_CONTENT_ID) FROM REG_CONTENT";

        int size = contentStream.available();
        PreparedStatement ps, ps1 = null;
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);

        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_CONTENT_ID") });
        } else {
            ps = conn.prepareStatement(sql);
        }

        try {
            ps.setBinaryStream(1, contentStream, size);
            ps.setInt(2, CurrentContext.getTenantId());
            ResultSet result;
            if (returnsGeneratedKeys) {
                ps.executeUpdate();
                result = ps.getGeneratedKeys();
            } else {
                synchronized (ADD_CONTENT_LOCK) {
                    ps.executeUpdate();
                    if (dbProductName.equals("OpenEdge RDBMS")) {
                        String sql2 = "UPDATE REG_CONTENT SET REG_CONTENT_ID = "
                                + "PUB.REG_CONTENT_SEQUENCE.NEXTVAL WHERE REG_CONTENT_ID = 0";
                        PreparedStatement ps2 = null;
                        try {
                            ps2 = conn.prepareStatement(sql2);
                            ps2.executeUpdate();
                        } finally {
                            if (ps2 != null) {
                                ps2.close();
                            }
                        }
                    }
                    ps1 = conn.prepareStatement(sql1);
                    result = ps1.executeQuery();
                }
            }
            try {
                if (result.next()) {
                    contentID = result.getInt(1);
                }
            } finally {
                if (result != null) {
                    result.close();
                }
            }
        } finally {
            try {
                if (ps1 != null) {
                    ps1.close();
                }
            } finally {
                if (ps != null) {
                    ps.close();
                }
            }
        }
    } catch (IOException e) {
        String msg = "An error occurred while processing content stream.";
        log.error(msg, e);
        throw new RepositoryServerContentException(msg, e);
    } catch (SQLException e) {
        String msg = "Failed to write resource content to the database.";
        log.error(msg, e);
        throw new RepositoryDBException(msg, e);
    }
    return contentID;
}

From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java

/**
 * Resolves counter id./*from ww w.j av a2  s. co  m*/
 *
 * @param counter Given counter
 * @param conn Connection to database
 * @return Id
 * @throws java.sql.SQLException
 */
private long getCounterId(Counter counter, Connection conn) throws SQLException {
    PreparedStatement select = null;
    PreparedStatement insert = null;
    ResultSet rsSelect = null;
    ResultSet rsInsert = null;

    try {
        select = conn.prepareStatement(crudQueries.getStmtSelectCounter());
        select.setString(1, counter.getName());

        rsSelect = select.executeQuery();

        if (rsSelect.next()) {
            return rsSelect.getLong(1);
        }

        insert = conn.prepareStatement(crudQueries.getStmtInsertCounter(), Statement.RETURN_GENERATED_KEYS);
        insert.setString(1, counter.getName());
        insert.executeUpdate();

        rsInsert = insert.getGeneratedKeys();

        if (!rsInsert.next()) {
            throw new SqoopException(CommonRepositoryError.COMMON_0010);
        }

        return rsInsert.getLong(1);
    } finally {
        closeResultSets(rsSelect, rsInsert);
        closeStatements(select, insert);
    }
}

From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java

/**
 * Resolves counter group database id./*w  w  w  .  j ava2s.com*/
 *
 * @param group Given group
 * @param conn Connection to database
 * @return Id
 * @throws java.sql.SQLException
 */
private long getCounterGroupId(CounterGroup group, Connection conn) throws SQLException {
    PreparedStatement select = null;
    PreparedStatement insert = null;
    ResultSet rsSelect = null;
    ResultSet rsInsert = null;

    try {
        select = conn.prepareStatement(crudQueries.getStmtSelectCounterGroup());
        select.setString(1, group.getName());

        rsSelect = select.executeQuery();

        if (rsSelect.next()) {
            return rsSelect.getLong(1);
        }

        insert = conn.prepareStatement(crudQueries.getStmtInsertCounterGroup(),
                Statement.RETURN_GENERATED_KEYS);
        insert.setString(1, group.getName());
        insert.executeUpdate();

        rsInsert = insert.getGeneratedKeys();

        if (!rsInsert.next()) {
            throw new SqoopException(CommonRepositoryError.COMMON_0010);
        }

        return rsInsert.getLong(1);
    } finally {
        closeResultSets(rsSelect, rsInsert);
        closeStatements(select, insert);
    }
}

From source file:org.wso2.appcloud.core.dao.ApplicationDAO.java

/**
 * Method for inserting deployment record.
 *
 * @param dbConnection database connection
 * @param deployment   deployment object
 * @param tenantId     id of tenant//w ww  . j  a v  a2 s  . c om
 * @return deployment ID
 * @throws AppCloudException
 */
private int addDeployment(Connection dbConnection, Deployment deployment, int tenantId)
        throws AppCloudException {

    PreparedStatement preparedStatement = null;
    int deploymentId = -1;
    ResultSet resultSet = null;

    try {

        preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.ADD_DEPLOYMENT,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, deployment.getDeploymentName());
        preparedStatement.setInt(2, deployment.getReplicas());
        preparedStatement.setInt(3, tenantId);
        preparedStatement.execute();

        resultSet = preparedStatement.getGeneratedKeys();

        if (resultSet.next()) {
            deploymentId = resultSet.getInt(1);
        }

        for (Container container : deployment.getContainers()) {
            addContainer(dbConnection, container, deploymentId, tenantId);
        }

    } catch (SQLException e) {
        String msg = "Error while inserting deployment record in tenant : " + tenantId;
        throw new AppCloudException(msg, e);
    } finally {
        DBUtil.closeResultSet(resultSet);
        DBUtil.closePreparedStatement(preparedStatement);
    }
    if (deploymentId == -1) {
        throw new AppCloudException("Failed to insert deployment record in tenant : " + tenantId);
    }
    return deploymentId;
}