Mybatis四种分页方式 - 教育改变生活

Mybatis四种分页方式

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

List<Student> queryStudentsByArray();

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

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

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

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

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

接口
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);
      returnstudentMapper.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

<plugininterceptor="com.autumn.interceptor.MyPageInterceptor">

<propertyname="limit" value="10"/>

List<AccountExt>getAllBookByPage(@Param("currPage")IntegerpageNo,@Param("pageSize")Integer pageSize);

<sqlid="getAllBooksql" >

acc.id,acc.cateCode, cate_name, user_id,u.name as user_name, money, remark, time

<selectid="getAllBook" resultType="com.autumn.pojo.AccountExt">

<includerefid="getAllBooksql" />

publicList<AccountExt> getAllBookByPage(String pageNo,String pageSize) {

returnaccountMapper.getAllBookByPage(Integer.parseInt(pageNo),Integer.parseInt(pageSize));

@RequestMapping("/getAllBook")

public PagegetAllBook(String pageNo,String pageSize,HttpServletRequestrequest,HttpServletResponse response){

pageNo=pageNo==null?"1":pageNo; //当前页码

pageSize=pageSize==null?"5":pageSize; //页面大小

//获取当前页数据

List<AccountExt> list =bookService.getAllBookByPage(pageNo,pageSize);

//获取总数据大小

inttotals = bookService.getAllBook();

//封装返回结果

Pagepage = new Page();

page.setTotal(totals+"");

package com.autumn.pojo;

import java.util.List;

* Created byAutumn on 2018/6/21.

privateString pageNo = null;

privateString pageSize = null;

privateString total = null;

private Listrows = null;

publicString getTotal() {

public voidsetTotal(String total) {

public ListgetRows() {

public voidsetRows(List rows) {

publicString getPageNo() {

public voidsetPageNo(String pageNo) {

this.pageNo = pageNo;

publicString getPageSize() {

public voidsetPageSize(String pageSize) {

this.pageSize = pageSize;

bootstrap-table接受数据格式

boostrap-table用法

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

mybatis接口加入RowBounds参数

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

@Transactional(isolation = Isolation.READ_COMMITTED, propagation =Propagation.SUPPORTS)

publicList<RoleBean> queryRolesByPage(String roleName, int start, int limit) {

returnroleDao.queryRolesByPage(roleName, new RowBounds(start, limit));