备注:一定要小心
流程
执行查询的sql语句
SELECT
*
FROM
t_admin
WHERE
login_acct LIKE CONCAT("%", "", "%")
OR user_name LIKE CONCAT("%", "", "%")
OR email LIKE CONCAT("%", "", "%");
CONCAT()函数的作用是拼字符串,执行字符串连接。
MyBatis的PageHelper插件
作用
以完全非侵入的方式在原有查询基础上附加分页效果。从SQL层面来说,在SQL语句后面附加LIMIT子句。从Java代码来说,把原来返回的List类型封装为Page类型。
依赖
<!-- 父工程 -->
<!-- MyBatis分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.0.0</version>
</dependency>
配置
所在工程:atcrowdfunding-admin-1-webui
配置文件:spring-persist-mybatis.xml
在SqlSessionFactoryBean中配置MyBatis插件
<!-- 配置插件 -->
<property name="plugins">
<array>
<!-- PageHelper插件全类名 -->
<bean class="com.github.pagehelper.PageHelper">
<!-- 配置插件属性 -->
<property name="properties">
<props>
<!-- 配置数据库方言 -->
<!-- MySQL分页:LIMIT -->
<!-- Oracle分页:TopN分析 -->
<prop key="dialect">mysql</prop>
<!-- 配置自动修正页码 -->
<!-- pageNo的有效范围:1~总页数 -->
<prop key="reasonable">true</prop>
</props>
</property>
</bean>
</array>
</property>
AdminMapper
Mapper配置文件
所在工程:atcrowdfunding-admin-1-webui
文件:resources/mybatis/mapper/AdminMapper.xml
<select id="selectAdminListByKeyword" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_admin
WHERE
login_acct LIKE CONCAT("%", #{keyword}, "%")
OR user_name LIKE CONCAT("%", #{keyword}, "%")
OR email LIKE CONCAT("%", #{keyword}, "%")
</select>
Mapper接口
所在工程:atcrowdfunding-admin-2-component
全类名:com.atguigu.crowd.funding.mapper.AdminMapper
List<Admin> selectAdminListByKeyword(String keyword);
AdminService
所在工程:atcrowdfunding-admin-2-component
接口方法:
PageInfo<Admin> queryForKeywordSearch(Integer pageNum, Integer pageSize, String keyword);
实现类方法:
@Override
public PageInfo<Admin> queryForKeywordSearch(Integer pageNum, Integer pageSize, String keyword) {
// 1.调用PageHelper的工具方法,开启分页功能
PageHelper.startPage(pageNum, pageSize);
// 2.执行分页查询
List<Admin> list = adminMapper.selectAdminListByKeyword(keyword);
// 3.将list封装到PageInfo对象中
return new PageInfo<>(list);
}
AdminHandler
所在工程:atcrowdfunding-admin-2-component
全类名:com.atguigu.crowd.funding.handler.AdminHandler
@RequestMapping("/admin/query/for/search")
public String queryForSearch(
// 如果页面上没有提供对应的请求参数,那么可以使用defaultValue指定默认值
@RequestParam(value="pageNum", defaultValue="1") Integer pageNum,
@RequestParam(value="pageSize", defaultValue="5") Integer pageSize,
@RequestParam(value="keyword", defaultValue="") String keyword,
Model model) {
PageInfo<Admin> pageInfo = adminService.queryForKeywordSearch(pageNum, pageSize, keyword);
model.addAttribute(CrowdFundingConstant.ATTR_NAME_PAGE_INFO, pageInfo);
return "admin-page";
}
public static final String ATTR_NAME_PAGE_INFO = "PAGE-INFO";
页面显示
1、增加连接:
修改/atcrowdfunding-admin-1-webui/src/main/webapp/WEB-INF/include-sidebar.jsp的
/atcrowdfunding-admin-1-webui/src/main/webapp/WEB-INF/admin-page.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>
<html lang="UTF-8">
<%@ include file="/WEB-INF/include-head.jsp" %>
<body>
<%@ include file="/WEB-INF/include-nav.jsp" %>
<div class="container-fluid">
<div class="row">
<%@ include file="/WEB-INF/include-sidebar.jsp" %>
<div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">
<c:forEach items="${requestScope['PAGE-INFO'].list}" var="admin">
${admin}<br>
</c:forEach>
</div>
</div>
</div>
</body>
</html>
3、插入测试数据
/atcrowdfunding-admin-1-webui/src/test/java/com/atguigu/crowd/funding/test/CrowdFundingTest.java
@Autowired
private AdminMapper adminMapper;
@Test
public void batchSaveAdmin() {
for(int i = 0; i < 500; i++) {
adminMapper.insert(new Admin(null, "loginAcct"+i, "1111111", "userName"+i, "email"+i+"@qq.com", null));
}
}
2、运行访问:
- http://localhost:8080/atcrowdfunding-admin-1-webui/admin/query/for/search.html
- http://localhost:8080/atcrowdfunding-admin-1-webui/admin/query/for/search.html?pageNum=2&pageSize=10
编写页面
环境搭建
- 将尚筹网/前端/pagination_zh/lib/pagination.css复制到/atcrowdfunding-admin-1-webui/src/main/webapp/css目录下
- 将尚筹网/前端/pagination_zh/lib/jquery.pagination.js复制到/atcrowdfunding-admin-1-webui/src/main/webapp/script目录下
页面编写
/atcrowdfunding-admin-1-webui/src/main/webapp/WEB-INF/admin-page.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>
<html lang="UTF-8">
<%@ include file="/WEB-INF/include-head.jsp"%>
<link rel="stylesheet" href="css/pagination.css" />
<script type="text/javascript" src="script/jquery.pagination.js"></script>
<script type="text/javascript">
$(function(){
initPagination();
});
// 声明函数封装导航条初始化操作
function initPagination() {
// 声明变量存储总记录数
var totalRecord = ${requestScope['PAGE-INFO'].total};
// 声明变量存储分页导航条显示时的属性设置
var paginationProperties = {
num_edge_entries :3, //边缘页数
num_display_entries : 5, //主体页数
callback : pageselectCallback, //回调函数
items_per_page : ${requestScope['PAGE-INFO'].pageSize}, //每页显示数据数量,就是pageSize
current_page : ${requestScope['PAGE-INFO'].pageNum - 1},//当前页页码
prev_text : "上一页", //上一页文本
next_text : "下一页" //下一页文本
};
// 显示分页导航条
$("#Pagination").pagination(totalRecord, paginationProperties);
}
// 在每一次点击“上一页”、“下一页”、“页码”时执行这个函数跳转页面
function pageselectCallback(pageIndex, jq) {
// pageIndex从0开始,pageNum从1开始
var pageNum = pageIndex + 1;
// 跳转页面
window.location.href = "?pageNum="+pageNum+"&keyword=${param.keyword}";
return false;
}
</script>
<body>
<%@ include file="/WEB-INF/include-nav.jsp"%>
<div class="container-fluid">
<div class="row">
<%@ include file="/WEB-INF/include-sidebar.jsp"%>
<div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">
<div class="panel panel-default">
<div class="panel-heading">
<h3 class="panel-title">
<i class="glyphicon glyphicon-th"></i> 数据列表
</h3>
</div>
<div class="panel-body">
<form action="admin/query/for/search.html" class="form-inline"
role="form" style="float: left;" method="post">
<div class="form-group has-feedback">
<div class="input-group">
<div class="input-group-addon">查询条件</div>
<input name="keyword" class="form-control has-success"
type="text" placeholder="请输入查询条件">
</div>
</div>
<button type="submit" class="btn btn-warning">
<i class="glyphicon glyphicon-search"></i> 查询
</button>
</form>
<button type="button" class="btn btn-danger"
style="float: right; margin-left: 10px;">
<i class=" glyphicon glyphicon-remove"></i> 删除
</button>
<button type="button" class="btn btn-primary"
style="float: right;" onclick="window.location.href='add.html'">
<i class="glyphicon glyphicon-plus"></i> 新增
</button>
<br>
<hr style="clear: both;">
<div class="table-responsive">
<table class="table table-bordered">
<thead>
<tr>
<th width="30">#</th>
<th width="30"><input id="summaryBox" type="checkbox"></th>
<th>账号</th>
<th>名称</th>
<th>邮箱地址</th>
<th width="100">操作</th>
</tr>
</thead>
<tbody>
<c:if test="${empty requestScope['PAGE-INFO'].list }">
<tr>
<td style="text-align: center;" colspan="6">抱歉!没有符合您要求的查询结果!</td>
</tr>
</c:if>
<c:if test="${!empty requestScope['PAGE-INFO'].list }">
<c:forEach items="${requestScope['PAGE-INFO'].list }"
var="admin" varStatus="myStatus">
<tr>
<td>${myStatus.count }</td>
<td><input class="itemBox" type="checkbox"></td>
<td>${admin.loginAcct }</td>
<td>${admin.userName }</td>
<td>${admin.email }</td>
<td>
<button type="button" class="btn btn-success btn-xs">
<i class=" glyphicon glyphicon-check"></i>
</button>
<button type="button" class="btn btn-primary btn-xs">
<i class=" glyphicon glyphicon-pencil"></i>
</button>
<button type="button" class="btn btn-danger btn-xs">
<i class=" glyphicon glyphicon-remove"></i>
</button>
</td>
</tr>
</c:forEach>
</c:if>
</tbody>
<tfoot>
<tr>
<td colspan="6" align="center">
<div id="Pagination" class="pagination">
<!-- 这里显示分页 -->
</div>
</td>
</tr>
</tfoot>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
备注:分页功能如果没有显示,看是否是因为jquery没有在head里面