一个批次中有两个不同的准备好的语句 [英] Two different prepared statements in one single batch

问题描述

我想在一个批次中发送两个不同的准备好的语句.

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;
    }
}

推荐答案

不能在一个批处理中执行两个不同的语句.正如@dan 提到的，你可以——而且必须——在一个事务中完成它们.

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

这篇关于一个批次中有两个不同的准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！