C3P0,Dbutils4mysql分页

引入相应的jar包。

C3P0.xml

<c3p0-config><default-config><property name="jdbcUrl">jdbc:mysql://localhost:3306/day20</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></c3p0-config>

entity包

package cn.itcast.entity;public class Employee {private int empId;private String empName;private int deptId;public 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 getDeptId() {return deptId;}public void setDeptId(int deptId) {this.deptId = deptId;}}

utils包

package cn.itcast.utils;import org.apache.commons.dbutils.QueryRunner;import com.mchange.v2.c3p0.ComboPooledDataSource;public class JdbcUtil {private static ComboPooledDataSource dataSource;static{dataSource = new ComboPooledDataSource();}public static QueryRunner getQueryRunner(){return new QueryRunner(dataSource);}}

package cn.itcast.utils;import java.util.List;public class PageBean<T>{private int currPage;private int pageSize = 4;private int totalPage;private int totalRows;private List<T> list;public int getCurrPage() {return currPage;}public void setCurrPage(int currPage) {this.currPage = currPage;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public int getTotalPage() {if (totalRows%pageSize==0) {totalPage = totalRows/pageSize;}else {totalPage = totalRows/pageSize+1;}return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public int getTotalRows() {return totalRows;}public void setTotalRows(int totalRows) {this.totalRows = totalRows;}public List<T> getList() {return list;}public void setList(List<T> list) {this.list = list;}}

dao包和impl

package cn.itcast.dao;import cn.itcast.entity.Employee;import cn.itcast.utils.PageBean;public interface IBeanDao {public int getTotalRows();public void getAll(PageBean<Employee> pb);}

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.IBeanDao;import cn.itcast.entity.Employee;import cn.itcast.utils.JdbcUtil;import cn.itcast.utils.PageBean;public class BeanDao implements IBeanDao{public int getTotalRows(){String sql = "select count(*) from employee";QueryRunner qr = JdbcUtil.getQueryRunner();Long count = null;try {count = qr.query(sql, new ScalarHandler<Long>());//ScalarHandler<Long> 泛型中的类型和返回值一致} catch (SQLException e) {e.printStackTrace();}return count.intValue();}public void getAll(PageBean<Employee> pb) {int totalCount = getTotalRows();pb.setTotalRows(totalCount);int index = 0;int pageCount = pb.getPageSize();if (pb.getCurrPage()==0||"".equals(pb.getCurrPage())) {pb.setCurrPage(1);}else if(pb.getCurrPage()>pb.getTotalPage()){pb.setCurrPage(pb.getTotalPage());}String sql = "select * from employee limit ?,?";index = (pb.getCurrPage()-1)*pb.getPageSize();QueryRunner qr = JdbcUtil.getQueryRunner();try {List<Employee> empList = qr.query(sql, new BeanListHandler<Employee>(Employee.class),index,pageCount);pb.setList(empList);} catch (SQLException e) {e.printStackTrace();}}}

service和impl

package cn.itcast.service;import cn.itcast.entity.Employee;import cn.itcast.utils.PageBean;public interface IBeanService {public void getAll(PageBean<Employee> pb);}

package cn.itcast.service.impl;import cn.itcast.dao.IBeanDao;import cn.itcast.dao.impl.BeanDao;import cn.itcast.entity.Employee;import cn.itcast.service.IBeanService;import cn.itcast.utils.PageBean;public class BeanService implements IBeanService{IBeanDao beanDao = new BeanDao();public void getAll(PageBean<Employee> pb) {beanDao.getAll(pb);}}

servlet包

package cn.itcast.servlet;import java.io.IOException;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.IBeanService;import cn.itcast.service.impl.BeanService;import cn.itcast.utils.PageBean;public class IndexServlet extends HttpServlet{IBeanService beanService = new BeanService();@Overrideprotected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {request.setCharacterEncoding("utf-8");String currPage = request.getParameter("currPage");if (currPage == null || "".equals(currPage.trim())){currPage = "1";  // 第一次访问，设置当前页为1;}// 转换int currentPage = Integer.parseInt(currPage);PageBean<Employee> pb = new PageBean<Employee>();pb.setCurrPage(Integer.parseInt(currPage));beanService.getAll(pb);request.setAttribute("pageBean", pb);request.getRequestDispatcher("/WEB-INF/list.jsp").forward(request, response);}@Overrideprotected 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">  <display-name></display-name>  <servlet><servlet-name>Index</servlet-name><servlet-class>cn.itcast.servlet.IndexServlet</servlet-class></servlet><servlet-mapping><servlet-name>Index</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"%><%@ 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>  </head>    <body>    <table border="1" width="80%" align="center" cellspacing="0" cellpadding="5">    <tr>    <td>员工号</td>    <td>员工姓名</td>    <td>部门编号</td>    </tr>    <c:choose>    <c:when test="${not empty requestScope.pageBean.list}">    <c:forEach items="${requestScope.pageBean.list}" var="emp" 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.currPage}/${requestScope.pageBean.totalPage}    <a href="${pageContext.request.contextPath}/index?currPage=1">首页</a>    <a href="${pageContext.request.contextPath}/index?currPage=${requestScope.pageBean.currPage-1}">上一页</a>    <a href="${pageContext.request.contextPath}/index?currPage=${requestScope.pageBean.currPage+1}">下一页</a>    <a href="${pageContext.request.contextPath}/index?currPage=${requestScope.pageBean.totalPage}">末页</a>    </td>    </tr>    </table>  </body></html>