对于数据库表的记录太多时,需要进行分页显示。
1:在AdminOperate.java文件中定义两个方法分别获取数据库中表的总记录数(totalrecoders)和特定记录数的集合
public int getTotalRecorders() throws SQLException {
int counts = 0;
Connection conn = DAO.getCon();
String SQL = "select count(*) from user";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(SQL);
rs.next();
counts = rs.getInt(1);
return counts;
}
//选择从第((pagenumber-1)*pagesize+1))条记录开始到((pagenumber-1)*pagesize+1))+pagesize条记录
public List<User> getPageUser(int pagenumber, int pagesize) {
List<User> users = new ArrayList<User>();
ResultSet rs = null;
Connection con = DAO.getCon();
PreparedStatement ps = null;
String SQL = "select * from user limit ?,?";
try {
ps = DAO.getPre(con, SQL);
ps.setInt(1, ((pagenumber-1)*pagesize+1));
ps.setInt(2, pagesize);
rs = ps.executeQuery();
while(rs.next()) {
User u = new User();
u.setAge(rs.getString("age"));
u.setBirth(rs.getString("birth"));
u.setId(rs.getString("id"));
u.setMail(rs.getString("mail"));
u.setName(rs.getString("name"));
u.setSex(rs.getString("sex"));
u.setPassword(rs.getString("password"));
users.add(u);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
ps.close();
con.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return users;
}
2:在表单中显示
<%@ page language="java" import="java.util.*" import="admin.*" import="usermanager.*" pageEncoding="GB18030"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'MyJsp.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<%!
private static final int pagesize = 6;
%>
<%-- 使用<%! %>定义的变量编译成servlet后成为servlet的私有成员变量,
在servlet环境中,对于一个JSP页只允许有一个结果servlet的实例在运行和响应所有的页面请求。
因此,在结果servlet中所有的成员变量可以被所有的请求所共享,所以只要成员变量没有重新赋值,下次访问该页面时仍然不变。
而在<% %>中定义的变量编译成servlet后成为service()方法的一个本地变量,
而service()方法中的本地变量只能每响应一次请求就重建一次。 --%>
<%
//总的页数
int totalpages = 0;
String strpgnb = request.getParameter("pagenumber");
//页码初始化为1
int pagenumber = 1;
if(strpgnb != null && strpgnb != "null") {
pagenumber = Integer.parseInt(strpgnb);
}
AdminOperate ao = new AdminOperate();
//返还总的页数 (totalrecoders+pagesize-1)/pagesize
totalpages = (ao.getTotalRecorders()+pagesize-1)/pagesize;
if(pagenumber>totalpages) {
pagenumber = totalpages;
}
if(pagenumber<=0) {
pagenumber = 1;
}
List<User> list = ao.getPageUser(pagenumber, pagesize);
%>
<center><h1>后台管理</h1></center>
<a href = "admin/adminlogin.jsp">返回</a>
<H2>欢迎你:<%=admin %></H2>
<table align = "center" width= "80%" border = "1">
<tr>
<td>工号</td><td>姓名</td><td>年龄</td><td>出生日期</td><td>性别</td><td>邮箱</td><td>处理</td>
</tr>
<%
if (list != null) {
for(int i=0; i<list.size(); i++) {
User u = list.get(i);
%>
<tr>
<th><%=u.getId() %></th>
<th><%=u.getName()%></th>
<th><%=u.getAge() %></th>
<th><%=u.getBirth() %></th>
<th><%=u.getSex() %></th>
<th><%=u.getMail() %>
<th><a href="deluser?name=<%=u.getName()%>">删除</a>
<a href="modifyuser?name=<%=u.getName() %>">修改</a></th>
</tr>
<%
}
}
%>
</table>
<center>共<%=totalpages%>页
<form action="admin/adminloginsucessful.jsp"> 第<input name="pagenumber" width="10px" type="text"value="<%=pagenumber %>" >页 <input type="submit" value="跳转" ></form>
<a href="admin/adminloginsucessful.jsp?pagenumber=<%=(pagenumber-1)%>">上一页</a>
<a href="admin/adminloginsucessful.jsp?pagenumber=<%=(pagenumber+1)%>">下一页</a>
<a href="admin/adminloginsucessful.jsp?pagenumber=<%=totalpages%>">最后一页</a> </center>
</body>
</html>
运行结果图