分页

use jdbc_demo;create table employee(    empId int not null AUTO_INCREMENT PRIMARY key,    empName varchar(30) not null,    dept_id int not null);

分页技术：

         JSP页面，用来显示数据！ 如果数据有1000条，分页显示，每页显示10条，共100页;   好处:  利于页面布局，且显示的效率高！

 分页关键点：

1.      分页SQL语句；

2.      后台处理： dao/service/servlet/JSP

-- 分页SQL语句-- limit 第一个参数：查询的起始行(从0开始)-- limit 第二个参数：返回的行数-- 每页显示4条-- 第一页SELECT * FROM employee LIMIT 0,4;-- (当前页-1)*每页显示的行数-- 第二页SELECT * FROM employee LIMIT 4,4;-- 第三页SELECT * FROM employee LIMIT 8,4;-- 分页SELECT * FROM 表 LIMIT (当前页-1)*每页显示的行数, 每页显示的行数

实现步骤：

0.      环境准备

a)        引入jar文件及引入配置文件

                                      i.             数据库驱动包

                                    ii.             C3P0连接池jar文件 及 配置文件

                                   iii.             DbUtis组件:    QueryRunner qr = new QueryRuner(dataSouce);

qr.update(sql);

b)        公用类: JdbcUtils.java

1.      先设计：PageBean.java

2.      Dao接口设计/实现：   2个方法

3.      Service/servlet

4.      JSP

cn.itcast.utils包

JdbcUtils.java

