Example usage for java.sql PreparedStatement executeBatch

List of usage examples for java.sql PreparedStatement 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.wso2.carbon.identity.oauth2.dao.TokenMgtDAO.java

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

    try {//from ww w  .  j a va2s . 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:org.owasp.dependencycheck.data.nvdcve.CveDB.java

/**
 * Updates the vulnerability within the database. If the vulnerability does
 * not exist it will be added.//  w ww. java 2 s  .c o m
 *
 * @param vuln the vulnerability to add to the database
 * @throws DatabaseException is thrown if the database
 */
public synchronized void updateVulnerability(Vulnerability vuln) throws DatabaseException {
    clearCache();
    ResultSet rs = null;
    try {
        int vulnerabilityId = 0;
        final PreparedStatement selectVulnerabilityId = getPreparedStatement(SELECT_VULNERABILITY_ID);
        selectVulnerabilityId.setString(1, vuln.getName());
        rs = selectVulnerabilityId.executeQuery();
        if (rs.next()) {
            vulnerabilityId = rs.getInt(1);
            // first delete any existing vulnerability info. We don't know what was updated. yes, slower but atm easier.
            final PreparedStatement deleteReference = getPreparedStatement(DELETE_REFERENCE);
            deleteReference.setInt(1, vulnerabilityId);
            deleteReference.execute();

            final PreparedStatement deleteSoftware = getPreparedStatement(DELETE_SOFTWARE);
            deleteSoftware.setInt(1, vulnerabilityId);
            deleteSoftware.execute();
        }

        DBUtils.closeResultSet(rs);

        if (vulnerabilityId != 0) {
            if (vuln.getDescription().contains("** REJECT **")) {
                final PreparedStatement deleteVulnerability = getPreparedStatement(DELETE_VULNERABILITY);
                deleteVulnerability.setInt(1, vulnerabilityId);
                deleteVulnerability.executeUpdate();
            } else {
                final PreparedStatement updateVulnerability = getPreparedStatement(UPDATE_VULNERABILITY);
                updateVulnerability.setString(1, vuln.getDescription());
                updateVulnerability.setString(2, vuln.getCwe());
                updateVulnerability.setFloat(3, vuln.getCvssScore());
                updateVulnerability.setString(4, vuln.getCvssAccessVector());
                updateVulnerability.setString(5, vuln.getCvssAccessComplexity());
                updateVulnerability.setString(6, vuln.getCvssAuthentication());
                updateVulnerability.setString(7, vuln.getCvssConfidentialityImpact());
                updateVulnerability.setString(8, vuln.getCvssIntegrityImpact());
                updateVulnerability.setString(9, vuln.getCvssAvailabilityImpact());
                updateVulnerability.setInt(10, vulnerabilityId);
                updateVulnerability.executeUpdate();
            }
        } else {
            final PreparedStatement insertVulnerability = getPreparedStatement(INSERT_VULNERABILITY);
            insertVulnerability.setString(1, vuln.getName());
            insertVulnerability.setString(2, vuln.getDescription());
            insertVulnerability.setString(3, vuln.getCwe());
            insertVulnerability.setFloat(4, vuln.getCvssScore());
            insertVulnerability.setString(5, vuln.getCvssAccessVector());
            insertVulnerability.setString(6, vuln.getCvssAccessComplexity());
            insertVulnerability.setString(7, vuln.getCvssAuthentication());
            insertVulnerability.setString(8, vuln.getCvssConfidentialityImpact());
            insertVulnerability.setString(9, vuln.getCvssIntegrityImpact());
            insertVulnerability.setString(10, vuln.getCvssAvailabilityImpact());
            insertVulnerability.execute();
            try {
                rs = insertVulnerability.getGeneratedKeys();
                rs.next();
                vulnerabilityId = rs.getInt(1);
            } catch (SQLException ex) {
                final String msg = String.format("Unable to retrieve id for new vulnerability for '%s'",
                        vuln.getName());
                throw new DatabaseException(msg, ex);
            } finally {
                DBUtils.closeResultSet(rs);
            }
        }

        PreparedStatement insertReference = getPreparedStatement(INSERT_REFERENCE);
        int countReferences = 0;
        for (Reference r : vuln.getReferences()) {
            insertReference.setInt(1, vulnerabilityId);
            insertReference.setString(2, r.getName());
            insertReference.setString(3, r.getUrl());
            insertReference.setString(4, r.getSource());
            if (isBatchInsertEnabled()) {
                insertReference.addBatch();
                countReferences++;
                if (countReferences % getBatchSize() == 0) {
                    insertReference.executeBatch();
                    insertReference = getPreparedStatement(INSERT_REFERENCE);
                    LOGGER.trace(getLogForBatchInserts(countReferences,
                            "Completed %s batch inserts to references table: %s"));
                    countReferences = 0;
                } else if (countReferences == vuln.getReferences().size()) {
                    if (LOGGER.isTraceEnabled()) {
                        LOGGER.trace(getLogForBatchInserts(countReferences,
                                "Completed %s batch inserts to reference table: %s"));
                    }
                    insertReference.executeBatch();
                    countReferences = 0;
                }
            } else {
                insertReference.execute();
            }
        }

        PreparedStatement insertSoftware = getPreparedStatement(INSERT_SOFTWARE);
        int countSoftware = 0;
        for (VulnerableSoftware vulnerableSoftware : vuln.getVulnerableSoftware()) {
            int cpeProductId = 0;
            final PreparedStatement selectCpeId = getPreparedStatement(SELECT_CPE_ID);
            selectCpeId.setString(1, vulnerableSoftware.getName());
            try {
                rs = selectCpeId.executeQuery();
                if (rs.next()) {
                    cpeProductId = rs.getInt(1);
                }
            } catch (SQLException ex) {
                throw new DatabaseException(
                        "Unable to get primary key for new cpe: " + vulnerableSoftware.getName(), ex);
            } finally {
                DBUtils.closeResultSet(rs);
            }

            if (cpeProductId == 0) {
                final PreparedStatement insertCpe = getPreparedStatement(INSERT_CPE);
                insertCpe.setString(1, vulnerableSoftware.getName());
                insertCpe.setString(2, vulnerableSoftware.getVendor());
                insertCpe.setString(3, vulnerableSoftware.getProduct());
                insertCpe.executeUpdate();
                cpeProductId = DBUtils.getGeneratedKey(insertCpe);
            }
            if (cpeProductId == 0) {
                throw new DatabaseException("Unable to retrieve cpeProductId - no data returned");
            }

            insertSoftware.setInt(1, vulnerabilityId);
            insertSoftware.setInt(2, cpeProductId);

            if (vulnerableSoftware.getPreviousVersion() == null) {
                insertSoftware.setNull(3, java.sql.Types.VARCHAR);
            } else {
                insertSoftware.setString(3, vulnerableSoftware.getPreviousVersion());
            }
            if (isBatchInsertEnabled()) {
                insertSoftware.addBatch();
                countSoftware++;
                if (countSoftware % getBatchSize() == 0) {
                    executeBatch(vuln, vulnerableSoftware, insertSoftware);
                    insertSoftware = getPreparedStatement(INSERT_SOFTWARE);
                    LOGGER.trace(getLogForBatchInserts(countSoftware,
                            "Completed %s batch inserts software table: %s"));
                    countSoftware = 0;
                } else if (countSoftware == vuln.getVulnerableSoftware().size()) {
                    if (LOGGER.isTraceEnabled()) {
                        LOGGER.trace(getLogForBatchInserts(countSoftware,
                                "Completed %s batch inserts software table: %s"));
                        countReferences = 0;
                    }
                    executeBatch(vuln, vulnerableSoftware, insertSoftware);
                }
            } else {
                try {
                    insertSoftware.execute();
                } catch (SQLException ex) {
                    if (ex.getMessage().contains("Duplicate entry")) {
                        final String msg = String.format("Duplicate software key identified in '%s:%s'",
                                vuln.getName(), vuln.getName());
                        LOGGER.info(msg, ex);
                    } else {
                        throw ex;
                    }
                }
            }
        }
    } catch (SQLException ex) {
        final String msg = String.format("Error updating '%s'", vuln.getName());
        LOGGER.debug(msg, ex);
        throw new DatabaseException(msg, ex);
    } finally {
        DBUtils.closeResultSet(rs);
    }
}

From source file:org.wso2.carbon.identity.openidconnect.dao.RequestObjectDAOImpl.java

private void insertRequestObjectClaims(int requestObjectId, List<List<RequestedClaim>> claims,
        Connection connection) throws IdentityOAuth2Exception {

    String sqlStmt = SQLQueries.STORE_IDN_OIDC_REQ_OBJECT_CLAIMS;
    PreparedStatement prepStmt = null;
    Map<Integer, List<String>> claimValues = new HashMap<>();
    try {//from  w  ww.j a  va2 s.com
        String dbProductName = connection.getMetaData().getDatabaseProductName();
        prepStmt = connection.prepareStatement(sqlStmt,
                new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, ID) });
        for (List<RequestedClaim> list : claims) {
            if (CollectionUtils.isNotEmpty(list)) {
                for (RequestedClaim claim : list) {
                    prepStmt.setInt(1, requestObjectId);
                    prepStmt.setString(2, claim.getName());
                    prepStmt.setString(3, claim.isEssential() ? "1" : "0");
                    prepStmt.setString(4, claim.getValue());
                    if (OIDCConstants.USERINFO.equals(claim.getType())) {
                        prepStmt.setString(5, "1");
                    } else if (OIDCConstants.ID_TOKEN.equals(claim.getType())) {
                        prepStmt.setString(5, "0");
                    }
                    prepStmt.addBatch();
                    if (log.isDebugEnabled()) {
                        log.debug("Claim :" + claim.getName() + "is added to the batch against :"
                                + claim.getType());
                    }
                }
            }
            prepStmt.executeBatch();
            connection.commit();
        }
        Map<Integer, String> insertedRequestObjectClaims = getInsertedRequestObjectClaims(requestObjectId);
        if (MapUtils.isNotEmpty(insertedRequestObjectClaims)) {
            for (Map.Entry<Integer, String> entry : insertedRequestObjectClaims.entrySet()) {
                for (List<RequestedClaim> list : claims) {
                    if (CollectionUtils.isNotEmpty(list)) {
                        for (RequestedClaim claim : list) {
                            if (claim.getName().equals(entry.getValue())) {
                                claimValues.put(entry.getKey(), claim.getValues());
                            }

                        }
                    }

                }
            }
            if (MapUtils.isNotEmpty(claimValues)) {
                insertRequestObjectClaimValues(claimValues, connection);
            }
        }
    } catch (SQLException e) {
        String errorMessage = "Error when storing the request object claims.";
        log.error(errorMessage, e);
        throw new IdentityOAuth2Exception(errorMessage, e);
    } finally {
        IdentityApplicationManagementUtil.closeStatement(prepStmt);
    }
}

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

