数据库分页技术--hibernate篇

数据库分页技术在以后的开发中经常用到，以后主要是通过各种框架来实现的，他的主要原理是
long rows = 0; //数据总数
int size = 10; //每一页显示的记录条数
int page = 1; //当前第几页
int pageCount = 0; //一共有多少页
由这四个参数来完成。

下面是通过hibernate 来得到list，
mysql是由limit实现
不同的数据库方法不同，但使用了hibernate后，这些就都不用管了，他会自己实现功能
由下面这两个方法就行
//page代表当前页，如page=1,那么他将从第一天显示
query.setFirstResult((page-1)*10); //每条记录开始的位置
query.setMaxResults(size); //每页显示的记录条数

servlet.java

protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {

            Configuration cfg=new Configuration().configure();
            StandardServiceRegistryBuilder ssrb=new StandardServiceRegistryBuilder()
            .applySettings(cfg.getProperties());
            ServiceRegistry serviceRegistry=ssrb.build();

            SessionFactory factory=cfg.buildSessionFactory(serviceRegistry);





        Long rows=(long) 0;     //数据总数
        int size=10;                //每一页显示的记录条数
        int page=1;             //当前页数
        int pageCount=0;        //页面总数

        List list=new ArrayList();

        String rp = req.getParameter("page");
        if(rp!=null&&rp.length()>0){

            try {
                page = Integer.parseInt(rp);
            } catch (NumberFormatException e) {
                e.printStackTrace();
                page = 1;
            }           
        }
        Session hsSession=null;
        try {
            hsSession=factory.openSession();
            hsSession.beginTransaction();
            //获取数据总数
            rows =(Long) hsSession.createQuery("select count(*) from Bookbean bb").iterate().next();

            Query query=hsSession.createQuery("from Bookbean bb");

            query.setFirstResult((page-1)*10);      //每条记录开始的位置
            query.setMaxResults(size);          //每页显示的记录条数

            list=query.list();


            hsSession.getTransaction().commit();




        } catch (Exception e) {
            e.printStackTrace();
            hsSession.getTransaction().rollback();
        }finally{
            hsSession.close();
        }

        Iterator iterator=list.iterator();
        while(iterator.hasNext()){
            Bookbean bbBookbean=(Bookbean)iterator.next();
            System.out.println(bbBookbean.getId()+" "+bbBookbean.getBname()+" "+bbBookbean.getPrice());

        }


        req.setAttribute("list", list);
        req.setAttribute("pageCount", pageCount);
        req.setAttribute("page", page);

        req.getRequestDispatcher("/paging/book.jsp").forward(req, resp);    

show.jsp

<form action="page" method="post">
        <table>
            <tr>
                <td>序号</td>
                <td>编号</td>
                <td>书名</td>
                <td>价格</td>
            </tr>           
            <c:choose>
                <c:when test="${empty list }">
                    <tr>
                        <td>对不起，没有查询到书本信息1</td>             
                    </tr>               
                </c:when>

                <c:otherwise>
                    <c:forEach items="${requestScope.list }" var="book" varStatus="id">
                        <tr>
                            <td>${id.index }</td>
                            <td>${book.id }</td>
                            <td>${book.bname }</td>
                            <td>${book.price }</td>
                        </tr>                   
                    </c:forEach>                    
                    <tr>
                        <td><a href="page?page=1">首页</a></td>
                        <td><a href="page?page=${page-1 }">上一页</a></td>
                        <td><a href="page?page=${page+1 }">下一页</a></td>

                    </tr>               

                </c:otherwise>          
            </c:choose>
            <tr>
                <td></td>
                <td></td>
                <td></td>
            </tr>                   
        </table>    
    </form>

—–华丽分割线—–

---

上面是我自己写的由hibernate实现

下面是老师的代码

servlet.java

public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        long rows = 0;      //数据总数
        int size = 10;      //每一页显示的记录条数
        int page = 1;       //当前第几页
        int pageCount = 0;  //一共有多少页

        String rp = request.getParameter("page");
        if(rp!=null&&rp.length()>0){

            try {
                page = Integer.parseInt(rp);
            } catch (NumberFormatException e) {
                e.printStackTrace();
                page = 1;
            }

        }


        try {
            InitialContext initCtx = new InitialContext();
            Context ctx = (Context) initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource) ctx.lookup("jdbcmysql");

            Connection conn = ds.getConnection();

            PreparedStatement stat = conn.prepareStatement("select count(*) as rows from student");

            ResultSet res = stat.executeQuery();

            if(res.next()){
                rows = res.getLong("rows");
            }

            //计算pageCount
            if(rows%size==0){
                pageCount = (int) (rows/size);
            }else{
                pageCount = (int) (rows/size)+1;
            }

            //根据当前页查询出需要的数据
            List<Student> list = new ArrayList<Student>();

            stat = conn.prepareStatement("select * from student limit ?,?");
            stat.setInt(1, (page-1)*size);
            stat.setInt(2, size);

            res = stat.executeQuery();

            while(res.next()){

                Student s = new Student();
                s.setId(res.getInt(1));
                s.setName(res.getString(2));
                s.setSex(res.getString(3));
                s.setClazz(res.getString(4));

                list.add(s);

            }

            conn.close();

            //数据发送给JSP，并转发
            request.setAttribute("list", list);
            request.setAttribute("pageCount", pageCount);
            request.setAttribute("page", page);

            request.getRequestDispatcher("/stus.jsp").forward(request, response);

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

show.jsp

 <h1>&nbsp;学生信息</h1>
   <table border="1" width="400" align="center">
    <tr>
        <th>序号</th>
        <th>ID</th>
        <th>姓名</th>
        <th>性别</th>
        <th>班级</th>
    </tr>
    <c:choose>
    <c:when test="${empty list}">
        <tr>
            <td colspan="5" align="center">对不起！没有学生信息</td>
        </tr>
    </c:when>
    <c:otherwise>
    <c:forEach items="${list }" var="st" varStatus="index">
        <tr>
            <td>${index.count }</td>
            <td>${st.id }</td>
            <td>${st.name }</td>
            <td>${st.sex }</td>
            <td>${st.clazz }</td>
        </tr>
    </c:forEach>
        <tr>
            <td colspan="5">
            <c:if test="${!(page==1) }">
            <a href="stus?page=${page-1}">上一页</a>&nbsp;
            </c:if>
            <c:if test="${!(page==pageCount) }">
            <a href="stus?page=${page+1}">下一页</a>&nbsp;
            </c:if>
                ||<c:forEach var="p" begin="1" end="${pageCount}">
                <a href="stus?page=${p}">第${p}页</a>&nbsp;
                </c:forEach>
                ||第${page }页
            </td>
        </tr>
    </c:otherwise>
    </c:choose>
   </table>