mybatis 分页_Mybatis分页

一.数据分页

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

1.mybatis接口

List queryStudentsByArray();

2.xml配置文件

     select * from student

3.service

##接口List queryStudentsByArray(int currPage, int pageSize);##实现接口@Overridepublic List queryStudentsByArray(int currPage, int pageSize) {     //查询全部数据     List students = studentMapper.queryStudentsByArray();     //从第几条数据开始     int firstIndex = (currPage - 1) * pageSize;     //到第几条数据结束     int lastIndex = currPage * pageSize;     return students.subList(firstIndex, lastIndex); //直接在list中截取}

4.controller

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

二.sql分页

1.mybatis接口

List queryStudentsBySql(Map data);

2.xml文件

    select * from student limit #{currIndex} , #{pageSize}

3.service

##接口List queryStudentsBySql(int currPage, int pageSize);##实现类public List queryStudentsBySql(int currPage, int pageSize) {    Map data = new HashedMap();    data.put("currIndex", (currPage-1)*pageSize);    data.put("pageSize", pageSize);    return studentMapper.queryStudentsBySql(data);}

4.Controller

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

三.拦截器分页

1.创建拦截器，拦截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) 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");    }}

2.Page实体类

package com.autumn.pojo;import java.util.List;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;    }}

3.配置文件SqlMapConfig.xml

4.mybatis配置

List<AccountExt> getAllBookByPage(@Param("currPage")Integer pageNo,@Param("pageSize")Integer pageSize);<sql id="getAllBooksql" >    acc.id, acc.cateCode, cate_name, user_id,u.name as user_name, money, remark, timesql><select id="getAllBookByPage" resultType="com.autumn.pojo.AccountExt" >    select    <include refid="getAllBooksql" />    from account as accselect>

5.service

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

这里我们虽然传入了currPage和pageSize两个参数，但是在sql的xml文件中并没有使用，直接在拦截器中获取到统一使用。

6.controller

@RequestMapping("/getAllBook")@ResponseBodypublic Page getAllBook(String pageNo,String pageSize,HttpServletRequest request,HttpServletResponse response){    pageNo=pageNo==null?"1":pageNo;   //当前页码    pageSize=pageSize==null?"5":pageSize;   //页面大小    //获取当前页数据    List list = bookService.getAllBookByPage(pageNo,pageSize);    //封装返回结果    Page page = new Page();    page.setRows(list);    return page;}

四.RowBounds分页

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

1.mybatis接口加入RowBounds参数

public ListqueryUsersByPage(String userName, RowBounds rowBounds);

2.service

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