一个批处理中的两个不同的预准备语句

java sql prepared-statement jdbc

2022-09-02 10:22:47

我想在一个批处理中发送两个不同的预准备语句。

目前，我正在两部分进行此操作，正如您在注释行中看到的那样，它有效，但这不是这里的主要目标。谁能告诉我用什么来代替这些评论来让这个东西工作？

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

答案 1

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

另一种选择是使用一个存储过程，该过程可以在到服务器的单个往返中完成所有操作，同时保持单个事务的好处

答案 2

你可以尝试执行两个语句是单个事务，如下所示：

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

问题是 addBatch 适用于单个预准备语句，请参阅这是如何将多个 sql 语句与 addBatch 一起使用。