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:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

@VisibleForTesting
void insertAttendees(AccessToken editor, Event ev, Connection con, List<Attendee> attendees)
        throws SQLException {
    String attQ = "INSERT INTO EventLink (" + ATT_INSERT_FIELDS + ") VALUES (" + "?, " + // event_id
            "?, " + // entity_id
            "?, " + // state
            "?, " + // required
            "?," + // percent
            "?," + // user_create
            "?" + // is_organizer
            ")";/*from   w  w w. j  av a2  s . c o m*/
    boolean shouldClearOrganizer = false;
    PreparedStatement ps = null;

    try {
        ps = con.prepareStatement(attQ);

        final int eventObmId = ev.getObmId().getObmId();
        final Set<Attendee> listAttendee = removeDuplicateAttendee(attendees);
        Set<EntityId> alreadyAddedAttendees = Sets.newHashSet();

        for (final Attendee at : listAttendee) {
            boolean isOrganizer = Objects.firstNonNull(at.isOrganizer(), false);

            String attendeeEmail = at.getEmail();
            EntityId userEntity = at.getEntityId();

            // There must be only one organizer in a given event
            if (isOrganizer) {
                shouldClearOrganizer = true;
            }

            if (alreadyAddedAttendees.contains(userEntity)) {
                logger.info("Attendee {} with entity ID {} already added, skipping.", attendeeEmail,
                        userEntity);

                continue;
            }

            ps.setInt(1, eventObmId);
            ps.setInt(2, userEntity.getId());
            ps.setObject(3, getJdbcObjectParticipation(at));
            ps.setObject(4, getJdbcObjectParticipationRole(at));
            ps.setInt(5, at.getPercent());
            ps.setInt(6, editor.getObmId());
            ps.setBoolean(7, isOrganizer);
            ps.addBatch();
            logger.info(LogUtils.prefix(editor) + "Adding " + attendeeEmail
                    + (isOrganizer ? " as organizer" : " as attendee"));

            alreadyAddedAttendees.add(userEntity);
        }

        // Clear the previous organizer if needed
        if (shouldClearOrganizer) {
            clearOrganizer(eventObmId, con);
        }

        ps.executeBatch();
    } finally {
        obmHelper.cleanup(null, ps, null);
    }
}

From source file:org.apache.marmotta.kiwi.persistence.KiWiConnection.java

public synchronized void flushBatch() throws SQLException {
    if (batchCommit && tripleBatch != null) {
        requireJDBCConnection();// w  w  w.ja v  a  2s .c  om

        commitLock.lock();
        try {
            RetryExecution execution = new RetryExecution("FLUSH BATCH");
            execution.setUseSavepoint(true);
            execution.execute(connection, new RetryCommand<Void>() {
                @Override
                public Void run() throws SQLException {
                    PreparedStatement insertTriple = getPreparedStatement("store.triple");
                    insertTriple.clearParameters();
                    insertTriple.clearBatch();

                    synchronized (tripleBatch) {
                        for (KiWiTriple triple : tripleBatch) {
                            // retrieve a new triple ID and set it in the object
                            if (triple.getId() < 0) {
                                triple.setId(getNextSequence());
                            }

                            insertTriple.setLong(1, triple.getId());
                            insertTriple.setLong(2, triple.getSubject().getId());
                            insertTriple.setLong(3, triple.getPredicate().getId());
                            insertTriple.setLong(4, triple.getObject().getId());
                            if (triple.getContext() != null) {
                                insertTriple.setLong(5, triple.getContext().getId());
                            } else {
                                insertTriple.setNull(5, Types.BIGINT);
                            }
                            insertTriple.setBoolean(6, triple.isInferred());
                            insertTriple.setTimestamp(7, new Timestamp(triple.getCreated().getTime()));

                            insertTriple.addBatch();
                        }
                    }
                    insertTriple.executeBatch();

                    tripleBatch.clear();

                    return null;
                }
            });

        } finally {
            commitLock.unlock();
        }

    }

}

From source file:HSqlManager.java

