public static void main(String[] args) {
MySQLHandler handler = new MySQLHandler();
// Create data structures to be added to DB
User user = new User("Wil", "Burns", "*****@*****.**", "password");
City city = new City("Charlottesville", "VA", "22903");
Store store = new Store("Harris Teeter", "975 Emmet Street North");
Location location = new Location("1", "1");
Item item1 = new Item("Milk", "1");
Item item2 = new Item("Eggs", "1");
Item item3 = new Item("Bacon", "1");
Line line1 = new Line("1", "1", "3.39", "2");
Line line2 = new Line("1", "2", "1.89", "1");
Line line3 = new Line("1", "3", "2.99", "3");
// Perform inserts
handler.addUser(user);
handler.addCity(city);
handler.addStore(store);
handler.addLocation(location);
handler.addItem(item1);
handler.addItem(item2);
handler.addItem(item3);
handler.addLine(line1);
handler.addLine(line2);
handler.addLine(line3);
handler.addReceipt(new Receipt("1", "1", "2009-12-07"));
user = handler.getUser("*****@*****.**");
System.out.println("User Id: " + user.getUserId());
// Print user's receipt list
ArrayList<Receipt> receipts = handler.getReceipts(user);
for (Receipt toPrint : receipts) {
System.out.println("Receipt id:" + toPrint.getReceiptId());
ArrayList<Line> lines = handler.getLines(toPrint);
for (Line line : lines) {
Item item = handler.getItem(line);
System.out.println(
"Item: "
+ item.getName()
+ " Price: "
+ line.getPrice()
+ " Quantity: "
+ line.getQuantity());
}
}
}
public ArrayList<Line> getLines(Receipt receipt) {
ArrayList<Line> toReturn = new ArrayList<Line>();
connect();
try {
// Create a stmt object
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
// Query the database, storing the result
// in an object of type ResultSet
rs =
stmt.executeQuery(
"SELECT * from Line WHERE " + "Line.ReceiptId='" + receipt.getReceiptId() + "'");
// Check if User is already in the database
while (rs.next()) {
String lineId = rs.getString("LineId");
String receiptId = rs.getString("ReceiptId");
String itemId = rs.getString("ItemId");
String price = rs.getString("Price");
String quantity = rs.getString("Quantity");
Line toAdd = new Line(lineId, receiptId, itemId, price, quantity);
toReturn.add(toAdd);
} // end while loop
} catch (Exception e) {
e.printStackTrace();
} // end catch
finally {
disconnect();
}
return toReturn;
}
public void addReceipt(Receipt toAdd) {
connect();
try {
// Create a stmt object
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
// Query the database, storing the result
// in an object of type ResultSet
rs =
stmt.executeQuery(
"SELECT * from Receipt WHERE "
+ "UserId='"
+ toAdd.getUserId()
+ "' AND "
+ "ReceiptNumber='"
+ toAdd.getReceiptNumber()
+ "' AND "
+ "Date='"
+ toAdd.getDate()
+ "'");
// Check if User is already in the database
while (rs.next()) {
System.out.println("Receipt already exists in DB.");
disconnect();
return;
} // end while loop
stmt.executeUpdate(
"INSERT INTO Receipt(UserId, ReceiptNumber, Date) VALUES ('"
+ toAdd.getUserId()
+ "', '"
+ toAdd.getReceiptNumber()
+ "', '"
+ toAdd.getDate()
+ "')");
} catch (Exception e) {
e.printStackTrace();
} // end catch
finally {
disconnect();
}
}