Mybatis四种分页方式

拼搏现实的明天。 2024-04-18 17:20 155阅读 0赞

转自：https://www.cnblogs.com/aeolian/p/9229149.html

mybatis的4种分页方式: https://blog.csdn.net/u010667011/article/details/83819277

mybatis-plus分页查询: https://www.jianshu.com/p/43bfe6fe8d89

MyBatis 分页插件 PageHelper: https://pagehelper.github.io/

数组分页

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

mybatis接口

  1. List<Student> queryStudentsByArray();

xml配置文件

  1. <select id="queryStudentsByArray"  resultMap="studentmapper">
  2.         select * from student
  3.  </select>

service

复制代码

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

复制代码

controller

复制代码

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

复制代码

sql分页

mybatis接口

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

xml文件

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

service

复制代码

  1. 接口
  2. List<Student> queryStudentsBySql(int currPage, int pageSize);
  3. 实现类
  4. public List<Student> queryStudentsBySql(int currPage, int pageSize) {
  5.         Map<String, Object> data = new HashedMap();
  6.         data.put("currIndex", (currPage-1)*pageSize);
  7.         data.put("pageSize", pageSize);
  8.         return studentMapper.queryStudentsBySql(data);
  9.     }

复制代码

springjdbctemplete写分页语句

拦截器分页

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

复制代码

  1. package com.autumn.interceptor;
  3. import org.apache.ibatis.executor.Executor;
  4. import org.apache.ibatis.executor.parameter.ParameterHandler;
  5. import org.apache.ibatis.executor.resultset.ResultSetHandler;
  6. import org.apache.ibatis.executor.statement.StatementHandler;
  7. import org.apache.ibatis.mapping.MappedStatement;
  8. import org.apache.ibatis.plugin.*;
  9. import org.apache.ibatis.reflection.MetaObject;
  10. import org.apache.ibatis.reflection.SystemMetaObject;
  12. import java.sql.Connection;
  13. import java.util.Map;
  14. import java.util.Properties;
  16. /**
  17.  * @Intercepts 说明是一个拦截器
  18.  * @Signature 拦截器的签名
  19.  * type 拦截的类型 四大对象之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler)
  20.  * method 拦截的方法
  21.  * args 参数,高版本需要加个Integer.class参数,不然会报错
  22.  */
  23. @Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
  24. public class MyPageInterceptor implements Interceptor {
  26.     //每页显示的条目数
  27.     private int pageSize;
  28.     //当前现实的页数
  29.     private int currPage;
  30.     //数据库类型
  31.     private String dbType;
  34.     @Override
  35.     public Object intercept(Invocation invocation) throws Throwable {
  36.         //获取StatementHandler，默认是RoutingStatementHandler
  37.         StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
  38.         //获取statementHandler包装类
  39.         MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);
  41.         //分离代理对象链
  42.         while (MetaObjectHandler.hasGetter("h")) {
  43.             Object obj = MetaObjectHandler.getValue("h");
  44.             MetaObjectHandler = SystemMetaObject.forObject(obj);
  45.         }
  47.         while (MetaObjectHandler.hasGetter("target")) {
  48.             Object obj = MetaObjectHandler.getValue("target");
  49.             MetaObjectHandler = SystemMetaObject.forObject(obj);
  50.         }
  52.         //获取连接对象
  53.         //Connection connection = (Connection) invocation.getArgs()[0];
  56.         //object.getValue("delegate");  获取StatementHandler的实现类
  58.         //获取查询接口映射的相关信息
  59.         MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");
  60.         String mapId = mappedStatement.getId();
  62.         //statementHandler.getBoundSql().getParameterObject();
  64.         //拦截以.ByPage结尾的请求，分页功能的统一实现
  65.         if (mapId.matches(".+ByPage$")) {
  66.             //获取进行数据库操作时管理参数的handler
  67.             ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler");
  68.             //获取请求时的参数
  69.             Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
  70.             //也可以这样获取
  71.             //paraObject = (Map<String, Object>) statementHandler.getBoundSql().getParameterObject();
  73.             //参数名称和在service中设置到map中的名称一致
  74.             currPage = (int) paraObject.get("currPage");
  75.             pageSize = (int) paraObject.get("pageSize");
  77.             String sql = (String) MetaObjectHandler.getValue("delegate.boundSql.sql");
  78.             //也可以通过statementHandler直接获取
  79.             //sql = statementHandler.getBoundSql().getSql();
  81.             //构建分页功能的sql语句
  82.             String limitSql;
  83.             sql = sql.trim();
  84.             limitSql = sql + " limit " + (currPage - 1) * pageSize + "," + pageSize;
  86.             //将构建完成的分页sql语句赋值个体'delegate.boundSql.sql'，偷天换日
  87.             MetaObjectHandler.setValue("delegate.boundSql.sql", limitSql);
  88.         }
  89.         //调用原对象的方法，进入责任链的下一级
  90.         return invocation.proceed();
  91.     }
  94.     //获取代理对象
  95.     @Override
  96.     public Object plugin(Object o) {
  97.         //生成object对象的动态代理对象
  98.         return Plugin.wrap(o, this);
  99.     }
  101.     //设置代理对象的参数
  102.     @Override
  103.     public void setProperties(Properties properties) {
  104.         //如果项目中分页的pageSize是统一的，也可以在这里统一配置和获取，这样就不用每次请求都传递pageSize参数了。参数是在配置拦截器时配置的。
  105.         String limit1 = properties.getProperty("limit", "10");
  106.         this.pageSize = Integer.valueOf(limit1);
  107.         this.dbType = properties.getProperty("dbType", "mysql");
  108.     }
  109. }

