The code given below represents a saveTransaction() method which is used to save data to a database from the Java program. Given the classes in the image as well as an image of the screen which will call the function, modify the given code so that it loops through the items again, this time as it loops through you are to insert the data into the salesdetails table, note that the SalesNumber from the AUTO-INCREMENT field from above is to be inserted here with each record being placed into the salesdetails table. Finally, as you loop through the items the product table must be update because as products are sold the onhand field in the products table must be updated. When multiple tables are to be updated with related data, you should wrap it into a DMBS transaction. The schema for the database is also depicted. public class PosDAO { private Connection connection; public PosDAO(Connection connection) { this.connection = connection; } public void saveTransaction(ArrayList salesDetailsList) { try { // Get the total sales by iterating through the sales details list
The code given below represents a saveTransaction() method which is used to save data to a
When multiple tables are to be updated with related data, you should wrap it into a DMBS transaction.
The schema for the database is also depicted.
public class PosDAO {
private Connection connection;
public PosDAO(Connection connection) {
this.connection = connection;
}
public void saveTransaction(ArrayList<SalesDetails> salesDetailsList) {
try {
// Get the total sales by iterating through the sales details list
double totalSales = 0.0;
for (SalesDetails salesDetails : salesDetailsList) {
totalSales += salesDetails.getProduct().getPrice() * salesDetails.getQuantity();
}
// Insert the current date and total sales into the sales table
PreparedStatement salesStatement = connection.prepareStatement(
"INSERT INTO Sales (SalesDate, TotalSales) VALUES (?, ?)"
);
salesStatement.setDate(1, new Date(System.currentTimeMillis())); // use current date
salesStatement.setDouble(2, totalSales);
salesStatement.executeUpdate();
// Get the auto-generated sales number
Statement salesNumberStatement = connection.createStatement();
ResultSet salesNumberResult = salesNumberStatement.executeQuery(
"SELECT last_insert_rowid() AS SalesNumber"
);
int salesNumber = salesNumberResult.getInt("SalesNumber");
// Insert each sales detail with the generated sales number
PreparedStatement salesDetailsStatement = connection.prepareStatement(
"INSERT INTO SalesDetails (SalesNumber, ProductCode, Quantity) VALUES (?, ?, ?)"
);
for (SalesDetails salesDetails : salesDetailsList) {
salesDetailsStatement.setInt(1, salesNumber);
salesDetailsStatement.setString(2, salesDetails.getProduct().getCode());
salesDetailsStatement.setInt(3, salesDetails.getQuantity());
salesDetailsStatement.executeUpdate();
}
} catch (SQLException e) {
System.err.println("Error saving transaction: " + e.getMessage());
}
}
}
Step by step
Solved in 3 steps
