// Update existing coupon
@Override
public void updateCoupon(Coupon coupon)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
PreparedStatement preparedStatement;
// Prepare and execute the update
try {
// Prepare SQL message to remove the Coupon
String updateSQL =
"UPDATE APP.COUPON SET "
+ "AMOUNT=?,MESSAGE=?,PRICE=?,TITLE=?,END_DATE=?,START_DATE=?,IMAGE=?,COUPON_TYPE=? "
+ "WHERE ID=?";
// Prepare statement
preparedStatement = connection.prepareStatement(updateSQL);
preparedStatement.setInt(1, coupon.getAmount());
preparedStatement.setString(2, coupon.getMessage());
preparedStatement.setDouble(3, coupon.getPrice());
preparedStatement.setString(4, coupon.getTitle());
preparedStatement.setDate(5, (java.sql.Date) coupon.getEndDate());
preparedStatement.setDate(6, (java.sql.Date) coupon.getStartDate());
preparedStatement.setString(7, coupon.getImage());
preparedStatement.setString(8, coupon.getType().name());
preparedStatement.setLong(9, coupon.getId());
// update the Coupon
preparedStatement.execute();
// Log
System.out.println(coupon.toString() + " was updated");
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close Connections
try {
preparedStatement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
}
// Removes relevant rows from CUSTOMER_COUPON, COMPANY_COUPON as well as coupon itself
@Override
public void removeCoupon(Coupon coupon)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Get coupon ID from DB
Statement statement;
ResultSet idFound;
try {
statement = connection.createStatement();
String sqlRequest = "SELECT ID FROM APP.COUPON WHERE TITLE='" + coupon.getTitle() + "'";
idFound = statement.executeQuery(sqlRequest);
idFound.next();
// coupon.setId(idFound.getLong("ID"));
// Prepare message to remove from purchase history
String removeSQL = "DELETE FROM APP.CUSTOMER_COUPON WHERE COUPON_ID =" + coupon.getId();
// Remove coupon from purchase history
statement.execute(removeSQL);
// Prepare message to remove from company's coupons
removeSQL = "DELETE FROM APP.COMPANY_COUPON WHERE COUPON_ID=" + coupon.getId();
// Remove coupon from company
statement.execute(removeSQL);
// Prepare SQL message to remove the Coupon
removeSQL = "DELETE FROM APP.COUPON WHERE ID=" + coupon.getId();
// Remove the Coupon himself
statement.execute(removeSQL);
System.out.println(coupon.toString() + " was deleted");
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
idFound.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
}
// Adds coupon to a coupons list
@Override
public void createCoupon(Coupon coupon)
throws FailedToCreateCouponException, WaitingForConnectionInterrupted {
// Get connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e1) {
throw new WaitingForConnectionInterrupted();
}
// Prepare SQL message to insert new company
String insertSQL =
"INSERT INTO APP.COUPON (IMAGE,PRICE,MESSAGE,COUPON_TYPE,AMOUNT,END_DATE,START_DATE,TITLE) VALUES"
+ "(?,?,?,?,?,?,?,?)";
PreparedStatement preparedStatement;
try {
preparedStatement = connection.prepareStatement(insertSQL);
preparedStatement.setInt(5, coupon.getAmount());
preparedStatement.setString(3, coupon.getMessage());
preparedStatement.setDouble(2, coupon.getPrice());
preparedStatement.setString(8, coupon.getTitle());
preparedStatement.setDate(6, (java.sql.Date) coupon.getEndDate());
preparedStatement.setDate(7, (java.sql.Date) coupon.getStartDate());
preparedStatement.setString(1, coupon.getImage());
preparedStatement.setString(4, coupon.getType().name());
// Execute prepared Statement
preparedStatement.executeUpdate();
// Close statement connection
preparedStatement.close();
} catch (SQLException e) {
throw new FailedToCreateCouponException();
} finally {
// close connection
pool.returnConnection(connection);
}
System.out.println(coupon.toString() + " was added to the table");
}