Mybatis四種分頁(yè)方式

數(shù)組分頁(yè)

查詢出全部數(shù)據(jù)，然后再list中截取需要的部分。

mybatis接口

List<Student> queryStudentsByArray();

xml配置文件

 <select id="queryStudentsByArray"  resultMap="studentmapper">
        select * from student
 </select>

service

接口
List<Student> queryStudentsByArray(int currPage, int pageSize);
實(shí)現(xiàn)接口
 @Override
    public List<Student> queryStudentsByArray(int currPage, int pageSize) {
        //查詢?nèi)繑?shù)據(jù)
        List<Student> students = studentMapper.queryStudentsByArray();
        //從第幾條數(shù)據(jù)開始
        int firstIndex = (currPage - 1) * pageSize;
        //到第幾條數(shù)據(jù)結(jié)束
        int lastIndex = currPage * pageSize;
        return students.subList(firstIndex, lastIndex); //直接在list中截取
    }

controller

    @ResponseBody
    @RequestMapping("/student/array/{currPage}/{pageSize}")
    public List<Student> getStudentByArray(@PathVariable("currPage") int currPage, @PathVariable("pageSize") int pageSize) {
        List<Student> student = StuServiceIml.queryStudentsByArray(currPage, pageSize);
        return student;
    }

sql分頁(yè)

mybatis接口

List<Student> queryStudentsBySql(Map<String,Object> data);

xml文件

<select id="queryStudentsBySql" parameterType="map" resultMap="studentmapper">
        select * from student limit #{currIndex} , #{pageSize}
</select>

service

接口
List<Student> queryStudentsBySql(int currPage, int pageSize);
實(shí)現(xiàn)類
public List<Student> queryStudentsBySql(int currPage, int pageSize) {
        Map<String, Object> data = new HashedMap();
        data.put("currIndex", (currPage-1)*pageSize);
        data.put("pageSize", pageSize);
        return studentMapper.queryStudentsBySql(data);
    }

攔截器分頁(yè)

創(chuàng)建攔截器，攔截mybatis接口方法id以ByPage結(jié)束的語(yǔ)句

package com.autumn.interceptor;
?
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
?
import java.sql.Connection;
import java.util.Map;
import java.util.Properties;
?
/**
 * @Intercepts 說(shuō)明是一個(gè)攔截器
 * @Signature 攔截器的簽名
 * type 攔截的類型 四大對(duì)象之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler)
 * method 攔截的方法
 * args 參數(shù),高版本需要加個(gè)Integer.class參數(shù),不然會(huì)報(bào)錯(cuò)
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class MyPageInterceptor implements Interceptor {
?
    //每頁(yè)顯示的條目數(shù)
    private int pageSize;
    //當(dāng)前現(xiàn)實(shí)的頁(yè)數(shù)
    private int currPage;
    //數(shù)據(jù)庫(kù)類型
    private String dbType;
?
?
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //獲取StatementHandler，默認(rèn)是RoutingStatementHandler
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //獲取statementHandler包裝類
        MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);
?
        //分離代理對(duì)象鏈
        while (MetaObjectHandler.hasGetter("h")) {
            Object obj = MetaObjectHandler.getValue("h");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }
?
        while (MetaObjectHandler.hasGetter("target")) {
            Object obj = MetaObjectHandler.getValue("target");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }
?
        //獲取連接對(duì)象
        //Connection connection = (Connection) invocation.getArgs()[0];
?
?
        //object.getValue("delegate");  獲取StatementHandler的實(shí)現(xiàn)類
?
        //獲取查詢接口映射的相關(guān)信息
        MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");
        String mapId = mappedStatement.getId();
?
        //statementHandler.getBoundSql().getParameterObject();
?
        //攔截以.ByPage結(jié)尾的請(qǐng)求，分頁(yè)功能的統(tǒng)一實(shí)現(xiàn)
        if (mapId.matches(".+ByPage$")) {
            //獲取進(jìn)行數(shù)據(jù)庫(kù)操作時(shí)管理參數(shù)的handler
            ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler");
            //獲取請(qǐng)求時(shí)的參數(shù)
            Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
            //也可以這樣獲取
            //paraObject = (Map<String, Object>) statementHandler.getBoundSql().getParameterObject();
?
            //參數(shù)名稱和在service中設(shè)置到map中的名稱一致
            currPage = (int) paraObject.get("currPage");
            pageSize = (int) paraObject.get("pageSize");
?
            String sql = (String) MetaObjectHandler.getValue("delegate.boundSql.sql");
            //也可以通過statementHandler直接獲取
            //sql = statementHandler.getBoundSql().getSql();
?
            //構(gòu)建分頁(yè)功能的sql語(yǔ)句
            String limitSql;
            sql = sql.trim();
            limitSql = sql + " limit " + (currPage - 1) * pageSize + "," + pageSize;
?
            //將構(gòu)建完成的分頁(yè)sql語(yǔ)句賦值個(gè)體'delegate.boundSql.sql'，偷天換日
            MetaObjectHandler.setValue("delegate.boundSql.sql", limitSql);
        }
        //調(diào)用原對(duì)象的方法，進(jìn)入責(zé)任鏈的下一級(jí)
        return invocation.proceed();
    }
?
?
    //獲取代理對(duì)象
    @Override
    public Object plugin(Object o) {
        //生成object對(duì)象的動(dòng)態(tài)代理對(duì)象
        return Plugin.wrap(o, this);
    }
?
    //設(shè)置代理對(duì)象的參數(shù)
    @Override
    public void setProperties(Properties properties) {
        //如果項(xiàng)目中分頁(yè)的pageSize是統(tǒng)一的，也可以在這里統(tǒng)一配置和獲取，這樣就不用每次請(qǐng)求都傳遞pageSize參數(shù)了。參數(shù)是在配置攔截器時(shí)配置的。
        String limit1 = properties.getProperty("limit", "10");
        this.pageSize = Integer.valueOf(limit1);
        this.dbType = properties.getProperty("dbType", "mysql");
    }
}

配置文件SqlMapConfig.xml

<configuration>

    <plugins>
        <plugin interceptor="com.autumn.interceptor.MyPageInterceptor">
            <property name="limit" value="10"/>
            <property name="dbType" value="mysql"/>
        </plugin>
    </plugins>

</configuration>

mybatis配置

<!--接口-->
List<AccountExt> getAllBookByPage(@Param("currPage")Integer pageNo,@Param("pageSize")Integer pageSize);
<!--xml配置文件-->
  <sql id="getAllBooksql" >
    acc.id, acc.cateCode, cate_name, user_id,u.name as user_name, money, remark, time
  </sql>
  <select id="getAllBook" resultType="com.autumn.pojo.AccountExt" >
    select
    <include refid="getAllBooksql" />
    from account as acc
  </select>

service

    public List<AccountExt> getAllBookByPage(String pageNo,String pageSize) {
        return accountMapper.getAllBookByPage(Integer.parseInt(pageNo),Integer.parseInt(pageSize));
    }

controller

    @RequestMapping("/getAllBook")
    @ResponseBody
    public Page getAllBook(String pageNo,String pageSize,HttpServletRequest request,HttpServletResponse response){
        pageNo=pageNo==null?"1":pageNo;   //當(dāng)前頁(yè)碼
        pageSize=pageSize==null?"5":pageSize;   //頁(yè)面大小
        //獲取當(dāng)前頁(yè)數(shù)據(jù)
        List<AccountExt> list = bookService.getAllBookByPage(pageNo,pageSize);
        //獲取總數(shù)據(jù)大小
        int totals = bookService.getAllBook();
        //封裝返回結(jié)果
        Page page = new Page();
        page.setTotal(totals+"");
        page.setRows(list);
        return page;
    }

Page實(shí)體類

package com.autumn.pojo;

import java.util.List;

/**
 * Created by Autumn on 2018/6/21.
 */
