public void addItem(Item 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 Item WHERE "
+ "Name='"
+ toAdd.getName()
+ "' AND "
+ "LocationId='"
+ toAdd.getLocationId()
+ "'");
// Check if User is already in the database
while (rs.next()) {
System.out.println("Item already exists in DB.");
disconnect();
return;
} // end while loop
stmt.executeUpdate(
"INSERT INTO Item(Name, LocationId) VALUES ('"
+ toAdd.getName()
+ "', '"
+ toAdd.getLocationId()
+ "')");
} catch (Exception e) {
e.printStackTrace();
} // end catch
finally {
disconnect();
}
}
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());
}
}
}