JSP分页(MySql+c3p0+dbutils)

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

第一步：导入相应的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))/10int pageCount = (totalRecord + (pageSize - 1)) / pageSize;if(pageNum < 0){pageNum = 1;}if(pageNum > pageCount){pageNum = pageCount;}//0, 10   10, 20int 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中进行数据库的访问操作，页面的显示也有待美化，待以后完善......