// Returns all existing coupons of a certain type
@Override
public Collection<Coupon> getCouponByType(CouponType couponType)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare ArrayList to return
ArrayList<Coupon> allCouponsFound = null;
// Prepare and execute statement
PreparedStatement statement = null;
ResultSet couponsFound = null;
// Prepare sql request
String sqlRequest;
try {
sqlRequest = "SELECT * FROM APP.COUPON WHERE COUPON_TYPE='" + couponType + "'";
statement = connection.prepareStatement(sqlRequest);
// Get all coupons in a ResultSet
couponsFound = statement.executeQuery();
// Prepare Collection
allCouponsFound = new ArrayList<Coupon>();
// Move all coupons from ResultSet to an ArrayList
while (couponsFound.next()) {
// Prepare temp coupon
Coupon tempCoupon = new Coupon();
tempCoupon.setId(couponsFound.getLong("ID"));
tempCoupon.setTitle(couponsFound.getString("TITLE"));
tempCoupon.setStartDate(couponsFound.getDate("START_DATE"));
tempCoupon.setEndDate(couponsFound.getDate("END_DATE"));
tempCoupon.setAmount(couponsFound.getInt("AMOUNT"));
tempCoupon.setType(CouponType.valueOf(couponsFound.getString("COUPON_TYPE")));
tempCoupon.setMessage(couponsFound.getString("MESSAGE"));
tempCoupon.setPrice(couponsFound.getDouble("PRICE"));
// Add coupon to the Collection
allCouponsFound.add(tempCoupon);
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
couponsFound.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
// returns NULL, when no coupons found
return allCouponsFound;
}
// Returns collection of all existing coupons
@Override
public Collection<Coupon> getAllCoupons()
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish db connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare and execute SELECT
Statement statement;
ArrayList<Coupon> coupons;
ResultSet rs;
try {
statement = connection.createStatement();
coupons = new ArrayList<Coupon>();
String sql = "SELECT * FROM APP.COUPON ";
rs = statement.executeQuery(sql);
while (rs.next()) {
Coupon coupon = new Coupon();
coupon.setAmount(rs.getInt("AMOUNT"));
coupon.setType(CouponType.valueOf(rs.getString("COUPON_TYPE")));
coupon.setEndDate(rs.getDate("END_DATE"));
coupon.setId(rs.getLong("ID"));
coupon.setImage(rs.getString("IMAGE"));
coupon.setMessage(rs.getString("MESSAGE"));
coupon.setPrice(rs.getDouble("PRICE"));
coupon.setTitle(rs.getString("TITLE"));
coupon.setStartDate(rs.getDate("START_DATE"));
coupons.add(coupon);
// System.out.println(coupon.toString());
coupons.add(coupon);
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
rs.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
return coupons;
}
// Returns coupon by Title
@Override
public Coupon getCoupon(String title)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare and execute coupon
Statement statement;
ResultSet rs;
String sql;
Coupon coupon = null;
;
try {
statement = connection.createStatement();
// Prepare SQL message to get the Coupon by the id
sql = "SELECT * FROM APP.COUPON WHERE TITLE='" + title + "'";
// getting the values into a result set
rs = statement.executeQuery(sql);
coupon = new Coupon();
if (rs.next()) {
coupon.setAmount(rs.getInt("AMOUNT"));
coupon.setId(rs.getLong("ID"));
coupon.setImage(rs.getString("IMAGE"));
coupon.setMessage(rs.getString("MESSAGE"));
coupon.setPrice(rs.getDouble("PRICE"));
coupon.setTitle(rs.getString("TITLE"));
coupon.setEndDate(rs.getDate("END_DATE"));
coupon.setStartDate(rs.getDate("START_DATE"));
coupon.setType(CouponType.valueOf(rs.getString("COUPON_TYPE")));
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
rs.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
return coupon;
}