public class Page {
    private String pageNo = null;
    private String pageSize = null;
    private String total = null;
    private List rows = null;

    public String getTotal() {
        return total;
    }

    public void setTotal(String total) {
        this.total = total;
    }

    public List getRows() {
        return rows;
    }

    public void setRows(List rows) {
        this.rows = rows;
    }

    public String getPageNo() {
        return pageNo;
    }

    public void setPageNo(String pageNo) {
        this.pageNo = pageNo;
    }

    public String getPageSize() {
        return pageSize;
    }

    public void setPageSize(String pageSize) {
        this.pageSize = pageSize;
    }

}

前端

bootstrap-table接受數(shù)據(jù)格式

{
  "total": 3,
  "rows": [
    {
      "id": 0,
      "name": "Item 0",
      "price": "$0"
    },
    {
      "id": 1,
      "name": "Item 1",
      "price": "$1"
    }
  ]
}

boostrap-table用法

        var $table = $('#table');
        $table.bootstrapTable({
        url: "/${appName}/manager/bookController/getAllBook",
        method: 'post',
        contentType: "application/x-www-form-urlencoded",
        dataType: "json",
        pagination: true, //分頁(yè)
        sidePagination: "server", //服務(wù)端處理分頁(yè)
        pageList: [5, 10, 25],
        pageSize: 5,
        pageNumber:1,
        //toolbar:"#tb",
        singleSelect: false,
        queryParamsType : "limit",
        queryParams: function queryParams(params) {   //設(shè)置查詢參數(shù)
          var param = {
            pageNo: params.offset/params.limit+1,  //offset為數(shù)據(jù)開始索引,轉(zhuǎn)換為顯示當(dāng)前頁(yè)
            pageSize: params.limit  //頁(yè)面大小
          };
          console.info(params);   //查看參數(shù)是什么
          console.info(param);   //查看自定義的參數(shù)
          return param;
        },
        cache: false,
        //data-locale: "zh-CN", //表格漢化
        //search: true, //顯示搜索框
        columns: [
                {
                    checkbox: true
                },
                {
                    title: '消費(fèi)類型',
                    field: 'cate_name',
                    valign: 'middle'
                },
                {
                    title: '消費(fèi)金額',
                    field: 'money',
                    valign: 'middle',
                    formatter:function(value,row,index){
                        if(!isNaN(value)){   //是數(shù)字
                            return value/100;
                        }
                    }
                },
                {
                    title: '備注',
                    field: 'remark',
                    valign: 'middle'
                },
                {
                    title: '消費(fèi)時(shí)間',
                    field: 'time',
                    valign: 'middle'
                },
                {
                    title: '操作',
                    field: '',
                    formatter:function(value,row,index){
                        var f = '<a href="#" class="btn btn-gmtx-define1" onclick="delBook(\''+ row.id +'\')">刪除</a> ';
                        return f;
                       }
                }
            ]
          });
      });

RowBounds分頁(yè)

數(shù)據(jù)量小時(shí)，RowBounds不失為一種好辦法。但是數(shù)據(jù)量大時(shí)，實(shí)現(xiàn)攔截器就很有必要了。

mybatis接口加入RowBounds參數(shù)

public List<UserBean> queryUsersByPage(String userName, RowBounds rowBounds);

service

    @Override
    @Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.SUPPORTS)
    public List<RoleBean> queryRolesByPage(String roleName, int start, int limit) {
        return roleDao.queryRolesByPage(roleName, new RowBounds(start, limit));
    }