Example usage for java.sql PreparedStatement addBatch

List of usage examples for java.sql PreparedStatement addBatch

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

Introduction

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

Prototype

void addBatch() throws SQLException;

Source Link

Document

Adds a set of parameters to this PreparedStatement object's batch of commands.

Usage

From source file:org.openmrs.util.databasechange.AddConceptMapTypesChangeset.java

/**
 * Executes all the changes to the concept names as a batch update.
 *
 * @param connection The database connection
 *///  ww w  .  j  av a2 s.  co  m
private void runBatchInsert(JdbcConnection connection) throws CustomChangeException {
    PreparedStatement pStmt = null;
    ResultSet rs = null;
    try {
        connection.setAutoCommit(false);

        Integer userId = DatabaseUpdater.getAuthenticatedUserId();
        //if we have no authenticated user(for API users), set as Daemon
        if (userId == null || userId < 1) {
            userId = getInt(connection, "SELECT min(user_id) FROM users");
            //leave it as null rather than setting it to 0
            if (userId < 1) {
                userId = null;
            }
        }

        //userId is not a param, because it's easier this way if it's null
        pStmt = connection.prepareStatement("INSERT INTO concept_map_type "
                + "(concept_map_type_id, name, is_hidden, retired, creator, date_created, uuid) VALUES(?,?,?,?,"
                + userId + ",?,?)");

        int mapTypeId = 1;

        for (String map : visibleConceptMapTypeArray) {
            String[] mapTypeAndUuid = map.trim().split("\\|");
            String mapType = mapTypeAndUuid[0];
            String mapUuid = mapTypeAndUuid[1];

            pStmt.setInt(1, mapTypeId);
            pStmt.setString(2, mapType);
            pStmt.setBoolean(3, false);
            pStmt.setBoolean(4, false);
            pStmt.setDate(5, new Date(Calendar.getInstance().getTimeInMillis()));
            pStmt.setString(6, mapUuid);
            pStmt.addBatch();

            mapTypeId++;
        }

        for (String map : hiddenConceptMapTypeArray) {
            String[] mapTypeAndUuid = map.trim().split("\\|");
            String mapType = mapTypeAndUuid[0];
            String mapUuid = mapTypeAndUuid[1];

            pStmt.setInt(1, mapTypeId);
            pStmt.setString(2, mapType);
            pStmt.setBoolean(3, true);
            pStmt.setBoolean(4, false);
            pStmt.setDate(5, new Date(Calendar.getInstance().getTimeInMillis()));
            pStmt.setString(6, mapUuid);
            pStmt.addBatch();

            mapTypeId++;
        }

        try {
            int[] updateCounts = pStmt.executeBatch();
            for (int i = 0; i < updateCounts.length; i++) {
                if (updateCounts[i] > -1) {
                    log.debug("Successfully executed: updateCount=" + updateCounts[i]);
                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                    log.debug("Successfully executed; No Success info");
                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                    log.warn("Failed to execute insert");
                }
            }

            log.debug("Committing inserts...");
            connection.commit();
        } catch (BatchUpdateException be) {
            log.warn("Error generated while processsing batch insert", be);
            int[] updateCounts = be.getUpdateCounts();

            for (int i = 0; i < updateCounts.length; i++) {
                if (updateCounts[i] > -1) {
                    log.warn("Executed with exception: insertCount=" + updateCounts[i]);
                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                    log.warn("Executed with exception; No Success info");
                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                    log.warn("Failed to execute insert with exception");
                }
            }

            try {
                log.debug("Rolling back batch", be);
                connection.rollback();
            } catch (Exception rbe) {
                log.warn("Error generated while rolling back batch insert", be);
            }

            //marks the changeset as a failed one
            throw new CustomChangeException("Failed to insert one or more concept map types", be);
        }
    } catch (DatabaseException e) {
        throw new CustomChangeException("Failed to insert one or more concept map types:", e);
    } catch (SQLException e) {
        throw new CustomChangeException("Failed to insert one or more concept map types:", e);
    } finally {
        //reset to auto commit mode
        try {
            connection.setAutoCommit(true);
        } catch (DatabaseException e) {
            log.warn("Failed to reset auto commit back to true", e);
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.warn("Failed to close the resultset object");
            }
        }

        if (pStmt != null) {
            try {
                pStmt.close();
            } catch (SQLException e) {
                log.warn("Failed to close the prepared statement object");
            }
        }
    }
}

From source file:org.wso2.carbon.identity.oauth2.dao.TokenMgtDAO.java