package cn.itcast.utils;import javax.sql.DataSource;import org.apache.commons.dbutils.QueryRunner;import com.mchange.v2.c3p0.ComboPooledDataSource;/** * 工具类 * 1. 初始化C3P0连接池 * 2. 创建DbUtils核心工具类对象 * @author Jie.Yuan * */public class JdbcUtils {/** *  1. 初始化C3P0连接池 */private static  DataSource dataSource;static {dataSource = new ComboPooledDataSource();}/** * 2. 创建DbUtils核心工具类对象 */public static QueryRunner getQueryRuner(){// 创建QueryRunner对象，传入连接池对象// 在创建QueryRunner对象的时候，如果传入了数据源对象；// 那么在使用QueryRunner对象方法的时候，就不需要传入连接对象；// 会自动从数据源中获取连接(不用关闭连接)return new QueryRunner(dataSource);}}

PageBean.java

package cn.itcast.utils;import java.util.List;import cn.itcast.entity.Employee;/** * 封装分页的参数 *  * @author Jie.Yuan *  */public class PageBean<T> {private int currentPage = 1; // 当前页, 默认显示第一页private int pageCount = 4;   // 每页显示的行数(查询返回的行数), 默认每页显示4行private int totalCount;      // 总记录数private int totalPage;       // 总页数 = 总记录数 / 每页显示的行数  (+ 1)private List<T> pageData;       // 分页查询到的数据// 返回总页数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 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 List<T> getPageData() {return pageData;}public void setPageData(List<T> pageData) {this.pageData = pageData;}}

c3p0-config.xml

<c3p0-config><default-config><property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_demo</property><property name="driverClass">com.mysql.jdbc.Driver</property><property name="user">root</property><property name="password">root</property><property name="initialPoolSize">3</property><property name="maxPoolSize">6</property><property name="maxIdleTime">1000</property></default-config><named-config name="oracle_config"><property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_demo</property><property name="driverClass">com.mysql.jdbc.Driver</property><property name="user">root</property><property name="password">root</property><property name="initialPoolSize">3</property><property name="maxPoolSize">6</property><property name="maxIdleTime">1000</property></named-config></c3p0-config>

cn.itcast.entity包
Employee.java

package cn.itcast.entity;/** * 1. 实体类设计 (因为用了DbUtils组件，属性要与数据库中字段一致) * @author Jie.Yuan * */public class Employee {private int empId;// 员工idprivate String empName;// 员工名称private int dept_id;// 部门idpublic int getEmpId() {return empId;}public void setEmpId(int empId) {this.empId = empId;}public String getEmpName() {return empName;}public void setEmpName(String empName) {this.empName = empName;}public int getDept_id() {return dept_id;}public void setDept_id(int deptId) {dept_id = deptId;}}

cn.itcast.dao

IEmployeeDao.java

package cn.itcast.dao;import cn.itcast.entity.Employee;import cn.itcast.utils.PageBean;/** * 2. 数据访问层，接口设计 * @author Jie.Yuan * */public interface IEmployeeDao {/** * 分页查询数据 */public void getAll(PageBean<Employee> pb);/** * 查询总记录数 */public int getTotalCount();}

cn.itcast.dao.impl包

EmployeeDao.java

package cn.itcast.dao.impl;import java.sql.SQLException;import java.util.List;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import cn.itcast.dao.IEmployeeDao;import cn.itcast.entity.Employee;import cn.itcast.utils.JdbcUtils;import cn.itcast.utils.PageBean;/** * 2. 数据访问层实现 * @author Jie.Yuan * */public class EmployeeDao implements IEmployeeDao {@Overridepublic void getAll(PageBean<Employee> pb) {//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<Employee> pageData = qr.query(sql, new BeanListHandler<Employee>(Employee.class), index, count);// 设置到pb对象中pb.setPageData(pageData);} catch (Exception e) {throw new RuntimeException(e);}}@Overridepublic int getTotalCount() {String sql = "select count(*) from employee";try {// 创建QueryRunner对象QueryRunner qr = JdbcUtils.getQueryRuner();// 执行查询， 返回结果的第一行的第一列Long count = qr.query(sql, new ScalarHandler<Long>());return count.intValue();} catch (Exception e) {throw new RuntimeException(e);}}}

cn.itcast.service包

IEmployeeService.java

package cn.itcast.service;import cn.itcast.entity.Employee;import cn.itcast.utils.PageBean;/** * 3. 业务逻辑层接口设计 * @author Jie.Yuan * */public interface IEmployeeService {/** * 分页查询数据 */public void getAll(PageBean<Employee> pb);}

cn.itcast.service.impl包
EmployeeService.java

package cn.itcast.service.impl;import cn.itcast.dao.IEmployeeDao;import cn.itcast.dao.impl.EmployeeDao;import cn.itcast.entity.Employee;import cn.itcast.service.IEmployeeService;import cn.itcast.utils.PageBean;/** * 3. 业务逻辑层，实现 * @author Jie.Yuan * */public class EmployeeService implements IEmployeeService {// 创建Dao实例private IEmployeeDao employeeDao = new EmployeeDao();@Overridepublic void getAll(PageBean<Employee> pb) {try {employeeDao.getAll(pb);} catch (Exception e) {throw new RuntimeException(e);}}}

cn.itcast.servlet包

IndexServlet.java

package cn.itcast.servlet;import java.io.IOException;import java.io.PrintWriter;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import cn.itcast.entity.Employee;import cn.itcast.service.IEmployeeService;import cn.itcast.service.impl.EmployeeService;import cn.itcast.utils.PageBean;/** * 4. 控制层开发 * @author Jie.Yuan * */public class IndexServlet extends HttpServlet {// 创建Service实例private IEmployeeService employeeService = new EmployeeService();// 跳转资源private String uri;public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, 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<Employee> pageBean = new PageBean<Employee>();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 void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {this.doGet(request, response);}}

web.xml

<?xml version="1.0" encoding="UTF-8"?><web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">  <servlet>    <servlet-name>IndexServlet</servlet-name>    <servlet-class>cn.itcast.servlet.IndexServlet</servlet-class>  </servlet>  <servlet-mapping>    <servlet-name>IndexServlet</servlet-name>    <url-pattern>/index</url-pattern>  </servlet-mapping>  <welcome-file-list>    <welcome-file>index.jsp</welcome-file>  </welcome-file-list></web-app>

list.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><!-- 引入jstl核心标签库 --><%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>        <title>分页查询数据</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">      </head>    <body>  <table border="1" width="80%" align="center" cellpadding="5" cellspacing="0">  <tr>  <td>序号</td>  <td>员工编号</td>  <td>员工姓名</td>  </tr>  <!-- 迭代数据 -->  <c:choose>  <c:when test="${not empty requestScope.pageBean.pageData}">  <c:forEach var="emp" items="${requestScope.pageBean.pageData}" varStatus="vs">  <tr>  <td>${vs.count }</td>  <td>${emp.empId }</td>  <td>${emp.empName }</td>  </tr>  </c:forEach>  </c:when>  <c:otherwise>  <tr>  <td colspan="3">对不起，没有你要找的数据</td>  </tr>  </c:otherwise>  </c:choose>    <tr>  <td colspan="3" align="center">  当前${requestScope.pageBean.currentPage }/${requestScope.pageBean.totalPage }页           <a href="${pageContext.request.contextPath }/index?currentPage=1">首页</a>  <a href="${pageContext.request.contextPath }/index?currentPage=${requestScope.pageBean.currentPage-1}">上一页 </a>  <a href="${pageContext.request.contextPath }/index?currentPage=${requestScope.pageBean.currentPage+1}">下一页 </a>  <a href="${pageContext.request.contextPath }/index?currentPage=${requestScope.pageBean.totalPage}">末页</a>  </td>  </tr>    </table>  </body></html>