Mybatis四种分页方式小结_Java

数组分页

查询出全部数据，然后再list中截取需要的部分。

mybatis接口

list<student> querystudentsbyarray();

xml配置文件

<select id="querystudentsbyarray" resultmap="studentmapper"> select * from student </select>

service

接口 list<student> querystudentsbyarray(int currpage, int pagesize); 实现接口 @override public list<student> querystudentsbyarray(int currpage, int pagesize) { //查询全部数据 list<student> students = studentmapper.querystudentsbyarray(); //从第几条数据开始 int firstindex = (currpage - 1) * pagesize; //到第几条数据结束 int lastindex = currpage * pagesize; return students.sublist(firstindex, lastindex); //直接在list中截取 }接口
list<student> querystudentsbyarray(int currpage, int pagesize);
实现接口
 @override
    public list<student> querystudentsbyarray(int currpage, int pagesize) {
        //查询全部数据
        list<student> students = studentmapper.querystudentsbyarray();
        //从第几条数据开始
        int firstindex = (currpage - 1) * pagesize;
        //到第几条数据结束
        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分页

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);
实现类
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);
    }

拦截器分页

创建拦截器，拦截mybatis接口方法id以bypage结束的语句

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 说明是一个拦截器
 * @signature 拦截器的签名
 * type 拦截的类型 四大对象之一( executor,resultsethandler,parameterhandler,statementhandler)
 * method 拦截的方法
 * args 参数,高版本需要加个integer.class参数,不然会报错
 */
@intercepts({@signature(type = statementhandler.class, method = "prepare", args = {connection.class})})
public class mypageinterceptor implements interceptor {

    //每页显示的条目数
    private int pagesize;
    //当前现实的页数
    private int currpage;
    //数据库类型
    private string dbtype;


    @override
    public object intercept(invocation invocation) throws throwable {
        //获取statementhandler，默认是routingstatementhandler
        statementhandler statementhandler = (statementhandler) invocation.gettarget();
        //获取statementhandler包装类
        metaobject metaobjecthandler = systemmetaobject.forobject(statementhandler);

        //分离代理对象链
        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);
        }

        //获取连接对象
        //connection connection = (connection) invocation.getargs()[0];


        //object.getvalue("delegate");  获取statementhandler的实现类

        //获取查询接口映射的相关信息
        mappedstatement mappedstatement = (mappedstatement) metaobjecthandler.getvalue("delegate.mappedstatement");
        string mapid = mappedstatement.getid();

        //statementhandler.getboundsql().getparameterobject();

        //拦截以.bypage结尾的请求，分页功能的统一实现
        if (mapid.matches(".+bypage$")) {
            //获取进行数据库操作时管理参数的handler
            parameterhandler parameterhandler = (parameterhandler) metaobjecthandler.getvalue("delegate.parameterhandler");
            //获取请求时的参数
            map<string, object> paraobject = (map<string, object>) parameterhandler.getparameterobject();
            //也可以这样获取
            //paraobject = (map<string, object>) statementhandler.getboundsql().getparameterobject();

            //参数名称和在service中设置到map中的名称一致
            currpage = (int) paraobject.get("currpage");
            pagesize = (int) paraobject.get("pagesize");

            string sql = (string) metaobjecthandler.getvalue("delegate.boundsql.sql");
            //也可以通过statementhandler直接获取
            //sql = statementhandler.getboundsql().getsql();

            //构建分页功能的sql语句
            string limitsql;
            sql = sql.trim();
            limitsql = sql + " limit " + (currpage - 1) * pagesize + "," + pagesize;

            //将构建完成的分页sql语句赋值个体'delegate.boundsql.sql'，偷天换日
            metaobjecthandler.setvalue("delegate.boundsql.sql", limitsql);
        }
        //调用原对象的方法，进入责任链的下一级
        return invocation.proceed();
    }


    //获取代理对象
    @override
    public object plugin(object o) {
        //生成object对象的动态代理对象
        return plugin.wrap(o, this);
    }

    //设置代理对象的参数
    @override
    public void setproperties(properties properties) {
        //如果项目中分页的pagesize是统一的，也可以在这里统一配置和获取，这样就不用每次请求都传递pagesize参数了。参数是在配置拦截器时配置的。
        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;   //当前页码
        pagesize=pagesize==null?"5":pagesize;   //页面大小
        //获取当前页数据
        list<accountext> list = bookservice.getallbookbypage(pageno,pagesize);
        //获取总数据大小
        int totals = bookservice.getallbook();
        //封装返回结果
        page page = new page();
        page.settotal(totals+"");
        page.setrows(list);
        return page;
    }

page实体类

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接受数据格式

{
  "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, //分页
        sidepagination: "server", //服务端处理分页
        pagelist: [5, 10, 25],
        pagesize: 5,
        pagenumber:1,
        //toolbar:"#tb",
        singleselect: false,
        queryparamstype : "limit",
        queryparams: function queryparams(params) {   //设置查询参数
          var param = {
            pageno: params.offset/params.limit+1,  //offset为数据开始索引,转换为显示当前页
            pagesize: params.limit  //页面大小
          };
          console.info(params);   //查看参数是什么
          console.info(param);   //查看自定义的参数
          return param;
        },
        cache: false,
        //data-locale: "zh-cn", //表格汉化
        //search: true, //显示搜索框
        columns: [
                {
                    checkbox: true
                },
                {
                    title: '消费类型',
                    field: 'cate_name',
                    valign: 'middle'
                },
                {
                    title: '消费金额',
                    field: 'money',
                    valign: 'middle',
                    formatter:function(value,row,index){
                        if(!isnan(value)){   //是数字
                            return value/100;
                        }
                    }
                },
                {
                    title: '备注',
                    field: 'remark',
                    valign: 'middle'
                },
                {
                    title: '消费时间',
                    field: 'time',
                    valign: 'middle'
                },
                {
                    title: '操作',
                    field: '',
                    formatter:function(value,row,index){
                        var f = '<a href="#" rel="external nofollow"  class="btn btn-gmtx-define1" onclick="delbook(\''+ row.id +'\')">删除</a> ';
                        return f;
                       }
                }
            ]
          });
      });

rowbounds分页

数据量小时，rowbounds不失为一种好办法。但是数据量大时，实现拦截器就很有必要了。

mybatis接口加入rowbounds参数

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));
    }

到此这篇关于mybatis四种分页方式小结的文章就介绍到这了,更多相关mybatis 分页内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！