public void deactivateAuthorizationCode(List<AuthzCodeDO> authzCodeDOs) throws IdentityOAuth2Exception {
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;

    try {/*w ww  . j a  va 2  s. c o  m*/
        prepStmt = connection.prepareStatement(SQLQueries.DEACTIVATE_AUTHZ_CODE_AND_INSERT_CURRENT_TOKEN);
        for (AuthzCodeDO authzCodeDO : authzCodeDOs) {
            prepStmt.setString(1, authzCodeDO.getOauthTokenId());
            prepStmt.setString(2,
                    persistenceProcessor.getPreprocessedAuthzCode(authzCodeDO.getAuthorizationCode()));
            prepStmt.addBatch();
        }
        prepStmt.executeBatch();
        connection.commit();
    } catch (SQLException e) {
        throw new IdentityOAuth2Exception("Error when deactivating authorization code", e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
}

From source file:com.flexive.ejb.beans.LanguageBean.java

/**
 * {@inheritDoc}/*w  ww .j a  va2s  . c  om*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void setAvailable(List<FxLanguage> available, boolean ignoreUsage) throws FxApplicationException {
    FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.GlobalSupervisor);
    Connection con = null;
    PreparedStatement ps = null;
    if (available == null || available.size() == 0)
        throw new FxInvalidParameterException("available", "ex.language.noAvailable");
    try {
        con = Database.getDbConnection();
        if (!ignoreUsage) {
            List<FxLanguage> orgLang = loadAvailable(true);
            boolean found;
            for (FxLanguage org : orgLang) {
                found = false;
                for (FxLanguage tmp : available) {
                    if (tmp.getId() == org.getId()) {
                        found = true;
                        break;
                    }
                }
                if (!found && hasUsages(con, org))
                    throw new FxInvalidParameterException("available", "ex.language.removeUsed",
                            org.getLabel());
            }
        }
        ps = con.prepareStatement("UPDATE " + TBL_LANG + " SET INUSE=?, DISPPOS=?");
        ps.setBoolean(1, false);
        ps.setNull(2, java.sql.Types.INTEGER);
        ps.executeUpdate();
        ps.close();
        int pos = 0;
        ps = con.prepareStatement("UPDATE " + TBL_LANG + " SET INUSE=?, DISPPOS=? WHERE LANG_CODE=?");
        ps.setBoolean(1, true);
        for (FxLanguage lang : available) {
            ps.setInt(2, pos++);
            ps.setLong(3, lang.getId());
            ps.addBatch();
        }
        ps.executeBatch();
        StructureLoader.updateLanguages(FxContext.get().getDivisionId(), loadAll(true, true));
    } catch (FxCacheException e) {
        LOG.error(e, e);
    } catch (SQLException e) {
        throw new FxUpdateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } finally {
        Database.closeObjects(LanguageBean.class, con, ps);
    }
}

From source file:com.wso2telco.dep.mediator.dao.SMSMessagingDAO.java

/**
 * Insert sms request ids./*from  ww w. jav a 2 s.  c  o  m*/
 *
 * @param requestID
 *            the request id
 * @param senderAddress
 *            the sender address
 * @param pluginRequestIDs
 *            the plugin request i ds
 * @return true, if successful
 * @throws Exception
 */
public void insertSMSRequestIds(String requestId, String senderAddress, Map<String, String> gatewayRequestIds)
        throws SQLException, Exception {

    Connection con = null;
    PreparedStatement ps = null;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);

        /**
         * Set autocommit off to handle the transaction
         */
        con.setAutoCommit(false);

        StringBuilder queryString = new StringBuilder("INSERT INTO ");
        queryString.append(DatabaseTables.SEND_SMS_REQID.getTableName());
        queryString.append(" (hub_requestid, sender_address, delivery_address, plugin_requestid) ");
        queryString.append("VALUES (?, ?, ?, ?)");

        ps = con.prepareStatement(queryString.toString());

        for (Map.Entry<String, String> entry : gatewayRequestIds.entrySet()) {

            ps.setString(1, requestId);
            ps.setString(2, senderAddress);
            ps.setString(3, entry.getKey());
            ps.setString(4, entry.getValue());

            ps.addBatch();
        }

        log.debug("sql query in insertSMSRequestIds : " + ps);

        ps.executeBatch();

        /**
         * commit the transaction if all success
         */
        con.commit();
    } catch (SQLException e) {

        /**
         * rollback if Exception occurs
         */
        con.rollback();

        log.error("database operation error in insertSMSRequestIds : ", e);
        throw e;
    } catch (Exception e) {

        /**
         * rollback if Exception occurs
         */
        con.rollback();

        log.error("error in insertSMSRequestIds : ", e);
        throw e;
    } finally {

        DbUtils.closeAllConnections(ps, con, null);
    }
}

From source file:com.sec.ose.osi.sdk.protexsdk.discovery.DCCodeMatch.java

