mybatis分页查询

1.分页的自定义实现（以采用ssm框架，前端使用layui的项目实现为例）

MenuMapper.xml中sql查询语句需要传入开始值和查询数量

  <!--获取所有菜单总数-->
  <select id="countMenu" resultType="java.lang.Integer">
    SELECT
      count(*)
    FROM
      auge_menu
  </select>
  <!--分页获取所有菜单-->
  <select id="listMenu" resultType="java.util.Map">
    SELECT
      am.id, am.name, am.path, am.icon, am.description, am.parent_id,
      DATE_FORMAT(am.create_time,'%Y-%m-%d %T') as create_time,
      DATE_FORMAT(am.update_time,'%Y-%m-%d %T') as update_time,
      aa.name as create_user, an.name as update_user,
      au.name parent_name
    from
      auge_menu am
    LEFT JOIN auge_menu au ON am.parent_id = au.id
    LEFT JOIN auge_admin aa ON am.create_user = aa.id
    LEFT JOIN auge_admin an ON am.update_user = an.id
    ORDER BY am.create_time
    LIMIT #{offset},#{limit}
  </select>

MenuMapper.java

    /**
     * 获取总菜单个数
     *
     * @return
     */
    int countMenu();
    /**
     * 获取所有菜单
     *
     * @param offset 开始值
     * @param limit  查询数据数量
     * @return
     */
    List<Map> listMenu(@Param("offset")int offset, @Param("limit")int limit);

PageBean.java列表数据封装，查询总数以及分页数据

import java.util.List;

/**
 * 作用:分页数据
 */
public class PageBean {

    private List data;

    private int count;

    public PageBean(List data, int count) {
        this.data = data;
        this.count = count;
    }

    public List getData() {
        return data;
    }

    public void setData(List data) {
        this.data = data;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }
}

service接口略过，MenuServiceImpl.java将查询结果封装到PageBean中

public PageBean getMenuList(Integer page, Integer limit){
    int offset = (page - 1) * limit;
	List<Map> menuList = augeMenuMapper.listMenu(offset, limit);
	int count = augeMenuMapper.countMenu();
	PageBean pageBean = new PageBean(menuList, count);
	return pageBean;
}

LayuiResponseJSONBean.java和jsp的交互json封装

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.springboot.first.domain.enums.ClientResponseStatusEnum;

/**
 * 为LayUI
 * Created by FENGCUIJIE on 2017/7/13.
 */
public class LayuiResponseJSONBean {
    /**
     * 错误消息
     */
    private String msg;

    /**
     * 返回代码
     */
    private String code;
    /**
     * 返回数据集合
     */
    private Object data;

    private int count;

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public static LayuiResponseJSONBean getSuccessClientResponseJSONBean() {
        LayuiResponseJSONBean clientResponseJSONBean = new LayuiResponseJSONBean();
        clientResponseJSONBean.setCode("0");
        clientResponseJSONBean.setMsg("");
        return clientResponseJSONBean;
    }

    public static LayuiResponseJSONBean getFailedClientResponseJSONBean() {
        LayuiResponseJSONBean clientResponseJSONBean = new LayuiResponseJSONBean();
        clientResponseJSONBean.setCode(ClientResponseStatusEnum.CLIENT_RESPONSE_FAILED.getStatusCode());
        clientResponseJSONBean.setMsg(ClientResponseStatusEnum.CLIENT_RESPONSE_FAILED.getMessage());
        return clientResponseJSONBean;
    }
    public void setCodeAndMessage(ClientResponseStatusEnum enums) {
        setCode(enums.getStatusCode());
        setMsg(enums.getMessage());
    }

    public final String toJSONString() {
        return JSON.toJSONString(this, SerializerFeature.WriteMapNullValue, SerializerFeature.DisableCircularReferenceDetect);
    }
}

ClientResponseStatusEnum.java

package com.xiaodou.hestia.entity.enums;

/**
 * @author tao
 */
public enum ClientResponseStatusEnum {
    CLIENT_RESPONSE_SUCCESS("SUCCESS", "成功"),
    CLIENT_RESPONSE_FAILED("FAILED", "失败"),
    public String getStatusCode() {
        return statusCode;
    }
    public void setStatusCode(String statusCode) {
        this.statusCode = statusCode;
    }
    public String getMessage() {
        return message;
    }
    public void setMessage(String message) {
        this.message = message;
    }
    private String statusCode;
    private String message;
    ClientResponseStatusEnum(String statusCode, String message) {
        this.statusCode = statusCode;
        this.message = message;
    }
}

MenuController.java

    /**
     * 分页获取菜单列表
     *
     * @param page  当前页
     * @param limit 每页大小
     * @return
     */
    @RequestMapping(value = "/list", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE + CHARSET)
    @ResponseBody
    public String listMenu(Integer page, Integer limit) {
        LayuiResponseJSONBean layuiResponseJSONBean = new LayuiResponseJSONBean();
        page = page == null ? 1 : page;
        limit = limit == null ? 10 : limit;
        try {
            PageBean pageBean = menuServiceImpl.getMenuList(page, limit);
            layuiResponseJSONBean = LayuiResponseJSONBean.getSuccessClientResponseJSONBean();
            layuiResponseJSONBean.setData(pageBean.getData());
            layuiResponseJSONBean.setCount(pageBean.getCount());
        } catch (Exception e) {
            e.printStackTrace();
            layuiResponseJSONBean = LayuiResponseJSONBean.getFailedClientResponseJSONBean();
            logger.error("获取菜单列表失败：" + e.getMessage());
        }
        return JSONObject.toJSONString(layuiResponseJSONBean);

    }

