JAVA分页原理（二）——使用SQL实现

JdbcSqlStudentDaoImpl.java

package com.imooc.page.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.imooc.page.Constant;
import com.imooc.page.model.Pager;
import com.imooc.page.model.Student;
import com.imooc.page.util.JdbcUtil;
/**
 * 使用mysql数据库limit关键字实现分页
 * @author yrui
 *
 */
public class JdbcSqlStudentDaoImpl implements StudentDao {

	@Override
	public Pager<Student> findStudent(Student searchModel, int pageNum, int pageSize) {
		Pager<Student> result = null;
		//存放查询参数
		List<Object> paramList = new ArrayList<Object>();
		String stuName = searchModel.getStuName();
		int gender = searchModel.getGender();
		
		StringBuilder sql = new StringBuilder("select * from t_student where 1=1");
		StringBuilder countSql = new StringBuilder("select count(id) as totalRecord from t_student where 1=1");
		if(stuName !=null&&!stuName.equals("")){
			sql.append(" and stu_name like ?");
			countSql.append(" and stu_name like ?");
			paramList.add("%"+stuName+"%");
		}
		if(gender==Constant.GENDER_FEMALE||gender==Constant.GENDER_MALE){
			sql.append(" and gender = ?");
			countSql.append(" and gender = ?");
			paramList.add(gender);
		}
		// 起始索引
		int fromIndex = pageSize * (pageNum - 1);
		//使用limit关键字实现分页
		sql.append(" limit "+ fromIndex+","+pageSize);
		
		//存放所有查询出的学生对象
		List<Student> studentList = new ArrayList<Student>();
		JdbcUtil jdbcUtil = null;
		try {
			jdbcUtil = new JdbcUtil();
			jdbcUtil.getConnection();//获取数据库连接
			//获取总记录数
			List<Map<String,Object>> countResult = jdbcUtil.findResult(countSql.toString(), paramList);
			Map<String,Object> countMap = countResult.get(0);
			int totalRecord = ((Number)countMap.get("totalRecord")).intValue();
			//获取查询的学生记录
			List<Map<String,Object>> studentResult = jdbcUtil.findResult(sql.toString(), paramList);
			if(studentResult!=null){
				for(Map<String,Object> map:studentResult){
					Student s = new Student(map);
					studentList.add(s);
				}
			}
			
			// 获取总页数
			int totalPage = totalRecord / pageSize;
			if (totalRecord % pageSize != 0) {
				totalPage = totalPage + 1;
			}
			//组装pager对象
			result = new Pager<>(pageSize, pageNum, totalRecord, totalPage, studentList);
		} catch (SQLException e) {
			throw new RuntimeException("查询所有数据异常！",e);
		}finally{
			if(jdbcUtil!=null){
				jdbcUtil.releaseConn();//一定要释放资源
			}
		}
		return result;
	}

}

JdbcSqlStudentServiceImpl.java

package com.imooc.page.service;

import com.imooc.page.dao.JdbcSqlStudentDaoImpl;
import com.imooc.page.dao.StudentDao;
import com.imooc.page.model.Pager;
import com.imooc.page.model.Student;

public class JdbcSqlStudentServiceImpl implements StudentService {

	private StudentDao studentDao;
	
	public JdbcSqlStudentServiceImpl(){
		//创建service实现类时，初始化dao对象
		studentDao = new JdbcSqlStudentDaoImpl();
	}
	@Override
	public Pager<Student> findStudent(Student searchModel, int pageNum, int pageSize) {
		Pager<Student> result = studentDao.findStudent(searchModel, pageNum, pageSize);
		return result;
	}
	public StudentDao getStudentDao() {
		return studentDao;
	}
	public void setStudentDao(StudentDao studentDao) {
		this.studentDao = studentDao;
	}

}

JdbcSqlServlet.java

package com.imooc.page.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.imooc.page.Constant;
import com.imooc.page.model.Pager;
import com.imooc.page.model.Student;
import com.imooc.page.service.JdbcSqlStudentServiceImpl;
import com.imooc.page.service.StudentService;
import com.imooc.page.util.StringUtil;


public class JdbcSqlServlet extends HttpServlet {
	
	private static final long serialVersionUID = -4873699362194465829L;
	private StudentService studentService = new JdbcSqlStudentServiceImpl();
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		//接收request里的参数
		String stuName = request.getParameter("stuName");
		