public void loadFromProtexServer(UIResponseObserver observer, ReportEntityList identifiedFiles,
        ReportEntityList compareCodeMatches, ReportEntityList codeMatchesPrecision) {

    PreparedStatement prepIdentifyTable = IdentificationDBManager
            .getIdentificationFilePreparedStatement(projectName);
    PreparedStatement prepCodematchTable = IdentificationDBManager.getCodeMatchPreparedStatement(projectName);
    PreparedStatement prepIdentifiedTable = IdentificationDBManager
            .getPreparedStatementForSettingIdentifiedFiles(projectName);

    if (compareCodeMatches == null) {
        System.err.println("Not Founded Compare CodeMatches.");
    } else {//  www  .  j a va2  s .  c  o m
        for (ReportEntity reportEntity : compareCodeMatches) {
            String codeMatchFilePath = reportEntity.getValue(ReportInfo.COMPARE_CODE_MATCHES.FULL_PATH);
            codeMatchFilePath = codeMatchFilePath.substring(1);
            String codeMatchURL = reportEntity
                    .getValue(ReportInfo.COMPARE_CODE_MATCHES.COMPARE_CODE_MATCHES_LINK);
            codeMatchURL = transURL(codeMatchURL);

            try {
                prepIdentifyTable.setString(1, codeMatchFilePath);
                prepIdentifyTable.setString(2, codeMatchURL);
                prepIdentifyTable.setString(3, "");
                prepIdentifyTable.addBatch();
            } catch (SQLException e) {
                log.warn(e);
            }
        }
        IdentificationDBManager.execute(prepIdentifyTable);
    }

    String CodeMatchFileName = "";
    String CodeMatchComponent = "";
    String CodeMatchVersion = "";
    String CodeMatchLicense = "";

    int CodeMatchUsage = CodeMatchInfo.USAGE_SNIPPET;
    int CodeMatchPercentage = 0;
    String CodeMatchedFile = "";
    String CodeMatchedStatus = "";
    String CodeMatchComment = "";

    if (codeMatchesPrecision == null) {
        System.err.println("Not Founded CodeMatches PendingIdentification Precision.");
    } else {
        for (ReportEntity tmpPendingFile : codeMatchesPrecision) {
            CodeMatchFileName = tmpPendingFile.getValue(ReportInfo.CODE_MATCHES_PRECISION.FILE);
            CodeMatchComponent = tmpPendingFile.getValue(ReportInfo.CODE_MATCHES_PRECISION.COMPONENT);
            CodeMatchVersion = tmpPendingFile.getValue(ReportInfo.CODE_MATCHES_PRECISION.VERSION);
            if (CodeMatchVersion == null)
                CodeMatchVersion = "";
            CodeMatchLicense = tmpPendingFile.getValue(ReportInfo.CODE_MATCHES_PRECISION.LICENSE);

            CodeMatchUsage = (tmpPendingFile.getValue(ReportInfo.CODE_MATCHES_PRECISION.USAGE).equals("File"))
                    ? CodeMatchInfo.USAGE_FILE
                    : CodeMatchInfo.USAGE_SNIPPET;
            CodeMatchedStatus = tmpPendingFile.getValue(ReportInfo.CODE_MATCHES_PRECISION.STATUS);
            String strCodeMatchPercentage = tmpPendingFile
                    .getValue(ReportInfo.CODE_MATCHES_PRECISION.PRECISION);
            CodeMatchPercentage = Tools.transStringToInteger(
                    strCodeMatchPercentage.substring(0, strCodeMatchPercentage.length() - 1));
            CodeMatchedFile = tmpPendingFile.getValue(ReportInfo.CODE_MATCHES_PRECISION.MATCHED_FILE);
            CodeMatchComment = tmpPendingFile.getValue(ReportInfo.CODE_MATCHES_PRECISION.FILE_COMMENT);
            CodeMatchComment = CodeMatchComment.replace("<br />", "\n");

            try {
                prepCodematchTable.setString(1, CodeMatchFileName);
                prepCodematchTable.setString(2, CodeMatchComponent);
                prepCodematchTable.setString(3, CodeMatchVersion);
                prepCodematchTable.setString(4, CodeMatchLicense);
                prepCodematchTable.setString(5, String.valueOf(CodeMatchUsage));
                if (CodeMatchedStatus.startsWith("Precision Match")) {
                    prepCodematchTable.setString(6, String.valueOf(AbstractMatchInfo.STATUS_PENDING));
                } else if (CodeMatchedStatus.startsWith("Identified")) {
                    prepCodematchTable.setString(6, String.valueOf(AbstractMatchInfo.STATUS_IDENTIFIED));
                } else if (CodeMatchedStatus.startsWith("Identified by Generic Version")) {
                    prepCodematchTable.setString(6, String.valueOf(AbstractMatchInfo.STATUS_REJECT));
                } else if (CodeMatchedStatus.startsWith("Rejected")) {
                    prepCodematchTable.setString(6, String.valueOf(AbstractMatchInfo.STATUS_REJECT));
                } else if (CodeMatchedStatus.startsWith("Declared")) {
                    prepCodematchTable.setString(6, String.valueOf(AbstractMatchInfo.STATUS_DECLARED));
                }
                prepCodematchTable.setInt(7, CodeMatchPercentage);
                prepCodematchTable.setString(8, CodeMatchedFile);
                prepCodematchTable.setString(9, CodeMatchComment);
                prepCodematchTable.addBatch();
            } catch (SQLException e) {
                log.warn(e);
            }
        }
        IdentificationDBManager.execute(prepCodematchTable);
    }

    if (identifiedFiles != null) {
        for (ReportEntity tmpIdentifiedFile : identifiedFiles.getEntityList()) {
            CodeMatchFileName = tmpIdentifiedFile.getValue(ReportInfo.IDENTIFIED_FILES.FILE_FOLDER_NAME)
                    .substring(1);
            String DiscoveryType = tmpIdentifiedFile.getValue(ReportInfo.IDENTIFIED_FILES.DISCOVERY_TYPE);
            String ResolutionType = tmpIdentifiedFile.getValue(ReportInfo.IDENTIFIED_FILES.RESOLUTION_TYPE);
            if (DiscoveryType.equals("String Search")) {
                DiscoveryType = "1";
            } else if (DiscoveryType.equals("Code Match") && !ResolutionType.equals("Declared")) {
                DiscoveryType = "2";
            } else if (DiscoveryType.equals("Other Supported Languages") || ResolutionType.equals("Declared")) {
                DiscoveryType = "3";
            } else {
                continue;
            }
            CodeMatchComponent = tmpIdentifiedFile.getValue(ReportInfo.IDENTIFIED_FILES.COMPONENT);
            CodeMatchVersion = tmpIdentifiedFile.getValue(ReportInfo.IDENTIFIED_FILES.VERSION);
            if (CodeMatchVersion == null)
                CodeMatchVersion = "";
            CodeMatchLicense = tmpIdentifiedFile.getValue(ReportInfo.IDENTIFIED_FILES.LICENSE);

            CodeMatchedFile = tmpIdentifiedFile.getValue(ReportInfo.IDENTIFIED_FILES.MATCHED_FILE);

            try {
                prepIdentifiedTable.setString(1, CodeMatchFileName);
                prepIdentifiedTable.setString(2, DiscoveryType);
                prepIdentifiedTable.setString(3, CodeMatchComponent);
                prepIdentifiedTable.setString(4, CodeMatchVersion);
                prepIdentifiedTable.setString(5, CodeMatchLicense);
                prepIdentifiedTable.setString(6, CodeMatchedFile);
                prepIdentifiedTable.addBatch();
            } catch (SQLException e) {
                log.warn(e);
            }
        }
        IdentificationDBManager.execute(prepIdentifiedTable);
    }

    try {
        if (prepIdentifiedTable != null) {
            try {
                prepIdentifiedTable.close();
            } catch (Exception e) {
                log.warn(e);
            }
        }
        if (prepCodematchTable != null) {
            try {
                prepCodematchTable.close();
            } catch (Exception e) {
                log.warn(e);
            }
        }
        if (prepIdentifyTable != null) {
            try {
                prepIdentifyTable.close();
            } catch (Exception e) {
                log.warn(e);
            }
        }
    } catch (Exception e) {
        log.warn(e);
    }
}

