// Creating coupon in DB @Override public void createCoupon(Coupon c) throws CoupSQLException { String type = null; String title = null; // Check if title is empty. if (c.getTitle() == null || c.getTitle().trim() == "") { throw new CoupSQLException("Please Enter Coupon Title."); } else { title = c.getTitle(); } // Setting CouponType. Default setting of CouponType is 'OTHER' if (c.getType() != null) { type = c.getType().name(); } else { type = CouponType.OTHER.name(); } String message = c.getMessage(); String image = c.getImage(); int amount = c.getAmount(); java.sql.Date startDate = c.getStartDate(); java.sql.Date endDate = c.getEndDate(); double price = c.getPrice(); String sql = "INSERT INTO "; Connection conn = ConnectionPool.getInstance().getConnection(); PreparedStatement prpst; try { prpst = conn.prepareStatement( sql + "CouponsL (Title , Message , Image, Amount, startDate, endDate, Price, Type) VALUES(?,?,?,?,?,?,?,?) "); prpst.setString(1, title); prpst.setString(2, message); prpst.setString(3, image); prpst.setInt(4, amount); prpst.setDate(5, startDate); prpst.setDate(6, endDate); prpst.setDouble(7, price); prpst.setString(8, type); prpst.executeUpdate(); PreparedStatement prpst2 = conn.prepareStatement("SELECT id FROM CouponsL WHERE Title='" + title + "'"); ResultSet rs = prpst2.executeQuery(); while (rs.next()) { long idnum = rs.getLong("id"); c.setId(idnum); } } catch (SQLException e) { throw new CoupSQLException( "Error: Coupon was not created. Remember, the coupon title has to be unique. " + e); } }
// Update coupon in database @Override public void updateCoupon(Coupon c) throws CoupSQLException { long id = c.getId(); System.out.println(); String title = c.getTitle(); String message = c.getMessage(); String image = c.getImage(); int amount = c.getAmount(); java.sql.Date startDate = c.getStartDate(); java.sql.Date endDate = c.getEndDate(); double price = c.getPrice(); System.out.println(title + " " + id); String type = null; if (c.getType() != null) { type = c.getType().name(); } String sql = "UPDATE CouponsL SET Title = ? , Message = ? , Image = ? ," + " Amount = ? , Price = ?, startDate = ? , endDate = ?, Type = ?"; Connection conn = ConnectionPool.getInstance().getConnection(); try { PreparedStatement prpst = conn.prepareStatement(sql + " WHERE id = " + id); if (id == 0) { throw new CoupSQLException("Couldn't find the coupon.Please try again"); } prpst.setString(1, title); prpst.setString(2, message); prpst.setString(3, image); prpst.setInt(4, amount); prpst.setDouble(5, price); prpst.setDate(6, startDate); prpst.setDate(7, endDate); prpst.setString(8, type); prpst.executeUpdate(); } catch (SQLException e) { throw new CoupSQLException( "Could not update coupon.Customer can purchase only 1 of the coupon. ", e); } }