// 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; }