为什么要对数据进行分页?当数据较多时,页面就会变的很庞大,不仅会影响到用户的使用,而且还有加重服务器的负担。下面简单的实现了数据的分页。
第一步:导入相应的jar包
需要导入c3p0,dbutils,mysql驱动等jar包。
第二步:创建数据库和表, 配置c3p0, 创建工具类,User类
创建数据库,并准备测试数据(可以自行生成)
create database contacts;
use contacts;
create table users(
id varchar(32),
username varchar(36),
password varchar(36),
constraint user_pk primary key(id)
);
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 默认配置,只可以出现一次 -->
<default-config>
<!-- 连接超时设置30秒 -->
<property name="checkoutTimeout">30000</property>
<!-- 30秒检查一次connection的空闲 -->
<property name="idleConnectionTestPeriod">30</property>
<!--初始化的池大小 -->
<property name="initialPoolSize">2</property>
<!-- 最多的一个connection空闲时间 -->
<property name="maxIdleTime">30</property>
<!-- 最多可以有多少个连接connection -->
<property name="maxPoolSize">10</property>
<!-- 最少的池中有几个连接 -->
<property name="minPoolSize">2</property>
<!-- 批处理的语句
-->
<property name="maxStatements">50</property>
<!-- 每次增长几个连接 -->
<property name="acquireIncrement">3</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">
<![CDATA[jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8]]>
</property>
<property name="user">root</property>
<property name="password">123456</property>
</default-config>
<named-config name="contacts">
<property name="checkoutTimeout">1000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">2</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">5</property>
<property name="minPoolSize">2</property>
<property name="maxStatements">50</property>
<property name="acquireIncrement">3</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">
<![CDATA[jdbc:mysql://127.0.0.1:3306/contacts?useUnicode=true&characterEncoding=UTF-8]]>
</property>
<property name="user">root</property>
<property name="password">123456</property>
</named-config>
</c3p0-config>
DataSourceUtil.java
package cn.zq.util;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DataSourceUtil {
private static DataSource ds;
static{
ds = new ComboPooledDataSource("contacts");
}
public static DataSource getDataSource(){
return ds;
}
public static Connection getConnection() throws SQLException{
return ds.getConnection();
}
}
User.java
package cn.zq.domain;
public class User {
private String id;
private String username;
private String password;
public User() {}
public User(String id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public void setId(String id) {
this.id = id;
}
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + "]";
}
public String getId() {
return id;
}
public String getUsername() {
return username;
}
public String getPassword() {
return password;
}
}
第三步:创建并配置servlet,创建显示页面
UserServlet.java
package cn.zq.servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import cn.zq.domain.User;
import cn.zq.util.DataSourceUtil;
public class UserServlet extends HttpServlet {
public void init() throws ServletException {
try {
Class.forName("cn.zq.util.DataSourceUtil");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//which page to show.
String pn = request.getParameter("pn");
int pageNum = 1;
try{
pageNum = Integer.parseInt(pn);
}catch(Throwable t){
//ignore
}
int pageSize = 10;
QueryRunner run = new QueryRunner(DataSourceUtil.getDataSource());
try {
String sql = "SELECT COUNT(1) from users";
int totalRecord = run.query( sql, new ScalarHandler<Long>() )
.intValue();
System.err.println("totalRecord = " + totalRecord);
//(11 + ( 10 -1))/10
int pageCount = (totalRecord + (pageSize - 1)) / pageSize;
if(pageNum < 0){
pageNum = 1;
}
if(pageNum > pageCount){
pageNum = pageCount;
}
//0, 10 10, 20
int m = (pageNum - 1)*pageSize;
int n = pageSize;
sql = "SELECT * FROM users LIMIT ?, ?";
List<User> userList = run.query(sql, new BeanListHandler<User>(User.class), m, n);
//分页显示多少个页号
int no = 10;
int beginPageIndex = 0;
int endPageIndex = 0;
if(pageCount <= no){
beginPageIndex = 1;
endPageIndex = pageNum;
}else{
beginPageIndex = pageNum - no/2;
endPageIndex = beginPageIndex + (no -1);
if(beginPageIndex < 1){
beginPageIndex = 1;
endPageIndex = no;
}
if(endPageIndex > pageCount){
endPageIndex = pageCount;
beginPageIndex = endPageIndex - (no - 1);
}
}
request.setAttribute("pageCount", pageCount);
request.setAttribute("totalRecord", totalRecord);
request.setAttribute("pageNum", pageNum);
request.setAttribute("beginPageIndex", beginPageIndex);
request.setAttribute("endPageIndex", endPageIndex);
request.setAttribute("userList", userList);
request.getRequestDispatcher("/page/user.jsp")
.forward(request, response);;
} catch (Exception e) {
e.printStackTrace();
}
}
}
web.xml
<servlet>
<servlet-name>UserServlet</servlet-name>
<servlet-class>cn.zq.servlet.UserServlet</servlet-class>
<load-on-startup>2</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>UserServlet</servlet-name>
<url-pattern>/servlet/UserServlet</url-pattern>
</servlet-mapping>
/page/user.jsp
<%@ page 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>My JSP 'index.jsp' starting page</title>
</head>
<body>
<table border="1">
<tr>
<th>INDEX</th>
<th>ID</th>
<th>USERNAME</th>
<th>PASSWORD</th>
</tr>
<c:forEach var="user" items="${userList}" varStatus="stat">
<tr>
<td>${stat.index + 1}</td>
<td>${user.id }</td>
<td>${user.username }</td>
<td>${user.password }</td>
</tr>
</c:forEach>
</table>
<div id="page">
<c:if test="${pageNum != 1 }">
<a href="<c:url value='/servlet/UserServlet?pn=1'/>">首页</a>
<a href="<c:url value='/servlet/UserServlet?pn=${pageNum-1 }'/>"><上一页</a>
</c:if>
<c:forEach begin="${beginPageIndex }" end="${endPageIndex }" step="1" var="num">
<c:choose>
<c:when test="${num != pageNum}">
<a href="<c:url value='/servlet/UserServlet?pn=${num }'/>">${num }</a>
</c:when>
<c:otherwise>
${num }
</c:otherwise>
</c:choose>
</c:forEach>
<c:if test="${pageNum != pageCount}">
<a href="<c:url value='/servlet/UserServlet?pn=${pageNum+1 }'/>">下一页></a>
<a href="<c:url value='/servlet/UserServlet?pn=${pageCount}'/>">尾页</a>
</c:if>
<input id="pn" type="text" name="pn" size="4"/> <button οnclick="go()">Go</button>
当前第${pageNum }页,总共${pageCount }页,共条${totalRecord }记录
<script>
function go(){
var input = document.getElementById("pn");
if(isNaN(input.value) || input.value.indexOf(".") != -1){
alert("请输入整数!");
}else if(input.value < 1
|| input.value > ${pageCount}){
alert("请输入1到${pageCount}之间的整数");
}else{
window.location.href = "<c:url value='/servlet/UserServlet?pn=' />"+input.value;
}
input.value = "";
input.focus();
}
</script>
</div>
</body>
</html>
启动tomcat并访问:
小结:通过上面的代码能基本的实现数据的分页显示,但是数据显示比较的零散,应该将上面的数据进行封装再传递到页面进行显示(java对数据进行封装很重要,不然这些数据显得彼此之间都没有关系),为了方便代码的重要应该对数据进行封装。
改造后的代码如下:
Page.java
package cn.zq.domain;
import java.util.Collection;
public class Page {
//每页显示多少条记录
private int pageSize = 10;
//显示多少个页号
private int no = 10;
//总记录数
private int totalRecord;
//分页数
private int pageCount;
//当前显示的页号
private int pageNum;
//分页起始页号
private int beginPageIndex;
//分页结束页号
private int endPageIndex;
//存放数据
private Collection cs;
/**
*
* @param pageNum 页号
* @param totalRecord 总记录数
* @param cs beans
*/
public Page(int pageNum, int totalRecord, Collection cs){
this.pageNum = pageNum;
this.totalRecord = totalRecord;
this.cs = cs;
//计算分页数
this.pageCount = (totalRecord + (pageSize - 1))/pageSize;
if(this.pageNum < 0){
this.pageNum = 1;
}else if(this.pageNum > pageCount){
this.pageNum = pageCount;
}
//计算开始页号和结束页号
if(pageCount <= no){
beginPageIndex = 1;
endPageIndex = pageCount;
}else{
beginPageIndex = (pageNum - no/2) + 1;
endPageIndex = beginPageIndex + (no-1);
if(beginPageIndex < 1){
beginPageIndex = 1;
endPageIndex = no;
}
if(endPageIndex > pageCount){
endPageIndex = pageCount;
beginPageIndex = endPageIndex - (no - 1);
}
}
}
public int getPageSize() {
return pageSize;
}
public void setCs(Collection cs) {
this.cs = cs;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public void setNo(int no) {
this.no = no;
}
public int getNo() {
return no;
}
public int getTotalRecord() {
return totalRecord;
}
public int getPageCount() {
return pageCount;
}
public int getPageNum() {
return pageNum;
}
public int getBeginPageIndex() {
return beginPageIndex;
}
public int getEndPageIndex() {
return endPageIndex;
}
public Collection getCs() {
return cs;
}
}
/page/user.jsp
<%@ page 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>My JSP 'index.jsp' starting page</title>
</head>
<body>
<table border="1">
<tr>
<th>INDEX</th>
<th>ID</th>
<th>USERNAME</th>
<th>PASSWORD</th>
</tr>
<c:forEach var="user" items="${page.cs}" varStatus="stat">
<tr>
<td>${stat.index + 1}</td>
<td>${user.id }</td>
<td>${user.username }</td>
<td>${user.password }</td>
</tr>
</c:forEach>
</table>
<div id="page">
<c:if test="${page.pageNum != 1 }">
<a href="<c:url value='/servlet/UserServlet?pn=1'/>">首页</a>
<a href="<c:url value='/servlet/UserServlet?pn=${page.pageNum-1 }'/>"><上一页</a>
</c:if>
<c:forEach begin="${page.beginPageIndex }" end="${page.endPageIndex }" step="1" var="num">
<c:choose>
<c:when test="${num != page.pageNum}">
<a href="<c:url value='/servlet/UserServlet?pn=${num }'/>">${num }</a>
</c:when>
<c:otherwise>
${num }
</c:otherwise>
</c:choose>
</c:forEach>
<c:if test="${page.pageNum != page.pageCount}">
<a href="<c:url value='/servlet/UserServlet?pn=${page.pageNum+1 }'/>">下一页></a>
<a href="<c:url value='/servlet/UserServlet?pn=${page.pageCount}'/>">尾页</a>
</c:if>
<input id="pn" type="text" name="pn" size="4"/> <button οnclick="go()">Go</button>
当前第${page.pageNum }页,总共${page.pageCount }页,共条${page.totalRecord }记录
<script>
function go(){
var input = document.getElementById("pn");
if(isNaN(input.value) || input.value.indexOf(".") != -1){
alert("请输入整数!");
}else if(input.value < 1
|| input.value > ${page.pageCount}){
alert("请输入1到${page.pageCount}之间的整数");
}else{
window.location.href = "<c:url value='/servlet/UserServlet?pn=' />"+input.value;
}
input.value = "";
input.focus();
}
</script>
</div>
</body>
</html>
总结:根据一定的算法,对数据进行分页处理,上面只是给出了实例代码,个人可以根据实际的需求给出自己的算法。上面的代码还有很多地方需要优化,比如:查询总记录数和数据,应该通过service层来获取,而不应该直接在servleyt中进行数据库的访问操作,页面的显示也有待美化,待以后完善......