@SuppressWarnings("Duplicates")
@Deprecated/*from w  w  w.j av  a2s . c  o m*/
private static void mycoCommonInitialize(int bps, Connection connection) throws SQLException, IOException {
    long time = System.currentTimeMillis();
    String base = new File("").getAbsolutePath();
    CSV.makeDirectory(new File(base + "/PhageData"));
    INSTANCE = ImportPhagelist.getInstance();
    //        INSTANCE.parseAllPhages(bps);
    written = true;
    Connection db = connection;
    db.setAutoCommit(false);
    Statement stat = db.createStatement();
    stat.execute("SET FILES LOG FALSE\n");
    PreparedStatement st = db.prepareStatement("Insert INTO Primerdb.Primers"
            + "(Bp,Sequence, CommonP, UniqueP, Picked, Strain, Cluster)" + " Values(?,?,true,false,false,?,?)");
    ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;");
    List<String[]> phages = new ArrayList<>();
    String strain = "";
    while (call.next()) {
        String[] r = new String[3];
        r[0] = call.getString("Strain");
        r[1] = call.getString("Cluster");
        r[2] = call.getString("Name");
        phages.add(r);
        if (r[2].equals("xkcd")) {
            strain = r[0];
        }
    }
    call.close();
    String x = strain;
    Set<String> clust = phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]).collect(Collectors.toSet());
    Map<String, List<String>> clusters = new HashMap<>();
    clust.parallelStream().forEach(cluster -> clusters.put(cluster, phages.stream()
            .filter(a -> a[0].equals(x) && a[1].equals(cluster)).map(a -> a[2]).collect(Collectors.toList())));
    for (String z : clusters.keySet()) {
        try {
            List<String> clustphages = clusters.get(z);
            Set<String> primers = Collections.synchronizedSet(
                    CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + clustphages.get(0) + ".csv"));
            clustphages.remove(0);
            for (String phage : clustphages) {
                //                    String[] seqs = Fasta.parse(base + "/Fastas/" + phage + ".fasta");
                //                    String sequence =seqs[0]+seqs[1];
                //                    Map<String, List<Integer>> seqInd = new HashMap<>();
                //                    for (int i = 0; i <= sequence.length()-bps; i++) {
                //                        String sub=sequence.substring(i,i+bps);
                //                        if(seqInd.containsKey(sub)){
                //                            seqInd.get(sub).add(i);
                //                        }else {
                //                            List<Integer> list = new ArrayList<>();
                //                            list.add(i);
                //                            seqInd.put(sub,list);
                //                        }
                //                    }
                //                    primers = primers.stream().filter(seqInd::containsKey).collect(Collectors.toSet());
                //                    primers =Sets.intersection(primers,CSV.readCSV(base + "/PhageData/"+Integer.toString(bps)
                //                            + phage + ".csv"));
                //                    System.gc();
                //                            String[] seqs = Fasta.parse(base + "/Fastas/" + phage + ".fasta");
                //                            String sequence =seqs[0]+seqs[1];
                //                            primers.stream().filter(sequence::contains);
                primers.retainAll(CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv"));
                //                    Set<CharSequence> prim = primers;
                //                    for (CharSequence primer: primers){
                //                        if(seqInd.containsKey(primer)){
                //                            prim.remove(primer);
                //                        }
                //                    }
                //                    primers=prim;
            }
            int i = 0;
            for (String a : primers) {
                try {
                    //finish update
                    st.setInt(1, bps);
                    st.setString(2, a);
                    st.setString(3, x);
                    st.setString(4, z);
                    st.addBatch();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("Error occurred at " + x + " " + z);
                }
                i++;
                if (i == 1000) {
                    i = 0;
                    st.executeBatch();
                    db.commit();
                }
            }
            if (i > 0) {
                st.executeBatch();
                db.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Error occurred at " + x + " " + z);
        }
        System.out.println(z);
    }
    stat.execute("SET FILES LOG TRUE\n");
    st.close();
    stat.close();
    System.out.println("Common Updated");
    System.out.println((System.currentTimeMillis() - time) / Math.pow(10, 3) / 60);
}

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

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

    PreparedStatement preparedStatementUpdate = null;
    PreparedStatement preparedStatementDelete = null;
    Statement statement = null;// ww  w  .  j  a va2 s . co m
    ResultSet resultSet = null;
    boolean continueUpdatingDB = true;
    long totalRecords = 0;
    long decryptionFailedRecords = 0;

    try {
        String query = "SELECT APPLICATION_ID, CONSUMER_KEY, KEY_TYPE FROM AM_APPLICATION_KEY_MAPPING";
        ArrayList<AppKeyMappingTableDTO> appKeyMappingTableDTOs = new ArrayList<>();
        ArrayList<AppKeyMappingTableDTO> appKeyMappingTableDTOsFailed = new ArrayList<>();

        statement = connection.createStatement();
        statement.setFetchSize(50);
        resultSet = statement.executeQuery(query);

        while (resultSet.next()) {
            ConsumerKeyDTO consumerKeyDTO = new ConsumerKeyDTO();
            consumerKeyDTO.setEncryptedConsumerKey(resultSet.getString("CONSUMER_KEY"));

            AppKeyMappingTableDTO appKeyMappingTableDTO = new AppKeyMappingTableDTO();
            appKeyMappingTableDTO.setApplicationId(resultSet.getString("APPLICATION_ID"));
            appKeyMappingTableDTO.setConsumerKey(consumerKeyDTO);
            appKeyMappingTableDTO.setKeyType(resultSet.getString("KEY_TYPE"));
            totalRecords++;
            if (ResourceModifier.decryptConsumerKeyIfEncrypted(consumerKeyDTO)) {

                appKeyMappingTableDTOs.add(appKeyMappingTableDTO);
                log.debug("Successfully decrypted consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " as : " + consumerKeyDTO.getDecryptedConsumerKey()
                        + " in AM_APPLICATION_KEY_MAPPING table");
            } else {
                log.error("Cannot decrypt consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " in AM_APPLICATION_KEY_MAPPING table");
                decryptionFailedRecords++;
                appKeyMappingTableDTOsFailed.add(appKeyMappingTableDTO);

                //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) {
            preparedStatementUpdate = connection
                    .prepareStatement("UPDATE AM_APPLICATION_KEY_MAPPING SET CONSUMER_KEY = ?"
                            + " WHERE APPLICATION_ID = ? AND KEY_TYPE = ?");

            for (AppKeyMappingTableDTO appKeyMappingTableDTO : appKeyMappingTableDTOs) {
                preparedStatementUpdate.setString(1,
                        appKeyMappingTableDTO.getConsumerKey().getDecryptedConsumerKey());
                preparedStatementUpdate.setString(2, appKeyMappingTableDTO.getApplicationId());
                preparedStatementUpdate.setString(3, appKeyMappingTableDTO.getKeyType());
                preparedStatementUpdate.addBatch();
            }
            preparedStatementUpdate.executeBatch();

            //deleting rows where consumer key decryption was unsuccessful
            preparedStatementDelete = connection
                    .prepareStatement("DELETE FROM AM_APPLICATION_KEY_MAPPING WHERE CONSUMER_KEY = ?");

            for (AppKeyMappingTableDTO appKeyMappingTableDTO : appKeyMappingTableDTOsFailed) {
                preparedStatementDelete.setString(1,
                        appKeyMappingTableDTO.getConsumerKey().getEncryptedConsumerKey());
                preparedStatementDelete.addBatch();
            }
            preparedStatementDelete.executeBatch();

            log.info("AM_APPLICATION_KEY_MAPPING table updated with " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries deleted as they cannot be decrypted");
        } else {
            log.error("AM_APPLICATION_KEY_MAPPING table not updated as " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries cannot be decrypted");
        }
    } finally {
        APIMgtDBUtil.closeAllConnections(null, null, resultSet);
        APIMgtDBUtil.closeAllConnections(preparedStatementUpdate, null, null);
        APIMgtDBUtil.closeAllConnections(preparedStatementDelete, null, null);

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error("Unable to close the statement", e);
            }
        }
    }
    return continueUpdatingDB;
}

From source file:com.alfaariss.oa.engine.tgt.jdbc.JDBCTGTFactory.java

/**
 * Uses a batch update to persist all supplied tgts.
 * @param tgts The TGTs to persist./*from w w  w  .  java 2s  .  c o  m*/
 * @throws PersistenceException If persistance fails.
 * 
 * @see IEntityManager#persist(IEntity[])
 * @see PreparedStatement#addBatch()
 */
public void persist(JDBCTGT[] tgts) throws PersistenceException {
    if (tgts == null)
        throw new IllegalArgumentException("Suplied tgt array is empty or invalid");

    List<TGTEventError> listTGTEventErrors = new Vector<TGTEventError>();

    Connection connection = null;
    PreparedStatement psInsert = null;
    PreparedStatement psDelete = null;
    PreparedStatement psDeleteAliasR = null;
    PreparedStatement psDeleteAliasF = null;
    PreparedStatement psUpdate = null;
    try {
        connection = _oDataSource.getConnection(); //Manage connection
        connection.setAutoCommit(false);

        if (_aliasStoreSP != null)
            psDeleteAliasR = connection.prepareStatement(_aliasStoreSP.getQueryAliasRemove());

        if (_aliasStoreIDP != null)
            psDeleteAliasF = connection.prepareStatement(_aliasStoreIDP.getQueryAliasRemove());

        psInsert = connection.prepareStatement(_sInsertQuery);
        psDelete = connection.prepareStatement(_sRemoveQuery);
        psUpdate = connection.prepareStatement(_sUpdateQuery);

        Vector<ITGT> vCreate = new Vector<ITGT>();
        Vector<ITGT> vUpdate = new Vector<ITGT>();
        Vector<ITGT> vRemove = new Vector<ITGT>();

        for (JDBCTGT tgt : tgts) //For all tgts
        {
            String id = tgt.getId();
            if (id == null) //New TGT
            {
                byte[] baId = new byte[ITGT.TGT_LENGTH];
                do {
                    _random.nextBytes(baId);
                    try {
                        id = ModifiedBase64.encode(baId);
                    } catch (UnsupportedEncodingException e) {
                        _logger.error("Could not create tgt id for byte[]: " + baId, e);
                        throw new PersistenceException(SystemErrors.ERROR_INTERNAL);
                    }
                } while (exists(id)); //Key allready exists   

                tgt.setId(id);
                //Update expiration time
                long expiration = System.currentTimeMillis() + _lExpiration;
                tgt.setTgtExpTime(expiration);
                psInsert.setString(1, id);
                psInsert.setTimestamp(2, new Timestamp(expiration));
                psInsert.setBytes(3, Serialize.encode(tgt.getUser()));
                psInsert.setBytes(4, Serialize.encode(tgt.getAuthenticationProfile()));
                psInsert.setBytes(5, Serialize.encode(tgt.getModifiableAuthNProfileIDs()));
                psInsert.setBytes(6, Serialize.encode(tgt.getModifiableRequestorIDs()));
                psInsert.setBytes(7, Serialize.encode(tgt.getAttributes()));
                psInsert.addBatch();

                vCreate.add(tgt);
            } else if (tgt.isExpired()) //Expired
            {
                _logger.debug("TGT Expired: " + id);

                if (psDeleteAliasR != null) {
                    psDeleteAliasR.setString(1, id);
                    psDeleteAliasR.addBatch();
                }

                if (psDeleteAliasF != null) {
                    psDeleteAliasF.setString(1, id);
                    psDeleteAliasF.addBatch();
                }

                vRemove.add(tgt);
            } else //Update
            {
                //Update expiration time
                long expiration = System.currentTimeMillis() + _lExpiration;
                tgt.setTgtExpTime(expiration);
                //Update tgt
                psUpdate.setTimestamp(1, new Timestamp(expiration));
                psUpdate.setBytes(2, Serialize.encode(tgt.getUser()));
                psUpdate.setBytes(3, Serialize.encode(tgt.getAuthenticationProfile()));
                psUpdate.setBytes(4, Serialize.encode(tgt.getModifiableAuthNProfileIDs()));
                psUpdate.setBytes(5, Serialize.encode(tgt.getModifiableRequestorIDs()));
                psUpdate.setBytes(6, Serialize.encode(tgt.getAttributes()));
                psUpdate.setString(7, id);
                psUpdate.addBatch();

                vUpdate.add(tgt);
            }
        }

        try {
            int iTotalAdded = 0;
            for (int i : psInsert.executeBatch()) {
                iTotalAdded += i;
            }
            _logger.debug(iTotalAdded + " new TGT(s) added by batch");

            for (ITGT tgt : vCreate) {
                try {
                    processEvent(TGTListenerEvent.ON_CREATE, tgt);
                } catch (TGTListenerException e) {
                    listTGTEventErrors.addAll(e.getErrors());
                }
            }
        } catch (SQLException e) {
            _logger.error("Could not execute insert batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_INSERT);
        }

        try {
            for (ITGT tgt : vRemove) {
                IUser tgtUser = tgt.getUser();
                _eventLogger.info(new UserEventLogItem(null, tgt.getId(), null, UserEvent.TGT_EXPIRED,
                        tgtUser.getID(), tgtUser.getOrganization(), null, null, this, null));

                try {
                    processEvent(TGTListenerEvent.ON_REMOVE, tgt);
                } catch (TGTListenerException e) {
                    listTGTEventErrors.addAll(e.getErrors());
                }
            }

            int iTotalDeleted = 0;
            for (int i : psDelete.executeBatch()) {
                iTotalDeleted += i;
            }
            _logger.debug(iTotalDeleted + " TGT(s) deleted by batch");
        } catch (SQLException e) {
            _logger.error("Could not execute delete batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
        }

        if (psDeleteAliasR != null) {
            try {
                int iTotalAliasDeleted = 0;
                for (int i : psDeleteAliasR.executeBatch()) {
                    iTotalAliasDeleted += i;
                }
                _logger.debug(iTotalAliasDeleted + " (requestor based) alias(es) deleted by batch");
            } catch (SQLException e) {
                _logger.error("Could not execute delete (requestor based) alias batch", e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
            }
        }

        if (psDeleteAliasF != null) {
            try {
                int iTotalAliasDeleted = 0;
                for (int i : psDeleteAliasF.executeBatch()) {
                    iTotalAliasDeleted += i;
                }
                _logger.debug(iTotalAliasDeleted + " (remote enitity based) alias(es) deleted by batch");
            } catch (SQLException e) {
                _logger.error("Could not execute delete (remote enitity based) alias batch", e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
            }
        }

        try {
            int iTotalUpdated = 0;
            for (int i : psUpdate.executeBatch()) {
                iTotalUpdated += i;
            }
            _logger.debug(iTotalUpdated + " TGT(s) updated by batch");

            for (ITGT tgt : vUpdate) {
                try {
                    processEvent(TGTListenerEvent.ON_UPDATE, tgt);
                } catch (TGTListenerException e) {
                    listTGTEventErrors.addAll(e.getErrors());
                }
            }
        } catch (SQLException e) {
            _logger.error("Could not execute update batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_UPDATE);
        }

        connection.commit();

        if (listTGTEventErrors != null) {//TGT Event processing failed, error has been logged already
            throw new TGTListenerException(listTGTEventErrors);
        }
    } catch (SQLException e) {
        _logger.error("Could not execute Batch", e);
        try {
            if (connection != null)
                connection.rollback();
        } catch (SQLException e1) {
            _logger.warn("Could not rollback batch", e);
        }

        throw new PersistenceException(SystemErrors.ERROR_INTERNAL);
    } catch (PersistenceException e) {
        try {
            if (connection != null)
                connection.rollback();
        } catch (SQLException e1) {
            _logger.warn("Could not rollback batch", e);
        }
        throw e;
    } catch (Exception e) {
        _logger.error("Could not connect to JDBC resource", e);
        throw new PersistenceException(SystemErrors.ERROR_RESOURCE_CONNECT);
    } finally {
        try {
            if (psInsert != null)
                psInsert.close();
        } catch (SQLException e) {
            _logger.debug("Could not close insert statement", e);
        }

        try {
            if (psDelete != null)
                psDelete.close();
        } catch (SQLException e) {
            _logger.debug("Could not close delete statement", e);
        }

        try {
            if (psDeleteAliasR != null)
                psDeleteAliasR.close();
        } catch (SQLException e) {
            _logger.debug("Could not close delete (requestor based) alias statement", e);
        }

        try {
            if (psDeleteAliasF != null)
                psDeleteAliasF.close();
        } catch (SQLException e) {
            _logger.debug("Could not close delete (remote entity based) alias statement", e);
        }

        try {
            if (psUpdate != null)
                psUpdate.close();
        } catch (SQLException e) {
            _logger.debug("Could not close update statement", e);
        }

        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            _logger.debug("Could not close connection", e);
        }
    }
}

From source file:org.opencb.cellbase.app.transform.GeneParser.java

@Deprecated
private void insertGenomeSequence(String sequenceName, boolean haplotypeSequenceType,
        PreparedStatement sqlInsert, StringBuilder sequenceStringBuilder) throws SQLException {
    int chunk = 0;
    int start = 1;
    int end = CHUNK_SIZE - 1;
    // if the sequence read is not HAP then we stored in sqlite
    if (!haplotypeSequenceType && !sequenceName.contains("PATCH")) {
        logger.info("Indexing genome sequence {} ...", sequenceName);
        sqlInsert.setString(5, sequenceName);
        //chromosome sequence length could shorter than CHUNK_SIZE
        if (sequenceStringBuilder.length() < CHUNK_SIZE) {
            sqlInsert.setString(1, sequenceName + "_" + chunk + "_" + chunkIdSuffix);
            sqlInsert.setInt(2, start);/*from  w  w  w  .  j a v  a  2 s.  c om*/
            sqlInsert.setInt(3, sequenceStringBuilder.length() - 1);
            sqlInsert.setString(4, sequenceStringBuilder.toString());

            // Sequence to store is larger than CHUNK_SIZE
        } else {
            int sequenceLength = sequenceStringBuilder.length();

            sqlConn.setAutoCommit(false);
            while (start < sequenceLength) {
                if (chunk % 10000 == 0 && chunk != 0) {
                    sqlInsert.executeBatch();
                    sqlConn.commit();
                }

                // chunkId is common for all the options
                sqlInsert.setString(1, sequenceName + "_" + chunk + "_" + chunkIdSuffix);
                if (start == 1) { // First chunk of the chromosome
                    // First chunk contains CHUNK_SIZE-1 nucleotides as index start at position 1 but must end at 1999
                    //                                        chunkSequence = sequenceStringBuilder.substring(start - 1, CHUNK_SIZE - 1);
                    //                                        genomeSequenceChunk = new GenomeSequenceChunk(chromosome, chromosome+"_"+chunk+"_"
                    // +chunkIdSuffix, start, end, sequenceType, sequenceAssembly, chunkSequence);
                    sqlInsert.setInt(2, start);
                    sqlInsert.setInt(3, end);
                    sqlInsert.setString(4, sequenceStringBuilder.substring(start - 1, CHUNK_SIZE - 1));

                    start += CHUNK_SIZE - 1;
                } else { // Regular chunk
                    if ((start + CHUNK_SIZE) < sequenceLength) {
                        sqlInsert.setInt(2, start);
                        sqlInsert.setInt(3, end);
                        sqlInsert.setString(4,
                                sequenceStringBuilder.substring(start - 1, start + CHUNK_SIZE - 1));
                        start += CHUNK_SIZE;
                    } else { // Last chunk of the chromosome
                        sqlInsert.setInt(2, start);
                        sqlInsert.setInt(3, sequenceLength);
                        sqlInsert.setString(4, sequenceStringBuilder.substring(start - 1, sequenceLength));
                        start = sequenceLength;
                    }
                }
                // we add the inserts in a batch
                sqlInsert.addBatch();

                end = start + CHUNK_SIZE - 1;
                chunk++;
            }

            sqlInsert.executeBatch();
            sqlConn.commit();

            sqlConn.setAutoCommit(true);
        }
    }
}

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

/**
 * {@inheritDoc}/*from  w  ww. j a  va  2s . c  o m*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void addItemData(long briefcaseId, List<BriefcaseItemData> itemDatas) throws FxApplicationException {
    if (itemDatas == null || itemDatas.size() == 0)
        return;
    Briefcase br = load(briefcaseId); // check read permissions
    Connection con = null;
    PreparedStatement stmt = null;
    long lastId = -1;
    int pos = -1;
    try {
        con = Database.getDbConnection();

        for (BriefcaseItemData itemData : itemDatas) {
            if (lastId != itemData.getId()) {
                lastId = itemData.getId();
                if (stmt != null) {
                    stmt.executeBatch();
                    stmt.close();
                }
                //existance check and evaluate position
                stmt = con.prepareStatement(
                        "SELECT COUNT(*) FROM " + TBL_BRIEFCASE_DATA + " WHERE briefcase_id=? AND id=?");
                stmt.setLong(1, briefcaseId);
                stmt.setLong(2, itemData.getId());
                ResultSet rs = stmt.executeQuery();
                if (rs == null || !rs.next() || rs.getLong(1) != 1)
                    throw new FxNotFoundException(LOG, "ex.briefcase.notFound.item", itemData.getId(),
                            br.getName());
                stmt.close();
                stmt = con.prepareStatement(
                        "SELECT MAX(pos) FROM " + TBL_BRIEFCASE_DATA_ITEM + " WHERE briefcase_id=? AND id=?");
                stmt.setLong(1, briefcaseId);
                stmt.setLong(2, itemData.getId());
                rs = stmt.executeQuery();
                pos = rs.next() ? rs.getInt(1) : 0;
                stmt.close();
                stmt = con.prepareStatement("INSERT INTO " + TBL_BRIEFCASE_DATA_ITEM
                        + "(briefcase_id, id, pos, intflag1, intflag2, intflag3, longflag1, longflag2, metadata) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
                stmt.setLong(1, briefcaseId);
                stmt.setLong(2, itemData.getId());
            }
            if (stmt == null) {
                LOG.fatal("PreparedStatement was null trying to add briefcase item data!");
                continue;
            }
            stmt.setLong(3, ++pos);
            if (itemData.isIntFlagSet(1))
                stmt.setInt(4, itemData.getIntFlag1());
            else
                stmt.setNull(4, Types.INTEGER);
            if (itemData.isIntFlagSet(2))
                stmt.setInt(5, itemData.getIntFlag2());
            else
                stmt.setNull(5, Types.INTEGER);
            if (itemData.isIntFlagSet(3))
                stmt.setInt(6, itemData.getIntFlag3());
            else
                stmt.setNull(6, Types.INTEGER);
            if (itemData.isLongFlagSet(1))
                stmt.setLong(7, itemData.getLongFlag1());
            else
                stmt.setNull(7, Types.BIGINT);
            if (itemData.isLongFlagSet(2))
                stmt.setLong(8, itemData.getLongFlag2());
            else
                stmt.setNull(8, Types.BIGINT);
            stmt.setString(9, itemData.getMetaData());
            stmt.addBatch();
        }
        if (stmt != null)
            stmt.executeBatch();
    } catch (Exception e) {
        EJBUtils.rollback(ctx);
        throw new FxUpdateException(LOG, e, "ex.briefcase.addItemData", br.getName(), lastId, e.getMessage());
    } finally {
        Database.closeObjects(BriefcaseEngineBean.class, con, stmt);
    }
}

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

private boolean updateAMApplicationKeyMapping(Connection connection) throws SQLException {
    log.info("Updating consumer keys in AM_APPLICATION_KEY_MAPPING");
    PreparedStatement preparedStatementUpdate = null;
    PreparedStatement preparedStatementDelete = null;
    Statement statement = null;/*from   w ww .  j a  v a 2s .co m*/
    ResultSet resultSet = null;
    boolean continueUpdatingDB = true;
    long totalRecords = 0;
    long decryptionFailedRecords = 0;

    try {
        String query = "SELECT APPLICATION_ID, CONSUMER_KEY, KEY_TYPE FROM AM_APPLICATION_KEY_MAPPING";
        ArrayList<AppKeyMappingTableDTO> appKeyMappingTableDTOs = new ArrayList<>();
        ArrayList<AppKeyMappingTableDTO> appKeyMappingTableDTOsFailed = new ArrayList<>();

        statement = connection.createStatement();
        statement.setFetchSize(50);
        resultSet = statement.executeQuery(query);

        while (resultSet.next()) {
            ConsumerKeyDTO consumerKeyDTO = new ConsumerKeyDTO();
            consumerKeyDTO.setEncryptedConsumerKey(resultSet.getString("CONSUMER_KEY"));

            AppKeyMappingTableDTO appKeyMappingTableDTO = new AppKeyMappingTableDTO();
            appKeyMappingTableDTO.setApplicationId(resultSet.getString("APPLICATION_ID"));
            appKeyMappingTableDTO.setConsumerKey(consumerKeyDTO);
            appKeyMappingTableDTO.setKeyType(resultSet.getString("KEY_TYPE"));
            totalRecords++;
            if (ResourceModifier.decryptConsumerKeyIfEncrypted(consumerKeyDTO)) {

                appKeyMappingTableDTOs.add(appKeyMappingTableDTO);
                log.debug("Successfully decrypted consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " as : " + consumerKeyDTO.getDecryptedConsumerKey()
                        + " in AM_APPLICATION_KEY_MAPPING table");
            } else {
                log.error("Cannot decrypt consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " in AM_APPLICATION_KEY_MAPPING table");
                decryptionFailedRecords++;
                appKeyMappingTableDTOsFailed.add(appKeyMappingTableDTO);

                //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) {
            preparedStatementUpdate = connection
                    .prepareStatement("UPDATE AM_APPLICATION_KEY_MAPPING SET CONSUMER_KEY = ?"
                            + " WHERE APPLICATION_ID = ? AND KEY_TYPE = ?");

            for (AppKeyMappingTableDTO appKeyMappingTableDTO : appKeyMappingTableDTOs) {
                preparedStatementUpdate.setString(1,
                        appKeyMappingTableDTO.getConsumerKey().getDecryptedConsumerKey());
                preparedStatementUpdate.setString(2, appKeyMappingTableDTO.getApplicationId());
                preparedStatementUpdate.setString(3, appKeyMappingTableDTO.getKeyType());
                preparedStatementUpdate.addBatch();
            }
            preparedStatementUpdate.executeBatch();

            //deleting rows where consumer key decryption was unsuccessful
            preparedStatementDelete = connection
                    .prepareStatement("DELETE FROM AM_APPLICATION_KEY_MAPPING WHERE CONSUMER_KEY = ?");

            for (AppKeyMappingTableDTO appKeyMappingTableDTO : appKeyMappingTableDTOsFailed) {
                preparedStatementDelete.setString(1,
                        appKeyMappingTableDTO.getConsumerKey().getEncryptedConsumerKey());
                preparedStatementDelete.addBatch();
            }
            preparedStatementDelete.executeBatch();
            log.info("AM_APPLICATION_KEY_MAPPING table updated with " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries deleted as they cannot be decrypted");
        } else {
            log.error("AM_APPLICATION_KEY_MAPPING table not updated as " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries cannot be decrypted");
        }
    } finally {
        if (preparedStatementUpdate != null)
            preparedStatementUpdate.close();
        if (preparedStatementDelete != null)
            preparedStatementDelete.close();
        if (statement != null)
            statement.close();
        if (resultSet != null)
            resultSet.close();
    }

    return continueUpdatingDB;
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///from w  w  w  .j  av  a  2  s  .  c  o  m
public void insertCategories(List<Category> categoryList, int virtualWikiId, int topicId, Connection conn)
        throws SQLException {
    if (topicId == -1) {
        throw new SQLException("Invalid topicId passed to method AnsiQueryHandler.insertCategories");
    }
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(STATEMENT_INSERT_CATEGORY);
        for (Category category : categoryList) {
            stmt.setInt(1, topicId);
            stmt.setString(2, category.getName());
            stmt.setString(3, category.getSortKey());
            stmt.addBatch();
        }
        stmt.executeBatch();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///from  w ww .j a  va 2 s  .  co m
public void insertTopicLinks(List<Topic> topicLinks, int topicId, Connection conn) throws SQLException {
    if (topicId == -1) {
        throw new SQLException("Invalid topicId passed to method AnsiQueryHandler.insertTopicLinks");
    }
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_LINKS);
        for (Topic topicLink : topicLinks) {
            stmt.setInt(1, topicId);
            stmt.setInt(2, topicLink.getNamespace().getId());
            stmt.setString(3, topicLink.getPageName());
            stmt.addBatch();
        }
        stmt.executeBatch();
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}