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();
	
	@Override
	protected 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);
		
	}

	@Override
	protected 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>