menu.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>菜单管理</title>
    <script src="${basePath}/resources/js/jquery.min.js"></script>
    <link href="${basePath}/resources/css/layui.css" rel="stylesheet">
    <script src="${basePath}/resources/layui.js"></script>
</head>

<body style="padding: 15px">
<table class="layui-table" lay-data="{ url:'${basePath}/menu/list', page:true, id:'menuTable'}" lay-size="sm"
       lay-filter="demo">
    <thead>
    <tr>
        <th lay-data="{width:'5%',type:'numbers',fixed: 'left'}">序号</th>
        <th lay-data="{width:'10%',field:'name',align:'center'}">菜单名称</th>
        <th lay-data="{width:'10%',field:'path',align:'center'}">菜单路径</th>
        <th lay-data="{width:'10%',field:'icon',align:'center'}">菜单图标</th>
        <th lay-data="{width:'7%',field:'parent_name',align:'center'}">父级菜单</th>
        <th lay-data="{width:'10%',field:'description',align:'center'}">菜单描述</th>
        <th lay-data="{width:'14%',field:'create_time',sort: true,align:'center'}">创建时间</th>
        <th lay-data="{width:'14%',field:'update_time',sort: true,align:'center'}">更新时间</th>
        <th lay-data="{fixed: 'right', width:'20%', align:'center', toolbar: '#barDemo'}">操作</th>
    </tr>
    </thead>
</table>

<script type="text/html" id="barDemo">
    <a class="layui-btn layui-btn-xs" lay-event="detail">子菜单列表</a>
    <button class="layui-btn layui-btn-xs" lay-event="edit"><i class="layui-icon"></i>编辑</button>
    <button class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del"><i class="layui-icon"></i>删除</button>
</script>
<script>
    layui.use('table', function () {
        var table = layui.table;
    });
</script>
</body>
</html>

2.通过分页插件的实现:Mybatis-Plus（本例为springboot项目，前端依然使用layui）

Mybatis-Plus（简称MP）是一个 Mybatis 的增强工具，在 Mybatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

添加Mybatis-Plus的maven依赖

		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus</artifactId>
			<version>2.1.9</version>
		</dependency>

spring注入mybatis配置分页插件 MybatisPlusConfig.java

import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableTransactionManagement
@Configuration
@MapperScan("com.springboot.first.mapper.*")
public class MybatisPlusConfig {
    /**
     * 分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

UserMapper.xml中按照全部查询方式检索即可

    <select id="getUserList" resultType="map">
        select loginName,password,userName,sex,age,address from user
    </select>

UserMapper.java中参数为Pageination

    List<Map> getUserList(Pagination page);

UserServiceImpl.java调用翻页方法，需要page.setRecords回传给页面

    public Page<Map> getUserList(Page page) {
        return page.setRecords(userMapper.getUserList(page));
    }

UserController.java中获取分页后的数据

    @RequestMapping("/toUserList")
    public String toUserList(){
        return "userList";
    }

    @RequestMapping("/getUserList")
    @ResponseBody
    public String getUserList(Integer page,Integer limit){
        LayuiResponseJSONBean layuiResponseJSONBean;
        page = page == null ? 1 : page;
        limit = limit == null ? 10 : limit;
        Page pages = new Page(page,limit);
        Page<Map> userList = userService.getUserList(pages);
        List<Map> list = userList.getRecords();
        //总数
        Integer total = userList.getTotal();
        layuiResponseJSONBean = LayuiResponseJSONBean.getSuccessClientResponseJSONBean();
        layuiResponseJSONBean.setCount(total);
        layuiResponseJSONBean.setData(list);
        return layuiResponseJSONBean.toJSONString();
    }

userList.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <title>用户列表</title>
    <link rel="stylesheet" href="${basePath}/layui/css/layui.css">
</head>
<body>
<table class="layui-table" lay-data="{ url:'${basePath}/user/getUserList', page:true, id:'userTable'}" lay-size="sm"
       lay-filter="user">
    <thead>
    <tr>
        <th lay-data="{width:'5%',type:'numbers',fixed:'left'}">序号</th>
        <th lay-data="{width:'15%',field:'loginName',align:'center'}">登陆名</th>
        <th lay-data="{width:'12%',field:'password',align:'center'}">密码</th>
        <th lay-data="{width:'10%',field:'userName',align:'center'}">用户昵称</th>
        <th lay-data="{width:'10%',field:'age',align:'center'}">年龄</th>
        <th lay-data="{width:'8%',field:'sex',align:'center',templet:'#sex'}">性别</th>
    </tr>
    </thead>
</table>
<script src="${basePath}/layui/layui.js"></script>
<script type="text/html" id="sex">
    {{# if(d.sex === '0'){}}
    <span style="color: #3cc51f;">男</span>
    {{# }else{}}
    <span style="color: #f43530;">女</span>
    {{#  } }}
</script>
<script>
    layui.use(['layer', 'table'], function () {
        var table = layui.table;
    });
</script>
</body>
</html>

请求列表：

控制台打印sql如下：