mybatis分页实现

古城微笑少年丶 2023-02-18 08:29 59阅读 0赞

### 1.Sql分页 ###

分页sql格式是：select \* from table limit (start-1)\*pageSize,pageSize; 其中start是页码，pageSize是每页显示的条数。

查询的分页数据是   从(start-1)\*pageSize条开始，每页显示pageSize条数

比如：

查询第1条到第5条的数据的sql是：select \* from table limit 0,5;   ->对应我们的需求就是查询第一页的数据：select \* from table limit (1-1)\*5,5;

查询第5条到第10条的数据的sql是：select \* from table limit 5,5;  ->对应我们的需求就是查询第二页的数据：select \* from table limit (2-1)\*5,5;

查询第10条到第15条的数据的sql是：select \* from table limit 20,10;  ->对应我们的需求就是查询第三页的数据：select \* from table limit (3-1)\*5,10;

具体的mapper接口，controller此处省略不写。

### 2.PageHelper插件进行分页 ###

PageHelper是MyBatis中非常方便的第三方分页插件。

官方文档：[https://github.com/pagehelper/Mybatis-PageHelper/blob/master/README\_zh.md][https_github.com_pagehelper_Mybatis-PageHelper_blob_master_README_zh.md]

下面使用maven项目进行演示

**1.在pom.xml中引入依赖jar包**

<dependency>
    			<groupId>com.github.jsqlparser</groupId>
    			<artifactId>jsqlparser</artifactId>
    			<version>0.9.5</version>
    		</dependency>
    		<dependency>
    			<groupId>com.github.pagehelper</groupId>
    			<artifactId>pagehelper</artifactId>
    			<version>5.0.0</version>
    		</dependency>

**2.在mybatis-config.xml中配置PageInterceptor拦截器**

**3.mapper接口**

public List<Employee> selectAllEmps();

**4.mapper映射文件**

<select id="selectAllEmps" resultMap="myEmpAndDept">
    		select e.id
    		eid,e.last_name,e.email,e.gender,d.id did,d.dept_name from
    		tbl_employee e,tbl_dept d where e.d_id=d.id
    	</select>
    	<resultMap type="employee" id="myEmpAndDept">
    		<id column="eid" property="id" />
    		<result column="last_name" property="lastName" />
    		<result column="email" property="email" />
    		<result column="gender" property="gender" />
    		
    		<association property="dept" javaType="com.hbsi.pojo.Department">
    			<id column="did" property="id" />
    			<result column="dept_name" property="departmentName" />
    		</association>
    	</resultMap>

5.controller

@RequestMapping(value = "/emps")
    	public String listAllEmps(@RequestParam(value = "pn", required = true, defaultValue = "1") Integer pn,
    			HttpServletRequest request, Model model) {
    		// 引入PageHelper分页插件
    		// 在查询之前只需要调用，传入页码，以及每页的大小
    		PageHelper.startPage(pn, 3);// 使用PageHelper设置分页信息
    		List<Employee> emps = employeeService.getAllEmps();
    		// 使用pageInfo包装查询后的结果，只需要将pageInfo交给页面就行了。
    		// 封装了详细的分页信息,包括有我们查询出来的数据，传入连续显示的页数
    		PageInfo<Employee> page = new PageInfo<Employee>(emps, 3);
    		System.out.println(emps);
    		model.addAttribute("pageInfo", page);
    		return "list";
    	}

**6.list.jsp用于显示分页数据**

<%@ page language="java" contentType="text/html; charset=UTF-8"
    	pageEncoding="UTF-8"%>
    <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>员工列表</title>
    <%
    	pageContext.setAttribute("APP_PATH", request.getContextPath());
    %>
    
    <script type="text/javascript"
    	src="${APP_PATH }/static/js/jquery-1.12.4.min.js"></script>
    <link
    	href="${APP_PATH }/static/bootstrap-3.3.7-dist/css/bootstrap.min.css"
    	rel="stylesheet">
    <script
    	src="${APP_PATH }/static/bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
    </head>
    <body>
    	
    	<div class="container">
    		
    		<div class="row">
    			<div class="col-md-12">
    				<h1>员工管理</h1>
    			</div>
    		</div>
    		
    		<div class="row">
    			<div class="col-md-4 col-md-offset-8">
    				<button class="btn btn-primary">新增</button>
    				<button class="btn btn-danger">删除</button>
    			</div>
    		</div>
    		
    		<div class="row">
    			<div class="col-md-12">
    				<table class="table table-hover">
    					<tr>
    						<th>id</th>
    						<th>empName</th>
    						<th>gender</th>
    						<th>email</th>
    						<th>deptName</th>
    						<th>操作</th>
    					</tr>
    					<c:forEach items="${pageInfo.list }" var="emp">
    						<tr>
    							<th>${emp.id }</th>
    							<td>${emp.lastName}</td>
    							<td>${emp.email}</td>
    							<td>${emp.gender==1?'男':'女'}</td>
    							<td>${emp.dept.departmentName}</td>
    							<th>
    								<button class="btn btn-primary btn-sm">
    									<span class="glyphicon glyphicon-pencil" aria-hidden="true"></span>
    									编辑
    								</button>
    								<button class="btn btn-danger btn-sm">
    									<span class="glyphicon glyphicon-trash" aria-hidden="true"></span>
    									删除
    								</button>
    							</th>
    						</tr>
    					</c:forEach>
    				</table>
    			</div>
    		</div>
    
    		
    		<div class="row">
    			
    			<div class="col-md-6">当前 ${pageInfo.pageNum }页,总${pageInfo.pages }
    				页,总 ${pageInfo.total } 条记录</div>
    			
    			<div class="col-md-6">
    				<nav aria-label="Page navigation">
    				<ul class="pagination">
    					<li><a href="${APP_PATH }/emps?pn=1">首页</a></li>
    					<c:if test="${pageInfo.hasPreviousPage }">
    						<li><a href="${APP_PATH }/emps?pn=${pageInfo.pageNum-1}"
    							aria-label="Previous"><span aria-hidden="true">«</span>
    						</a></li>
    					</c:if>
    
    
    					 <c:forEach items="${pageInfo.navigatepageNums }" var="page_Num">
    						<c:if test="${page_Num == pageInfo.pageNum }">
    							<li class="active"><a href="#">${page_Num }</a></li>
    						</c:if>
    						<c:if test="${page_Num != pageInfo.pageNum }">
    							<li><a href="${APP_PATH }/emps?pn=${page_Num }">${page_Num }</a></li>
    						</c:if>
    
    					</c:forEach>					<c:if test="${pageInfo.hasNextPage }">
    						<li><a href="${APP_PATH }/emps?pn=${pageInfo.pageNum+1 }"
    							aria-label="Next"><span aria-hidden="true">»</span>
    						</a></li>
    					</c:if>
    					<li><a href="${APP_PATH }/emps?pn=${pageInfo.pages}">末页</a></li>
    				</ul>
    				</nav>
    			</div>
    		</div>
    
    	</div>
    </body>
    </html>

浏览器发送请求[http://localhost:8080/CRM/emps][http_localhost_8080_CRM_emps]，得到分页数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xicWx6Y2g_size_16_color_FFFFFF_t_70][]

更多分页资料请参考[https://www.cnblogs.com/aeolian/p/9229149.html][https_www.cnblogs.com_aeolian_p_9229149.html]

[https_github.com_pagehelper_Mybatis-PageHelper_blob_master_README_zh.md]: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/README_zh.md
[http_localhost_8080_CRM_emps]: http://localhost:8080/CRM/emps
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xicWx6Y2g_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200616220333168.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xicWx6Y2g=,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_aeolian_p_9229149.html]: https://www.cnblogs.com/aeolian/p/9229149.html