【左京淳的JAVA WEB学习笔记】第十六章 分页功能(查询用户购买纪录)

後台

管理员能在後台页面查询用户购买纪录及明细
第一次进入此页面时无参数，在表单填入以下资讯後返回结果列表。

• 用户名称
• 查询范围(开始日期及结束日期)
• 页数(点选页数连结可跳到指定页)

定义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