Example usage for java.sql PreparedStatement executeBatch

List of usage examples for java.sql PreparedStatement executeBatch

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

Introduction

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

Prototype

int[] executeBatch() throws SQLException;

Source Link

Document

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Usage

From source file:org.obm.domain.dao.CalendarDaoJdbcImpl.java

private Event removeEventByExtId(Connection con, ObmUser calendar, AccessToken token, EventExtId extId) {
    Event ev = findEventByExtId(token, calendar, extId);
    if (ev == null) {
        return null;
    }/*from  www .ja v a2s.  co  m*/
    PreparedStatement dev = null;
    try {
        dev = con.prepareStatement(
                "INSERT INTO DeletedEvent (deletedevent_event_id, deletedevent_user_id, deletedevent_origin, deletedevent_timestamp, deletedevent_event_ext_id) "
                        + "VALUES (?, ?, ?, now(), ?)");
        EventObmId databaseId = ev.getObmId();
        for (Attendee at : ev.getAttendees()) {
            Integer userId = userDao.userIdFromEmail(con, at.getEmail(), token.getDomain().getId());
            if (userId != null) {
                dev.setInt(1, databaseId.getObmId());
                dev.setInt(2, userId);
                dev.setString(3, token.getOrigin());
                dev.setString(4, extId.getExtId());
                dev.addBatch();
            }
        }
        dev.executeBatch();
        dev.close();

        removeEventExceptions(con, databaseId);

        dev = con.prepareStatement("DELETE FROM Event WHERE event_id=?");
        dev.setInt(1, databaseId.getObmId());
        dev.executeUpdate();
        if (dev.getUpdateCount() <= 0) {
            throw new EventNotFoundException("Unexpected Event deletion failure : " + databaseId.getObmId());
        }
    } catch (Throwable se) {
        logger.error(se.getMessage(), se);
    } finally {
        obmHelper.cleanup(null, dev, null);
    }

    return ev;
}

From source file:org.wso2.carbon.is.migration.service.v510.migrator.IdentityDataMigrator.java

/**
 * migrate data in the identity database and finalize the database table restructuring
 *//*w  w  w  .j a  v  a  2s  .c  o m*/
