一批中有两种不同的预处理语句 [英] 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屋！