single PreparedStatement for batch processiong

Question

I want to insert record in one table and at the same time I want to update record in another table. for this I want to use transaction if any one of query get failed then all will get rollback. For this I m using batch processing...steps as follows

First I m setting AutoCommit(false) to connection obj.

Then dmlPreparedStatement = dbConnection.prepareStatement(Insertquery);

setting parameters to dmlPreparedStatement
dmlPreparedStatement.addBatch();

after this I want to add UPDATE query in batch for this I did-

dmlPreparedStatement = dbConnection.prepareStatement(Updatequery); same PreparedStatement object but with new update query
setting parameters to dmlPreparedStatement
dmlPreparedStatement.addBatch();

when dmlPreparedStatement.executeBatch(); is done it only execute update statement insert operation is not done.

Is their any way to use same PreparedStatement object for multiple diff. queries.

CODE IN TRY BLOCK

String query="INSERT INTO CANDIDATEANSWER " +
                "(CANDIDATEEXAMITEMID,FKCANDIDATEID,FKITEMID,OPTIONID,ATTEMPTEDTIME,ISCORRECT) " +
                "VALUES (?,?,?,?,?,?)";
        dbConnection = icrudCandidate.getConnection();
        dbConnection.setAutoCommit(false);
        dmlPreparedStatement = dbConnection.prepareStatement(query);
        for (CandidateAnswer candidateAnswer : candidateItemAssociation.getCandidateAnswers()) {
            if(candidateAnswer.getOptionID() != 0)
            {
                dmlPreparedStatement.setLong(1, candidateAnswer.getCandidateExamItemID());
                dmlPreparedStatement.setLong(2, candidateAnswer.getFkcandidateID());
                dmlPreparedStatement.setLong(3, candidateAnswer.getFkItemID());
                dmlPreparedStatement.setLong(4, candidateAnswer.getOptionID());
                java.util.Date dt =  new java.util.Date();
                dmlPreparedStatement.setObject(5, dt,java.sql.Types.TIMESTAMP,java.sql.Types.TIMESTAMP);
                String sql = "SELECT ISCORRECT FROM ITEMOPTION IO WHERE IO.OPTIONID="+candidateAnswer.getOptionID();
                preparedStatement = dbConnection.prepareStatement(query);
                resultSet = preparedStatement.executeQuery(sql);
                while(resultSet.next())
                {
                    if(resultSet.getBoolean("ISCORRECT"))
                    {
                        dmlPreparedStatement.setBoolean(6, true);
                    }
                    else
                    {
                        isCorrect=false;
                        dmlPreparedStatement.setBoolean(6, false);
                    }
                }
                resultSet.close();
            }
            dmlPreparedStatement.addBatch();
        }
         query="UPDATE CANDIDATEITEMASSOCIATION SET ISCORRECT=?,MARKSOBTAINED=? WHERE CANDIDATEEXAMITEMID="+candidateItemAssociation.getCandidateAnswers().get(0).getCandidateExamItemID();
         PreparedStatement dmlPreparedStatement1 = dbConnection.prepareStatement(query);
         dmlPreparedStatement1.setBoolean(1, isCorrect);
         if(isCorrect)
         {
             dmlPreparedStatement1.setString(2, String.valueOf(getPerItemMarksByPaperId(paperId)));              
         }
         else
         {               
             dmlPreparedStatement1.setString(2, String.valueOf((getNagetiveMarksPerItemByPaperIdAndEventID(eventID,paperId)*-1)));
         }
         dmlPreparedStatement1.addBatch();

        int[] count = dmlPreparedStatement.executeBatch();
        count = dmlPreparedStatement1.executeBatch();
        dbConnection.commit();
        return true;

Guillaume Guillaume · Accepted Answer · 2013-07-23T06:05:21

Batch processing and transactions are two different things: when using batch you don't get a rollback if something fails in between. The main advantage of batches is that it allows you to execute the same instruction with multiple parameters.

What you need to do is execute the first insert batch with dmlPreparedStatement.executeBatch(); before starting to do the update.

When the update batch has been executed you need to commit the transaction with connection.commit(); or rollback it if something failed.

More info in this example

single PreparedStatement for batch processiong

2 Answers