public void migrateIdentityData() throws MigrationClientException {
    log.info("MIGRATION-LOGS >> Going to start : migrateIdentityData.");
    Connection identityConnection = null;
    PreparedStatement selectFromAccessTokenPS = null;
    PreparedStatement insertScopeAssociationPS = null;
    PreparedStatement insertTokenScopeHashPS = null;
    PreparedStatement insertTokenIdPS = null;
    PreparedStatement updateUserNamePS = null;
    PreparedStatement selectFromAuthorizationCodePS = null;
    PreparedStatement updateUserNameAuthorizationCodePS = null;
    PreparedStatement selectIdnAssociatedIdPS = null;
    PreparedStatement updateIdnAssociatedIdPS = null;
    PreparedStatement selectConsumerAppsPS = null;
    PreparedStatement updateConsumerAppsPS = null;

    ResultSet accessTokenRS = null;
    ResultSet authzCodeRS = null;
    ResultSet selectIdnAssociatedIdRS = null;
    ResultSet selectConsumerAppsRS = null;
    try {
        identityConnection = getDataSource().getConnection();
        identityConnection.setAutoCommit(false);

        try {
            selectConsumerAppsPS = identityConnection.prepareStatement(SQLQueries.SELECT_FROM_CONSUMER_APPS);
            updateConsumerAppsPS = identityConnection.prepareStatement(SQLQueries.UPDATE_CONSUMER_APPS);

            selectConsumerAppsRS = selectConsumerAppsPS.executeQuery();
            log.info("MIGRATION-LOGS >> Executed query : " + selectConsumerAppsPS.toString());
            boolean isConsumerAppsAvail = false;
            while (selectConsumerAppsRS.next()) {
                int id = selectConsumerAppsRS.getInt("ID");
                String username = selectConsumerAppsRS.getString("USERNAME");
                String userDomainFromDB = selectConsumerAppsRS.getString("USER_DOMAIN");

                try {
                    if (userDomainFromDB == null) {
                        String userDomain = UserCoreUtil.extractDomainFromName(username);
                        username = UserCoreUtil.removeDomainFromName(username);

                        updateConsumerAppsPS.setString(1, username);
                        updateConsumerAppsPS.setString(2, userDomain);
                        updateConsumerAppsPS.setInt(3, id);
                        if (isBatchUpdate()) {
                            isConsumerAppsAvail = true;
                            updateConsumerAppsPS.addBatch();
                        } else {
                            updateConsumerAppsPS.executeUpdate();
                            log.info("MIGRATION-LOGS >> Executed query : " + updateConsumerAppsPS.toString());
                        }
                        if (log.isDebugEnabled()) {
                            log.debug("MIGRATION-LOGS >> migrating consumer app :" + id);
                        }
                    }
                } catch (Exception e) {
                    log.error("MIGRATION-ERROR-LOGS-011 >> Error while executing the migration.", e);
                    if (!isContinueOnError()) {
                        throw new MigrationClientException("Error while executing the migration.", e);
                    }
                }
            }
            if (isConsumerAppsAvail && isBatchUpdate()) {
                int[] ints = updateConsumerAppsPS.executeBatch();
                log.info("MIGRATION-LOGS >> Executed query : " + updateConsumerAppsPS.toString());
            }
        } catch (Exception e) {
            log.error("MIGRATION-ERROR-LOGS-012 >> Error while executing the migration.", e);
            if (!isContinueOnError()) {
                throw new MigrationClientException("Error while executing the migration.", e);
            }
        }

        String selectFromAccessToken = SQLQueries.SELECT_FROM_ACCESS_TOKEN;
        selectFromAccessTokenPS = identityConnection.prepareStatement(selectFromAccessToken);

        String insertScopeAssociation = SQLQueries.INSERT_SCOPE_ASSOCIATION;
        insertScopeAssociationPS = identityConnection.prepareStatement(insertScopeAssociation);

        String insertTokenScopeHash = SQLQueries.INSERT_TOKEN_SCOPE_HASH;
        insertTokenScopeHashPS = identityConnection.prepareStatement(insertTokenScopeHash);

        String insertTokenId = SQLQueries.INSERT_TOKEN_ID;
        insertTokenIdPS = identityConnection.prepareStatement(insertTokenId);

        String updateUserName = SQLQueries.UPDATE_USER_NAME;
        updateUserNamePS = identityConnection.prepareStatement(updateUserName);

        try {
            accessTokenRS = selectFromAccessTokenPS.executeQuery();
            log.info("MIGRATION-LOGS >> Executed query : " + selectFromAccessTokenPS.toString());
            while (accessTokenRS.next()) {
                String accessToken = null;
                try {
                    accessToken = accessTokenRS.getString("ACCESS_TOKEN");
                    String scopeString = accessTokenRS.getString("TOKEN_SCOPE");
                    String authzUser = accessTokenRS.getString("AUTHZ_USER");
                    String tokenIdFromDB = accessTokenRS.getString("TOKEN_ID");

                    if (tokenIdFromDB == null) {
                        String tokenId = UUID.randomUUID().toString();

                        String username = UserCoreUtil
                                .removeDomainFromName(MultitenantUtils.getTenantAwareUsername(authzUser));
                        String userDomain = UserCoreUtil.extractDomainFromName(authzUser);
                        int tenantId = ISMigrationServiceDataHolder.getRealmService().getTenantManager()
                                .getTenantId(MultitenantUtils.getTenantDomain(authzUser));

                        try {
                            insertTokenIdPS.setString(1, tokenId);
                            insertTokenIdPS.setString(2, accessToken);

                            if (isBatchUpdate()) {
                                insertTokenIdPS.addBatch();
                            } else {
                                insertTokenIdPS.executeUpdate();
                                log.info("MIGRATION-LOGS >> Executed query : " + insertTokenIdPS.toString());
                            }
                        } catch (Exception e) {
                            log.error("MIGRATION-ERROR-LOGS-013 >> Error while executing the migration.", e);
                            if (!isContinueOnError()) {
                                throw new MigrationClientException("Error while executing the migration.", e);
                            }
                        }

                        try {
                            updateUserNamePS.setString(1, username);
                            updateUserNamePS.setInt(2, tenantId);
                            updateUserNamePS.setString(3, userDomain);
                            updateUserNamePS.setString(4, authzUser);
                            updateUserNamePS.setString(5, accessToken);
                            if (isBatchUpdate()) {
                                updateUserNamePS.addBatch();
                            } else {
                                updateConsumerAppsPS.executeUpdate();
                                log.info("MIGRATION-LOGS >> Executed query : "
                                        + updateConsumerAppsPS.toString());
                            }
                        } catch (Exception e) {
                            log.error("MIGRATION-ERROR-LOGS-014 >> Error while executing the migration.", e);
                            if (!isContinueOnError()) {
                                throw new MigrationClientException("Error while executing the migration.", e);
                            }
                        }

                        try {
                            insertTokenScopeHashPS.setString(1, DigestUtils.md5Hex(scopeString));
                            insertTokenScopeHashPS.setString(2, accessToken);
                            if (isBatchUpdate()) {
                                insertTokenScopeHashPS.addBatch();
                            } else {
                                insertTokenScopeHashPS.executeUpdate();
                                log.info("MIGRATION-LOGS >> Executed query : "
                                        + insertTokenScopeHashPS.toString());
                            }
                        } catch (Exception e) {
                            log.error("MIGRATION-ERROR-LOGS-015 >> Error while executing the migration.", e);
                            if (!isContinueOnError()) {
                                throw new MigrationClientException("Error while executing the migration.", e);
                            }
                        }

                        if (log.isDebugEnabled()) {
                            log.debug("MIGRATION-LOGS >> migrating access token : " + accessToken);
                        }

                        if (scopeString != null) {
                            String scopes[] = scopeString.split(" ");
                            for (String scope : scopes) {
                                try {
                                    insertScopeAssociationPS.setString(1, tokenId);
                                    insertScopeAssociationPS.setString(2, scope);
                                    if (isBatchUpdate()) {
                                        insertScopeAssociationPS.addBatch();
                                    } else {
                                        insertScopeAssociationPS.executeUpdate();
                                        log.info("MIGRATION-LOGS >> Executed query : "
                                                + insertScopeAssociationPS.toString());
                                    }
                                } catch (Exception e) {
                                    log.error(
                                            "MIGRATION-ERROR-LOGS-016 >> Error while executing the migration.",
                                            e);
                                    if (!isContinueOnError()) {
                                        throw new MigrationClientException(
                                                "Error while executing the migration.", e);
                                    }
                                }
                            }
                        }
                    }
                } catch (UserStoreException e) {
                    log.error("MIGRATION-ERROR-LOGS-017 >> Error while executing the migration.", e);
                    if (!isContinueOnError()) {
                        throw new MigrationClientException("Error while executing the migration.", e);
                    }
                }
            }
            if (isBatchUpdate()) {
                try {
                    insertTokenIdPS.executeBatch();
                    log.info("MIGRATION-LOGS >> Executed query : " + insertTokenIdPS.toString());
                } catch (SQLException e) {
                    log.error("MIGRATION-ERROR-LOGS-018 >> Error while executing the migration.", e);
                    if (!isContinueOnError()) {
                        throw new MigrationClientException("Error while executing the migration.", e);
                    }
                }

                try {
                    log.info("MIGRATION-LOGS >> Started : " + insertScopeAssociationPS.toString());
                    insertScopeAssociationPS.executeBatch();
                    log.info("MIGRATION-LOGS >> Executed query : " + insertScopeAssociationPS.toString());
                } catch (SQLException e) {
                    log.error("MIGRATION-ERROR-LOGS-019 >> Error while executing the migration.", e);
                    if (!isContinueOnError()) {
                        throw new MigrationClientException("Error while executing the migration.", e);
                    }
                }
                try {
                    updateUserNamePS.executeBatch();
                    log.info("MIGRATION-LOGS >> Executed query : " + updateUserNamePS.toString());
                } catch (SQLException e) {
                    log.error("MIGRATION-ERROR-LOGS-020 >> Error while executing the migration.", e);
                    if (!isContinueOnError()) {
                        throw new MigrationClientException("Error while executing the migration.", e);
                    }
                }

                try {
                    insertTokenScopeHashPS.executeBatch();
                    log.info("MIGRATION-LOGS >> Executed query : " + insertTokenScopeHashPS.toString());
                } catch (SQLException e) {
                    log.error("MIGRATION-ERROR-LOGS-021 >> Error while executing the migration.", e);
                    if (!isContinueOnError()) {
                        throw new MigrationClientException("Error while executing the migration.", e);
                    }
                }
            }
        } catch (Exception e) {
            log.error("MIGRATION-ERROR-LOGS-022 >> Error while executing the migration.", e);
            if (!isContinueOnError()) {
                throw new MigrationClientException("Error while executing the migration.", e);
            }
        }

        String selectFromAuthorizationCode = SQLQueries.SELECT_FROM_AUTHORIZATION_CODE;
        selectFromAuthorizationCodePS = identityConnection.prepareStatement(selectFromAuthorizationCode);

        String updateUserNameAuthorizationCode = SQLQueries.UPDATE_USER_NAME_AUTHORIZATION_CODE;
        updateUserNameAuthorizationCodePS = identityConnection
                .prepareStatement(updateUserNameAuthorizationCode);

        try {
            authzCodeRS = selectFromAuthorizationCodePS.executeQuery();
            log.info("MIGRATION-LOGS >> Executed query : " + authzCodeRS.toString());
            while (authzCodeRS.next()) {
                String authorizationCode = null;
                try {
                    authorizationCode = authzCodeRS.getString("AUTHORIZATION_CODE");
                    String authzUser = authzCodeRS.getString("AUTHZ_USER");
                    String userDomainFromDB = authzCodeRS.getString("USER_DOMAIN");

                    if (userDomainFromDB == null) {
                        String username = UserCoreUtil
                                .removeDomainFromName(MultitenantUtils.getTenantAwareUsername(authzUser));
                        String userDomain = UserCoreUtil.extractDomainFromName(authzUser);
                        int tenantId = ISMigrationServiceDataHolder.getRealmService().getTenantManager()
                                .getTenantId(MultitenantUtils.getTenantDomain(authzUser));

                        try {
                            updateUserNameAuthorizationCodePS.setString(1, username);
                            updateUserNameAuthorizationCodePS.setInt(2, tenantId);
                            updateUserNameAuthorizationCodePS.setString(3, userDomain);
                            updateUserNameAuthorizationCodePS.setString(4, UUID.randomUUID().toString());
                            updateUserNameAuthorizationCodePS.setString(5, authzUser);
                            updateUserNameAuthorizationCodePS.setString(6, authorizationCode);
                            if (isBatchUpdate()) {
                                updateUserNameAuthorizationCodePS.addBatch();
                            } else {
                                updateUserNameAuthorizationCodePS.executeUpdate();
                                log.info("MIGRATION-LOGS >> Executed query : "
                                        + updateUserNameAuthorizationCodePS.toString());
                            }
                        } catch (Exception e) {
                            log.error("MIGRATION-ERROR-LOGS-023 >> Error while executing the migration.", e);
                            if (!isContinueOnError()) {
                                throw new MigrationClientException("Error while executing the migration.", e);
                            }
                        }
                        if (log.isDebugEnabled()) {
                            log.debug("MIGRATION-LOGS >> migrating authorization code : " + authorizationCode);
                        }
                    }
                } catch (UserStoreException e) {
                    log.warn("MIGRATION-LOGS >> Error while migrating authorization code : "
                            + authorizationCode);
                    if (!isContinueOnError()) {
                        throw new MigrationClientException("Error while executing the migration.", e);
                    }
                }
            }
            if (isBatchUpdate()) {
                updateUserNameAuthorizationCodePS.executeBatch();
                log.info("MIGRATION-LOGS >> Executed query : " + updateUserNameAuthorizationCodePS.toString());
            }
        } catch (Exception e) {
            log.error("MIGRATION-ERROR-LOGS-024 >> Error while executing the migration.", e);
            if (!isContinueOnError()) {
                throw new MigrationClientException("Error while executing the migration.", e);
            }
        }

        String selectIdnAssociatedId = SQLQueries.SELECT_IDN_ASSOCIATED_ID;
        selectIdnAssociatedIdPS = identityConnection.prepareStatement(selectIdnAssociatedId);

        try {
            selectIdnAssociatedIdRS = selectIdnAssociatedIdPS.executeQuery();

            updateIdnAssociatedIdPS = identityConnection.prepareStatement(SQLQueries.UPDATE_IDN_ASSOCIATED_ID);

            while (selectIdnAssociatedIdRS.next()) {
                int id = selectIdnAssociatedIdRS.getInt("ID");
                String username = selectIdnAssociatedIdRS.getString("USER_NAME");
                String userDomainFromDB = selectIdnAssociatedIdRS.getString("DOMAIN_NAME");

                if (userDomainFromDB == null) {
                    try {
                        updateIdnAssociatedIdPS.setString(1, UserCoreUtil.extractDomainFromName(username));
                        updateIdnAssociatedIdPS.setString(2, UserCoreUtil.removeDomainFromName(username));
                        updateIdnAssociatedIdPS.setInt(3, id);
                        if (isBatchUpdate()) {
                            updateIdnAssociatedIdPS.addBatch();
                        } else {
                            updateIdnAssociatedIdPS.executeUpdate();
                            log.info(
                                    "MIGRATION-LOGS >> Executed query : " + updateIdnAssociatedIdPS.toString());
                        }
                        if (log.isDebugEnabled()) {
                            log.debug("MIGRATION-LOGS >> migrating IdnAssociatedId : " + id);
                        }
                    } catch (Exception e) {
                        log.error("MIGRATION-ERROR-LOGS-024 >> Error while executing the migration.", e);
                        if (!isContinueOnError()) {
                            throw new MigrationClientException("Error while executing the migration.", e);
                        }
                    }
                }
            }
            if (isBatchUpdate()) {
                updateIdnAssociatedIdPS.executeBatch();
                log.info("MIGRATION-LOGS >> Executed query : " + updateIdnAssociatedIdPS.toString());
            }
        } catch (Exception e) {
            log.error("MIGRATION-ERROR-LOGS-025 >> Error while executing the migration.", e);
            if (!isContinueOnError()) {
                throw new MigrationClientException("Error while executing the migration.", e);
            }
        }

        identityConnection.commit();

    } catch (SQLException e) {
        IdentityDatabaseUtil.rollBack(identityConnection);
        log.error("MIGRATION-ERROR-LOGS--026 >> Error while executing the migration.", e);
        if (!isContinueOnError()) {
            throw new MigrationClientException("Error while executing the migration.", e);
        }
    } catch (Exception e) {
        log.error("MIGRATION-ERROR-LOGS-027 >> Error while executing the migration.", e);
        if (!isContinueOnError()) {
            throw new MigrationClientException("Error while executing the migration.", e);
        }
    } finally {
        try {
            IdentityDatabaseUtil.closeResultSet(accessTokenRS);
            IdentityDatabaseUtil.closeResultSet(authzCodeRS);
            IdentityDatabaseUtil.closeResultSet(selectIdnAssociatedIdRS);
            IdentityDatabaseUtil.closeResultSet(selectConsumerAppsRS);

            IdentityDatabaseUtil.closeStatement(selectFromAccessTokenPS);
            IdentityDatabaseUtil.closeStatement(insertScopeAssociationPS);
            IdentityDatabaseUtil.closeStatement(insertTokenIdPS);
            IdentityDatabaseUtil.closeStatement(updateUserNamePS);
            IdentityDatabaseUtil.closeStatement(insertTokenScopeHashPS);
            IdentityDatabaseUtil.closeStatement(updateUserNameAuthorizationCodePS);
            IdentityDatabaseUtil.closeStatement(selectFromAuthorizationCodePS);
            IdentityDatabaseUtil.closeStatement(selectIdnAssociatedIdPS);
            IdentityDatabaseUtil.closeStatement(updateIdnAssociatedIdPS);
            IdentityDatabaseUtil.closeStatement(selectConsumerAppsPS);
            IdentityDatabaseUtil.closeStatement(updateConsumerAppsPS);

            IdentityDatabaseUtil.closeConnection(identityConnection);
        } catch (Exception e) {
            log.error("MIGRATION-ERROR-LOGS-028 >> Error while executing the migration.", e);
        }
    }
    log.info("MIGRATION-LOGS >> Done : migrateIdentityData.");
}

