使用pagination和pagehelper实现分页以及关键字查询功能

1.添加依赖

      <!--分页 pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>

在application.yml中的配置

#  分页助手
pagehelper:
  reasonable: ture
  supportMethodsArguments: true
  params: count=countSql
  helper-dialect: mysql

sql语句

<sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, userName, password, address, phone, email, sex, createDate, modifyDate, picturename, usertype,account
  </sql>

 <select id="selectByKeyword" resultMap="UserMap">
   select
    <include refid="Base_Column_List" />
    from user
    where
     account like concat("%",#{keyword},"%") or
    username like concat("%",#{keyword},"%") or
    email like concat("%",#{keyword},"%")
</select>

dao层

    /**
     * 模糊查询
     * @param keyword 关键字
     * @return
     */
    List<User> selectByKeyword(String keyword);

service层和impl层

 PageInfo<User> getPageInfo(String keyword, Integer pageNum, Integer pageSize);

 /**
     * 分页功能
     * @param keyword 关键字
     * @param pageNum 当前页
     * @param pageSize 每页的数量
     * @return
     */
    @Override
    public PageInfo<User> getPageInfo(String keyword, Integer pageNum, Integer pageSize) {
//        调用PageHelper的静态方法开启分页功能
        PageHelper.startPage(pageNum,pageSize);
//        执行查询
        List<User> userList = userDao.selectByKeyword(keyword);
//        封装到PageInfo对象中
        return new PageInfo<>(userList);
    }

controller层

@Controller
@Slf4j
@RequestMapping("/user")
@Api(tags = "用户模块API文档")
public class UserController {

    /**
     * 服务对象
     */
    @Resource
    private UserService userService;
 /**
     * 分页和关键字查询功能
     * @param keyword 关键字
     * @param pageNum 分页数量
     * @param pageSize 分页大小
     * @return
     */
    @GetMapping("/pageInfo")
    @ResponseBody
    public AjaxResult<PageInfo<User>> getPageInfo(@RequestParam(value = "keyword",defaultValue = "") String keyword,
                                                  @RequestParam(value = "pageNum",defaultValue = "1") Integer pageNum,
                                                  @RequestParam(value = "pageSize",defaultValue = "1") Integer pageSize){
        log.info("pageNum{}",pageNum);
        log.info("pageSize{}",pageSize);
        PageInfo<User> pageInfo = userService.getPageInfo(keyword, pageNum, pageSize);
        return AjaxResult.success(pageInfo);
    }
}

前端页面使用pagination需要引入bootstrap、jquery.min.js、pagination.css、jquery.pagination.js、pagination.js等样式

html页面

		<div class="pagination" id="Pagination">

							 </div>
							 
							   <script th:inline="javascript"> var ctx = [[@{/}]];</script>
							   <script src="../static/jquery/jquery.min.js" th:src="@{/jquery/jquery.min.js}"></script>
<script src="../static/jquery/jquery-2.1.1.min.js" th:src="@{/jquery/jquery-2.1.1.min.js}"></script>
<script src="../static/bootstrap/js/bootstrap.js" th:src="@{/bootstrap/js/bootstrap.js}"></script>
<script src="../static/bootstrap/js/bootstrap-paginator.js" th:src="@{/bootstrap/js/bootstrap-paginator.js}"></script>
<script src="../static/script/docs.min.js" th:src="@{/script/docs.min.js}"></script>
<script th:src="@{/layer/layer.js}" src="../static/layer/layer.js"></script>
<script src="../static/jquery/jquery.pagination.js" th:src="@{/jquery/jquery.pagination.js}"></script>
<script src="../static/jquery/pagination.js" th:src="@{/jquery/pagination.js}"></script>
  <script  src="../static/js/user.js" th:src="@{/js/user.js}"></script>

user.js的写法

let totalRecord='';
let properties='';

let tbody = $("#tbody");
$(function() {
    searchUser();
});
function searchUser() {
    let keyword = $("#keyword").val();
    let html='';

    $.ajax({
        type: "get",
        url: ctx + "user/pageInfo",
        data:{"keyword":keyword},
        dataType: "json",
        contentType: "application/json;charset=UTF-8",
        success: function(result) {
            console.log("输出结果：",result.data);
            if (result.data.list!=null){
                totalRecord = result.data.total;
                properties = {
                    num_edge_entries: 3, //边缘页数
                    num_display_entries: 5, //主体页数
                    callback: pageSelectCallback, // 指定用户点击“翻页”的按钮时跳转页面的回调函数
                    items_per_page:result.data.pageSize, //每页要显示的数据的数量
                    current_page: result.data.pageNum-1, //
                    prev_text: "上一页",  //上一页按钮上显示的文本
                    next_text: "下一页",  //下一页按钮上显示的文本
                }

                $.each(result.data.list,function (index,item) {
                    var number = index+1;
                    html+='<tr>' +
                        '<td>'+number+'</td>' +
                        '<td><input type="checkbox"></td>' +
                        '<td>'+item.account+'</td>' +
                        '<td>'+item.username+'</td>' +
                        '<td>'+item.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>';
                })
            }else {
                html+='<tr style="color: red;font-size: 20px;">' +
                    '<td>暂无此数据</td></tr>'
            }
            $("#Pagination").pagination(totalRecord,properties);
            tbody.html(html);
        },
        error:function (error) {
            console.log("错误信息",this.error);
        }
    });

}
//回调函数
function pageSelectCallback(pageIndex,jQuery) {
    let keyword = $("#keyword").val();
    console.log("pageIndex",pageIndex);
//根据pageIndex计算得到pageNum
    let pageNum=pageIndex+1;
    console.log("pageNum",pageNum);
    let html='';
    $.ajax({
        type: "get",
        url: ctx + "user/pageInfo",
        data:{"pageNum":pageNum,"keyword":keyword},
        dataType: "json",
        contentType: "application/json;charset=UTF-8",
        success: function(result) {
            console.log("输出结果：",result.data);
            if (result.data.list!=null){

                $.each(result.data.list,function (index,item) {

                    let number = index+1;
                    html+='<tr>' +
                        '<td>'+number+'</td>' +
                        '<td><input type="checkbox"></td>' +
                        '<td>'+item.account+'</td>' +
                        '<td>'+item.username+'</td>' +
                        '<td>'+item.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>';
                })
            }else {
                html+='<tr style="color: red;font-size: 20px;">' +
                    '<td>暂无此数据</td></tr>'
            }

            tbody.html(html);
        },
        error:function (error) {
            console.log("错误信息",this.error);
        }
    });
    //由于每一个页码按钮都是超链接，所以在这个函数最后取消超链接的默认行为
    return false;
}

后台返回结果我使用的是json格式的统一返回结果类型，如需了解可以看我的另一篇文章。