/**
 * {@inheritDoc}/*from  w  w  w  . j  a va 2  s  .  co m*/
 */
@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:datawarehouse.CSVLoader.java

/**
 * Parse CSV file using OpenCSV library and load in given database table.
 *
 * @param csvFile Input CSV file//  ww w .  j ava2  s .co  m
 * @param tableName Database table name to import data
 * @param truncateBeforeLoad Truncate the table before inserting new
 * records.
 * @throws Exception
 */
public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad) throws Exception {

    CSVReader csvReader = null;
    if (null == this.connection) {
        throw new Exception("Not a valid connection.");
    }
    try {

        csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

    } catch (Exception e) {
        e.printStackTrace();
        throw new Exception("Error occured while executing file. " + e.getMessage());
    }

    String[] headerRow = csvReader.readNext();

    if (null == headerRow) {
        throw new FileNotFoundException(
                "No columns defined in given CSV file." + "Please check the CSV file format.");
    }

    String questionmarks = StringUtils.repeat("?,", headerRow.length);
    questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

    String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
    query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
    query = query.replaceFirst(VALUES_REGEX, questionmarks);

    System.out.println("Query: " + query);

    String[] nextLine;
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = this.connection;
        con.setAutoCommit(false);
        ps = con.prepareStatement(query);

        if (truncateBeforeLoad) {
            //delete data from table before loading csv
            con.createStatement().execute("DELETE FROM " + tableName);
        }

        final int batchSize = 1000;
        int count = 0;
        while ((nextLine = csvReader.readNext()) != null) {
            if (null != nextLine) {
                int index = 1;
                for (String string : nextLine) {
                    //System.out.print(string + ": ");
                    try {
                        DateFormat format = new SimpleDateFormat("dd.mm.yyyy");
                        Date date = format.parse(string);
                        ps.setDate(index++, new java.sql.Date(date.getTime()));
                        //System.out.println("date");
                    } catch (ParseException | SQLException e) {
                        try {
                            Double income = parseDouble(string.replace(",", "."));
                            ps.setDouble(index++, income);
                            //System.out.println("double");
                        } catch (NumberFormatException | SQLException err) {
                            ps.setString(index++, string);
                            //System.out.println("string");
                        }
                    }
                }
                ps.addBatch();
            }
            if (++count % batchSize == 0) {
                ps.executeBatch();
            }
        }
        ps.executeBatch(); // insert remaining records
        con.commit();
    } catch (Exception e) {
        con.rollback();
        e.printStackTrace();
        throw new Exception("Error occured while loading data from file to database." + e.getMessage());
    } finally {
        if (null != ps) {
            ps.close();
        }
        if (null != con) {
            con.close();
        }

        csvReader.close();
    }
}

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