From source file:com.oltpbenchmark.benchmarks.tpcc.TPCCLoader.java

protected int loadItem(int itemKount) {

    int k = 0;//from  w  w  w .ja va  2s .  c o m
    int t = 0;
    int randPct = 0;
    int len = 0;
    int startORIGINAL = 0;

    try {
        PreparedStatement itemPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_ITEM);

        now = new java.util.Date();
        t = itemKount;
        LOG.debug("\nStart Item Load for " + t + " Items @ " + now + " ...");

        if (outputFiles == true) {
            out = new PrintWriter(new FileOutputStream(fileLocation + "item.csv"));
            LOG.debug("\nWriting Item file to: " + fileLocation + "item.csv");
        }

        Item item = new Item();

        for (int i = 1; i <= itemKount; i++) {

            item.i_id = i;
            item.i_name = TPCCUtil.randomStr(TPCCUtil.randomNumber(14, 24, gen));
            item.i_price = (float) (TPCCUtil.randomNumber(100, 10000, gen) / 100.0);

            // i_data
            randPct = TPCCUtil.randomNumber(1, 100, gen);
            len = TPCCUtil.randomNumber(26, 50, gen);
            if (randPct > 10) {
                // 90% of time i_data isa random string of length [26 .. 50]
                item.i_data = TPCCUtil.randomStr(len);
            } else {
                // 10% of time i_data has "ORIGINAL" crammed somewhere in
                // middle
                startORIGINAL = TPCCUtil.randomNumber(2, (len - 8), gen);
                item.i_data = TPCCUtil.randomStr(startORIGINAL - 1) + "ORIGINAL"
                        + TPCCUtil.randomStr(len - startORIGINAL - 9);
            }

            item.i_im_id = TPCCUtil.randomNumber(1, 10000, gen);

            k++;

            if (outputFiles == false) {
                itemPrepStmt.setLong(1, item.i_id);
                itemPrepStmt.setString(2, item.i_name);
                itemPrepStmt.setDouble(3, item.i_price);
                itemPrepStmt.setString(4, item.i_data);
                itemPrepStmt.setLong(5, item.i_im_id);
                itemPrepStmt.addBatch();

                if ((k % configCommitCount) == 0) {
                    long tmpTime = new java.util.Date().getTime();
                    String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000)
                            + "                    ";
                    LOG.debug(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
                    lastTimeMS = tmpTime;
                    itemPrepStmt.executeBatch();
                    itemPrepStmt.clearBatch();
                    transCommit();
                }
            } else {
                String str = "";
                str = str + item.i_id + ",";
                str = str + item.i_name + ",";
                str = str + item.i_price + ",";
                str = str + item.i_data + ",";
                str = str + item.i_im_id;
                out.println(str);

                if ((k % configCommitCount) == 0) {
                    long tmpTime = new java.util.Date().getTime();
                    String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000)
                            + "                    ";
                    LOG.debug(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
                    lastTimeMS = tmpTime;
                }
            }

        } // end for

        long tmpTime = new java.util.Date().getTime();
        String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + "                    ";
        LOG.debug(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
        lastTimeMS = tmpTime;

        if (outputFiles == false) {
            itemPrepStmt.executeBatch();
        }

        transCommit();
        now = new java.util.Date();
        LOG.debug("End Item Load @  " + now);

    } catch (SQLException se) {
        LOG.debug(se.getMessage());
        transRollback();
    } catch (Exception e) {
        e.printStackTrace();
        transRollback();
    }

    return (k);

}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private Event removeEventByExtId(Connection con, ObmUser calendar, AccessToken token, EventExtId extId) {
    Event ev = findEventByExtId(token, calendar, extId);
    if (ev == null) {
        return null;
    }//from  w ww  .  ja  v  a2 s  . com
    PreparedStatement dev = null;
    try {
        dev = con.prepareStatement(
                "INSERT INTO DeletedEvent (deletedevent_event_id, deletedevent_user_id, deletedevent_origin, deletedevent_timestamp, deletedevent_event_ext_id) "
                        + "VALUES (?, ?, ?, now(), ?)");
        EventObmId databaseId = ev.getObmId();
        for (Attendee at : ev.getAttendees()) {
            Integer userId = userDao.userIdFromEmail(con, at.getEmail(), token.getDomain().getId());
            if (userId != null) {
                dev.setInt(1, databaseId.getObmId());
                dev.setInt(2, userId);
                dev.setString(3, token.getOrigin());
                dev.setString(4, extId.getExtId());
                dev.addBatch();
            }
        }
        dev.executeBatch();
        dev.close();

        removeEventExceptions(con, databaseId);

        dev = con.prepareStatement("DELETE FROM Event WHERE event_id=?");
        dev.setInt(1, databaseId.getObmId());
        dev.executeUpdate();

    } catch (Throwable se) {
        logger.error(se.getMessage(), se);
    } finally {
        obmHelper.cleanup(null, dev, null);
    }

    return ev;
}

