JSP分页(MySql+c3p0+dbutils)

浏览数：41 / 时间：2015年06月12日

为什么要对数据进行分页？当数据较多时，页面就会变的很庞大，不仅会影响到用户的使用，而且还有加重服务器的负担。下面简单的实现了数据的分页。

第一步：导入相应的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 onclick="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 onclick="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中进行数据库的访问操作，页面的显示也有待美化，待以后完善......

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

JSP分页(MySql+c3p0+dbutils)