Two different prepared statements in one single batch

Question

I want to send two different prepared statements in one single batch.

Currently I am doing this in two as you can see in the commented lines and it works, but that is not the main objective here. Can anyone tell me what to put in place of those comments to get this thing to work?

import java.lang.ClassNotFoundException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DriverManager;

public class Main
{
    public static void main(String[] args)
    {
        Connection connection = null;
        PreparedStatement preparedStatementWithdraw = null;
        PreparedStatement preparedStatementDeposit = null;

        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/youtube", "root", "root");

            preparedStatementWithdraw = withdrawFromChecking(connection, preparedStatementWithdraw, new BigDecimal(100), 1);
            preparedStatementDeposit = depositIntoSaving(connection, preparedStatementDeposit, new BigDecimal(300), 1);

            //preparedStatementDeposit.executeBatch();
            //preparedStatementWithdraw.executeBatch();
            System.out.println("Account Modified!");
        }
        catch(ClassNotFoundException error)
        {
            System.out.println("Error: " + error.getMessage());
        }
        catch(SQLException error)
        {
            System.out.println("Error: " + error.getMessage());
        }
        finally
        {
            if(connection != null) try{connection.close();} catch(SQLException error) {}
            if(preparedStatementDeposit != null) try{preparedStatementDeposit.close();} catch(SQLException error) {}
        }
    }

    public static PreparedStatement withdrawFromChecking(Connection connection, PreparedStatement preparedStatement, BigDecimal balance, int id) throws SQLException
    {
        preparedStatement = connection.prepareStatement("UPDATE bankAccount SET checkingBalance = checkingBalance - ? WHERE id = ?");
        preparedStatement.setBigDecimal(1, balance);
        preparedStatement.setInt(2, id);
        preparedStatement.addBatch();

        return preparedStatement;
    }

    public static PreparedStatement depositIntoSaving(Connection connection, PreparedStatement preparedStatement, BigDecimal balance, int id) throws SQLException
    {
        preparedStatement = connection.prepareStatement("UPDATE bankAccount SET savingBalance = savingBalance + ? WHERE id = ?");
        preparedStatement.setBigDecimal(1, balance);
        preparedStatement.setInt(2, id);
        preparedStatement.addBatch();

        return preparedStatement;
    }
}

See also [Execute two different queries in one transaction](https://stackoverflow.com/questions/4860634/execute-two-different-queries-in-one-transactio) — Vadzim, Feb 28 '18 at 08:50

score 12 · Accepted Answer · answered Oct 19 '12 at 15:39

You cannot execute two different statements in a single batch. As @dan mentioned you can -- and must -- do them in a single transaction.

Another option is to use a stored procedure that can do it all in a single roundtrip to the server while maintaining the benefits of single transaction

score 11 · Answer 2 · edited Jul 04 '17 at 12:45

11

You can try execute the two statement is a single transaction, like this:

connection.setAutoCommit(false);
try {
    stmt1.execute();
    stmt2.execute();
    connection.commit();
} catch (Exception ex) {
    connection.rollback();
}

The issue is that addBatch works on a single prepared statement, see this is how you can use multiple sql statements with addBatch.

edited Jul 04 '17 at 12:45

cнŝdk

31,391
7
56
78

answered Oct 19 '12 at 15:34

dan

13,132
3
38
49

Thought about it. That will be the alternative if someone tells me here it's not possible. – gmustudent Oct 19 '12 at 15:34
1

See my updated answer, the issue is that addBatch is associated with a single prepared statement. – dan Oct 19 '12 at 15:44

Sujay · Answer 3 · 2012-10-19T15:50:46.387

I am trying to utilize prepared statements and a batch! I say statements because I would like to send two prepared statements in one batch.

When you're talking of PreparedStatement, a batch is associated with this PreparedStatement object's batch of commands and NOT the other way round. You should look at the javadoc for addBatch() method to know more about this.

So in your case, here's what I would have done:

Created a new transaction and set a batch limit
Created a set of batch for each PreparedStatement and increment a batch counter
Executed the batch when I hit the limit and reset the counter
Committed my transaction once I'm done

So your code would look something like this:

preparedStatementWithdraw = connection.prepareStatement(....);
preparedStatementDeposit  = connection.prepareStatement(....);
boolean autoCommit        = connection.getAutoCommit();

int batchLimit = 1000; //limit that you can vary
int batchCounter = 0;
try{
    connection.setAutoCommit(false);

    //set the params and start adding your batch statements, as per your requirement, something like
    preparedStatementWithdraw.addBatch();
    preparedStatementDeposit.addBatch();
    batchCounter++;

    if(batchCounter == batchLimit){
        try{
            preparedStatementWithdraw.executeBatch();
            preparedStatementDeposit.executeBatch();
        }catch(Exception exe){
            //log your error
        }finally{
            preparedStatementWithdraw.clearBatch();
            preparedStatementDeposit.clearBatch();
            batchCounter = 0;
        }
    }
}finally{
        //process if any more statements are remaining in the batch
        try{
            preparedStatementWithdraw.executeBatch();
            preparedStatementDeposit.executeBatch();
        }catch(Exception exe){
            //log your error
        }finally{
            preparedStatementWithdraw.clearBatch();
            preparedStatementDeposit.clearBatch();
        }

    //1. depending on your requirement, commit/rollback the transation
    //2. Set autocommit to its original value
    connection.setAutoCommit(autoCommit);
    //3. Resoure management statements
}

score 0 · Answer 4 · answered Oct 19 '12 at 15:52

I think you may want to merge your statement queries as one and do something like this:

 String updateAccount= "UPDATE bankAccount 
                      SET if(? is not null ) 
                        then checkingBalance = checkingBalance - ? end if, 
                        if(? is not null ) 
                         then savingBalance = savingBalance + ? end if
                      WHERE id = ?";                
 PreparedStatement = dbConnection.prepareStatement(updateAccount);

 preparedStatement.setDouble(1, new Double(100));
 preparedStatement.setDouble(2, new Double(100));
 preparedStatement.setDouble(3, null);
 preparedStatement.setDouble(4, null);
 preparedStatement.setInt(5, 1);
 preparedStatement.addBatch();

 preparedStatement.setDouble(1, null);
 preparedStatement.setDouble(2, null);
 preparedStatement.setDouble(3, new Double(100));
 preparedStatement.setDouble(4, new Double(100));
 preparedStatement.setInt(5, 1);
 preparedStatement.addBatch();
 preparedStatement.executeBatch();

 dbConnection.commit();

Two different prepared statements in one single batch

4 Answers4

Linked