1.JdbcUtil(使用c3p0组件)
public class JdbcUtil { private static DataSource dataSource; static { dataSource = new ComboPooledDataSource(); } public static QueryRunner getQueryRunner(){ return new QueryRunner(dataSource); } }
2.PageBean
public class PageBean <T>{ private int currentPage = 1; private int pageCount = 4; //每页的行数 private int totalCount; //总记录数 private int totalPage; //总页数 private List<T> pageData; //分页查到的数据 public int getCurrentPage() { return currentPage; } public void setCurrentPage(int currentPage) { this.currentPage = currentPage; } public int getPageCount() { return pageCount; } public void setPageCount(int pageCount) { this.pageCount = pageCount; } public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public int getTotalPage() { if(totalCount % pageCount == 0) totalPage = totalCount / pageCount; else totalPage = totalCount / pageCount + 1; return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public List<T> getPageData() { return pageData; } public void setPageData(List<T> pageData) { this.pageData = pageData; } }
3.Dao
public interface IStudentDao { //分页查询数据 public void getAll(PageBean<Student> pb); //查询总记录数 public int getTotalCount(); }
public class StudentDao implements IStudentDao{ @Override public void getAll(PageBean pb) { //获取当前页,计算查询的起始行、返回的行数,作为sql语句的参数 int currentPage = pb.getCurrentPage(); int pageCount = pb.getPageCount(); //查询总记录数,保存到pb中 int totalCount = this.getTotalCount(); pb.setTotalCount(totalCount); int totalPage = pb.getTotalPage(); if(currentPage>totalPage){ currentPage = totalPage; pb.setCurrentPage(currentPage); } if(currentPage<1){ currentPage =1; pb.setCurrentPage(currentPage); } int start = (currentPage-1)*pageCount; //分页查询数据,把查询到的数据设置到pb中 String sql = "select * from student_info limit ?,?"; QueryRunner queryRunner = JdbcUtil.getQueryRunner(); try { List<Student> pageData = queryRunner.query(sql, new BeanListHandler<Student>(Student.class), start, pageCount); pb.setPageData(pageData); } catch (SQLException e) { throw new RuntimeException(e); } } @Override public int getTotalCount() { String sql = "select count(1) from student_info"; QueryRunner queryRunner = JdbcUtil.getQueryRunner(); //返回结果的第一行的第一列 try { Long count = (Long)queryRunner.query(sql, new ScalarHandler()); return count.intValue(); } catch (SQLException e) { throw new RuntimeException(e); } } }
4.service
public interface IStudentService { public void getAll(PageBean<Student> pb); }
public class StudentService implements IStudentService{ private IStudentDao studentDao = new StudentDao(); @Override public void getAll(PageBean<Student> pb) { studentDao.getAll(pb); } }
5.servlet
public class IndexServlet extends javax.servlet.http.HttpServlet { private StudentService studentService = new StudentService(); protected void doPost(HttpServletRequest request, HttpServletResponse response) throws javax.servlet.ServletException, IOException { PageBean<Student> studentPageBean = new PageBean<>(); String currentPage = request.getParameter("currentPage"); if(currentPage == null || "".equals(currentPage)){ studentPageBean.setCurrentPage(1); }else{ studentPageBean.setCurrentPage(Integer.parseInt(currentPage)); } studentService.getAll(studentPageBean); request.setAttribute("pageBean",studentPageBean); request.getRequestDispatcher("/WEB-INF/list_student.jsp").forward(request,response); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws javax.servlet.ServletException, IOException { doPost(request,response); } }
6.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <html> <head> <title>分页数据</title> </head> <body> <table> <tr> <td>学号</td> <td>姓名</td> <td>电话</td> </tr> <c:choose > <c:when test="${not empty requestScope.pageBean}"> <c:forEach items="${requestScope.pageBean.pageData}" var="student"> <tr> <td>${student.stuID}</td> <td>${student.stuName}</td> <td>${student.telephone}</td> </tr> </c:forEach> </c:when> <c:otherwise> <tr> <td>对不起,没有你要找的数据!</td> </tr> </c:otherwise> </c:choose> <tr> <td colspan="3"> 当前${requestScope.pageBean.currentPage}/${requestScope.pageBean.totalPage}页 <a href="${pageContext.request.contextPath}/index.do?currentPage=1">首页</a> <a href="${pageContext.request.contextPath}/index.do?currentPage=${requestScope.pageBean.currentPage-1}">上一页</a> <a href="${pageContext.request.contextPath}/index.do?currentPage=${requestScope.pageBean.currentPage+1}">下一页</a> <a href="${pageContext.request.contextPath}/index.do?currentPage=${requestScope.pageBean.totalPage}">末页</a> </td> </tr> </table> </body> </html>
7.效果
8.hibernate中的分页
@Test public void test1(){ Session session = sf.openSession(); session.beginTransaction(); Query query = session.createQuery("from Employee"); ScrollableResults scroll = query.scroll();//得到滚动的结果集 scroll.last();//滚动到最后一行 int totalCount = scroll.getRowNumber() + 1;//得到总记录数 //设置分页参数 query.setFirstResult(3); query.setMaxResults(3); session.getTransaction().commit(); session.close(); }