javaee会议管理系统中-分页查询

因为分页查询很重要,所以另外开一篇来总结了

数据多的时候,需要分页显示

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>

 

成功