後台

管理員能在後臺頁面查詢用戶購買紀錄及明細
第一次進入此頁面時無參數，在表單填入以下資訊後返回結果列表。

• 用戶名稱
• 查詢範圍(開始日期及結束日期)
• 頁數(點選頁數連結可跳到指定頁)

定義BuyRecordSvl

    public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
      int pageNumber = 1; //設置頁號默認值
      //取得頁面參數
      String pageNum = request.getParameter("page");
      String uname = request.getParameter("uname");
      String beginDate = request.getParameter("beginDate");
      String endDate = request.getParameter("endDate");
      
      //物件初始化
      Date bDate = null;
      Date eDate = null;
      UserBiz biz = new UserBiz();
      
      try {
        //String資料型態轉換
        SimpleDateFormat sd = new SimpleDateFormat("MM/dd/yyyy");
        if(beginDate != null && !beginDate.trim().equals("")) {
          try {
            bDate = sd.parse(beginDate);
          }catch(Exception e) {
            Log.logger.error(e.getMessage());
          }
        }
        if(endDate != null && !endDate.trim().equals("")) {
          try {
            eDate = sd.parse(endDate);
          }catch(Exception e) {
            Log.logger.error(e.getMessage());
          }
        }
        if(pageNum != null) {
          try {
            pageNumber = Integer.parseInt(pageNum);
          }catch(Exception e) {
            Log.logger.error(e.getMessage());
          }
        }
        //取得分頁所需資料列表
        TurnPage tp = new TurnPage();//tp物件為保存分頁所需參數之單純物件
        tp.rowsOnePage = 8;//指定每頁顯示筆數
        if(pageNumber < 1) {pageNumber = 1;}//修正不正資料
        tp.currentPage = pageNumber;//取得用戶所在頁數
        //取得列表、搜尋日期範圍、所在分頁、最大頁數、資料總筆數回傳給頁面
        List<BuyRecord> records = biz.getUserBuyRecord(uname,bDate,eDate,tp);
        request.setAttribute("records", records);
        request.setAttribute("uname", uname);
        request.setAttribute("beginDate", beginDate);
        request.setAttribute("endDate", endDate);
        request.setAttribute("CurrentPageNumber", tp.currentPage);
        request.setAttribute("maxPageNo", tp.allPages);
        request.setAttribute("RecordAllCount", tp.allRows);
      request.getRequestDispatcher("/WEB-INF/back/BuyRecord.jsp").forward(request, response);
      }catch(Exception e) {
        Log.logger.error(e.getMessage(),e);
        request.setAttribute("msg", "網路異常，請和管理員聯繫");
      request.getRequestDispatcher("/error.jsp").forward(request, response);
      }
    }

TrunPage物件

public class TurnPage {
  //當前頁號，預設為1
  public int currentPage = 1;
  //每頁行數，預設為10
  public int rowsOnePage = 10;
  //資料總行數
  public int allRows;
  //總頁數
  public int allPages;
}

在UserBiz新增getUserBuyRecord()

  public List<BuyRecord> getUserBuyRecord(String uname, Date beginDate,Date endDate,TurnPage tp) {
    IUserDao dao = new UserDaoMysql();
    try {
      return dao.getUserBuyRecord(uname,beginDate,endDate,tp);
    }finally {
      dao.closeConnection();
    }
  }

在UserDaoMysql新增getUserBuyRecord()

public class UserDaoMysql extends BaseDao {
  //參數為網頁來的資料及TurnPage物件(記錄換頁相關參數)
  public List<BuyRecord> getUserBuyRecord(String uname, Date beginDate,Date endDate,TurnPage tp) throws Exception{
    List<BuyRecord> records = null;
    //從購買紀錄、購買明細、書籍等三張表取得資料
    String sql = "select d.bcount,bk.bname,bk.isbn,bk.press,bk.price,bk.pdate" + 
    ",br.allmoney,br.buytime,br.uname,br.buyid" + 
    " from tbuydetail d,tbuyrecord br,tbook bk " + 
    " where br.buyid = d.buyid and bk.isbn = d.isbn";
    //設定搜索條件
    if(uname != null && !uname.trim().equals("")) {
      sql = sql + " and uname like '%" + uname + "%'";
    }
    SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
    if(beginDate != null) {
      sql = sql + " and buytime >='" + sd.format(beginDate) + "'";
    }
    if(endDate != null) {
      sql = sql + " and buytime <='" + sd.format(endDate) + "'";
    }
    this.openConnection();
    //取得資料總行數
    tp.allRows = this.getAllCount(sql);
    //計算分頁數，+1表示至少1頁。-1表示若總行數剛好填滿頁面則不換頁。
    tp.allPages = (tp.allRows - 1)/tp.rowsOnePage + 1;
    //調整頁數不超過最大頁數
    if(tp.currentPage > tp.allPages) {tp.currentPage = tp.allPages;}
    //計算本頁開始顯示筆數
    int iStart;
    iStart = (tp.currentPage - 1) * tp.rowsOnePage;
    String newSql = this.getTurnPageSqlMysql(sql,iStart,tp.rowsOnePage);
    PreparedStatement ps = this.connection.prepareStatement(newSql);
    ResultSet rs = ps.executeQuery();
    if(rs != null) {
      records = new ArrayList<BuyRecord>(15);
      while(rs.next()) {
        BuyRecord br = new BuyRecord();
        br.setAllmoney(rs.getDouble("allmoney"));
        br.setBcount(rs.getInt("bcount"));
        br.setBname(rs.getString("bname"));
        br.setBuyid(rs.getString("buyid"));
        br.setBuytime(rs.getTimestamp("buytime"));
        br.setIsbn(rs.getString("isbn"));
        br.setPdate(rs.getDate("pdate"));
        br.setPress(rs.getString("press"));
        br.setPrice(rs.getDouble("price"));
        br.setUname(rs.getString("uname"));
        records.add(br);
      }
    }
    rs.close();
    ps.close();
    return records;
  }
  private int getAllCount(String sql) throws Exception {
    String CountSql = "";
    Integer res = 0;
    CountSql = "select COUNT(*) from(" + sql + ") tb";
    PreparedStatement ps = this.connection.prepareStatement(CountSql);
    ResultSet rs = ps.executeQuery();
    while(rs.next()) {
      res = rs.getInt("COUNT(*)");
    }
    return res;
  }

在BaseDao追加getTurnPageSqlMysql() (因為是通用方法)

  /*
   * mysql使用limit做分頁處理 
   * 文法為limit m,n，m為起點，n為顯示行數
   */
  protected String getTurnPageSqlMysql(String sql,int iStart,int num) {
    String newSql = "";
    newSql = "select * from(" + sql + ") tb limit " + iStart + "," + num;
    return newSql;
  }

換頁機能記憶重點

• 從頁面取得搜尋條件(若有的話)，本案例為用戶名、開始時間及結束時間。
• 建立turnPage物件，存放換頁所需變數(總行數、每頁顯示數、總頁數、當前頁數)
• 有各項參數後，傳給Dao進行判斷，決定要顯示的資料範圍。
• mysql使用limit做分頁處理，由於是通用處理應放進BaseDao
• 從DB取得列表後，轉存進javaBean