public void migrateUMData() throws MigrationClientException {
    log.info("MIGRATION-LOGS >> Going to start : migrateUMData.");
    Connection identityConnection = null;
    Connection umConnection = null;

    PreparedStatement selectServiceProviders = null;
    PreparedStatement updateRole = null;

    ResultSet selectServiceProvidersRS = null;

    try {//from w w  w  . j ava  2s .  c o  m
        identityConnection = getDataSource(Schema.IDENTITY.getName()).getConnection();
        umConnection = getDataSource().getConnection();

        identityConnection.setAutoCommit(false);
        umConnection.setAutoCommit(false);

        selectServiceProviders = identityConnection.prepareStatement(SQLQueries.LOAD_APP_NAMES);
        selectServiceProvidersRS = selectServiceProviders.executeQuery();

        updateRole = umConnection.prepareStatement(SQLQueries.UPDATE_ROLES);
        while (selectServiceProvidersRS.next()) {
            try {
                String appName = selectServiceProvidersRS.getString("APP_NAME");
                int tenantId = selectServiceProvidersRS.getInt("TENANT_ID");
                updateRole.setString(1,
                        ApplicationConstants.APPLICATION_DOMAIN + UserCoreConstants.DOMAIN_SEPARATOR + appName);
                updateRole.setString(2, appName);
                updateRole.setInt(3, tenantId);
                if (isBatchUpdate()) {
                    updateRole.addBatch();
                } else {
                    updateRole.executeUpdate();
                    log.info("MIGRATION-LOGS >> Executed query : " + updateRole.toString());
                }
            } catch (Exception e) {
                log.error("MIGRATION-ERROR-LOGS-037 >> Error while executing the migration.", e);
                if (!isContinueOnError()) {
                    throw new MigrationClientException("Error while executing the migration.", e);
                }
            }
        }
        if (isBatchUpdate()) {
            updateRole.executeBatch();
            log.info("MIGRATION-LOGS >> Executed query : " + updateRole.toString());
        }
        identityConnection.commit();
        umConnection.commit();
    } catch (SQLException e) {
        log.error("MIGRATION-ERROR-LOGS-038 >> Error while executing the migration.", e);
        if (!isContinueOnError()) {
            throw new MigrationClientException("Error while executing the migration.", e);
        }
    } finally {
        try {
            IdentityDatabaseUtil.closeResultSet(selectServiceProvidersRS);
            IdentityDatabaseUtil.closeStatement(selectServiceProviders);
            IdentityDatabaseUtil.closeStatement(updateRole);
            IdentityDatabaseUtil.closeConnection(identityConnection);
            IdentityDatabaseUtil.closeConnection(umConnection);
        } catch (Exception e) {

        }
    }
    log.info("MIGRATION-LOGS >> Done : migrateUMData.");
}

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 {//w w  w.  jav  a  2  s .  c  om
        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:com.wso2telco.dep.mediator.dao.SMSMessagingDAO.java

/**
 * Insert sms request ids.//from   ww  w. j  a  va  2 s .c  om
 *
 * @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:org.openbel.framework.core.kam.JdbcKAMLoaderImpl.java

/**
 * {@inheritDoc}//from  w w  w.ja  v  a 2  s. c om
 */
@Override
public void loadEdges(StatementTable st, TermTable tt, ProtoNodeTable pnt, ProtoEdgeTable pet)
        throws SQLException {
    // load kam edges and associate to kam nodes (global terms)
    PreparedStatement keps = getPreparedStatement(KAM_EDGE_SQL);

    final Map<Integer, Integer> eqn = pnt.getEquivalences();
    final Map<Integer, Integer> eqs = pet.getEquivalences();
    final List<TableProtoEdge> edges = pet.getProtoEdges();
    Set<Integer> added = new HashSet<Integer>();

    for (int i = 0, n = edges.size(); i < n; i++) {
        final Integer eqId = eqs.get(i);

        // continue if we have already seen this equivalent proto edge
        if (added.contains(eqId)) {
            continue;
        }

        added.add(eqId);

        final TableProtoEdge edge = edges.get(i);

        // XXX offset
        keps.setInt(1, eqId + 1);
        // XXX offset
        keps.setInt(2, eqn.get(edge.getSource()) + 1);
        // XXX offset
        keps.setInt(3, eqn.get(edge.getTarget()) + 1);

        RelationshipType r = RelationshipType.getRelationshipType(edge.getRel());
        keps.setInt(4, r.getValue());
        keps.addBatch();
    }

    keps.executeBatch();

    // load statements
    final List<StatementTable.TableStatement> ts = st.getStatements();
    final Map<Integer, Integer> sdm = st.getStatementDocument();

    PreparedStatement sps = getPreparedStatement(STATEMENT_SQL);
    for (int i = 0, n = ts.size(); i < n; i++) {
        final TableStatement stmt = ts.get(i);

        // XXX offset
        sps.setInt(1, i + 1);
        // XXX offset
        sps.setInt(2, sdm.get(i) + 1);
        // XXX offset
        sps.setInt(3, stmt.getSubjectTermId() + 1);

        if (stmt.getRelationshipName() == null) {
            // load definitional statement
            sps.setNull(4, Types.INTEGER);
            sps.setNull(5, Types.INTEGER);
            sps.setNull(6, Types.INTEGER);
            sps.setNull(7, Types.INTEGER);
            sps.setNull(8, Types.INTEGER);
        } else if (stmt.getObjectTermId() != null) {
            // load simple statement
            RelationshipType r = RelationshipType.getRelationshipType(stmt.getRelationshipName());
            sps.setInt(4, r.getValue());

            // XXX offset
            sps.setInt(5, stmt.getObjectTermId() + 1);
            sps.setNull(6, Types.INTEGER);
            sps.setNull(7, Types.INTEGER);
            sps.setNull(8, Types.INTEGER);
        } else {
            // load nested statement
            RelationshipType r = RelationshipType.getRelationshipType(stmt.getRelationshipName());
            sps.setInt(4, r.getValue());

            // set null for object term since this is a nested statement
            sps.setNull(5, Types.INTEGER);

            // XXX offset
            sps.setInt(6, stmt.getNestedSubject() + 1);

            RelationshipType nr = RelationshipType.getRelationshipType(stmt.getNestedRelationship());

            sps.setInt(7, nr.getValue());

            // XXX offset
            sps.setInt(8, stmt.getNestedObject() + 1);
        }
        sps.addBatch();
    }

    sps.executeBatch();

    // load many-to-many association of edges to statements
    PreparedStatement skes = getPreparedStatement(KAM_EDGE_STATEMENT_SQL);
    final Map<Integer, Set<Integer>> edgeStmts = pet.getEdgeStatements();
    added.clear();
    for (int i = 0, n = edges.size(); i < n; i++) {
        final Integer eqId = eqs.get(i);

        // continue if we have already seen this equivalent proto edge
        if (added.contains(eqId)) {
            continue;
        }

        added.add(eqId);

        // retrieve statements for this edge
        final Set<Integer> stmtIds = edgeStmts.get(i);

        // if we have the edge, then assert that we have its statements
        assert stmtIds != null && !stmtIds.isEmpty();

        for (final Integer stmtId : stmtIds) {
            // XXX offset
            skes.setInt(1, eqId + 1);
            // XXX offset
            skes.setInt(2, stmtId + 1);
            skes.addBatch();
        }
    }

    skes.executeBatch();
}

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 {/*from ww w .j a  v a 2s. 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();
        }
    }
}