分页技术--sql语句的分页

最新推荐文章于 2024-07-24 20:31:18 发布

未名胡

最新推荐文章于 2024-07-24 20:31:18 发布

阅读量6.1k

点赞数 2

文章标签：分页

如有雷同，不喜勿喷

本文链接：https://blog.csdn.net/qq_30641447/article/details/51577214

版权

常见分页的几种方式：

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