带有用户输入的JDBC事务 [英] JDBC Transaction with user input

问题描述

我知道打开交易，等待用户输入以及继续进行交易是一种非常糟糕的编程方式.但是，这是我正在做的一部分课程所必需的-我们不允许更改已提供给我们的表格.

I know that opening a transaction , waiting for user input and the carrying on with a transaction is a very bad way of programming. However it is required for a piece of coursework I am doing - we are not allowed to alter the table which has been given to us.

我正在使用具有runQuery和update方法的数据库访问类.为了使用事务，我创建了一种称为提交事务的新方法-

I am using a database access class which has a runQuery and update method. To use a transaction I have created a new method called commit transaction -

   public static void commitTransaction(String SQLStatement) throws SQLException,
            Exception {
        Connection dbConnect = DatabaseAccess.getConnection();

        PreparedStatement p = null;

        try {
            dbConnect.setAutoCommit(false);
            p = dbConnect.prepareStatement(SQLStatement);
        } catch (Exception e) {
            System.out.println("An error has occured");
        }

        p.executeUpdate();
        //dbConnect.setAutoCommit(true);
        dbConnect.close();
    }

由于dbConnect.setAutoCommit(false)，我创建了一个单独的函数，我认为需要此函数来允许用户在交易期间输入.我尝试创建的方法可以进行安全的预订-启动交易，等待一堆输入，然后提交交易.我没有收到来自Java的错误-但是没有任何内容添加到数据库中.

I have created a separate function due to the dbConnect.setAutoCommit(false), which I think is needed to allow user input during the transaction. The method I am trying to create makes a safe booking - starts the transaction, waits for a bunch of inputs and then commits the transaction. I am not getting an error from java - however nothing gets added to the database.

我认为我的交易可能未提交的原因之一是我在用户输入中运行查询.我需要输出这些查询的结果集-因此，我没有使用已禁用autoCommit的commitTransaction方法.这可能是怎么回事?如果可以，我该如何解决?

One reason I think my transaction might not be committing is I run queries within the user inputs. I need the result set from these query to be output - so I am not using the commitTransaction method which has the autoCommit disabled. Could this be whats wrong? If so how can I work around it?

        //Start the transaction
        DatabaseAccess.commitTransaction("START TRANSACTION;");


        bookingID = BookingErrorChecks.createBookingID();

       /////////////////////////////////////////////
       //User inputs for flight they wish to book
       /////////////////////////////////////////////

        System.out.println("Showing available seats on your flight:");

        SQLStatement = "SELECT Flight.flightID, flightDate,\n"
                + "(MaxCapacity - SUM(NumSeats))\n"
                + "AS AvailableSeats FROM Flight JOIN\n"
                + " FlightBooking ON Flight.flightID =\n"
                + " FlightBooking.flightID\n"
                + "WHERE (Status = 'R' OR Status = 'H') AND Flight.flightID="
                + flightID
                + "GROUP BY Flight.flightID";

        answer = DatabaseAccess.runQuery(SQLStatement);


        /////////////////////////////////////////////
        //Prints out table of results 
        /////////////////////////////////////////////

        if (Integer.parseInt(availableSeats) < 1) 
        {
            System.out.println("There are no available seats, choose another"
                    + "flight");
            DatabaseAccess.commitTransaction("ROLLBACK");
        } else {
            save = DatabaseAccess.setSavepoint();
        }

       /////////////////////////////////////////////
       //User input for the number of seats and their first and last name
       /////////////////////////////////////////////

        SQLStatement = "SELECT customerID FROM LeadCustomer WHERE FirstName = '"
                + firstName + "' AND Surname = '" + lastName + "'";

        answer = DatabaseAccess.runQuery(SQLStatement);


        if (answer.next()) {
            customerID = answer.getInt("CustomerID");
        } else {
           /////////////////////////////////////////////
           //Create customer
           /////////////////////////////////////////////
        }

       /////////////////////////////////////////////
       //Final user inputs about the flight
       /////////////////////////////////////////////

 SQLStatement = "INSERT INTO FlightBooking values("
                + bookingID + "," + customerID + "," + flightID + ","
                + numSeats + ",'" + status + "','" + time + "',"
                + totalCost+")";
        DatabaseAccess.commitTransaction(SQLStatement);
        DatabaseAccess.commitTransaction("COMMIT TRANSACTION;");

        System.out.println("Flight Booking was sucessfully added.");
    }

}

推荐答案

在dbConnect.setAutoCommit(false);之后，您需要调用dbConnect.commit()或更改自动提交模式，例如dbConnect.setAutoCommit(true);.

After dbConnect.setAutoCommit(false); you need to either call dbConnect.commit() or change the auto-commit mode like dbConnect.setAutoCommit(true);.

这篇关于带有用户输入的JDBC事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！