Skip to content

Instantly share code, notes, and snippets.

@cmh114933
Created July 5, 2019 00:41
Show Gist options
  • Save cmh114933/73bc328035461063be240a1adbf27e60 to your computer and use it in GitHub Desktop.
Save cmh114933/73bc328035461063be240a1adbf27e60 to your computer and use it in GitHub Desktop.
### Updateing Item after Update SQL statement
```
public void updateBooking(int id, Booking booking) {
String sql = "UPDATE booking SET totalPrice=? WHERE id = ?";
int totalPrice = booking.getTotalPrice();
this.jdbcTemplate.update(sql, totalPrice, id);
String selectCheckInDate = "SELECT checkInDate FROM booking WHERE id = ?";
String checkInDate = this.jdbcTemplate.queryForObject(selectCheckInDate, new Object[] { id }, String.class);
String selectCheckOutDate = "SELECT checkOutDate FROM booking WHERE id = ?";
String checkOutDate = this.jdbcTemplate.queryForObject(selectCheckOutDate, new Object[] { id }, String.class);
String selectRemarks = "SELECT remarks FROM booking WHERE id = ?";
String remarks = this.jdbcTemplate.queryForObject(selectRemarks, new Object[] { id }, String.class);
String selectNumGuest = "SELECT numGuest FROM booking WHERE id = ?";
int numGuest = this.jdbcTemplate.queryForObject(selectNumGuest, new Object[] { id }, Integer.class);
String selectUserId = "SELECT user_id FROM booking WHERE id = ?";
int userId = this.jdbcTemplate.queryForObject(selectUserId, new Object[] { id }, Integer.class);
String selectPropertyId = "SELECT property_id FROM booking WHERE id = ?";
int propertyId = this.jdbcTemplate.queryForObject(selectPropertyId, new Object[] { id }, Integer.class);
booking.setId(id);
booking.setCheckInDate(checkInDate);
booking.setCheckOutDate(checkOutDate);
booking.setRemarks(remarks);
booking.setNumGuest(numGuest);
booking.setUserId(userId);
booking.setPropertyId(propertyId);
}
```
### Obtaining Created Item id
```
public void createReview(Review review, int id) {
String sql = "INSERT INTO review VALUES(?, ?, ?) ";
int rating = review.getRating();
String remark = review.getRemark();
this.jdbcTemplate.update(sql, rating, remark, id);
sql = "SELECT TOP 1 * FROM review ORDER BY id DESC ";
RowMapper<Review> rowMapper = new ReviewRowMapper();
List<Review> reviews = this.jdbcTemplate.query(sql, rowMapper);
review.setId(reviews.get(0).getId());
review.setPropertyId(id);
}
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment