实现上图的分页功能，每页显示3条数据，每页显示3页供用户访问，点击左右双箭头时，可以跳转至上一个或者下一个大页，如点击右双箭头显示如下：
1 连接数据库的工具类
package com.latiny.db; import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties; /*
*操作数据库的工具类
*/ public class DBUtil { //定义需要的变量
private static String driver =null;
private static String url =null;
private static String user=null;
private static String password=null; private static Connection conn;
//使用PreparedStatment可以防止sql注入
private static PreparedStatement ps;
private static ResultSet rs;
private static CallableStatement cs; //读配置文件
private static Properties pp=null;
private static InputStream fis=null; //加载驱动，只需要执行一次
static
{
try
{
pp = new Properties(); //当我们使用java web的时候，读取文件要使用类加载器
fis = DBUtil.class.getClassLoader().getResourceAsStream("dbinfo.properties"); pp.load(fis);
driver = pp.getProperty("DRIVER");
url = pp.getProperty("URL");
user = pp.getProperty("USER");
password = pp.getProperty("PASSWORD"); // 1 加载驱动
Class.forName(driver); }
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try
{
fis.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
fis = null;
}
} /*
* 获取Connection连接
*/
public static Connection getConn()
{
try
{
// 2 获取数据库连接
conn = DriverManager.getConnection(url, user, password);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} return conn;
} /*
* 直接返回rs结果，此方法不能关闭rs，因为后面调用它的类还会用到，如果关闭则不能正常使用
*/
public static ResultSet queryResult(String sql, String[] parameters)
{
try
{
conn = getConn();
// 3 创建Statement对象
ps = conn.prepareStatement(sql);
// 4 给问号赋值，即给sql语句的条件参数赋值如果需要的话
if(parameters!=null)
{
for(int i=1; i<=parameters.length; i++)
{
ps.setString(i, parameters[i-1]);
}
} // 5 执行sql获取返回结果
rs = ps.executeQuery();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} return rs;
} /*
* 将rs结果封装成ArrayList，然后可以关闭rs，节省数据库访问资源
*/
public static ArrayList queryResult2(String sql, String[] parameters)
{
ArrayList al = new ArrayList(); try
{
//2 获取数据库连接
conn = getConn();
//3 创建Statement对象
ps = conn.prepareStatement(sql); //4 给问号赋值，即给sql语句的条件参数赋值如果需要的话
if(parameters!=null)
{
for(int i=1; i<=parameters.length; i++)
{
ps.setString(i, parameters[i-1]);
}
} //5 执行sql语句获取返回结果
rs = ps.executeQuery(); //获取rs的结构
ResultSetMetaData rsmd = rs.getMetaData();
//获取查询语句的列数
int column = rsmd.getColumnCount(); while(rs.next())
{
//对象数组，存储一行数据
Object[] objs = new Object[column];
for(int i=0; i<objs.length; i++)
{
objs[i] = rs.getObject(i+1);
}
al.add(objs);
} }
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
//关闭资源
close(rs, ps, conn);
} return al;
} //调用存储过程，带输入输出参数的
public static CallableStatement callProcedure(String sql, String[] inputPara, Integer[] outputPara)
{ try
{
conn = getConn();
cs = conn.prepareCall(sql);
for(int i=0; inputPara!=null && i<inputPara.length; i++)
{
cs.setObject(i+1, inputPara[i]);
} //给output参数赋值
for(int j=0; outputPara!=null && j<outputPara.length; j++)
{
cs.registerOutParameter(inputPara.length+1+j, outputPara[j]);
} cs.execute(); } catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
close(rs, ps, conn);
} return cs; } //update, insert, delete
public static Integer updateData(String sql, String[] parameters)
{
int result = 0;
try
{
conn = getConn();
ps = conn.prepareStatement(sql);
if(parameters!=null)
{
for(int i=0; i<parameters.length; i++)
{
ps.setObject(i+1, parameters[i]);
}
} //执行executeUpdate并且返回受影响的行数
result = ps.executeUpdate(); }
catch(Exception e)
{
e.printStackTrace();
}
finally
{
close(rs, ps, conn);
} return result;
} //关闭对应的数据库连接资源
public static void close(ResultSet rs1, PreparedStatement ps1, Connection conn1)
{ try
{
if(rs1!=null)
{
rs1.close();
}
if(ps1!=null)
{
ps1.close();
}
if(conn1!=null)
{
conn1.close();
} } catch (SQLException e) {
e.printStackTrace();
} }
}
2 从数据库获取需要的数据用于计算与显示
package com.latiny.dao; /*
* 从数据库获取页面需要的数据：1 记录总数； 2 获取每页需要显示的数据
*/ import java.sql.ResultSet;
import java.util.ArrayList;
import com.latiny.db.DBUtil;
import com.latiny.model.Users; public class UserDao { private ArrayList al; public ArrayList<Users> getUserByPage(int startIndex, int pageSize)
{
ArrayList<Users> array = new ArrayList<Users>();
//查询sql
String sql="select * from users order by user_id limit "+startIndex +","+pageSize;
ResultSet rs = DBUtil.queryResult(sql, null); ArrayList al2 = DBUtil.queryResult2(sql, null); //二次封装，将rs结果封装到array中
for(int i=0; i<al2.size(); i++)
{
Users user = new Users();
Object[] objs = (Object[])al2.get(i); //user.setUserId(Integer.parseInt(objs[0].toString()));
user.setUserId((Integer)objs[0]);
user.setUser_name((String)objs[1]);
user.setPasswd((String)objs[2]);
user.setEmail((String)objs[3]);
user.setGrade(Byte.parseByte(objs[4].toString())); //将user放入到array中
array.add(user);
}
return array;
} public int getTotalRows()
{
int rowCount=0; String sql="select count(1) from users";
al = DBUtil.queryResult2(sql, null); //注意需要将游标下移，之后才能获取到查询结果
try
{
Object[] obj = (Object[])al.get(0);
rowCount = Integer.parseInt(obj[0].toString()); //获取数据库总的记录数 } catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} return rowCount;
}
}
3 通用分页类
package com.latiny.bean;
import java.util.List;
public class PageBean<T> {
//已知数据
private int currentPage; //当前页
private int pageSize; //每页显示的数据条数
private int totalRecord; //总的记录条数，查询数据库得到
private int pageNum; //当前页显示可访问的页数，如当前页面显示1,2,3,4,5页，则pageNum应设为5
//根据已知数据计算
private int totalPage; //小页总的页数
private int totalBigPage; //大页的总页数
//保存每页要显示的数据
private List<T> list;
//查询数据库的开始索引
private int startIndex;
//分页显示的页数，如当前页面显示1,2,3,4,5页，则start为1， end为5.
private int start;
private int end;
int currentBigPage = 1; //当前所在大页序号
int pageBigCount = 0; //共有多少大页数
public PageBean(int currentPage, int currentBigPage, int pageSize, int totalRecord, int pageNum)
{
this.currentPage = currentPage;
this.currentBigPage = currentBigPage;
this.pageSize = pageSize;
this.totalRecord = totalRecord;
//根据totalRecord 与 pageSize 计算总页数
totalPage = (this.totalRecord-1)/this.pageSize+1;
totalBigPage = (totalPage-1)/pageNum+1;
startIndex = (currentPage-1)*pageSize;
//根据小页数计算大页数(每页显示多少页可供用户选择跳转)
pageBigCount = (totalPage-1)/pageNum+1;
start = (currentBigPage-1)*pageNum+1;
end = currentBigPage*pageNum<totalPage?currentBigPage*pageNum:totalPage;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getPageBigCount() {
return pageBigCount;
}
public void setPageBigCount(int pageBigCount) {
this.pageBigCount = pageBigCount;
}
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
public int getStartIndex() {
return startIndex;
}
public void setStartIndex(int startIndex) {
this.startIndex = startIndex;
}
public int getStart() {
return start;
}
public void setStart(int start) {
this.start = start;
}
public void setEnd(int end) {
this.end = end;
}
public int getEnd() {
return end;
}
public int getTotalBigPage() {
return totalBigPage;
}
public void setTotalBigPage(int totalBigPage) {
this.totalBigPage = totalBigPage;
}
public int getCurrentBigPage()
{
return currentBigPage;
}
}
4 封装数据对象类
package com.latiny.model;
public class Users {
private int userId;
private String userName;
private String passwd;
private String email;
private byte grade;
public Users()
{
}
public Users(int userId, String userName, String passwd, String email, byte grade)
{
this.userId = userId;
this.userName = userName;
this.passwd = passwd;
this.email = email;
this.grade = grade;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUser_name() {
return userName;
}
public void setUser_name(String user_name) {
this.userName = user_name;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public byte getGrade() {
return grade;
}
public void setGrade(byte grade) {
this.grade = grade;
}
}
5 Serivce层 结合dao 层与Page工具类
package com.latiny.service; import java.util.List; import com.latiny.dao.UserDao;
import com.latiny.model.Page;
import com.latiny.model.Users; public class UserService { UserDao userDao = new UserDao(); public Page pageUsers(int currentPage, int pageBigCurrent, int pageSize, int pageNum){ int totalRows = userDao.getTotalRows();
//根据页面传递的参数初始化page对象
Page<Users> page = new Page<Users>(currentPage, pageBigCurrent, pageSize, totalRows, pageNum);
//将要显示的数据赋值给Page 的list属性
List<Users> users = userDao.getUserByPage(page.getStartIndex(), pageSize);
page.setList(users); return page;
}
}
6 Servlet 显示分页结果
package com.latiny.view; import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.latiny.model.Page;
import com.latiny.model.Users;
import com.latiny.service.UserService; public class ManagerUser2 extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter(); out.println("<script type='text/javascript' languge='javascript'>");
out.println("function gotoPageCurrent(){" +
"var pageCurrent=document.getElementById('pageCurrent'); " +
//"window.alert('pageCurrent='+pageCurrent.value);" +
"window.open('/Page/ManagerUser2?pageCurrent='+pageCurrent.value+'&pageBigCurrent='+parseInt(((pageCurrent.value-1)/3+1)),'_self');}" +
"function confirmOper()" +
"{return window.confirm('确认删除该用户？'); }");
out.println("</script>"); //当前页
int pageCurrent=1;
//当前页每页显示数据条数
int pageSize = 3;
//当前页显示多少页可供用户点击访问
int pageNum = 3;
//当前大页
int pageBigCurrent = 1; //获取用户点击的页数(用户想要跳转到的页数)，用户点击之后传递过来
String sPageCurrent = request.getParameter("pageCurrent"); if(sPageCurrent!=null)
{
pageCurrent = Integer.parseInt(sPageCurrent);
} //获取用户点击的大页数(下一个大页界面)
String sPageCurrent2 = request.getParameter("pageBigCurrent");
if(sPageCurrent2!=null)
{
pageBigCurrent = Integer.parseInt(sPageCurrent2);
} System.out.println(pageBigCurrent);
System.out.println(pageCurrent+" "+ pageBigCurrent+" ");
UserService useSer = new UserService();
Page page = useSer.pageUsers(pageCurrent, pageBigCurrent, pageSize, pageNum); //返回一个页面(html技术)
out.println("<html><br/>");
out.println("<image src='images/logo1.jpg' /><hr/>");
out.println("<head><br/>"+
"<title>ManagerUser</title> <br/>"+
"</head> <br/>");
out.println("<body> <br/>");
out.println("<h1>用户管理 </h1> <a href='/Page/OperUser?&type=goAddUser'>添加用户</a>"); //定义一个table来显示数据
out.println("<table border=1 width=500px>");
//表头
out.println("<tr><th>id</th><th>用户名</th><th>email</th><th>等级</th><th>删除</th><th>修改</th></tr>"); //5 根据结果作处理
List<Users> array = page.getList();
for(Users u: array)
{
out.println("<tr><td>"+u.getUserId()+"</td>" +
"<td>"+u.getUser_name()+"</td>" +
"<td>"+u.getEmail()+"</td>" +
"<td>"+u.getGrade()+"</td> " +
"<td><a onClick='return confirmOper();' href='/Page/OperUser?userId="+u.getUserId()+"&type=del"+" '>删除</a> </td>" +
"<td><a href='/Page/OperUser?type=gotoUpView&userId="+u.getUserId()+"'>修改</a> </td>" +
"</tr>");
}
out.println("</table><br/>"); //上一大页超链接
if(pageBigCurrent>1)
{
out.println("<a href='/Page/ManagerUser2?pageBigCurrent=" +(pageBigCurrent-1)+"&pageCurrent="+((pageBigCurrent-2)*pageNum+1)+"' style='text-decoration:none'> << </a>"+" ");
} //显示分页选项
for(int i=page.getStart(); i<=page.getEnd();i++)
{
//此处需要传递两个参数到下一个分页页面，一个是小当前小页数，一个是当前大页数
out.println("<a href='/Page/ManagerUser2?pageCurrent="+i+"&pageBigCurrent="+pageBigCurrent+"'>"+i+"</a> ");
} //下一大页超链接
if(pageBigCurrent<page.getTotalBigPage())
{
//此处需要传递两个参数，一个是下一个大页数，一个是下一个大页数的开始小页数
out.println("<a href='/Page/ManagerUser2?pageBigCurrent=" +(pageBigCurrent+1)+"&pageCurrent="+((pageBigCurrent)*pageNum+1)+"' style='text-decoration:none'>>></a>"+" ");
} //显示当前页/总页数
out.println("当前页"+pageCurrent+"/总页数"+page.getTotalPage()+"<br/>");
out.println("跳转：<input type='text' id='pageCurrent' maxlength='6' name='pageCurrent'/><input type='button' value='跳' onClick='gotoPageCurrent()' /> <br/>"); out.println("pageCurrent="+pageCurrent+" & "+"pageCurrent2="+pageBigCurrent);
out.println("<br/><br/>");
out.println("<a href='/Page/MainFrame'>返回主页</a>");
out.println("</body> <br/>");
out.println("</html>"); } public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { this.doGet(request, response);
} }