JavaEE分页查询

本文主要介绍JavaEE中采用泛型进行分页查询。该分页是通过当前页号及每页记录数在数据库中进行查询，假设数据库是Oracle。

完整代码可以到 https://github.com/psp0001060/LearnServlet下载 。

一、项目结构图

二、关键代码

PageModel

package com.hr.pojo;import java.util.List;public class PageModel<E> {    private List<E> list;    private int pageNo;//页号    private int pageSize;//每页显示记录数    private int totalNum; //总记录数    private int totalPage;//总页数    public List<E> getList() {        return list;    }    public void setList(List<E> list) {        this.list = list;    }    public int getPageNo() {        return pageNo;    }    public void setPageNo(int pageNo) {        this.pageNo = pageNo;    }    public int getPageSize() {        return pageSize;    }    public void setPageSize(int pageSize) {        this.pageSize = pageSize;    }    public int getTotalNum() {        return totalNum;    }    public void setTotalNum(int totalNum) {        this.totalNum = totalNum;        setTotalPage((getTotalNum() % pageSize) == 0 ? (getTotalNum() / pageSize) : (getTotalNum() / pageSize + 1));    }    public int getTotalPage() {        return totalPage;    }    public void setTotalPage(int totalPage) {        this.totalPage = totalPage;    }    // 获取第一页    public int getFirstPage() {        return 1;    }    // 获取最后页    public int getLastPage() {        return totalPage;    }    // 获取前页    public int getPrePage() {        if (pageNo > 1)            return pageNo - 1;        return 1;    }    // 获取后页    public int getBackPage() {        if (pageNo < totalPage)            return pageNo + 1;        return totalPage;    }    // 判断'首页'及‘前页'是否可用    public String isPreable() {        if (pageNo == 1)            return "disabled";        return "";    }    // 判断'尾页'及‘下页'是否可用    public String isBackable() {        if (pageNo == totalPage)            return "disabled";        return "";    }}

deptList.jsp

<table class="hovertable" width="100%">                <tr>                    <td>部门编号</td>                    <td>部门名称</td>                    <td>部门地址</td>                    <td>操作1</td>                    <td>操作2</td>                </tr>                <c:forEach items="${requestScope.pagination.list }" var="dept">                    <tr>                        <td>${dept.deptId }</td>                        <td>${dept.deptName }</td>                        <td>${dept.deptLoc }</td>                        <td><a href="ToDeptUpdateServlet?deptId=${dept.deptId }">更新</a></td>                        <td><a href="">删除</a></td>                    </tr>                </c:forEach>                <tr>                    <td colspan="5" align="right">                    <c:if test="${pagination.pageNo > 1 }">                        <a href="DeptListServlet?pageNo=1">首页</a>                        <a href="DeptListServlet?pageNo=${pagination.pageNo-1} ">上一页</a>                    </c:if>                    <c:if test="${pagination.pageNo < pagination.totalPage }">                        <a href="DeptListServlet?pageNo=${pagination.pageNo+1}">下一页</a>                        <a href="DeptListServlet?pageNo=${pagination.totalPage}">末页</a>                    </c:if>                </tr>            </table>

DeptListServlet

@WebServlet("/DeptListServlet")public class DeptListServlet extends HttpServlet {    private static final long serialVersionUID = 1L;    private int pageSize = 0;     /**     * @see HttpServlet#HttpServlet()     */    public DeptListServlet() {        super();    }    @Override     public void init() throws ServletException {         pageSize = Integer.valueOf(this.getServletContext().getInitParameter("pageSize")); //从web.xml中获取pageSize数值    }    /**     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)     */    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        int pageNo = 1;//默认是第一页        if (null != request.getParameter("pageNo")) {            pageNo = Integer.parseInt(request.getParameter("pageNo"));         }        DeptService ds = new DeptService();        if(null !=request.getAttribute("msg")){            String toPage = (String) request.getAttribute("toPage");            request.getRequestDispatcher(toPage).forward(request, response);        }else{//          List<Dept> list = ds.queryAll();            PageModel<Dept> pagination = ds.queryForPage(pageNo,pageSize);            request.setAttribute("pagination", pagination);            request.getRequestDispatcher("dept/deptList.jsp").forward(request, response);        }    }    /**     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)     */    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        // TODO Auto-generated method stub        doGet(request, response);    }}

DeptService

    /**     * 分页查询记录     * @param pageNo      * @param pageSize     * @return      */    public PageModel<Dept> queryForPage(int pageNo, int pageSize) {        DeptDao deptDao = new DeptDao();        PageModel<Dept> pagination = new PageModel<Dept>();        pagination.setList(deptDao.queryForPage(pageNo, pageSize));        pagination.setPageNo(pageNo);        pagination.setPageSize(pageSize);        pagination.setTotalNum(deptDao.getTotalNum());        return pagination;    }

DeptDao

/**     * 分页查询记录     * @param pageNo      * @param pageSize     * @return      */    public List<Dept> queryForPage(int pageNo, int pageSize) {        Connection conn = DBUtil.getConnection();        PreparedStatement ps = null;        ResultSet result = null;        Dept dept = null;        List<Dept> resultList = new ArrayList<Dept>();        try {            String startPageSql = "select * from(select rownum num,u.* from(";            String innerSql = "select * from dept";            String endPageSql = ") u where rownum<=?) where num>=?";            String sql = startPageSql + innerSql + endPageSql;            ps = conn.prepareStatement(sql);            ps.setInt(1, pageNo * pageSize);            ps.setInt(2, (pageNo - 1) * pageSize + 1);            result = ps.executeQuery();            while (result.next()) {                dept = new Dept();                dept.setDeptId(result.getInt(2));                dept.setDeptName(result.getString(3));                dept.setDeptLoc(result.getString(4));                resultList.add(dept);            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            DBUtil.closeResultSet(result);            DBUtil.closePreparedStatement(ps);            DBUtil.closeConnection(conn);        }        return resultList;    }    /**     * 获取总记录数     * @return     */    public int getTotalNum() {        Connection conn = DBUtil.getConnection();        PreparedStatement ps = null;        ResultSet result = null;        int totalCount = 0;        try {            String sql = "select count(1) from dept";            ps = conn.prepareStatement(sql);             result = ps.executeQuery();            if(result.next()) {                totalCount = result.getInt(1);            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            DBUtil.closeResultSet(result);            DBUtil.closePreparedStatement(ps);            DBUtil.closeConnection(conn);        }        return totalCount;    }

三、效果图