复制代码

配置文件SqlMapConfig.xml

复制代码

  1. <configuration>
  3.     <plugins>
  4.         <plugin interceptor="com.autumn.interceptor.MyPageInterceptor">
  5.             <property name="limit" value="10"/>
  6.             <property name="dbType" value="mysql"/>
  7.         </plugin>
  8.     </plugins>
  10. </configuration>

复制代码

mybatis配置

复制代码

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

复制代码

service

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

controller

复制代码

  1. @RequestMapping("/getAllBook")
  2.     @ResponseBody
  3.     public Page getAllBook(String pageNo,String pageSize,HttpServletRequest request,HttpServletResponse response){
  4.         pageNo=pageNo==null?"1":pageNo;   //当前页码
  5.         pageSize=pageSize==null?"5":pageSize;   //页面大小
  6.         //获取当前页数据
  7.         List<AccountExt> list = bookService.getAllBookByPage(pageNo,pageSize);
  8.         //获取总数据大小
  9.         int totals = bookService.getAllBook();
  10.         //封装返回结果
  11.         Page page = new Page();
  12.         page.setTotal(totals+"");
  13.         page.setRows(list);
  14.         return page;
  15.     }

复制代码

Page实体类

复制代码

  1. package com.autumn.pojo;
  3. import java.util.List;
  5. /**
  6.  * Created by Autumn on 2018/6/21.
  7.  */
  8. public class Page {
  9.     private String pageNo = null;
  10.     private String pageSize = null;
  11.     private String total = null;
  12.     private List rows = null;
  14.     public String getTotal() {
  15.         return total;
  16.     }
  18.     public void setTotal(String total) {
  19.         this.total = total;
  20.     }
  22.     public List getRows() {
  23.         return rows;
  24.     }
  26.     public void setRows(List rows) {
  27.         this.rows = rows;
  28.     }
  30.     public String getPageNo() {
  31.         return pageNo;
  32.     }
  34.     public void setPageNo(String pageNo) {
  35.         this.pageNo = pageNo;
  36.     }
  38.     public String getPageSize() {
  39.         return pageSize;
  40.     }
  42.     public void setPageSize(String pageSize) {
  43.         this.pageSize = pageSize;
  44.     }
  46. }

复制代码

前端

bootstrap-table接受数据格式

复制代码

  1. {
  2.   "total": 3,
  3.   "rows": [
  4.     {
  5.       "id": 0,
  6.       "name": "Item 0",
  7.       "price": "$0"
  8.     },
  9.     {
  10.       "id": 1,
  11.       "name": "Item 1",
  12.       "price": "$1"
  13.     }
  14.   ]
  15. }

复制代码

boostrap-table用法

复制代码

  1. var $table = $('#table');
  2.         $table.bootstrapTable({
  3.         url: "/${appName}/manager/bookController/getAllBook",
  4.         method: 'post',
  5.         contentType: "application/x-www-form-urlencoded",
  6.         dataType: "json",
  7.         pagination: true, //分页
  8.         sidePagination: "server", //服务端处理分页
  9.         pageList: [5, 10, 25],
  10.         pageSize: 5,
  11.         pageNumber:1,
  12.         //toolbar:"#tb",
  13.         singleSelect: false,
  14.         queryParamsType : "limit",
  15.         queryParams: function queryParams(params) {   //设置查询参数
  16.           var param = {
  17.             pageNo: params.offset/params.limit+1,  //offset为数据开始索引,转换为显示当前页
  18.             pageSize: params.limit  //页面大小
  19.           };
  20.           console.info(params);   //查看参数是什么
  21.           console.info(param);   //查看自定义的参数
  22.           return param;
  23.         },
  24.         cache: false,
  25.         //data-locale: "zh-CN", //表格汉化
  26.         //search: true, //显示搜索框
  27.         columns: [
  28.                 {
  29.                     checkbox: true
  30.                 },
  31.                 {
  32.                     title: '消费类型',
  33.                     field: 'cate_name',
  34.                     valign: 'middle'
  35.                 },
  36.                 {
  37.                     title: '消费金额',
  38.                     field: 'money',
  39.                     valign: 'middle',
  40.                     formatter:function(value,row,index){
  41.                         if(!isNaN(value)){   //是数字
  42.                             return value/100;
  43.                         }
  44.                     }
  45.                 },
  46.                 {
  47.                     title: '备注',
  48.                     field: 'remark',
  49.                     valign: 'middle'
  50.                 },
  51.                 {
  52.                     title: '消费时间',
  53.                     field: 'time',
  54.                     valign: 'middle'
  55.                 },
  56.                 {
  57.                     title: '操作',
  58.                     field: '',
  59.                     formatter:function(value,row,index){
  60.                         var f = '<a href="#" class="btn btn-gmtx-define1" οnclick="delBook(\''+ row.id +'\')">删除</a> ';
  61.                         return f;
  62.                        }
  63.                 }
  64.             ]
  65.           });
  66.       });

复制代码

RowBounds分页

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

mybatis接口加入RowBounds参数

public List queryUsersByPage(String userName, RowBounds rowBounds);

service

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

面朝大海，春暖花开~

发表评论

表情：
评论列表 （有 0 条评论，155人围观）

还没有评论，来说两句吧...

相关阅读