MySql+dbutils+c3p0实现jsp分页

下载c3p0jar包及配置文件，dbutils及Mysql jar包在前几篇博客有下载地址
链接：https://pan.baidu.com/s/1wLaTXVGmGE5aEjQ7Ge1iUw
提取码：dkrj
新建Web项目，红框内为实现后配置效果

先在数据库中增加一定的数据，博主用SQLyag创建好信息如下

代码如下
1、c3p0.properties中配置自己的数据库信息
2、创建entity实体类包

package com.offcn.entity;
public class Pet {
	private int id;
	private String name;
	private int health;
	private int love;
	private String strain;
	
	
	public Pet() {
		super();
	}
	public Pet(int id, String name, int health, int love, String strain) {
		super();
		this.id = id;
		this.name = name;
		this.health = health;
		this.love = love;
		this.strain = strain;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getHealth() {
		return health;
	}
	public void setHealth(int health) {
		this.health = health;
	}
	public int getLove() {
		return love;
	}
	public void setLove(int love) {
		this.love = love;
	}
	public String getStrain() {
		return strain;
	}
	public void setStrain(String strain) {
		this.strain = strain;
	}
	@Override
	public String toString() {
		return "Pet [id=" + id + ", name=" + name + ", health=" + health
				+ ", love=" + love + ", strain=" + strain + "]";
	}
}

3、创建petDao层包

package com.offcn.dao;
import java.util.List;
import com.offcn.entity.Pet;

public interface PetDao {
	//分页方法
	List<Pet> selectByPage(Integer currentPage,Integer pageSize);
	//查询总数方法
	Integer selectAll();
}

4、实现Dao层方法

package com.offcn.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 com.mchange.v2.c3p0.ComboPooledDataSource;
import com.offcn.dao.PetDao;
import com.offcn.entity.Pet;

public class PetDaoImpl implements PetDao {
	//先获取连接
	ComboPooledDataSource ds = new ComboPooledDataSource();
	QueryRunner qr = new QueryRunner(ds);
	//分页方法
	public List<Pet> selectByPage(Integer currentPage, Integer pageSize) {
		String sql = "select * from pet limit ?,?";
		Object[] object = {(currentPage-1)*pageSize,pageSize};
		List<Pet> petList = null;
		try {
			petList = qr.query(sql, object, new BeanListHandler<Pet>(Pet.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return petList;
	}
	//查询总数方法
	public Integer selectAll() {
		String sql = "select count(*) from pet";
		Long num;
		Integer totalAll = null;
		try {
			num = (Long) qr.query(sql, new ScalarHandler());
			totalAll = num.intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return totalAll;
	}
}

5、创建ServiceDao层包
该层主要是做逻辑判断，此次没有用到逻辑判断，但要养成该习惯

package com.offcn.service;
import java.util.List;
import com.offcn.entity.Pet;
public interface PetService {
		//分页方法
		List<Pet> selectByPage(Integer currentPage,Integer pageSize);
		//查询总数方法
		Integer selectAll();
}

6、实现ServiceDao层方法

package com.offcn.service.impl;

import java.util.List;

import com.offcn.dao.PetDao;
import com.offcn.dao.impl.PetDaoImpl;
import com.offcn.entity.Pet;
import com.offcn.service.PetService;

public class PetServiceImpl implements PetService {
	PetDao petdao = new PetDaoImpl();
	//分析查询
	public List<Pet> selectByPage(Integer currentPage, Integer pageSize) {
		return petdao.selectByPage(currentPage, pageSize);
	}
	//查询全部数量
	public Integer selectAll() {
		return petdao.selectAll();
	}
}

7、创建测试类，该类也可以不写

package com.offcn.test;

import java.util.List;

import com.offcn.entity.Pet;
import com.offcn.service.PetService;
import com.offcn.service.impl.PetServiceImpl;

public class Test {
	public static void main(String[] args) {
		//分页测试
		PetService petservice = new  PetServiceImpl();
		List<Pet> petList = petservice.selectByPage(1, 2);
		for (Pet pet : petList) {
			System.out.println(pet);
		}
		//查询总数据数测试
		Integer num = petservice.selectAll();
		System.out.println(num);
	}
}

8、创建分页工具包
若想实现分页
需要拿到5个数据
分别为：当前页、共多少页、每页的数据量、总数据数、查询出来的集合

package com.offcn.utils;

import java.util.List;

import com.offcn.entity.Pet;

public class PageUtils {
	private Integer currentPage;
	private Integer totalPage;
	private Integer pageSize;
	private Integer selectAll;
	private List<Pet> petList;
	public Integer getCurrentPage() {
		return currentPage;
	}
	public void setCurrentPage(Integer currentPage) {
		this.currentPage = currentPage;
	}
	public Integer getTotalPage() {
		return totalPage;
	}
	public void setTotalPage(Integer totalPage) {
		this.totalPage = totalPage;
	}
	public Integer getPageSize() {
		return pageSize;
	}
	public void setPageSize(Integer pageSize) {
		this.pageSize = pageSize;
	}
	public Integer getSelectAll() {
		return selectAll;
	}
	public void setSelectAll(Integer selectAll) {
		this.selectAll = selectAll;
	}
	public List<Pet> getPetList() {
		return petList;
	}
	public void setPetList(List<Pet> petList) {
		this.petList = petList;
	}
	public PageUtils(Integer currentPage, Integer totalPage, Integer pageSize,
			Integer selectAll, List<Pet> petList) {
		super();
		this.currentPage = currentPage;
		this.totalPage = totalPage;
		this.pageSize = pageSize;
		this.selectAll = selectAll;
		this.petList = petList;
	}
	public PageUtils() {
		super();
	}
}

9、在WebRoot目录下面新建doIndex.jsp来处理跳转页面
只留下第一行的指令，其他的全部删除

写完后代码

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="com.offcn.utils.PageUtils"%>
<%@page import="com.offcn.entity.Pet"%>
<%@page import="com.offcn.service.impl.PetServiceImpl"%>
<%@page import="com.offcn.service.PetService"%>
<%@page import="org.omg.CORBA.Request"%>
<%@page import="com.sun.xml.internal.ws.client.RequestContext"%>

/* 以下用来处理跳转页面 */
<%

//得到5大元素(当前页，显示页量，总信息数，总页数，显示集合)，获取信息总数，集合需要实例化函数
PetService petService = new PetServiceImpl();
//获取当前页，如果初次进入，值为空，则默认访问第一页
String currentPageStr = request.getParameter("currentPage");
if(currentPageStr == null ){
	currentPageStr = "1";
}
//不等于null则继续赋值访问
Integer currentPage = Integer.parseInt(currentPageStr);
//访问的页量
Integer pageSize = 4;
//总信息数
Integer selectAll = petService.selectAll();
//总页数
Integer totalPage = selectAll%pageSize == 0?selectAll/pageSize : (selectAll/pageSize) + 1;
//显示集合
List<Pet> petList = petService.selectByPage(currentPage, pageSize);
//赋值
PageUtils pageUtils = new PageUtils();
pageUtils.setCurrentPage(currentPage);
pageUtils.setPageSize(pageSize);
pageUtils.setSelectAll(selectAll);
pageUtils.setTotalPage(totalPage);
pageUtils.setPetList(petList);
//把值存进内置对象
request.setAttribute("pageUtils",pageUtils);
//转发到另一个页面。第一个是要转发的路径
request.getRequestDispatcher("index.jsp").forward(request, response);

 %>

10、index.jsp代码

<%@page import="com.offcn.entity.Pet"%>
<%@page import="org.omg.CORBA.Request"%>
<%@page import="com.sun.corba.se.impl.interceptors.RequestInfoImpl"%>
<%@page import="com.offcn.utils.PageUtils"%>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
 <head>
   <base href="<%=basePath%>">
   
   <title>My JSP 'index.jsp' starting page</title>
   <meta http-equiv="pragma" content="no-cache">
   <meta http-equiv="cache-control" content="no-cache">
   <meta http-equiv="expires" content="0">    
   <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
   <meta http-equiv="description" content="This is my page">
   <!--
   <link rel="stylesheet" type="text/css" href="styles.css">
   -->
 </head>

 <body>
  <%
 //对键进行判断，首次进入键为空则返回第一页
 	if(request.getAttribute("pageUtils") == null){
 		request.getRequestDispatcher("doIndex.jsp").forward(request, response);
 		return;
 	}
 	PageUtils pageUtils = (PageUtils)request.getAttribute("pageUtils");
 	//创建表
  %>
      <table border = "1">
   	<tr>
   		<td>编号</td>
   		<td>姓名</td>
   		<td>健康值</td>
   		<td>亲密度</td>
   		<td>品种</td>
   	</tr>
   	<%List<Pet>petList = pageUtils.getPetList();
   	/* 循环输出对应的值 */
   	for(int i = 0;i<petList.size();i++){%>
   		<tr>
   			<td><%=petList.get(i).getId()%></td>
   			<td><%=petList.get(i).getName()%></td>
   			<td><%=petList.get(i).getHealth()%></td>
   			<td><%=petList.get(i).getLove()%></td>
   			<td><%=petList.get(i).getStrain()%></td>
   		</tr>
   	<%} %>
   </table>
   <a href = doIndex.jsp?currentPage=1>首页</a>
   <!-- 如果当前页>1则显示 上一页 -->
   <%if(pageUtils.getCurrentPage()>1){ %>
  		 <a href = doIndex.jsp?currentPage=<%=pageUtils.getCurrentPage()-1%>>上一页</a>
   <%} %>
   <!-- 如果当前页 < 总页数，则显示下一页 -->
   <%if(pageUtils.getCurrentPage()<pageUtils.getTotalPage()) {%>
  		 <a href = doIndex.jsp?currentPage=<%=pageUtils.getCurrentPage()+1%>>下一页</a>
   <%} %>
   <a href = doIndex.jsp?currentPage=<%=pageUtils.getTotalPage()%>>尾页</a>
   <span>当前第<%=pageUtils.getCurrentPage()%>页，共<%=pageUtils.getTotalPage()%>页</span>
 </body>
</html>

11、打开tomcat服务器，输入对应的地址,显示以下效果

有问题可联系QQ ：237680098