From source file:gemlite.core.internal.db.DBSynchronizer.java

/**
 * Get or create a {@link PreparedStatement} for a primary key based update
 * operation./*from   w w w  . j  av a 2  s.  c om*/
 */
protected PreparedStatement getExecutableUpdatePrepStmntPKBased(AsyncEvent pkEvent, PreparedStatement prevPS)
        throws SQLException {
    final String regionName = pkEvent.getRegion().getName();
    IMapperTool tool = DomainRegistry.getMapperTool(regionName);
    String tableName = DomainRegistry.regionToTable(regionName);
    List<String> valueFields = tool.getValueFieldNames();

    final int numUpdatedCols = valueFields.size();
    StringBuilder searchKeyBuff = new StringBuilder(tableName);
    int paramIndex;
    for (paramIndex = 0; paramIndex < numUpdatedCols; paramIndex++) {
        searchKeyBuff.append('_');
        searchKeyBuff.append(valueFields.get(paramIndex));
    }
    String searchKey = searchKeyBuff.toString();
    final Object pkValues = pkEvent.getDeserializedValue();
    final List<String> keyFields = tool.getKeyFieldNames();
    PreparedStatement ps = this.updtStmntMap.get(searchKey);
    if (ps == null) {
        final String dmlString = AsyncEventHelper.getUpdateString(tableName, keyFields, valueFields);
        if (logger.isDebugEnabled()) {
            logger.info("DBSynchronizer::getExecutableInsertPrepStmntPKBased: " + "preparing '" + dmlString
                    + "' for event: " + pkEvent);
        }
        ps = conn.prepareStatement(dmlString);
        this.updtStmntMap.put(searchKey, ps);
    } else if (prevPS == ps) {
        // add a new batch of values
        ps.addBatch();
    }
    // Set updated col values
    Class valueClass = tool.getValueClass();
    Class keyClass = tool.getKeyClass();
    for (paramIndex = 1; paramIndex <= numUpdatedCols; paramIndex++) {
        String field = valueFields.get(paramIndex - 1);
        try {
            Map map = PropertyUtils.describe(pkEvent.getDeserializedValue());
            Object val = map.get(field);
            String type = valueClass.getDeclaredField(field).getType().getName();
            helper.setColumnInPrepStatement(type, val, ps, this, paramIndex);
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }
    // Now set the Pk values
    setKeysInPrepStatement(pkValues, keyFields, valueClass, ps, paramIndex);
    return ps;
}

From source file:com.esofthead.mycollab.module.project.service.ibatis.GanttAssignmentServiceImpl.java

private void massUpdateBugGanttItems(final List<TaskGanttItem> taskGanttItems, Integer sAccountId) {
    if (CollectionUtils.isNotEmpty(taskGanttItems)) {
        Lock lock = DistributionLockUtil.getLock("gantt-bug-service" + sAccountId);
        try {/*ww  w  .j a  v a 2 s.c o m*/
            final long now = new GregorianCalendar().getTimeInMillis();
            if (lock.tryLock(30, TimeUnit.SECONDS)) {
                try (Connection connection = dataSource.getConnection()) {
                    connection.setAutoCommit(false);
                    PreparedStatement batchTasksStatement = connection.prepareStatement(
                            "UPDATE `m_tracker_bug` SET " + "summary = ?, `startdate` = ?, `enddate` = ?, "
                                    + "`lastUpdatedTime`=?, `percentagecomplete`=?, `assignuser`=?, `ganttindex`=?, "
                                    + "`milestoneId`=? WHERE `id` = ?");
                    for (int i = 0; i < taskGanttItems.size(); i++) {
                        TaskGanttItem ganttItem = taskGanttItems.get(i);
                        if (ProjectTypeConstants.BUG.equals(ganttItem.getType())) {
                            batchTasksStatement.setString(1, ganttItem.getName());
                            batchTasksStatement.setDate(2, getDateWithNullValue(ganttItem.getStartDate()));
                            batchTasksStatement.setDate(3, getDateWithNullValue(ganttItem.getEndDate()));
                            batchTasksStatement.setDate(4, new Date(now));
                            batchTasksStatement.setDouble(5,
                                    MoreObjects.firstNonNull(ganttItem.getProgress(), 0d));
                            batchTasksStatement.setString(6, ganttItem.getAssignUser());
                            batchTasksStatement.setInt(7, ganttItem.getGanttIndex());
                            batchTasksStatement.setObject(8, ganttItem.getMilestoneId());
                            batchTasksStatement.setInt(9, ganttItem.getId());
                            batchTasksStatement.addBatch();
                        }

                    }
                    batchTasksStatement.executeBatch();
                    connection.commit();
                }
            }
        } catch (Exception e) {
            throw new MyCollabException(e);
        } finally {
            DistributionLockUtil.removeLock("gantt-bug-service" + sAccountId);
            lock.unlock();
        }
    }
}

From source file:edu.ncsa.sstde.indexing.postgis.PostgisIndexer.java

private void writeIndex(CloseableIteration<? extends BindingSet, QueryEvaluationException> iterator,
        boolean autoCommit) throws SQLException, QueryEvaluationException {
    PreparedStatement statement = getInsertStatment();
    boolean oldAutoCommit = statement.getConnection().getAutoCommit();
    statement.getConnection().setAutoCommit(autoCommit);
    // Object[] varNames = this.getSettings().getIndexGraph().getVarNames()
    // .toArray();
    // int[] types = getSQLTypes(varNames);

    int cache = 0;
    int batchsize = this.getSettings().getBatchSize();
    long count = 0;
    DIGEST.reset();//from  w w  w  .  ja  v a  2s.  c om
    for (; iterator.hasNext(); cache++) {

        BindingSet bindingSet = iterator.next();
        setInsertValue(this.getVarNames(), this.getSQLTypes(), statement, bindingSet);

        try {
            if (autoCommit) {
                statement.executeUpdate();
            } else {
                statement.addBatch();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        if (cache > batchsize) {
            if (!autoCommit) {
                statement.executeBatch();
                statement.getConnection().commit();
            }

            LOG.info(this.name + " " + (count += cache));
            cache = 0;
        }
    }

    if (!autoCommit) {
        statement.getConnection().commit();
    }
    statement.getConnection().setAutoCommit(oldAutoCommit);
}

From source file:org.wso2.carbon.identity.oauth2.dao.TokenMgtDAO.java

public void revokeTokensBatch(String[] tokens) throws IdentityOAuth2Exception {

    String accessTokenStoreTable = OAuthConstants.ACCESS_TOKEN_STORE_TABLE;
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement ps = null;
    try {//from w  ww  . j  a va  2s.  c  o  m
        String sqlQuery = SQLQueries.REVOKE_ACCESS_TOKEN.replace(IDN_OAUTH2_ACCESS_TOKEN,
                accessTokenStoreTable);
        ps = connection.prepareStatement(sqlQuery);
        for (String token : tokens) {
            ps.setString(1, OAuthConstants.TokenStates.TOKEN_STATE_REVOKED);
            ps.setString(2, UUID.randomUUID().toString());
            ps.setString(3, persistenceProcessor.getProcessedAccessTokenIdentifier(token));
            ps.addBatch();
        }
        ps.executeBatch();
        connection.commit();
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollBack(connection);
        throw new IdentityOAuth2Exception("Error occurred while revoking Access Tokens : " + tokens.toString(),
                e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, ps);
    }
}

From source file:org.sakaiproject.nakamura.lite.storage.jdbc.JDBCStorageClient.java

public void insert(String keySpace, String columnFamily, String key, Map<String, Object> values,
        boolean probablyNew) throws StorageClientException {
    checkClosed();//w w w  .j av  a 2 s.  c om

    Map<String, PreparedStatement> statementCache = Maps.newHashMap();
    boolean autoCommit = true;
    try {
        autoCommit = startBlock();
        String rid = rowHash(keySpace, columnFamily, key);
        for (Entry<String, Object> e : values.entrySet()) {
            String k = e.getKey();
            Object o = e.getValue();
            if (o instanceof byte[]) {
                throw new RuntimeException(
                        "Invalid content in " + k + ", storing byte[] rather than streaming it");
            }
        }

        Map<String, Object> m = get(keySpace, columnFamily, key);
        for (Entry<String, Object> e : values.entrySet()) {
            String k = e.getKey();
            Object o = e.getValue();

            if (o instanceof RemoveProperty || o == null) {
                m.remove(k);
            } else {
                m.put(k, o);
            }
        }
        LOGGER.debug("Saving {} {} {} ", new Object[] { key, rid, m });
        if (probablyNew && !UPDATE_FIRST_SEQUENCE.equals(getSql(SQL_STATEMENT_SEQUENCE))) {
            PreparedStatement insertBlockRow = getStatement(keySpace, columnFamily, SQL_BLOCK_INSERT_ROW, rid,
                    statementCache);
            insertBlockRow.clearWarnings();
            insertBlockRow.clearParameters();
            insertBlockRow.setString(1, rid);
            InputStream insertStream = null;
            try {
                insertStream = Types.storeMapToStream(rid, m, columnFamily);
            } catch (UTFDataFormatException e) {
                throw new DataFormatException(INVALID_DATA_ERROR, e);
            }
            if ("1.5".equals(getSql(JDBC_SUPPORT_LEVEL))) {
                insertBlockRow.setBinaryStream(2, insertStream, insertStream.available());
            } else {
                insertBlockRow.setBinaryStream(2, insertStream);
            }
            int rowsInserted = 0;
            try {
                rowsInserted = insertBlockRow.executeUpdate();
            } catch (SQLException e) {
                LOGGER.debug(e.getMessage(), e);
            }
            if (rowsInserted == 0) {
                PreparedStatement updateBlockRow = getStatement(keySpace, columnFamily, SQL_BLOCK_UPDATE_ROW,
                        rid, statementCache);
                updateBlockRow.clearWarnings();
                updateBlockRow.clearParameters();
                updateBlockRow.setString(2, rid);
                try {
                    insertStream = Types.storeMapToStream(rid, m, columnFamily);
                } catch (UTFDataFormatException e) {
                    throw new DataFormatException(INVALID_DATA_ERROR, e);
                }
                if ("1.5".equals(getSql(JDBC_SUPPORT_LEVEL))) {
                    updateBlockRow.setBinaryStream(1, insertStream, insertStream.available());
                } else {
                    updateBlockRow.setBinaryStream(1, insertStream);
                }
                if (updateBlockRow.executeUpdate() == 0) {
                    throw new StorageClientException("Failed to save " + rid);
                } else {
                    LOGGER.debug("Updated {} ", rid);
                }
            } else {
                LOGGER.debug("Inserted {} ", rid);
            }
        } else {
            PreparedStatement updateBlockRow = getStatement(keySpace, columnFamily, SQL_BLOCK_UPDATE_ROW, rid,
                    statementCache);
            updateBlockRow.clearWarnings();
            updateBlockRow.clearParameters();
            updateBlockRow.setString(2, rid);
            InputStream updateStream = null;
            try {
                updateStream = Types.storeMapToStream(rid, m, columnFamily);
            } catch (UTFDataFormatException e) {
                throw new DataFormatException(INVALID_DATA_ERROR, e);
            }
            if ("1.5".equals(getSql(JDBC_SUPPORT_LEVEL))) {
                updateBlockRow.setBinaryStream(1, updateStream, updateStream.available());
            } else {
                updateBlockRow.setBinaryStream(1, updateStream);
            }
            if (updateBlockRow.executeUpdate() == 0) {
                PreparedStatement insertBlockRow = getStatement(keySpace, columnFamily, SQL_BLOCK_INSERT_ROW,
                        rid, statementCache);
                insertBlockRow.clearWarnings();
                insertBlockRow.clearParameters();
                insertBlockRow.setString(1, rid);
                try {
                    updateStream = Types.storeMapToStream(rid, m, columnFamily);
                } catch (UTFDataFormatException e) {
                    throw new DataFormatException(INVALID_DATA_ERROR, e);
                }
                if ("1.5".equals(getSql(JDBC_SUPPORT_LEVEL))) {
                    insertBlockRow.setBinaryStream(2, updateStream, updateStream.available());
                } else {
                    insertBlockRow.setBinaryStream(2, updateStream);
                }
                if (insertBlockRow.executeUpdate() == 0) {
                    throw new StorageClientException("Failed to save " + rid);
                } else {
                    LOGGER.debug("Inserted {} ", rid);
                }
            } else {
                LOGGER.debug("Updated {} ", rid);
            }
        }
        if ("1".equals(getSql(USE_BATCH_INSERTS))) {
            Set<PreparedStatement> removeSet = Sets.newHashSet();
            // execute the updates and add the necessary inserts.
            Map<PreparedStatement, List<Entry<String, Object>>> insertSequence = Maps.newHashMap();

            Set<PreparedStatement> insertSet = Sets.newHashSet();

            for (Entry<String, Object> e : values.entrySet()) {
                String k = e.getKey();
                Object o = e.getValue();
                if (shouldIndex(keySpace, columnFamily, k)) {
                    if (o instanceof RemoveProperty || o == null) {
                        PreparedStatement removeStringColumn = getStatement(keySpace, columnFamily,
                                SQL_REMOVE_STRING_COLUMN, rid, statementCache);
                        removeStringColumn.setString(1, rid);
                        removeStringColumn.setString(2, k);
                        removeStringColumn.addBatch();
                        removeSet.add(removeStringColumn);
                    } else {
                        // remove all previous values
                        PreparedStatement removeStringColumn = getStatement(keySpace, columnFamily,
                                SQL_REMOVE_STRING_COLUMN, rid, statementCache);
                        removeStringColumn.setString(1, rid);
                        removeStringColumn.setString(2, k);
                        removeStringColumn.addBatch();
                        removeSet.add(removeStringColumn);
                        // insert new values, as we just removed them we know we can insert, no need to attempt update
                        // the only thing that we know is the colum value changes so we have to re-index the whole
                        // property
                        Object[] valueMembers = (o instanceof Object[]) ? (Object[]) o : new Object[] { o };
                        for (Object ov : valueMembers) {
                            String valueMember = ov.toString();
                            PreparedStatement insertStringColumn = getStatement(keySpace, columnFamily,
                                    SQL_INSERT_STRING_COLUMN, rid, statementCache);
                            insertStringColumn.setString(1, valueMember);
                            insertStringColumn.setString(2, rid);
                            insertStringColumn.setString(3, k);
                            insertStringColumn.addBatch();
                            LOGGER.debug("Insert Index {} {}", k, valueMember);
                            insertSet.add(insertStringColumn);
                            List<Entry<String, Object>> insertSeq = insertSequence.get(insertStringColumn);
                            if (insertSeq == null) {
                                insertSeq = Lists.newArrayList();
                                insertSequence.put(insertStringColumn, insertSeq);
                            }
                            insertSeq.add(e);
                        }
                    }
                }
            }

            if (!StorageClientUtils.isRoot(key)) {
                // create a holding map containing a rowhash of the parent and then process the entry to generate a update operation.
                Map<String, Object> autoIndexMap = ImmutableMap.of(InternalContent.PARENT_HASH_FIELD,
                        (Object) rowHash(keySpace, columnFamily, StorageClientUtils.getParentObjectPath(key)));
                for (Entry<String, Object> e : autoIndexMap.entrySet()) {
                    // remove all previous values
                    PreparedStatement removeStringColumn = getStatement(keySpace, columnFamily,
                            SQL_REMOVE_STRING_COLUMN, rid, statementCache);
                    removeStringColumn.setString(1, rid);
                    removeStringColumn.setString(2, e.getKey());
                    removeStringColumn.addBatch();
                    removeSet.add(removeStringColumn);
                    PreparedStatement insertStringColumn = getStatement(keySpace, columnFamily,
                            SQL_INSERT_STRING_COLUMN, rid, statementCache);
                    insertStringColumn.setString(1, (String) e.getValue());
                    insertStringColumn.setString(2, rid);
                    insertStringColumn.setString(3, e.getKey());
                    insertStringColumn.addBatch();
                    LOGGER.debug("Insert {} {}", e.getKey(), e.getValue());
                    insertSet.add(insertStringColumn);
                    List<Entry<String, Object>> insertSeq = insertSequence.get(insertStringColumn);
                    if (insertSeq == null) {
                        insertSeq = Lists.newArrayList();
                        insertSequence.put(insertStringColumn, insertSeq);
                    }
                    insertSeq.add(e);
                }
            }

            LOGGER.debug("Remove set {}", removeSet);

            for (PreparedStatement pst : removeSet) {
                pst.executeBatch();
            }

            LOGGER.debug("Insert set {}", insertSet);
            for (PreparedStatement pst : insertSet) {
                int[] res = pst.executeBatch();
                List<Entry<String, Object>> insertSeq = insertSequence.get(pst);
                for (int i = 0; i < res.length; i++) {
                    Entry<String, Object> e = insertSeq.get(i);
                    if (res[i] <= 0 && res[i] != -2) { // Oracle drivers respond with -2 on a successful insert when the number is not known http://download.oracle.com/javase/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html
                        LOGGER.warn("Index failed for {} {} ", new Object[] { rid, e.getKey(), e.getValue() });

                    } else {
                        LOGGER.debug("Index inserted for {} {} ",
                                new Object[] { rid, e.getKey(), e.getValue() });

                    }
                }
            }

        } else {
            for (Entry<String, Object> e : values.entrySet()) {
                String k = e.getKey();
                Object o = e.getValue();
                if (shouldIndex(keySpace, columnFamily, k)) {
                    if (o instanceof RemoveProperty || o == null) {
                        PreparedStatement removeStringColumn = getStatement(keySpace, columnFamily,
                                SQL_REMOVE_STRING_COLUMN, rid, statementCache);
                        removeStringColumn.clearWarnings();
                        removeStringColumn.clearParameters();
                        removeStringColumn.setString(1, rid);
                        removeStringColumn.setString(2, k);
                        int nrows = removeStringColumn.executeUpdate();
                        if (nrows == 0) {
                            m = get(keySpace, columnFamily, key);
                            LOGGER.debug("Column Not present did not remove {} {} Current Column:{} ",
                                    new Object[] { getRowId(keySpace, columnFamily, key), k, m });
                        } else {
                            LOGGER.debug("Removed Index {} {} {} ",
                                    new Object[] { getRowId(keySpace, columnFamily, key), k, nrows });
                        }
                    } else {
                        PreparedStatement removeStringColumn = getStatement(keySpace, columnFamily,
                                SQL_REMOVE_STRING_COLUMN, rid, statementCache);
                        removeStringColumn.clearWarnings();
                        removeStringColumn.clearParameters();
                        removeStringColumn.setString(1, rid);
                        removeStringColumn.setString(2, k);
                        int nrows = removeStringColumn.executeUpdate();
                        if (nrows == 0) {
                            m = get(keySpace, columnFamily, key);
                            LOGGER.debug("Column Not present did not remove {} {} Current Column:{} ",
                                    new Object[] { getRowId(keySpace, columnFamily, key), k, m });
                        } else {
                            LOGGER.debug("Removed Index {} {} {} ",
                                    new Object[] { getRowId(keySpace, columnFamily, key), k, nrows });
                        }
                        Object[] os = (o instanceof Object[]) ? (Object[]) o : new Object[] { o };
                        for (Object ov : os) {
                            String v = ov.toString();
                            PreparedStatement insertStringColumn = getStatement(keySpace, columnFamily,
                                    SQL_INSERT_STRING_COLUMN, rid, statementCache);
                            insertStringColumn.clearWarnings();
                            insertStringColumn.clearParameters();
                            insertStringColumn.setString(1, v);
                            insertStringColumn.setString(2, rid);
                            insertStringColumn.setString(3, k);
                            LOGGER.debug("Non Batch Insert Index {} {}", k, v);
                            if (insertStringColumn.executeUpdate() == 0) {
                                throw new StorageClientException("Failed to save "
                                        + getRowId(keySpace, columnFamily, key) + "  column:[" + k + "] ");
                            } else {
                                LOGGER.debug("Inserted Index {} {} [{}]",
                                        new Object[] { getRowId(keySpace, columnFamily, key), k, v });
                            }
                        }
                    }
                }
            }

            if (!StorageClientUtils.isRoot(key)) {
                String parent = StorageClientUtils.getParentObjectPath(key);
                String hash = rowHash(keySpace, columnFamily, parent);
                LOGGER.debug("Hash of {}:{}:{} is {} ", new Object[] { keySpace, columnFamily, parent, hash });
                Map<String, Object> autoIndexMap = ImmutableMap.of(InternalContent.PARENT_HASH_FIELD,
                        (Object) hash);
                for (Entry<String, Object> e : autoIndexMap.entrySet()) {
                    String k = e.getKey();
                    Object v = e.getValue();
                    PreparedStatement removeStringColumn = getStatement(keySpace, columnFamily,
                            SQL_REMOVE_STRING_COLUMN, rid, statementCache);
                    removeStringColumn.clearWarnings();
                    removeStringColumn.clearParameters();
                    removeStringColumn.setString(1, rid);
                    removeStringColumn.setString(2, k);
                    int nrows = removeStringColumn.executeUpdate();
                    if (nrows == 0) {
                        m = get(keySpace, columnFamily, key);
                        LOGGER.debug("Column Not present did not remove {} {} Current Column:{} ",
                                new Object[] { getRowId(keySpace, columnFamily, key), k, m });
                    } else {
                        LOGGER.debug("Removed Index {} {} {} ",
                                new Object[] { getRowId(keySpace, columnFamily, key), k, nrows });
                    }

                    PreparedStatement insertStringColumn = getStatement(keySpace, columnFamily,
                            SQL_INSERT_STRING_COLUMN, rid, statementCache);
                    insertStringColumn.clearWarnings();
                    insertStringColumn.clearParameters();
                    insertStringColumn.setString(1, v.toString());
                    insertStringColumn.setString(2, rid);
                    insertStringColumn.setString(3, k);
                    LOGGER.debug("Non Batch Insert Index {} {}", k, v);
                    if (insertStringColumn.executeUpdate() == 0) {
                        throw new StorageClientException("Failed to save "
                                + getRowId(keySpace, columnFamily, key) + "  column:[" + k + "] ");
                    } else {
                        LOGGER.debug("Inserted Index {} {} [{}]",
                                new Object[] { getRowId(keySpace, columnFamily, key), k, v });
                    }
                }
            }

        }
        endBlock(autoCommit);
    } catch (SQLException e) {
        abandonBlock(autoCommit);
        LOGGER.warn("Failed to perform insert/update operation on {}:{}:{} ",
                new Object[] { keySpace, columnFamily, key }, e);
        throw new StorageClientException(e.getMessage(), e);
    } catch (IOException e) {
        abandonBlock(autoCommit);
        LOGGER.warn("Failed to perform insert/update operation on {}:{}:{} ",
                new Object[] { keySpace, columnFamily, key }, e);
        throw new StorageClientException(e.getMessage(), e);
    } finally {
        close(statementCache);
    }
}