如果一张表的数据有非常多,页面就需要分页展示,但一次性从数据库中读取全部的数据会给服务器带来巨大压力。jsp分页技术的设计思想来源于“select * from employee limit ?,?”这条sql语句,第一个“?”指查询的起始位置,第二个“?”代表偏移量。页面需要展示多少数据,每次就从服务器读取多少数据,大大减轻了服务器的压力。下面开始实现一个javaweb的demo帮助大家更好的理解。
一.准备工作:
MySQL中创建一张用例表
CREATE TABLEemployee(
empIdINT PRIMARY KEYAUTO_INCREMENT,
empNameVARCHAR(20),
dept_idVARCHAR(20)
);
往里面插入足够的数据(20条就够了)
二.demo采用mvc模式,首先编写JSP页面
表格代码如下
序号员工编号员工姓名${vs.count }${emp.empId }${emp.empName }对不起,没有你要找的数据
当前${requestScope.pageBean.currentPage }/${requestScope.pageBean.totalPage }页
首页
上一页
下一页
末页
View Code
三.表对应实体类的设计,分页的几个重要参数也封装到一个实体类里
packagecn.cracker.entity;public classEmployee {private int empId; //员工id
private String empName; //员工名称
private int dept_id; //部门id
public intgetEmpId() {returnempId;
}public void setEmpId(intempId) {this.empId =empId;
}publicString getEmpName() {returnempName;
}public voidsetEmpName(String empName) {this.empName =empName;
}public intgetDept_id() {returndept_id;
}public void setDept_id(intdeptId) {
dept_id=deptId;
}
}
View Code
packagecn.cracker.utils;importjava.util.List;importcn.cracker.entity.Employee;public class PageBean{private int currentPage = 1; //当前页, 默认显示第一页
private int pageCount = 4; //每页显示的行数(查询返回的行数), 默认每页显示4行
private int totalCount; //总记录数
private int totalPage; //总页数 = 总记录数 / 每页显示的行数 (+ 1)
private List pageData; //分页查询到的数据//返回总页数
public intgetTotalPage() {if (totalCount % pageCount == 0) {
totalPage= totalCount /pageCount;
}else{
totalPage= totalCount / pageCount + 1;
}returntotalPage;
}public void setTotalPage(inttotalPage) {this.totalPage =totalPage;
}public intgetCurrentPage() {returncurrentPage;
}public void setCurrentPage(intcurrentPage) {this.currentPage =currentPage;
}public intgetPageCount() {returnpageCount;
}public void setPageCount(intpageCount) {this.pageCount =pageCount;
}public intgetTotalCount() {returntotalCount;
}public void setTotalCount(inttotalCount) {this.totalCount =totalCount;
}public ListgetPageData() {returnpageData;
}public void setPageData(ListpageData) {this.pageData =pageData;
}
}
View Code
四.DbUtils和C3p0配置文件
packagecn.cracker.utils;importjavax.sql.DataSource;importorg.apache.commons.dbutils.QueryRunner;importcom.mchange.v2.c3p0.ComboPooledDataSource;/*** 工具类*/
public classJdbcUtils {/*** 1. 初始化C3P0连接池*/
private staticDataSource dataSource;static{
dataSource= newComboPooledDataSource();
}/*** 2. 创建DbUtils核心工具类对象*/
public staticQueryRunner getQueryRuner(){return newQueryRunner(dataSource);
}
}
View Code
jdbc:mysql://localhost:3306/jdbc_demo
com.mysql.jdbc.Driver
root
root
3
6
1000
jdbc:mysql://localhost:3306/jdbc_demo
com.mysql.jdbc.Driver
root
root
3
6
1000
View Code
五.数据库访问层的设计(包含接口与实现)
实现查询分页的数据和总数据两个功能
packagecn.cracker.dao;importcn.cracker.entity.Employee;importcn.cracker.utils.PageBean;public interfaceIEmployeeDao {public void getAll(PageBeanpb);public intgetTotalCount();
}
View Code
packagecn.cracker.dao.impl;importjava.sql.SQLException;importjava.util.List;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.handlers.BeanListHandler;importorg.apache.commons.dbutils.handlers.ScalarHandler;importcn.cracker.dao.IEmployeeDao;importcn.cracker.entity.Employee;importcn.cracker.utils.JdbcUtils;importcn.cracker.utils.PageBean;/*** 2. 数据访问层实现*/
public class EmployeeDao implementsIEmployeeDao {
@Overridepublic void getAll(PageBeanpb) {//2. 查询总记录数; 设置到pb对象中
int totalCount = this.getTotalCount();
pb.setTotalCount(totalCount);/** 问题: jsp页面,如果当前页为首页,再点击上一页报错!
* 如果当前页为末页,再点下一页显示有问题!
* 解决:
* 1. 如果当前页 <= 0; 当前页设置当前页为1;
* 2. 如果当前页 > 最大页数; 当前页设置为最大页数*/
//判断
if (pb.getCurrentPage() <=0) {
pb.setCurrentPage(1); //把当前页设置为1
} else if (pb.getCurrentPage() >pb.getTotalPage()){
pb.setCurrentPage(pb.getTotalPage());//把当前页设置为最大页数
}//1. 获取当前页: 计算查询的起始行、返回的行数
int currentPage =pb.getCurrentPage();int index = (currentPage -1 ) * pb.getPageCount(); //查询的起始行
int count = pb.getPageCount(); //查询返回的行数//3. 分页查询数据; 把查询到的数据设置到pb对象中
String sql = "select * from employee limit ?,?";try{//得到Queryrunner对象
QueryRunner qr =JdbcUtils.getQueryRuner();//根据当前页,查询当前页数据(一页数据)
List pageData = qr.query(sql, new BeanListHandler(Employee.class), index, count);//设置到pb对象中
pb.setPageData(pageData);
}catch(Exception e) {throw newRuntimeException(e);
}
}
@Overridepublic intgetTotalCount() {
String sql= "select count(*) from employee";try{//创建QueryRunner对象
QueryRunner qr =JdbcUtils.getQueryRuner();//执行查询, 返回结果的第一行的第一列
Long count = qr.query(sql, new ScalarHandler());returncount.intValue();
}catch(Exception e) {throw newRuntimeException(e);
}
}
}
View Code
六.业务层
packagecn.cracker.service;importcn.cracker.entity.Employee;importcn.cracker.utils.PageBean;public interfaceIEmployeeService {/*** 分页查询数据*/
public void getAll(PageBeanpb);
}
View Code
packagecn.cracker.service.impl;importcn.cracker.dao.IEmployeeDao;importcn.cracker.dao.impl.EmployeeDao;importcn.cracker.entity.Employee;importcn.cracker.service.IEmployeeService;importcn.cracker.utils.PageBean;/*** 3. 业务逻辑层,实现*/
public class EmployeeService implementsIEmployeeService {//创建Dao实例
private IEmployeeDao employeeDao = newEmployeeDao();
@Overridepublic void getAll(PageBeanpb) {try{
employeeDao.getAll(pb);
}catch(Exception e) {throw newRuntimeException(e);
}
}
}
View Code
七.控制层
packagecn.cracker.servlet;importjava.io.IOException;importjava.io.PrintWriter;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importcn.cracker.entity.Employee;importcn.cracker.service.IEmployeeService;importcn.cracker.service.impl.EmployeeService;importcn.cracker.utils.PageBean;/*** 4. 控制层开发*/
public class IndexServlet extendsHttpServlet {//创建Service实例
private IEmployeeService employeeService = newEmployeeService();//跳转资源
privateString uri;public voiddoGet(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {try{//1. 获取“当前页”参数; (第一次访问当前页为null)
String currPage = request.getParameter("currentPage");//判断
if (currPage == null || "".equals(currPage.trim())){
currPage= "1"; //第一次访问,设置当前页为1;
}//转换
int currentPage =Integer.parseInt(currPage);//2. 创建PageBean对象,设置当前页参数; 传入service方法参数
PageBean pageBean = new PageBean();
pageBean.setCurrentPage(currentPage);//3. 调用service
employeeService.getAll(pageBean); //【pageBean已经被dao填充了数据】//4. 保存pageBean对象,到request域中
request.setAttribute("pageBean", pageBean);//5. 跳转
uri = "/WEB-INF/list.jsp";
}catch(Exception e) {
e.printStackTrace();//测试使用//出现错误,跳转到错误页面;给用户友好提示
uri = "/error/error.jsp";
}
request.getRequestDispatcher(uri).forward(request, response);
}public voiddoPost(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {this.doGet(request, response);
}
}
View Code
PS:别忘了导入这几个lib包 c3p0-0.9.1.2.jar、commons-dbutils-1.6.jar、mysql-connector-java-5.1.12-bin.jar