新建PayMoneySvl
付款後清空購物車並更新帳戶餘額
為避免重複扣款，重定向到付款成功頁面。

@WebServlet("/user/PayMoneySvl")
public class PayMoneySvl extends HttpServlet {
  public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    //取得使用者
    TUser user = (TUser)request.getSession().getAttribute("user");
    //取得購買總額
    String value = request.getParameter("allMoney");
    double allMoney = Double.parseDouble(value);
    //若餘額足夠
    if(user.getAccount() >= allMoney){
      //執行扣款及清理購物車
      UserBiz biz = new UserBiz();
      Map<String,Integer> car = (Map<String,Integer>)request.getSession().getAttribute("ShopCar");
      try {
        //處理訂單，傳入使用者名稱、結帳總額、購物清單
        biz.buyBooks(user.getUname(),allMoney,car);
        //付款成功後清除購物車，並更新帳戶餘額
        car.clear();
        user.setAccount(user.getAccount() - allMoney);
        //重定向至付款成功頁面
        String path = request.getContextPath();
          String basePath = request.getScheme() + "://" + request.getServerName()  
          + ":" + request.getServerPort() + path + "/";
          response.sendRedirect(basePath + "PayOkSvl?allMoney=" + allMoney);
      }catch(Exception e) {
        request.setAttribute("msg", "網路異常，請和管理員聯繫");
        request.getRequestDispatcher("/error.jsp").forward(request, response);
      }
    }else {
      request.setAttribute("msg", "餘額不足");
      request.getRequestDispatcher("/error.jsp").forward(request, response);
    }
  }

在UserBiz新增buyBooks()方法

  public void buyBooks(String uname, double allMoney, Map<String, Integer> shopCar) {
    IUserDao dao = new UserDaoMysql();
    try {
      //扣款時若其中一個動作失敗，就全部回復。
      dao.beginTransaction();
      //因為是扣款，所以金額用負數
      dao.updateUserAccount(uname,-allMoney);
      dao.addBuyRecord(uname,allMoney,shopCar);
      dao.commit();
    }catch(Exception e) {
      dao.rollback();
      Log.logger.error(e.getMessage(),e);
      throw e;
    }finally {
      dao.closeConnection();
    }
  }

在UserDaoMysql新增updateUserAccount()方法，實現扣款。

  public void updateUserAccount(String uname,double money) throws Exception{
    String sql = "update tuser set account = account + ? where uname = ?";
    this.openConnection();
    PreparedStatement ps = this.connection.prepareStatement(sql);
    ps.setDouble(1, money);
    ps.setString(2, uname);
    ps.executeUpdate();
    ps.close();
  }

在UserDaoMysql新增addBuyRecord()方法，生成訂單。

  public void addBuyRecord(String uname,double allmoney,Map<String,Integer>ShopCar) throws Exception{
    String orderNo = OrderUtil.createNewOrderNo();
    String sql = "insert into tbuyrecord values(?,?,?,?)";
    this.openConnection();
    PreparedStatement ps = this.connection.prepareStatement(sql);
    ps.setString(1, orderNo);
    ps.setString(2, uname);
    ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
    ps.setDouble(4, allmoney);
    ps.executeUpdate();
    ps.close();
  }

在UserDaoMysql新增addBuyDetail()方法，添加訂單明細。

  public void addBuyRecord(String uname,double allmoney,Map<String,Integer>shopCar) throws Exception{
    //新增訂單
    String orderNo = OrderUtil.createNewOrderNo();
    String sql = "insert into tbuyrecord values(?,?,?,?)";
    this.openConnection();
    PreparedStatement ps = this.connection.prepareStatement(sql);
    ps.setString(1, orderNo);
    ps.setString(2, uname);
    ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
    ps.setDouble(4, allmoney);
    ps.executeUpdate();
    //更新DB內商品資料
    Set <String> isbns = shopCar.keySet();
    for(String isbn:isbns) {
      //把商品條碼、購買數量、訂單編號等資訊整理進TBuyDetail物件中。
      TBuyDetail detail = new TBuyDetail();
      detail.setIsbn(isbn);
      detail.setBuycount(shopCar.get(isbn));
      detail.setBuyid(orderNo);
      //使用TBuyDetail物件對DB資料進行更新。
      addBuyDetail(detail);
    }
    ps.close();
  }
  
  private void addBuyDetail(TBuyDetail detail) throws Exception{  
    //取得tbuydetail表取得autoId，再加入其他參數(商品號、訂單號、數量)塞回去。
    String sql = "insert into tbuydetail values((select * from (select IFNULL(max(autoid),0) + 1 from tbuydetail) t1),?,?,?)";
    this.openConnection();
    PreparedStatement ps = this.connection.prepareStatement(sql);
    ps.setString(1, detail.getIsbn());
    ps.setString(2, detail.getBuyid());
    ps.setInt(3, detail.getBuycount());
    ps.executeUpdate();
    ps.close();
    
    //用BookDao.updateBookCount()扣除庫存
    //為了能夠完整進行transaction，把同一個connection丟給BookDao繼續使用
    BookDaoMysql bookDao = new BookDaoMysql();
    bookDao.setConnection(this.getConnection());
    bookDao.updateBookCount(detail.getIsbn(),-detail.getBuycount());
  }
  
  public void updateBookCount(String isbn, int bookCount) throws Exception{
    String sql = "update tbook set bkcount = bkcount + ? where isbn = ?";
    this.openConnection(); 
    PreparedStatement ps = this.connection.prepareStatement(sql);
    ps.setInt(1, bookCount);
    ps.setString(1, isbn);
    ps.executeUpdate();
    ps.close();
  }

流程整理

從頁面取得: 使用者名稱、結帳金額、購物車，傳給後台: PayMoneySvl
呼叫biz.buyBooks()執行transaction任務
1.執行扣款
2.使用addBuyRecord()新增訂單，並更新庫存
2-1使用userDao將資料分別填入訂單表及訂單細目表
訂單表(訂單號、客戶名、時間、總額)
訂單細目表(autoId、商品號、訂單號、數量)
2-2
使用bookDao.updateBookCount()更新庫存。與userDao使用同一個connection。
3.commit()

新建PayOkSvl，處理付款成功後的重定向

@WebServlet("/PayOkSvl")
public class PayOkSvl extends HttpServlet {
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    request.getRequestDispatcher("/WEB-INF/main/PayOk.jsp").forward(request, response);
  }
}

新建PayOk.jsp，顯示付款成功訊息。

  <table align="center" width=60%>
    <tr>
      <td height="180"></td>
      <td style="color:black;font-size:18px">
        付款成功!付款人:${user.uname }<br>
        付款金額:<fmt:formatNumber value="${allMoney }" pattern="#.00" type="number"></fmt:formatNumber>
        <p style="color:red;font-size:30px">我們會盡快為您寄送商品</p>
      </td>
    </tr>
    <tr><td height="80"></td></tr>
    <tr>
      <td colspan="2" align="center">
        <a href="<%=basePath%>MainSvl">返回首頁</a>
      </td>
    </tr>
  </table>

付款異常處理

在transaction任務內的任何一步驟添加throw new RuntimeException("異常測試");
即可在DB進行數據確認，以及在console進行log確認