From source file:org.wso2.carbon.apimgt.migration.client.MigrateFrom19to110.java

private boolean updateAMAppKeyDomainMapping(Connection connection) throws SQLException {
    log.info("Updating consumer keys in AM_APP_KEY_DOMAIN_MAPPING");

    Statement selectStatement = null;
    Statement deleteStatement = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;//from   ww  w . j a  va 2s .  c  o  m
    boolean continueUpdatingDB = true;
    long totalRecords = 0;
    long decryptionFailedRecords = 0;

    try {
        ArrayList<KeyDomainMappingTableDTO> keyDomainMappingTableDTOs = new ArrayList<>();
        String query = "SELECT * FROM AM_APP_KEY_DOMAIN_MAPPING";

        selectStatement = connection.createStatement();
        selectStatement.setFetchSize(50);
        resultSet = selectStatement.executeQuery(query);
        while (resultSet.next()) {
            ConsumerKeyDTO consumerKeyDTO = new ConsumerKeyDTO();
            consumerKeyDTO.setEncryptedConsumerKey(resultSet.getString("CONSUMER_KEY"));
            totalRecords++;
            if (ResourceModifier.decryptConsumerKeyIfEncrypted(consumerKeyDTO)) {
                KeyDomainMappingTableDTO keyDomainMappingTableDTO = new KeyDomainMappingTableDTO();
                keyDomainMappingTableDTO.setConsumerKey(consumerKeyDTO);
                keyDomainMappingTableDTO.setAuthzDomain(resultSet.getString("AUTHZ_DOMAIN"));

                keyDomainMappingTableDTOs.add(keyDomainMappingTableDTO);
            } else {
                log.error("Cannot decrypt consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " in AM_APP_KEY_DOMAIN_MAPPING table");
                decryptionFailedRecords++;
                //If its not allowed to remove decryption failed entries from DB, we will not continue updating 
                // tables even with successfully decrypted entries to maintain DB integrity
                if (!removeDecryptionFailedKeysFromDB) {
                    continueUpdatingDB = false;
                }
            }
        }

        if (continueUpdatingDB) { // Modify table only if decryption is successful
            preparedStatement = connection.prepareStatement(
                    "INSERT INTO AM_APP_KEY_DOMAIN_MAPPING " + "(CONSUMER_KEY, AUTHZ_DOMAIN) VALUES (?, ?)");

            for (KeyDomainMappingTableDTO keyDomainMappingTableDTO : keyDomainMappingTableDTOs) {
                preparedStatement.setString(1,
                        keyDomainMappingTableDTO.getConsumerKey().getDecryptedConsumerKey());
                preparedStatement.setString(2, keyDomainMappingTableDTO.getAuthzDomain());
                preparedStatement.addBatch();
            }

            deleteStatement = connection.createStatement();
            deleteStatement.execute("DELETE FROM AM_APP_KEY_DOMAIN_MAPPING");

            preparedStatement.executeBatch();
            log.info("AM_APP_KEY_DOMAIN_MAPPING table updated with " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries deleted as they cannot be decrypted");
        } else {
            log.error("AM_APP_KEY_DOMAIN_MAPPING table not updated as " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries" + " cannot be decrypted");
        }
    } finally {
        if (selectStatement != null)
            selectStatement.close();
        if (deleteStatement != null)
            deleteStatement.close();
        if (preparedStatement != null)
            preparedStatement.close();
        if (resultSet != null)
            resultSet.close();
    }

    return continueUpdatingDB;
}

From source file:org.hyperic.hq.measurement.server.session.DataManagerImpl.java

private boolean insertTopData(Connection conn, List<TopNData> topNData, boolean continueOnSQLException)
        throws SQLException {
    if (log.isDebugEnabled()) {
        log.debug("Inserting Top Processes data - '" + topNData + "'");
    }//from  w  w w .j  a v a 2  s  . c o  m
    PreparedStatement stmt = null;
    final StringBuilder buf = new StringBuilder();

    Map<Date, List<TopNData>> dayToDataMap = mapDayToData(topNData);

    for (Entry<Date, List<TopNData>> entry : dayToDataMap.entrySet()) {
        try {

            String partitionName = getAndCreatePartition(TopNData.class.getSimpleName(), entry.getKey(), conn);
            stmt = conn.prepareStatement(buf.append("INSERT INTO ").append(partitionName)
                    .append(" (resourceId, time, data) VALUES (?, ?, ?)").toString());

            for (TopNData data : entry.getValue()) {
                stmt.setInt(1, data.getResourceId());
                stmt.setTimestamp(2, new java.sql.Timestamp(data.getTime().getTime()));
                stmt.setBytes(3, data.getData());
                stmt.addBatch();
            }
            int[] execInfo = stmt.executeBatch();

        } catch (BatchUpdateException e) {
            if (!continueOnSQLException) {
                throw e;
            }
        } catch (SQLException e) {
            if (!continueOnSQLException) {
                throw e;
            }
            if (log.isDebugEnabled()) {
                log.debug("A general SQLException occurred during the insert of TopN. ", e);
            }

        } finally {
            DBUtil.closeStatement(LOG_CTX, stmt);
        }

    }
    return true;
}

From source file:com.drevelopment.couponcodes.bukkit.coupon.BukkitCouponHandler.java

@Override
public boolean addCouponToDatabase(Coupon coupon) {
    if (couponExists(coupon))
        return false;
    try {//from  w ww. j  ava 2s. c o  m
        Connection con = databaseHandler.getConnection();
        PreparedStatement p = null;

        if (coupon instanceof ItemCoupon) {
            ItemCoupon c = (ItemCoupon) coupon;
            p = con.prepareStatement(
                    "INSERT INTO couponcodes (name, ctype, usetimes, usedplayers, ids, timeuse) "
                            + "VALUES (?, ?, ?, ?, ?, ?)");
            p.setString(1, c.getName());
            p.setString(2, c.getType());
            p.setInt(3, c.getUseTimes());
            p.setString(4, playerHashToString(c.getUsedPlayers()));
            p.setString(5, itemHashToString(c.getIDs()));
            p.setInt(6, c.getTime());
        } else

        if (coupon instanceof EconomyCoupon) {
            EconomyCoupon c = (EconomyCoupon) coupon;
            p = con.prepareStatement(
                    "INSERT INTO couponcodes (name, ctype, usetimes, usedplayers, money, timeuse) "
                            + "VALUES (?, ?, ?, ?, ?, ?)");
            p.setString(1, c.getName());
            p.setString(2, c.getType());
            p.setInt(3, c.getUseTimes());
            p.setString(4, playerHashToString(c.getUsedPlayers()));
            p.setInt(5, c.getMoney());
            p.setInt(6, c.getTime());
        } else

        if (coupon instanceof RankCoupon) {
            RankCoupon c = (RankCoupon) coupon;
            p = con.prepareStatement(
                    "INSERT INTO couponcodes (name, ctype, usetimes, usedplayers, groupname, timeuse) "
                            + "VALUES (?, ?, ?, ?, ?, ?)");
            p.setString(1, c.getName());
            p.setString(2, c.getType());
            p.setInt(3, c.getUseTimes());
            p.setString(4, playerHashToString(c.getUsedPlayers()));
            p.setString(5, c.getGroup());
            p.setInt(6, c.getTime());
        } else

        if (coupon instanceof XpCoupon) {
            XpCoupon c = (XpCoupon) coupon;
            p = con.prepareStatement(
                    "INSERT INTO couponcodes (name, ctype, usetimes, usedplayers, timeuse, xp) "
                            + "VALUES (?, ?, ?, ?, ?, ?)");
            p.setString(1, c.getName());
            p.setString(2, c.getType());
            p.setInt(3, c.getUseTimes());
            p.setString(4, playerHashToString(c.getUsedPlayers()));
            p.setInt(5, c.getTime());
            p.setInt(6, c.getXp());
        } else

        if (coupon instanceof CommandCoupon) {
            CommandCoupon c = (CommandCoupon) coupon;
            p = con.prepareStatement(
                    "INSERT INTO couponcodes (name, ctype, usetimes, usedplayers, timeuse, command) "
                            + "VALUES (?, ?, ?, ?, ?, ?)");
            p.setString(1, c.getName());
            p.setString(2, c.getType());
            p.setInt(3, c.getUseTimes());
            p.setString(4, playerHashToString(c.getUsedPlayers()));
            p.setInt(5, c.getTime());
            p.setString(6, c.getCmd());
        }

        p.addBatch();
        con.setAutoCommit(false);
        p.executeBatch();
        con.setAutoCommit(true);
        return true;
    } catch (SQLException e) {
        return false;
    }
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private void insertIntoDeletedEvent(Connection con, AccessToken token, Event event, EventType eventType,
        Collection<Integer> attendeeIds) throws SQLException {
    PreparedStatement insertStatement = null;
    try {/*w ww.j a  v  a  2  s  . c o m*/
        insertStatement = con
                .prepareStatement("INSERT INTO DeletedEvent (deletedevent_event_id, deletedevent_user_id, "
                        + "deletedevent_origin, deletedevent_type, deletedevent_timestamp, deletedevent_event_ext_id) "
                        + "VALUES (?, ?, ?, ?, now(), ?)");
        EventObmId databaseId = event.getObmId();
        for (int attendeeId : attendeeIds) {
            insertStatement.setInt(1, databaseId.getObmId());
            insertStatement.setInt(2, attendeeId);
            insertStatement.setString(3, token.getOrigin());
            insertStatement.setObject(4,
                    obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, eventType.toString()));
            insertStatement.setString(5, event.getExtId().getExtId());
            insertStatement.addBatch();
        }
        insertStatement.executeBatch();
    } finally {
        obmHelper.cleanup(null, insertStatement, null);
    }
}

From source file:org.wso2.carbon.idp.mgt.dao.IdPManagementDAO.java

/**
 * @param conn// w  ww .j  a va 2 s .c  om
 * @param idPId
 * @param tenantId
 * @param claimMappings
 * @throws SQLException
 * @throws IdentityProviderManagementException
 */
private void addDefaultClaimValuesForLocalIdP(Connection conn, int idPId, int tenantId,
        ClaimMapping[] claimMappings) throws SQLException, IdentityProviderManagementException {

    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    String sqlStmt;

    try {

        if (claimMappings == null || claimMappings.length == 0) {
            return;
        }

        sqlStmt = IdPManagementConstants.SQLQueries.ADD_LOCAL_IDP_DEFAULT_CLAIM_VALUES_SQL;
        prepStmt = conn.prepareStatement(sqlStmt);
        for (ClaimMapping mapping : claimMappings) {
            if (mapping != null && mapping.getLocalClaim() != null
                    && mapping.getLocalClaim().getClaimUri() != null) {

                prepStmt.setInt(1, idPId);
                prepStmt.setString(2, CharacterEncoder.getSafeText(mapping.getLocalClaim().getClaimUri()));
                prepStmt.setString(3, CharacterEncoder.getSafeText(mapping.getDefaultValue()));
                prepStmt.setInt(4, tenantId);
                if (mapping.isRequested()) {
                    prepStmt.setString(5, "1");
                } else {
                    prepStmt.setString(5, "0");
                }
                prepStmt.addBatch();
            }
        }

        prepStmt.executeBatch();

    } finally {
        IdentityApplicationManagementUtil.closeStatement(prepStmt);
        IdentityApplicationManagementUtil.closeResultSet(rs);
        IdentityApplicationManagementUtil.closeStatement(prepStmt);
    }
}

From source file:org.wso2.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java

/**
 * @param applicationID/*from   w  ww  .j  a  v  a 2  s .co m*/
 * @param permissionsAndRoleConfiguration
 * @param connection
 * @throws SQLException
 */
private void updatePermissionAndRoleConfiguration(int applicationID,
        PermissionsAndRoleConfig permissionsAndRoleConfiguration, Connection connection) throws SQLException {

    if (permissionsAndRoleConfiguration == null || permissionsAndRoleConfiguration.getRoleMappings() == null
            || permissionsAndRoleConfiguration.getRoleMappings().length == 0) {
        return;
    }

    RoleMapping[] roleMappings = permissionsAndRoleConfiguration.getRoleMappings();
    int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId();

    PreparedStatement storeRoleMapPrepStmt = null;
    try {
        storeRoleMapPrepStmt = connection.prepareStatement(ApplicationMgtDBQueries.STORE_ROLE_MAPPING);
        for (RoleMapping roleMapping : roleMappings) {
            // TENANT_ID, IDP_ROLE, SP_ROLE, APP_ID
            storeRoleMapPrepStmt.setInt(1, tenantID);
            storeRoleMapPrepStmt.setString(2,
                    CharacterEncoder.getSafeText(roleMapping.getLocalRole().getLocalRoleName()));
            storeRoleMapPrepStmt.setString(3, CharacterEncoder.getSafeText(roleMapping.getRemoteRole()));
            storeRoleMapPrepStmt.setInt(4, applicationID);
            storeRoleMapPrepStmt.addBatch();

            if (debugMode) {
                log.debug("Storing Claim Mapping. IDPRole: " + roleMapping.getLocalRole() + " SPRole: "
                        + roleMapping.getRemoteRole());
            }
        }

        storeRoleMapPrepStmt.executeBatch();
    } finally {
        IdentityApplicationManagementUtil.closeStatement(storeRoleMapPrepStmt);
    }
}