public Location getLocation(Store store, City city) {
Location toReturn = null;
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 LocationId from Location, Store, City WHERE "
+ "Store.StoreId='"
+ store.getStoreId()
+ "' AND City.CityId='"
+ city.getCityId()
+ "'");
// Check if User is already in the database
while (rs.next()) {
String locationId = rs.getString("LocationId");
toReturn = new Location(locationId, store.getStoreId(), city.getCityId());
} // end while loop
} catch (Exception e) {
e.printStackTrace();
} // end catch
finally {
disconnect();
}
return toReturn;
}
public void addCity(City 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 City WHERE "
+ "City='"
+ toAdd.getCity()
+ "' AND "
+ "State='"
+ toAdd.getState()
+ "' AND "
+ "Zip='"
+ toAdd.getZip()
+ "'");
// Check if User is already in the database
while (rs.next()) {
System.out.println("City already exists in DB.");
disconnect();
return;
} // end while loop
stmt.executeUpdate(
"INSERT INTO City(City, State, Zip) VALUES ('"
+ toAdd.getCity()
+ "', '"
+ toAdd.getState()
+ "', '"
+ toAdd.getZip()
+ "')");
} catch (Exception e) {
e.printStackTrace();
} // end catch
finally {
disconnect();
}
}