数组分页
查询出全部数据,然后再list中截取需要的部分。
mybatis接口
1 |
List<Student> queryStudentsByArray(); |
xml配置文件
1 2 3 |
<select id= "queryStudentsByArray" resultMap= "studentmapper" > select * from student </select> |
service
1 2 3 4 5 6 7 8 9 10 11 12 13 |
接口 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
1 2 3 4 5 6 |
@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接口
1 |
List<Student> queryStudentsBySql(Map<String,Object> data); |
xml文件
1 2 3 |
<select id= "queryStudentsBySql" parameterType= "map" resultMap= "studentmapper" > select * from student limit #{currIndex} , #{pageSize} </select> |
service
1 2 3 4 5 6 7 8 9 |
接口 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结束的语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 |
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
1 2 3 4 5 6 7 8 9 10 |
<configuration>
<plugins> <plugin interceptor= "com.autumn.interceptor.MyPageInterceptor" > <property name= "limit" value= "10" /> <property name= "dbType" value= "mysql" /> </plugin> </plugins>
</configuration> |
mybatis配置
1 2 3 4 5 6 7 8 9 10 11 |
<!--接口--> 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
1 2 3 |
public List<AccountExt> getAllBookByPage(String pageNo,String pageSize) { return accountMapper.getAllBookByPage(Integer.parseInt(pageNo),Integer.parseInt(pageSize)); } |
controller
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
@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实体类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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接受数据格式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
{ "total" : 3 , "rows" : [ { "id" : 0 , "name" : "Item 0" , "price" : "$0" }, { "id" : 1 , "name" : "Item 1" , "price" : "$1" } ] } |
boostrap-table用法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
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参数
1 |
public List<UserBean> queryUsersByPage(String userName, RowBounds rowBounds); |
service
1 2 3 4 5 |
@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分页的方式实例请查看下面的相关链接
原文链接:https://HdhCmsTestcnblogs测试数据/aeolian/p/9229149.html
查看更多关于Mybatis分页的4种方式实例的详细内容...