用JDBC实现数据库分页

一、所需参数

(1)当前页数(currentPage)

就是来指明当前到底显示到了第几页

(2)每页显示的数目(pageSize)

比如每页显示20条

(3)总记录数(totalcount)

一般可由“selectcount(*) from table_name”获得

(4)总页数(totalPage)

totalPage=((totalCount+pageSize)-1)/pageSize

或者

if(totalCount%pageSize==0)

totalPage=totalCount/pageSize;

else

totalPage=totalCount/pageSize+1;

注意要确保总页数至少为1

if(totalPage==0)

totalPage=1;

(5)JDBC ResultSet类定义结果集的方法rs.absolute(int n);

例如:rs.absolute((currentPage-1)*pageSize);

二、下面给出一个实例

1.建立用mysql数据库及其表person

create database person;
use person;
create table person(id varchar(20),name varchar(20),age varchar(20),sex varchar(20));

2.编写数据库访问公用类DBAccess.java

package com.zxc.struts.bean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBAccess {
	/**
	 * @param args
	 */
	public DBAccess(){
	}
	public static Connection getConnection(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("驱动程序未找到");
			e.printStackTrace();
		}
		String url="jdbc:mysql://localhost:3306/person";
		String user="root";
		String password="123";
		Connection conn=null;
		try {
			conn=DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("数据库连接出错啦!");
			e.printStackTrace();
		}
		 System.out.println("Connection连接成功!");
		return conn;
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
        Connection connection=getConnection();
	}
}

3.编写javabean

(1)Person.java

package com.zxc.struts.bean;
public class Person {
	private String id;
	private String name;
	private int age;
	private String sex;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
}

(2)PageListData.java(封装分页参数以及页面表现层函数的bean)

package com.zxc.struts.bean;
import java.util.ArrayList;
import java.util.Locale;
import javax.servlet.http.HttpServletRequest;
public class PageListData {
	// 保存分页数据对象
	private ArrayList dataArray = null;
	// 记录总数
	private int count = 0;
	// 每页显示记录数
	private int pageSize = 0;
	// 当前页数
	private int page = 1;
	public PageListData() {
		super();
		newDataArray();
	}
	public ArrayList getDataArray() {
		return dataArray;
	}
	public void setDataArray(ArrayList dataArray) {
		this.dataArray = dataArray;
	}
	public void newDataArray() {
		if (dataArray == null)
			dataArray = new ArrayList();
	}
	public void setData(Object data) {
		newDataArray();
		getDataArray().add(data);
	}
	public void setData(int i, Object data) {
		newDataArray();
		getDataArray().add(i, data);
	}
	// 记录总数
	public int getCount() {
		return count;
	}
	public void setCount(int count) {
		this.count = count;
	}
	// 总页数
	public int getPageCount() {
		if(count%pageSize==0){
			return count/pageSize;
		}else{
			return count/pageSize+1;
		}
	}
	// 每页显示 录数
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	// 当前页数
	public int getPage() {
		return page;
	}
	public void setPage(int page) {
		this.page = page;
	}
	/**分页表现层
	 * 封装分页栏函数  必需被包含在某个Form之中
	 * @param request  HttpServletRequest
	 * @return String  pages  当前页号  pageSize  每页显示行数
	 */
	public String getFooter(HttpServletRequest request) {
		String previous = "上一页";
		String next ="下一页";
		StringBuffer pageStr = new StringBuffer("");
		pageStr.append("<center><p class='pages'>");
		int nTotalPages = getPageCount(); //总页数
		int nCurrentPage = getPage();   //当前页
		int nPrevPage = getPage() - 1;   //上一页
		int nNextPage = getPage() + 1;   //下一页
		int first = 0;
		int last = 0;
		if (nCurrentPage > 1){
			pageStr.append("<a onclick='document.getElementById(/"pages/").value=" + nPrevPage + ";document.getElementById(/"pages/").form.submit();' class='prevpage' style="cursor: pointer;" mce_style="cursor: pointer;" ><strong>" + previous +"</strong></a>");
		}
		
		for (int i = 1; i <= nTotalPages; i++) {
			if (i == nCurrentPage){
				pageStr.append("<span style="padding:1px 8px 1px 8px;color:#fff;text-decoration:none!important;background:url(./pgback.png) no-repeat center center;" mce_style="padding:1px 8px 1px 8px;color:#fff;text-decoration:none!important;background:url(pgback.png) no-repeat center center;">" + i + "</span>");
			} else {
				if(nCurrentPage == 1 && (i == 2 || i==3)) {
					pageStr.append("<a id='Pagelist' onclick='document.getElementById(/"pages/").value="+ i +";document.getElementById(/"pages/").form.submit();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> ");
				} else if (i != 1 && nCurrentPage >= 5 && i < nCurrentPage) {
					if(first == 0) {
						pageStr.append("<span class='pgempty'>...</span>");
						first = 1;
					}
					if(nCurrentPage == nTotalPages && i == nTotalPages - 1) {
						pageStr.append("<a id='Pagelist' onclick='document.getElementById(/"pages/").value="+ i +";document.getElementById(/"pages/").form.submit();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> ");
					}
				} else if( i <= nTotalPages && nCurrentPage <= nTotalPages - 4 && i > (nCurrentPage + 1)) {
					if(last == 0) {
						pageStr.append("<span class='pgempty'>...</span>");
						last = 1;
					}
					if(i==nTotalPages){
						pageStr.append("<a id='Pagelist' onclick='document.getElementById(/"pages/").value="+ i +";document.getElementById(/"pages/").form.submit();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> ");
					}
				} else {
					pageStr.append("<a id='Pagelist' onclick='document.getElementById(/"pages/").value="+ i +";document.getElementById(/"pages/").form.submit();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> ");
				}
				
			}
		}
		if (nCurrentPage < nTotalPages)
		{
			pageStr.append("<a class='nextpage'style='cursor: pointer;' onclick='document.getElementById(/"pages/").value=" + nNextPage + ";document.getElementById(/"pages/").form.submit();'><strong>" + next +"</strong></a></center>");
		}
		pageStr.append("</p>");
		pageStr.append("<input type='hidden' value='" + nCurrentPage
						+ "' name='pages' id='pages' />");
		pageStr.append("<input type='hidden' value='" + pageSize
				+ "' name='pageSize' id='pageSize' />");
		return pageStr.toString();
	}
//	public String getPager(HttpServletRequest request) {
//		String previous = "previous";
//		String next = "next";
//	
//		StringBuffer pageStr = new StringBuffer("");
//		pageStr.append("<div id='pager' class='pager'>");
//		pageStr.append("<div id='pagenumber' class='pagedisplay pages'></div>");
//		pageStr.append("<input class='pagesize' type='hidden' value='"+ pageSize
//								+ "' name='pageSize' id='pageSize' />");
//		pageStr.append("<input type='hidden' value='"+ previous
//				+ "' name='labpre' id='labpre' />");
//		pageStr.append("<input type='hidden' value='"+ next
//				+ "' name='labnext' id='labnext' />");
//		pageStr.append("</div>");
//	
//		return pageStr.toString();
//	}
}

4.编写数据访问层ManagerPerson.java

package com.zxc.struts.bean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.zxc.struts.bean.DBAccess;
public class ManagerPerson {
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
        //往数据库插入100条数据
		List list1=new ArrayList();
		for (int i = 1; i < 101; i++) {
			Person person = new Person();
			person.setId(String.valueOf(i));
			person.setName("A");
			person.setAge(i + 20);
			person.setSex("man");
			list1.add(person);
		}
		insertData(list1);
	}
	//返回的map中包含所需的结果集和总的记录数
	public static Map getList(int pageSize,int currentPage){
		String sql="select * from person";
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Map result=null;
		List list=null;
		Person p=null;
		try{
			conn=DBAccess.getConnection();
			result=new HashMap();
			list=new ArrayList();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if((currentPage-1)*pageSize!=0)
			    rs.absolute((currentPage-1)*pageSize);
			int i=0;
			while(rs.next()&&i++<pageSize){
				p=new Person();
				p.setId(rs.getString("id"));
				p.setName(rs.getString("name"));
				p.setSex(rs.getString("sex"));
				p.setAge(Integer.parseInt(rs.getString("age")));
		        list.add(p);
		        
			}
			result.put("list", list);
			rs.close();
			ps.close();
			sql="select count(*) from person";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery(sql);
			if(rs.next()){
			    result.put("tatalcount", rs.getInt(1));
			}
			rs.close();
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return result;
	}
	//将list中的数据插入到数据库中
	public static void insertData(List list){
		String sql="insert into person values(?,?,?,?)";
		Connection conn=DBAccess.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		int n=0;
		for(int i=0;i<list.size();i++){
			try{
				Person p=(Person)list.get(i);
				ps=conn.prepareStatement(sql);
				ps.setString(1, p.getId());
				ps.setString(2, p.getName());
				ps.setString(3, String.valueOf(p.getAge()));
				ps.setString(4,p.getSex());
				n=ps.executeUpdate();
				if(n==1){
					System.out.println("插入一条数据成功！");
				}else{
					System.out.println("插入一条数据失败!");
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		try{
			ps.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	
}

5.编写业务逻辑层PaginationAction.java

/*
 * Generated by MyEclipse Struts
 * Template path: templates/java/JavaClass.vtl
 */
package com.zxc.struts.action;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.zxc.struts.bean.ManagerPerson;
import com.zxc.struts.bean.PageListData;
import com.zxc.struts.bean.Person;

/** 
 * MyEclipse Struts
 * Creation date: 08-10-2010
 * 
 * XDoclet definition:
 * @struts.action validate="true"
 */
public class PaginationAction extends Action {
	/*
	 * Generated Methods
	 */
	/** 
	 * Method execute
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return ActionForward
	 */
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		// TODO Auto-generated method stub
		int currPage = request.getParameter("pages")==null?1:Integer.parseInt(request.getParameter("pages"));
		int pageSize = request.getParameter("pageSize")==null?5:Integer.parseInt(request.getParameter("pageSize"));
		Map map=ManagerPerson.getList(pageSize, currPage);
		PageListData listdata=new PageListData();
		listdata.setCount(Integer.parseInt(map.get("tatalcount")==null?"":map.get("tatalcount").toString()));
		listdata.setPage(currPage);
		listdata.setPageSize(pageSize);
		listdata.setDataArray((ArrayList)map.get("list"));
		request.setAttribute("footer", listdata.getFooter(request));
		System.out.println(listdata.getFooter(request));
		request.setAttribute("listdata", listdata);
        return mapping.findForward("success");
	}
}

6.配置struts-config.properties

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.3//EN" "http://struts.apache.org/dtds/struts-config_1_3.dtd">
<struts-config>
  <form-beans />
  <global-exceptions />
  <global-forwards />
  <action-mappings >
     <action
      path="/pagination"
      type="com.zxc.struts.action.PaginationAction"
      cancellable="true" >
      <forward name="success" path="/pagesort.jsp"/>
      </action>
  </action-mappings>
  <message-resources parameter="com.zxc.struts.ApplicationResources"/>
</struts-config>

7.表现层页面

(1)index.jsp

<%@ page language="java" pageEncoding="UTF-8"%>
<%
	String path = request.getContextPath();
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
	<body>
	<form>
		<a href="<%=path%>/pagination.do">JDBC分页练习</a>
		</form>
	</body>
</html>

(2)pagesort.jsp

<%@ page language="java" isELIgnored="false" pageEncoding="utf-8"%>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c"%>
<%
	String path = request.getContextPath();
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<link href="<%=path%>/tefort.css" type="text/css" rel="stylesheet"/>
<link href="<%=path%>/template01.css" type="text/css" rel="stylesheet"/>
</head>
	<body>
		<form action="<%=path%>/pagination.do" method="post">
          
			<table>
				<thead>
					<tr>
						<td style="width:20%">
							id
						</td>
						<td style="width:30">
							name
						</td>
						<td style="width:25%">
							sex
						</td>
						<td style="width:25%">
							age
						</td>
					</tr>
				</thead>
				<tbody>
					<c:if test="${not empty listdata}">
						<c:forEach items="${listdata.dataArray}" var="data">
						<tr>
						<td>${data.id}</td>
						<td>${data.name}</td>
						<td>${data.age}</td>
						<td>${data.sex }</td>
						</tr>
						</c:forEach>
					</c:if>
				</tbody>
			</table>
			${footer}
		</form>
	</body>
</html>

8.运行结果

在浏览器输入http://localhost:8080/j2eePractice