JSP Paging (Mysql+c3p0+dbutils)

Why do you want to page out data? When the data is more, the page will become very large, not only affect the user's use, but also the burden of aggravating the server. The following is a simple implementation of the data paging.

First step: Import the appropriate jar package

You need to import jar packages such as c3p0,dbutils,mysql drivers.

Step two: Create a database and table, configure C3P0, create a tool class, user class

Create the database and prepare the test data (you can build it yourself)

Create DATABASE Contacts;use contacts;create table users (ID varchar (+), username varchar, password varchar (36), Constraint USER_PK primary key (ID));

C3p0-config.xml

<?xml version= "1.0" encoding= "UTF-8"?><c3p0-config><!--default configuration, can only occur once--><default-config> <!--connection Timeout setting 30 seconds--><property name= "Checkouttimeout" >30000</property><!--30 seconds Check Connection idle- -><property name= "Idleconnectiontestperiod" >30</property><!--initialized pool size--><property name= " Initialpoolsize ">2</property><!--up to one connection idle time--><property name=" MaxIdleTime ">30< /property><!--can have up to how many connections connection--><property name= "Maxpoolsize" >10</property><!-- The fewest pools have several connections--><property name= "minpoolsize" >2</property><!--batch of statements--><property Name= " Maxstatements ">50</property><!--each growth several connections--><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;}}

Step three: Create and configure a servlet to create a display page

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 the users"; int total Record = run.query (sql, New Scalarhandler<long> ()). Intvalue (); System.err.println ("Totalrecord =" + Totalrecord);//(one + (10-1))/10int PageCount = (Totalrecord + (pageSize-1))/PA Gesize;if (Pagenum < 0) {pagenum = 1;} if (Pagenum > PageCount) {pagenum = PageCount;} 0, ten, 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);//pagination shows how many page numbers 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 ();}}}

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" >
Launch Tomcat and access:
Summary: Through the above code can basically achieve the paging display of data, but the data display is fragmented, the above data should be encapsulated and then passed to the page to display (Java to encapsulate the data is very important, otherwise the data does not seem to be related to each other), In order to facilitate the importance of the code, the data should be encapsulated.

The modified code is as follows:
Page.java
Package Cn.zq.domain;import Java.util.collection;public Class Page {//each page shows how many records private int pageSize = 10;// Shows how many page numbers private int no = 10;//total records private int totalrecord;//pages private int pagecount;//The page number that is currently displayed private int pagenum;//  Page start page number private int beginpageindex;//page end number private int endpageindex;//store data Private Collection cs;/** * * @param pagenum Page number * @param totalrecord Total records * @param cs beans */public Page (int pagenum, int totalrecord, Collection cs) {this.pagenum = Pagen Um;this.totalrecord = Totalrecord;this.cs = cs;//calculates the number of pages This.pagecount = (Totalrecord + (pageSize-1))/pagesize;if ( This.pagenum < 0) {this.pagenum = 1;} else if (This.pagenum > PageCount) {this.pagenum = PageCount;} Calculate Start and End page numbers 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" >
Summary: According to a certain algorithm, the data paging processing, above just give the instance code, the individual can according to the actual needs to give their own algorithm. There are many areas of the above code to optimize, such as: Query total records and data, should be obtained through the service layer, and should not be directly in the SERVLEYT database access operations, the page display also needs to be beautified, to be perfected later ...


JSP Paging (Mysql+c3p0+dbutils)