		int gender = Constant.DEFAULT_GENDER;
		String genderStr = request.getParameter("gender");
		if(genderStr!=null&&!"".equals(genderStr.trim())){
			gender = Integer.parseInt(genderStr);
		}
		//校验pageNum参数输入的合法性
		String pageNumStr = request.getParameter("pageNum");
		if(pageNumStr!=null&&!StringUtil.isNum(pageNumStr)){
			request.setAttribute("errorMsg", "参数传输错误");
			request.getRequestDispatcher("jdbcSqlStudent.jsp").forward(request, response);
			return;
		}
		int pageNum = Constant.DEFAULT_PAGE_NUM;
		
		if(pageNumStr!=null&&!"".equals(pageNumStr.trim())){
			pageNum = Integer.parseInt(pageNumStr);
		}
		
		int pageSize = Constant.DEFAULT_PAGE_SIZE;
		String pageSizeStr = request.getParameter("pageSize");
		if(pageSizeStr!=null&&!"".equals(pageSizeStr.trim())){
			pageSize = Integer.parseInt(pageSizeStr);
		}
		//组装查询条件
		Student searchModel = new Student();
		searchModel.setStuName(stuName);
		searchModel.setGender(gender);
		//调用service获取查询结果
		Pager<Student> result = studentService.findStudent(searchModel, pageNum, pageSize);
		
		//返回结果到页面
		request.setAttribute("result", result);
		request.setAttribute("stuName", stuName);
		request.setAttribute("gender", gender);
		request.getRequestDispatcher("jdbcSqlStudent.jsp").forward(request, response);
	}

}

引用了github上https://github.com/gbirke/jquery_pagination的分页工具

导入了一个css文件和2个js文件

 

jdbcSqlStudent.jsp

<%@ page language="java" import="java.util.*"
	contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>学生信息</title>
</head>
<%
	//获取请求上下文
	String context = request.getContextPath();
%>
<link href="../css/pagination.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="../js/jquery-1.11.3.js"></script>
<script type="text/javascript" src="../js/jquery.pagination.js"></script>
<script type="text/javascript">
//点击分页按钮以后触发的动作
function handlePaginationClick(new_page_index, pagination_container) {
    $("#stuForm").attr("action", "<%=context %>/jdbcSql/JdbcSqlServlet?pageNum=" + (new_page_index+1));
    $("#stuForm").submit();
    return false;
}

$(function(){
	$("#News-Pagination").pagination(${result.totalRecord}, {
        items_per_page:${result.pageSize}, // 每页显示多少条记录
        current_page:${result.currentPage} - 1, // 当前显示第几页数据
        num_display_entries:8, // 分页显示的条目数
        next_text:"下一页",
        prev_text:"上一页",
        load_first_page: false,
        num_edge_entries:2, // 连接分页主体，显示的条目数
        callback:handlePaginationClick
	});
	
	// 设置学生默认性别
	$("#gender").val("${gender}");
});
</script>
<body>
	<div style="margin-left: 100px; margin-top: 100px;">
		<div>
			<font color="red">${errorMsg }</font>
		</div>
		<div>
			<form action="<%=context%>/jdbcSql/JdbcSqlServlet" id="stuForm"
				method="post">
				姓名 <input type="text" name="stuName" id="stu_name"  value="${stuName }"
					style="width: 120px;"> &nbsp; 性别 <select name="gender"
					id="gender" style="width: 80px">
					<option value="0">全部</option>
					<option value="1">男</option>
					<option value="2">女</option>
				</select> &nbsp;&nbsp; <input type="submit" value="查询">
			</form>
		</div>
		<br> 学生信息列表：<br> <br>
		<!-- 后台返回结果为空 -->
		<c:if test="${fn:length(result.dataList) eq 0}">
			<span>查询的结果不存在</span>
		</c:if>
		<!-- 后台返回结果不为空 -->
		<c:if test="${fn:length(result.dataList) gt 0}">
			<!-- 大于0 -->

			<table border="1px" cellspacing="0px"
				style="border-collapse: collapse">
				<thead>
					<tr height="30">
						<th width="130">姓名</th>
						<th width="130">性别</th>
						<th width="130">年龄</th>
						<th width="130">家庭地址</th>
					</tr>
				</thead>
				<c:forEach items="${result.dataList}" var="student">
					<tr>
						<td><c:out value="${student.stuName }"></c:out></td>
						<td><c:if test="${student.gender eq 1 }">男</c:if> <c:if
								test="${student.gender eq 2 }">女</c:if></td>
						<td><c:out value="${student.age }"></c:out></td>
						<td><c:out value="${student.address }"></c:out></td>
					</tr>
				</c:forEach>
			</table>
			<br>
				<div id="News-Pagination"></div>
		</c:if>
	</div>
</body>
</html>

好啦，完成啦