Example #1
0
 // 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);
 }
Example #2
0
 // 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);
 }
Example #3
0
  // 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");
  }