3.25

javaweb实现数据查询分页

1. 首先我们需要一个用于分页的工具类

   package com.lgh.app.page;
   /**
    * @author lgh
    * 
    * 此类是用于分页的工具类
    * 可以重复利用
    * */
   import java.util.List;
   
   public class Page<T> {
       //当前是第几页
       private int pageNo ;
       //当前页的List
       private List<T> list;
   
       //每页显示多少条记录
           private int pageSize = 0;
   
           //共有多少条记录
           private long totalItemNumber;
   
   
   
           //构造器中需要对 pageNo 进行初始化
           public Page(int pageNo) {
               super();
               this.pageNo = pageNo;
           //  System.out.println("page构造方法中的pageNO "+ this.pageNo );
           }
   
           //需要校验一下
           public int getPageNo() {
           //  System.out.println("page  getPageNo方法开始时中的pageNO "+ this.pageNo );
               if(pageNo < 0)
                   pageNo = 1;
               if(getTotalItemNumber()==0){
                   return 1;
               }
               if(pageNo > getTotalPageNumber()){
                   pageNo = getTotalPageNumber();
               }
   
               return pageNo;
           }
   
   
   
           public long getTotalItemNumber() {
               return totalItemNumber;
           }
   
           public void setPageSize(int pageSize) {
               this.pageSize = pageSize;
           }
   
           public int getPageSize() {
               return pageSize;
           }
   
           public void setList(List<T> list) {
               this.list = list;
           }
   
           public List<T> getList() {
               return list;
           }
   
           //获取总页数
           public int getTotalPageNumber(){
   
               int totalPageNumber = (int)totalItemNumber / pageSize;
   
               if(totalItemNumber % pageSize != 0){
                   totalPageNumber++;
               }
   
               return totalPageNumber;
           }
   
           public void setTotalItemNumber(long totalItemNumber) {
               this.totalItemNumber = totalItemNumber;
           }
   
           public boolean isHasNext(){
               if(getPageNo() < getTotalPageNumber()){
                   return true;
               }
   
               return false;
           }
   
           public boolean isHasPrev(){
               if(getPageNo() > 1){
                   return true;
               }
   
               return false;
           }
   
           public int getPrevPage(){
               if(isHasPrev()){
                   return getPageNo() - 1;
               }
   
               return getPageNo();
           }
   
           public int getNextPage(){
               if(isHasNext()){
                   return getPageNo() + 1;
               }
   
               return getPageNo();
           }
   
   }
   

   　　封装查询条件的类

   package com.lgh.app.page;
   
   
   /**
    * 封装查询条件的类
    * 
    * */
   public class CriterFile {
   
       private int pageNo;     //查询页数
       private String textName;  //检索条件  
       private String descValue;  //  用户输入的检索值
   
       public int getPageNo() {
           return pageNo;
       }
   
       public void setPageNo(int pageNo) {
           this.pageNo = pageNo;
       }
   
       public CriterFile(int pageNo) {
           super();
           this.pageNo = pageNo;
       }
   
       public String getTextName() {
           if(textName==null){
               return "%%";
           }else{
               textName = "%"+textName+"%";
               return textName;
           }
       }
   
       public void setTextName(String textName) {
           this.textName = textName;
       }
   
   
       public String getDescValue() {
           if(descValue==null){
               return "%%";
           }else{
               descValue = "%"+descValue+"%";
               return descValue;
           }
       }
   
       public void setDescValue(String descValue) {
           this.descValue = descValue;
       }
   
       @Override
       public String toString() {
           return "CriterFile [pageNo=" + pageNo + "]";
       }
   
   
   }
   

   　　使用dbutils的Dao 设计

   package com.lgh.app.db;
   
   import java.lang.reflect.ParameterizedType;
   import java.lang.reflect.Type;
   import java.sql.Connection;
   import java.util.List;
   
   import org.apache.commons.dbutils.QueryRunner;
   import org.apache.commons.dbutils.handlers.BeanHandler;
   import org.apache.commons.dbutils.handlers.BeanListHandler;
   import org.apache.commons.dbutils.handlers.ScalarHandler;
   
   /**
    * 封装了基本的 CRUD 的方法, 以供子类继承使用
    * 当前 DAO 直接在方法中获取数据库连接. 
    * 整个 DAO 采取 DBUtils 解决方案. 
    * @param <T>: 当前 DAO 处理的实体类的类型是什么
    */
   public class Dao<T> {
   
       private QueryRunner queryRunner = new QueryRunner();
   
       private Class<T> clazz;
   
       public Dao() {
           Type superClass = this.getClass().getGenericSuperclass();
           if(superClass instanceof ParameterizedType){
               ParameterizedType parameterizedType = (ParameterizedType) superClass;
               Type[] typeArgs = parameterizedType.getActualTypeArguments();
               if(typeArgs != null && typeArgs.length>0){
                   if(typeArgs[0] instanceof Class){
                       clazz = (Class<T>) typeArgs[0];
                   //  System.out.println("dao类中的构造方法泛型类型："+clazz.getName());
                   }
               }
           }
       }
   
       /**
        * 返回某一个字段的值：例如返回某一条记录的 customerName, 或返回数据表中有多少条记录等. 
        * @param sql
        * @param args
        * @return
        */
       public <E> E getForValue(String sql,Object ...args){
           Connection conn = null;
           try {
           //  System.out.println("getForValue方法内部泛型类型："+clazz.getName());
   
               conn = JdbcUtils.getConnection();
   
               return (E) queryRunner.query(conn, sql,new ScalarHandler(), args);
   
           } catch (Exception e) {
               e.printStackTrace();
           }finally{
               JdbcUtils.releaseConnection(conn);
           }
           return null;
       }
   
   
       /**
        * 返回 T 所对应的 List 
        * @param sql
        * @param args
        * @return
        */
       public List<T> getForList(String sql,Object ...args){
           Connection conn = null;
           try {
               conn = JdbcUtils.getConnection();
               return queryRunner.query(conn, sql,new BeanListHandler<>(clazz), args);
   
           } catch (Exception e) {
               e.printStackTrace();
           }finally{
               JdbcUtils.releaseConnection(conn);
           }
           return null;
       }
   
       /**
        * 返回对应的 T 的一个实例类的对象. 
        * @param sql
        * @param args
        * @return
        */
   
       public T get(String sql ,Object ...args){
           Connection conn = null;
           try {
               conn = JdbcUtils.getConnection();
               return queryRunner.query(conn, sql,new BeanHandler<>(clazz), args);
   
           } catch (Exception e) {
               e.printStackTrace();
           }finally{
               JdbcUtils.releaseConnection(conn);
           }
           return null;
       }
   
       /**
        * 该方法封装了 INSERT、DELETE、UPDATE 操作.
        * @param sql: SQL 语句
        * @param args: 填充 SQL 语句的占位符.
        */
       public void Update(String sql,Object ...args){
           Connection conn = null;
           try {
               conn = JdbcUtils.getConnection();
               queryRunner.update(conn, sql, args);
   
           } catch (Exception e) {
               e.printStackTrace();
           }finally{
               JdbcUtils.releaseConnection(conn);
           }
       }
   }
   

   　　分页查询数据

   package com.lgh.app.dao;
   
   import java.util.List;
   
   import com.lgh.app.beans.FileBean;
   import com.lgh.app.db.Dao;
   import com.lgh.app.page.CriterFile;
   import com.lgh.app.page.Page;
   
   public class PageFileDao extends Dao<FileBean> {
   
   
   
       public Page<FileBean> getPage(CriterFile cf ,int pageSize){
   
           Page page = new Page<FileBean>(cf.getPageNo());
           //得到总记录数
           page.setPageSize(pageSize);
           page.setTotalItemNumber(getCount(cf));
           System.out.println(getCount(cf));
           cf.setPageNo(page.getPageNo());
           page.setList(getPageFiles(cf, pageSize));
   
   
           return page;
       }
       /**
        * 分页查询数据
        * 根据传入的页数来返回集合
        * 当用户刚进入是 页面默认为1 
        * 要查到有总共多少数据来判断可以分配多少页
        * 
        * */
   
       public List<FileBean>   getPageFiles(CriterFile cf ,int pageSize){
   
               String sql = "SELECT id, name fileName, path filePath, " +
                       "filedesc fileDesc FROM uploadfiles where name like ? and filedesc like ? limit ?,? ";
               return getForList(sql,cf.getTextName(),cf.getDescValue(),(cf.getPageNo()-1)*pageSize,pageSize);
       }
   
       //向数据库中添加数据 
       public void   saveAll(List<FileBean> beans){
           String sql = "insert into uploadfiles values(null,?,?,?)";
           for(FileBean fub : beans){
               Update(sql, fub.getFileName(),fub.getFilePath(),fub.getFileDesc());
           }
       }
   
       public long getCount(CriterFile cf ){
           String sql = "select count(name) from uploadfiles where name like ? and filedesc like ? ";
           return getForValue(sql,cf.getTextName(),cf.getDescValue());
       }
       public void delById(Integer id){
           String sql ="delete from uploadfiles where id = ?";
           Update(sql,id);
       }
   
       public FileBean getByBookId(int id){
           String sql = "select id, name fileName, path filePath, filedesc  from uploadfiles where id = ?";
           return get(sql, id);
       }
   }
   
   

   实现了这些类，我们的分页查询基本上就已经完成了

   剩下的就是在jsp 上进项显示了
   2. 查询用的servlet

   protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
           List<FileBean> beans = new ArrayList<>();
           //向数据库中查询数据
   
           PageFileDao pfd = new PageFileDao();
           //得到要查询的页数  
           String pageNoStr = request.getParameter("pagenum");
           String textName = request.getParameter("textName");
           String selectName = request.getParameter("selectName");
           int num = 1 ;
           try {
               //第一次是没有 查询页数  默认为1
               num = Integer.parseInt(pageNoStr);
           } catch (NumberFormatException e) {
   
   
           }
           /**
            * 封装查询条件的类
            * 
            * */
           CriterFile cf = new CriterFile(num);
           if("bookName".equals(selectName)){
               cf.setTextName(textName);
   
           }else{
               cf.setDescValue(textName);
           }
       //  beans = pfd.getPageFiles(cf, 5);
           //beans = ufd.getFiles();
   //      for(FileUploadBean bean : beans){
   //          System.out.println(bean);
   //      }
   
           Page<FileBean> pageFile = pfd.getPage(cf, 8);
           request.getSession().setAttribute("pageFile", pageFile);
           //request.setAttribute("beans", beans);
           request.getRequestDispatcher("/app/download.jsp").forward(request, response);
   
       }
   

   jsp页面显示 

   <%@ page language="java" contentType="text/html; charset=utf-8"
       pageEncoding="utf-8"%>
       <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
        <%@taglib uri="/WEB-INF/mystr.tld" prefix="ms" %>
   <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
   <html>
   <head>
   <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
   <title>下载页面</title>
   <meta http-equiv="X-UA-Compatible" content="IE=edge">
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <link href="bs/css/bootstrap.min.css" rel="stylesheet">
       <script src="bs/js/jquery.min.js"></script>
       <script src="bs/js/bootstrap.min.js"></script>
   <script type="text/javascript" src="scripts/jquery-1.7.2.js"></script>
   <script type="text/javascript">
   window.onload = function(){
   
   
       var dela = document.getElementsByName("delA");
       for(var i = 0 ;i <dela.length;i++){
           var del = dela[i];
           del.onclick = function(){
           if(confirm("确定要删除吗？")){
               }else{
                   return false;
                    }
                  }
           }
   
   
   }
   
   $(function(){
   
   
        $("a[name='aname']").click(function(){
              // alert(123);
               var serializeVal = $(":hidden").serialize();
           //  alert(serializeVal);
               var href = this.href +"&"+ serializeVal;
               //可以用隐藏域来序列化参数 也可以直接写 
               //"&selectName=${param.selectName }&textName=${param.textName }"
               window.location.href = href;
               //注意 return false 是必须的  
               return false;
           //  this.href = htef;
               });
   
   
       $("#pageNum").change(function(){
           var val = $(this).val();
           val = $.trim(val);
   
           //1. 校验 val 是否为数字 1, 2, 而不是 a12, b
           var flag = false;
           var reg = /^\d+$/g;
           var pageNo = 0;
   
           if(reg.test(val)){
               //2. 校验 val 在一个合法的范围内： 1-totalPageNumber
               pageNo = parseInt(val);
               if(pageNo >= 1 && pageNo <= parseInt("${pageFile.totalPageNumber }")){
                   flag = true;
               }
           }
   
   
           if(!flag){
               alert("输入的不是合法的页码.");
               $(this).val("");
               return;
           }
   
           //3. 页面跳转
           var href = "<%=request.getContextPath() %>/GetFileListServlet?pagenum=" + pageNo + "&" + $(":hidden").serialize();
           window.location.href = href;
       });
   })
   
   </script>
   </head>
   <body>
   <%@include file="navbar.jsp" %>
   <%-- ${pageContext.request.contextPath}--%>
   <font color ="red">${message }</font>
   <br>
   <div class="container-fluid">
           <div class="row" style="text-align: center;">
   
           <form action="<%=request.getContextPath() %>/GetFileListServlet">
           请输入关键字 ：<select name="selectName"><option value="bookName">书名</option> <option value="leibie">类别</option></select> <input type="text" name="textName"> <input type="submit" value="搜索">
           </form>
           <!-- 隐藏域 让有查询条件时超链接带上查询参数 -->
           <input type="hidden" name="selectName" value="${param.selectName }"/>
           <input type="hidden" name="textName" value="${param.textName }"/>
               <div class="col-md-12">
   
                   <table class="table table-bordered table-striped table-hover">
                       <thead>
   
   <tr><td>文件编号</td><td>文件名</td><td>文件描述</td><td align="center">操作</td></tr>
   </thead>
   <tbody>
   <c:forEach items="${pageFile.list }" var="bean">
   <c:url value="/FileDownLoad" var="downUrl">
   <c:param name="path" value="${bean.filePath}"></c:param>
   <c:param name="filename" value="${bean.fileName }"></c:param>
   </c:url>
   <c:url value="/FileDel" var="delUrl">
   <c:param name="path" value="${bean.filePath}"></c:param>
   <c:param name="fileId" value="${bean.id }"></c:param>
   <c:param name="pagenum" value="${pageFile.pageNo }"></c:param>
   </c:url>
   <tr><td>${bean.id }</td><td><a href="bookmseeage?bookid=${bean.id }"><ms:sub>${bean.fileName }</ms:sub></a></td><td>${bean.fileDesc }</td><td><a href="${downUrl }">下载</a><c:if test="${userid == 2 }"> <a name="delA" href="${delUrl}" >删除</a></c:if></td></tr>
   </c:forEach>
       </tbody>            
                   </table>
                   <div style="text-align: center">
                   <c:forEach begin="${pageFile.pageNo-3>0?pageFile.pageNo-3:1 }" end="${pageFile.pageNo+3>pageFile.totalPageNumber?pageFile.totalPageNumber:pageFile.pageNo+3 }" step="1" var="i">
   
   
                   <c:choose>
                   <c:when test="${pageFile.pageNo == i }">
                   <b><a name="aname" href="<%=request.getContextPath() %>/GetFileListServlet?pagenum=${i}" >${i}</a></b>
                   </c:when>
                   <c:otherwise>
                   <a name="aname" href="<%=request.getContextPath() %>/GetFileListServlet?pagenum=${i}" >${i}</a>
                   </c:otherwise>
                   </c:choose>
   
   
                   </c:forEach>
                     共${pageFile.totalPageNumber }页
                     当前第${pageFile.pageNo }页  
                   <c:if test="${pageFile.hasPrev }">
                   <a name="aname" href="<%=request.getContextPath() %>/GetFileListServlet?pagenum=1">首页</a>  
                     <a name="aname" href="<%=request.getContextPath() %>/GetFileListServlet?pagenum=${pageFile.prevPage}">上一页</a>  
                   </c:if>
                    <c:if test="${pageFile.hasNext }">
                     <a name="aname" href="<%=request.getContextPath() %>/GetFileListServlet?pagenum=${pageFile.nextPage}">下一页</a>  
                   <a name="aname" href="<%=request.getContextPath() %>/GetFileListServlet?pagenum=${pageFile.totalPageNumber}">尾页</a>  
                   </c:if>
                     
                                     转到 <input type="text" size="1"  id="pageNum"/> 页  
   
                   </div>
               </div>
           </div>
       </div>
   </body>
   </html>