常见分页的几种方式:
1.使用LIst接口中的subList(int startIndex,int endIndex)方法实现分页
2.直接使用数据库SQL语句实现分页
3.使用hibernate等框架实现跨数据库的分页
4.使用PageHelper插件分页
四种分页方式的比较:
实现方式 优点 缺点 适用场景
1 subList 简单易用 效率低 无法按需批量获取数据
2 sql语句 简单、直接、高效 数据库兼容性差 不要求数据库兼容
3 Hibernate框架 面向对象、兼容性强 复杂查询 性能低 兼容不同的数据库
4 PageHelper插件 简单易用 不兼容逆向功能 mybatis框架下
2.使用SQL语句实现分页:
利用自带的分页语法,使用分页语句,获取分页数据(例如:MySQL数据库使用limit关键字,oracle数据库使用rownum关键字等)
举例:
—从学生表(t_student)中查询出前十条数据
Mysql数据库实现:查询语句
select * from t_student limit 0,10
PostgreSQL数据库实现:查询语句
select * from t_studentlimit 10 offset 0
Oracle数据库实现:查询语句
select* from(select s.*,rownum rn from(select * from t_student)s whererownum<=10)where rn>=1
实例
数据库:
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(16) NOT NULL,
`gender` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
INSERT INTO `t_student` VALUES ('1', '王小军', '1', '17', '北京市东城区');
INSERT INTO `t_student` VALUES ('2', '李雷雷', '1', '16', '北京市朝阳区');
INSERT INTO `t_student` VALUES ('3', '张静', '2', '16', '北京市昌平区');
INSERT INTO `t_student` VALUES ('4', '王晓萌', '2', '17', '北京市顺义区');
INSERT INTO `t_student` VALUES ('5', '韩梅梅', '2', '16', '北京市朝阳区');
INSERT INTO `t_student` VALUES ('6', '李小军', '1', '17', '北京市海淀区');
INSERT INTO `t_student` VALUES ('7', '成龙', '1', '16', '北京市石景山区');
INSERT INTO `t_student` VALUES ('8', '李海飞', '2', '16', '北京市海淀区');
INSERT INTO `t_student` VALUES ('9', '罗红', '2', '16', '北京市朝阳区');
INSERT INTO `t_student` VALUES ('10', '孙海杰', '1', '16', '北京市石景山区');
INSERT INTO `t_student` VALUES ('11', '王海龙', '1', '16', '北京市东城区');
jdbc.properties jdbc.username=root
jdbc.password=123456
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/imooc
Dao层 JdbcSqlStudentDaoImpl.java
/**
* 使用mysql数据库limit关键字实现分页
*
* @author lenjey
*
*/
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++;
}
// 组装pager对象
result = new Pager<Student>(pageSize, pageNum,
totalRecord, totalPage, studentList);
} catch (SQLException e) {
throw new RuntimeException("查询所有数据异常!", e);
} finally {
if (jdbcUtil != null) {
jdbcUtil.releaseConn(); // 一定要释放资源
}
}
return result;
}
}
Service层 JdbcSqlStudentServiceImpl.java
public class JdbcSqlStudentServiceImpl implements StudentService {
private StudentDao studentDao;
public JdbcSqlStudentServiceImpl(){
studentDao = new JdbcSqlStudentDaoImpl();
}
@Override
public Pager<Student> findStudent(Student searchModel, int pageNum,
int pageSize) {
Pager<Student> result = studentDao.findStudent(searchModel, pageNum,
pageSize);
return result;
}
}
web servlet层 JdbcSqlServlet.java
public class JdbcSqlServlet extends HttpServlet {
private static final long serialVersionUID = -318134993070614515L;
private StudentService studentService = new JdbcSqlStudentServiceImpl();
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 接收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);
}
}
Jsp页面 jdbcSqlStudent.jsp
<%@ page language="java" 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:"上一页",
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" style="width:120px" value="${stuName }">
性别
<select name="gender" id="gender" style="width:80px">
<option value="0">全部</option>
<option value="1">男</option>
<option value="2">女</option>
</select>
<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 }">
<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="190">家庭地址</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>
其他工具类、配置文件请参考: http://blog.csdn.net/qq_30641447/article/details/51576923