因为分页查询很重要,所以另外开一篇来总结了
数据多的时候,需要分页显示
1.要根据需求来分批量地查询数据(而不是返回所有结果)
如:查询100条,我们可以查询10次,每次返回10条
那么要怎么设置参数来返回某一页的数据呢?
不同的数据库支持的方式不一样,
mysql 支持用limit , oracel自己查询一下吧
limit后面整数的含义:第一个是索引值,从0开始,第二个是每页需要查询的数量,当前默认是3
1.在EmployeeDAO那里新增一个方法,
每次只查询一页
方法的参数是员工名,用户名,注册状态和从哪开始,以及查询多少条(end这里的命名不好)
/**
* 方法七:分页查询
* 根据姓名、用户名、状态, 从哪开始,count是多少行的意思。
* @param employeename
* @param username
* @param status
* @param start
* @param count
* @return
*/
public List<Employee> selectEmployeesOfOnePage(String employeename,String username,String status,int start,int count){
conn=ConnectionFactory.getConnection();
List<Employee> employeeslist=new ArrayList<Employee>();
Employee employee=null;
try {
PreparedStatement st=null;
String sql=null;
String usernamesql,employeenamesql,statussql;
//这里三个查询条件可以任意为空
if(employeename==null||employeename.equals("")){
employeenamesql="";
}else{
employeenamesql=" and employeename='"+employeename+"'";
}
if(username==null||username.equals("")){
usernamesql="";
}else{
usernamesql=" and username='"+username+"'";
}
if(status==null||status.equals("")||status.equals("3")){
statussql="";
}else{
statussql=" and status='"+status+"'";
}
//limit是MySQL中用来分页查询的,第一个int参数表示开始的索引,从0开始,第二个参数表示要查询的条数
//role为2表示是员工
sql="select * from Employee where role='2' "+usernamesql+employeenamesql+statussql+" limit "+start+
" ,"+count;
st = conn.prepareStatement(sql);
ResultSet rs =st.executeQuery(sql);
while(rs.next()){
//把结果集放在employee那
employee=new Employee();
employee.setEmployeeid(rs.getInt("employeeid"));
employee.setEmployeename(rs.getString("employeename"));
employee.setUsername(rs.getString("username"));
employee.setPhone(rs.getString("phone"));
employee.setEmail(rs.getString("email"));
employee.setStatus(rs.getString("status"));
employee.setDepartmentid(rs.getInt("departmentid"));
employee.setPassword(rs.getString("password"));
employee.setRole(rs.getString("role"));
employeeslist.add(employee);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
ConnectionFactory.closeConnection();
}
return employeeslist;
}
2.修改EmployeeService增加三个变量,保存关键数据
// 保存页数
private int countOfPages;
// 保存所有记录数量
private int countOfEmployees;
// 保存每一页记录数
private int pageSize=3;
修改searchEmployee方法,获得记录数量
原方法:
修改后的方法:
新增方法:
// 查询每一页的数据集合
public List<Employee> searchEmployeesOfOnePage(String employeename,String username,String status,int start,int count){
return dao.selectEmployeesOfOnePage(employeename, username, status, start, count);
}
// 返回总页数
public int getCountOfPages(){
//查询页数是,如果总数(countOfEmployees)除以每一页的记录数,如果除数不为0,也就是多了东西,就页数加一
countOfPages=(countOfEmployees%pageSize==0)?countOfEmployees/pageSize:countOfEmployees/pageSize+1;
return this.countOfPages;
}
// 返回所有记录条数
public int getCountOfEmployees(){
return this.countOfEmployees;
}
// 返回每页的记录条数,默认为3
public int getPageSize(){
return this.pageSize;
}
3.修改SearchEmployeeServlet (变化较大,以前是所有都取出来,现在是根据页码取出来)
package com.meeting.servlet;
import java.io.IOException;
import java.io.PrintWriter;
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.meeting.dao.EmployeeDAO;
import com.meeting.service.EmployeeService;
import com.meeting.vo.Employee;
/**
* 搜索员工功能的
* @author Administrator
*
*/
public class SearchEmployeesServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 取出前端的参数,获取输入的查询条件:用户名,账户名,注册状态
String employeename = request.getParameter("employeename");
String username = request.getParameter("username");
String status = request.getParameter("status");
//调用业务逻辑,得到结果集,存到请求中,跳转到JSP页面的过程
EmployeeService service = new EmployeeService();
//当前页码 假设当前页码是三,那么就会显示第三页,那么我们需要知道它是从第几条记录开始
String pageNumStr=request.getParameter("pageNum");
int pageNum=0;
if(pageNumStr==null||pageNumStr.equals("")){
pageNum=1;
}else{
pageNum=Integer.parseInt(pageNumStr);
}
// 每页的记录数量
int pageSize=service.getPageSize();
// 起始记录索引 三页,每页三条,那么起始记录的是6
int start=(pageNum-1)*pageSize;
// 查询的数量=每一页的数量
int count=pageSize;
// 获得所有记录数量,先调用DAO中的search方法
service.searchEmployees(employeename, username, status);
int countOfEmployees=service.getCountOfEmployees();
// 页数
int countOfPages=service.getCountOfPages();
//调用searchEmployee
//List<Employee> list = service.searchEmployees(employeename, username, status);
//调用这个方法去取出值出来
List<Employee> list=service.searchEmployeesOfOnePage(employeename, username, status,start,count);
//得到一个集合赋给Employeelist,然后把它存到searchemployees.jsp
request.setAttribute("employeesList", list);
//这里存了个属性为1的search
request.setAttribute("search", "1");
//存储页数、所有记录的数量、当前页码 存成属性
request.setAttribute("countOfPages", countOfPages);
request.setAttribute("countOfEmployees", countOfEmployees);
request.setAttribute("pageNum", pageNum);
request.getRequestDispatcher("searchemployees.jsp").forward(request, response);
}
}
4.修改searchEmployee.jsp
<%@ page language="java"
import="java.util.*,com.chinasofti.meeting.vo.*" pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<title>CoolMeeting会议管理系统</title>
<link rel="stylesheet" href="styles/common03.css" />
<style type="text/css">
</style>
<script type="text/javascript">
function goToOnePage(employeename,username,status) {
var pageNum=document.getElementById("pageNum").value;
if(pageNum==""){
window.location.href="#";
}else{
window.location.href="SearchEmployeesServlet?employeename="+employeename+"&username="+username+"&status="+status+"&pageNum="+pageNum;
}
}
</script>
</head>
<body>
<div class="page-content">
<div class="content-nav">会议预定 >搜索员工</div>
<form method="post" action="SearchEmployeesServlet">
<fieldset>
<legend>搜索员工</legend>
<table class="formtable">
<tr>
<td>姓名:</td>
<td><input type="text" id="employeename" name="employeename"
value="${param.employeename}" maxlength="20" /></td>
<td>账号名:</td>
<td><input type="text" id="username" name="username"
value="${param.username}" maxlength="20" /></td>
<td>状态:</td>
<td><c:if test="${param.status eq null or param.status eq 3}">
<input type="radio" id="status" name="status" value="1" />
<label>已批准</label>
<input type="radio" id="status" name="status" value="0" />
<label>待审批</label>
<input type="radio" id="status" name="status" value="2" />
<label>已关闭</label>
<input type="radio" id="status" name="status" value="3" checked>
<label>所有</label>
</c:if> <c:if test="${param.status eq '1'}">
<input type="radio" id="status" name="status" value="1" checked />
<label>已批准</label>
<input type="radio" id="status" name="status" value="0" />
<label>待审批</label>
<input type="radio" id="status" name="status" value="2" />
<label>已关闭</label>
<input type="radio" id="status" name="status" value="3" />
<label>所有</label>
</c:if> <c:if test="${param.status eq '0'}">
<input type="radio" id="status" name="status" value="1" />
<label>已批准</label>
<input type="radio" id="status" name="status" value="0" checked />
<label>待审批</label>
<input type="radio" id="status" name="status" value="2" />
<label>已关闭</label>
<input type="radio" id="status" name="status" value="3" />
<label>所有</label>
</c:if> <c:if test="${param.status eq '2'}">
<input type="radio" id="status" name="status" value="1" />
<label>已批准</label>
<input type="radio" id="status" name="status" value="0" />
<label>待审批</label>
<input type="radio" id="status" name="status" value="2" checked />
<label>已关闭</label>
<input type="radio" id="status" name="status" value="3" />
<label>所有</label>
</c:if></td>
</tr>
<tr>
<td colspan="6" class="command"><input type="submit"
class="clickbutton" value="查询" /> <input type="reset"
class="clickbutton" value="重置" /></td>
</tr>
</table>
</fieldset>
</form>
<c:if test="${requestScope.search eq 1 }">
<div>
<h3 style="text-align:center;color:black">查询结果</h3>
<div class="pager-header">
<div class="header-info">
共<span class="info-number">${requestScope.countOfEmployees}</span>条结果,
分成<span class="info-number">${requestScope.countOfPages}</span>页显示,
当前第<span class="info-number">${requestScope.pageNum}</span>页
</div>
<div class="header-nav">
<input type="button" class="clickbutton" value="首页"
onclick="window.location.href='SearchEmployeesServlet?employeename=${param.employeename}&username=${param.username}&status=${param.status}&pageNum=1'" />
<c:if test="${requestScope.pageNum ne '1'}">
<input type="button" class="clickbutton" value="上页"
onclick="window.location.href='SearchEmployeesServlet?employeename=${param.employeename}&username=${param.username}&status=${param.status}&pageNum=${requestScope.pageNum-1}'" />
</c:if>
<c:if test="${requestScope.pageNum ne requestScope.countOfPages}">
<input type="button" class="clickbutton" value="下页"
onclick="window.location.href='SearchEmployeesServlet?employeename=${param.employeename}&username=${param.username}&status=${param.status}&pageNum=${requestScope.pageNum+1}'" />
</c:if>
<input type="button" class="clickbutton" value="末页"
onclick="window.location.href='SearchEmployeesServlet?employeename=${param.employeename}&username=${param.username}&status=${param.status}&pageNum=${requestScope.countOfPages}'" />
跳到第<input type="text" id="pageNum" name="pageNum"
class="nav-number" value=${param.pageNum}>页 <input type="button" class="clickbutton"
value="跳转"
onclick="goToOnePage('${param.employeename}','${param.username}','${param.status}')" />
</div>
</div>
</div>
<table class="listtable">
<tr class="listheader">
<th>姓名</th>
<th>账号名</th>
<th>联系电话</th>
<th>电子邮件</th>
<th>操作</th>
</tr>
<c:forEach var="emp" items="${requestScope.employeesList}">
<tr>
<td>${emp.employeename}</td>
<td>${emp.username}</td>
<td>${emp.phone}</td>
<td>${emp.email}</td>
<c:if test="${emp.status eq '2' }">
<td>账号已关闭</td>
</c:if>
<c:if test="${emp.status ne '2' }">
<td><a class="clickbutton"
href="ApproveServlet?employeeid=${emp.employeeid}&employeename=${param.employeename}&username=${param.username}&status=${param.status}&pageNum=${requestScope.pageNum}&oper=close">关闭账号</a>
</td>
</c:if>
</tr>
</c:forEach>
</table>
</div>
</c:if>
<div class="page-footer">
<hr />
更多问题,欢迎联系<a href="mailto:webmaster@eeg.com">管理员</a> <img
src="images/footer.png" alt="CoolMeeting" />
</div>
</body>
</html>
成功