Mybatis分页的4种方式实例

Mybatis分页的4种方式实例

数组分页

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

mybatis接口

List queryStudentsByArray();

xml配置文件

select * from student

service

接口

List queryStudentsByArray(int currPage, int pageSize);

实现接口

@Override

public 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中截取

}

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分页

mybatis接口

List queryStudentsBySql(Map data);

xml文件

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

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

}

-分页

创建-，拦截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 paraObject = (Map) 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");

}

}

配置文件SqlMapConfig.xml

mybatis配置

List getAllBookByPage(@Param("currPage")Integer pageNo,@Param("pageSize")Integer pageSize);

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

select

from account as acc

service

public List 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 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/boohiqavoxZYkController/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 = '删除 ';

return f;

}

}

]

});

});

RowBounds分页

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

mybatis接口加入RowBounds参数

public List queryUsersByPage(String userName, RowBounds rowBounds);

service

@Override

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

public List queryRolesByPage(String roleName, int start, int limit) {

return roleDao.queryRolesByPage(roleName, new RowBounds(start, limit));

}

更多关于Mybatis分页的方式实例请查看下面的相关链接

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。