分页技术在web开发中必不可少,一些成熟的框架已经将分页技术封装好,直接用就可以了。这里写一下分页技术的基础,JSP+Servlet+c3p0+mysql。
数据库
建立如图所示的表t_emp,有三个字段:empId,empName,deptId。
项目构建
项目采用c3p0连接池和DButils工具,前台使用JSP,业务逻辑控制使用Servlet。
工具类
代码:
package utils;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* JDBCUtils工具类
* @author 90948
*/
public class JdbcUtil {
/**
* 初始化c3p0连接池
*/
private static DataSource dataSource = null;
static {
dataSource = new ComboPooledDataSource();
}
/**
* 创建DbUtils核心类
* 如果在创建QueryRunner对象时传入了数据源,那么在使用QueryRunner对象方法时候就不需要
* 使用连接对象,也不用显示关闭数据库连接对象,由连接池管理。
*/
public static QueryRunner getQueryRuuner() {
return new QueryRunner(dataSource);
}
}
这里使用了c3p0连接池和DBUtil组件,c3p0连接池的配置文件:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/db_page</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">5</property>
<property name="minPoolSize">1</property>
<property name="acquireIncrement">2</property>
</default-config>
</c3p0-config>
分页Bean
package pojo;
import java.util.List;
/**
* 分页实体
* @author 90948
*/
public class PageBean<T> {
private int currentPage = 1; //当前页
private int pageCount = 4; //每页显示的行数
private int totalCount;//总记录数
private int totalPage; //总页数=总记录数/每页显示的行数,有余数则+1
private List<T> pageData;
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 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 List<T> getPageData() {
return pageData;
}
public void setPageData(List<T> pageData) {
this.pageData = pageData;
}
}
这里需要注意的是总页数的计算方法:总页数=总记录数/每页显示的行数,有余数则+1
Employee Bean
package pojo;
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;
}
}
普通的员工Bean,没有特殊的地方。
Dao层
接口:
package dao;
import java.sql.SQLException;
import pojo.Employee;
import pojo.PageBean;
public interface IEmployeeDao {
/**
* 分页查询数据
* @param pb
*/
void getAll(PageBean<Employee> pb) throws SQLException;
/**
* 获取总记录数
*/
int getTotalCount() throws SQLException;
}
实现类:
package 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 pojo.Employee;
import pojo.PageBean;
import utils.JdbcUtil;
import dao.IEmployeeDao;
public class EmployeeDaoImpl implements IEmployeeDao {
@Override
public void getAll(PageBean<Employee> pb) throws SQLException {
//获取当前页,计算查询的起始行和返回的行数
int currentPage = pb.getCurrentPage();
int start = (currentPage - 1) * pb.getPageCount();
int count = pb.getPageCount();
//查询总记录数
int totalCount = this.getTotalCount();
pb.setTotalCount(totalCount);
//分页查询数据,将数据存储到pb当中
String sql = "select * from t_emp limit ?,?";
QueryRunner qr = JdbcUtil.getQueryRuuner();
List<Employee> pageData = qr.query(sql, new BeanListHandler<Employee>(
Employee.class), start, count);
pb.setPageData(pageData);
}
@Override
public int getTotalCount() throws SQLException {
String sql = "select count(*) from t_emp";
QueryRunner qr = JdbcUtil.getQueryRuuner();
Long count = qr.query(sql, new ScalarHandler<Long>());
return count.intValue();
}
}
Service层
接口:
package service;
import java.sql.SQLException;
import pojo.Employee;
import pojo.PageBean;
public interface IEmployeeService {
/**
* 分页查询数据
* @param pb
*/
void getAll(PageBean<Employee> pb) throws SQLException;
}
实现类:
package service.impl;
import java.sql.SQLException;
import pojo.Employee;
import pojo.PageBean;
import service.IEmployeeService;
import dao.IEmployeeDao;
import dao.impl.EmployeeDaoImpl;
public class EmployeeServiceImpl implements IEmployeeService {
private IEmployeeDao dao = new EmployeeDaoImpl();
@Override
public void getAll(PageBean<Employee> pb) throws SQLException {
dao.getAll(pb);
}
}
Servlet
package servlet;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import pojo.Employee;
import pojo.PageBean;
import service.IEmployeeService;
import service.impl.EmployeeServiceImpl;
public class IndexServlet extends HttpServlet {
private IEmployeeService service = new EmployeeServiceImpl();
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//获取当前页,第一次访问当前也参数为空
String currentPage = request.getParameter("currentPage");
if (currentPage == null || "".equals(currentPage.trim())) {
currentPage = "1";//第一次访问,当前页为1
}
int currPage = Integer.parseInt(currentPage);
//创建PageBean对象
PageBean<Employee> pageBean = new PageBean<Employee>();
pageBean.setCurrentPage(currPage);
//调用Service方法
try {
service.getAll(pageBean);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//将PageBean设置到request域中
request.setAttribute("pageBean", pageBean);
//跳转
request.getRequestDispatcher("/WEB-INF/list.jsp").forward(request,
response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
JSP
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!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">
<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>
<table border="1" align="center" cellpadding="5" cellspacing="0" width="80%">
<tr>
<th align="center">序号</th>
<th align="center">员工编号</th>
<th align="center">员工姓名</th>
</tr>
<c:choose>
<c:when test="${not empty requestScope.pageBean.pageData }">
<c:forEach var="emp" items="${requestScope.pageBean.pageData }" varStatus="vs">
<tr>
<td align="center">${vs.count }</td>
<td align="center">${emp.empId }</td>
<td align="center">${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">
<span>当前${requestScope.pageBean.currentPage }/${requestScope.pageBean.totalPage }页</span>
<c:if test="${requestScope.pageBean.currentPage != 1 }">
<a href="${pageContext.request.contextPath }/IndexServlet?currentPage=1">首页</a>
<a href="${pageContext.request.contextPath }/IndexServlet?currentPage=${requestScope.pageBean.currentPage-1}">上一页</a>
</c:if>
<c:if test="${requestScope.pageBean.currentPage != requestScope.pageBean.totalPage }">
<a href="${pageContext.request.contextPath }/IndexServlet?currentPage=${requestScope.pageBean.currentPage+1}">下一页</a>
<a href="${pageContext.request.contextPath }/IndexServlet?currentPage=${requestScope.pageBean.totalPage}">末页</a>
</c:if>
</td>
</tr>
</table>
</body>
</html>
页面展示1:
页面展示2:
页面展示3: