带有用户输入的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屋!