MyBatis实现物理分页的实例
MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据offset和limit截断记录返回
为了在数据库层面上实现物理分页,又不改变原来MyBatis的函数逻辑,可以编写plugin截获MyBatisExecutor的statementhandler,重写SQL来执行查询
下面的插件代码只针对MySQL
plugin代码
packageplugin; importjava.sql.Connection; importjava.sql.PreparedStatement; importjava.sql.ResultSet; importjava.sql.SQLException; importjava.util.Properties; importorg.apache.ibatis.executor.parameter.ParameterHandler; importorg.apache.ibatis.executor.statement.StatementHandler; importorg.apache.ibatis.logging.Log; importorg.apache.ibatis.logging.LogFactory; importorg.apache.ibatis.mapping.BoundSql; importorg.apache.ibatis.mapping.MappedStatement; importorg.apache.ibatis.plugin.Interceptor; importorg.apache.ibatis.plugin.Intercepts; importorg.apache.ibatis.plugin.Invocation; importorg.apache.ibatis.plugin.Plugin; importorg.apache.ibatis.plugin.Signature; importorg.apache.ibatis.reflection.MetaObject; importorg.apache.ibatis.reflection.factory.DefaultObjectFactory; importorg.apache.ibatis.reflection.factory.ObjectFactory; importorg.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory; importorg.apache.ibatis.reflection.wrapper.ObjectWrapperFactory; importorg.apache.ibatis.scripting.defaults.DefaultParameterHandler; importorg.apache.ibatis.session.Configuration; importorg.apache.ibatis.session.RowBounds; /** *通过拦截<code>StatementHandler</code>的<code>prepare</code>方法,重写sql语句实现物理分页。 *老规矩,签名里要拦截的类型只能是接口。 * */ @Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})}) publicclassPaginationInterceptorimplementsInterceptor{ privatestaticfinalLoglogger=LogFactory.getLog(PaginationInterceptor.class); privatestaticfinalObjectFactoryDEFAULT_OBJECT_FACTORY=newDefaultObjectFactory(); privatestaticfinalObjectWrapperFactoryDEFAULT_OBJECT_WRAPPER_FACTORY=newDefaultObjectWrapperFactory(); privatestaticStringDEFAULT_PAGE_SQL_ID=".*Page$";//需要拦截的ID(正则匹配) @Override publicObjectintercept(Invocationinvocation)throwsThrowable{ StatementHandlerstatementHandler=(StatementHandler)invocation.getTarget(); MetaObjectmetaStatementHandler=MetaObject.forObject(statementHandler,DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); RowBoundsrowBounds=(RowBounds)metaStatementHandler.getValue("delegate.rowBounds"); //分离代理对象链(由于目标类可能被多个拦截器拦截,从而形成多次代理,通过下面的两次循环可以分离出最原始的的目标类) while(metaStatementHandler.hasGetter("h")){ Objectobject=metaStatementHandler.getValue("h"); metaStatementHandler=MetaObject.forObject(object,DEFAULT_OBJECT_FACTORY,DEFAULT_OBJECT_WRAPPER_FACTORY); } //分离最后一个代理对象的目标类 while(metaStatementHandler.hasGetter("target")){ Objectobject=metaStatementHandler.getValue("target"); metaStatementHandler=MetaObject.forObject(object,DEFAULT_OBJECT_FACTORY,DEFAULT_OBJECT_WRAPPER_FACTORY); } //property在mybatissettings文件内配置 Configurationconfiguration=(Configuration)metaStatementHandler.getValue("delegate.configuration"); //设置pageSqlId StringpageSqlId=configuration.getVariables().getProperty("pageSqlId"); if(null==pageSqlId||"".equals(pageSqlId)){ logger.warn("PropertypageSqlIdisnotsetted,usedefault'.*Page$'"); pageSqlId=DEFAULT_PAGE_SQL_ID; } MappedStatementmappedStatement=(MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); //只重写需要分页的sql语句。通过MappedStatement的ID匹配,默认重写以Page结尾的MappedStatement的sql if(mappedStatement.getId().matches(pageSqlId)){ BoundSqlboundSql=(BoundSql)metaStatementHandler.getValue("delegate.boundSql"); ObjectparameterObject=boundSql.getParameterObject(); if(parameterObject==null){ thrownewNullPointerException("parameterObjectisnull!"); }else{ Stringsql=boundSql.getSql(); //重写sql StringpageSql=sql+"LIMIT"+rowBounds.getOffset()+","+rowBounds.getLimit(); metaStatementHandler.setValue("delegate.boundSql.sql",pageSql); //采用物理分页后,就不需要mybatis的内存分页了,所以重置下面的两个参数 metaStatementHandler.setValue("delegate.rowBounds.offset",RowBounds.NO_ROW_OFFSET); metaStatementHandler.setValue("delegate.rowBounds.limit",RowBounds.NO_ROW_LIMIT); } } //将执行权交给下一个拦截器 returninvocation.proceed(); } @Override publicObjectplugin(Objecttarget){ //当目标类是StatementHandler类型时,才包装目标类,否者直接返回目标本身,减少目标被代理的次数 if(targetinstanceofStatementHandler){ returnPlugin.wrap(target,this); }else{ returntarget; } } @Override publicvoidsetProperties(Propertiesproperties){ //TochangebodyofimplementedmethodsuseFile|Settings|FileTemplates. } }
配置plugin
<plugins> <plugininterceptor="plugin.PaginationInterceptor"/> </plugins>
查询SQL
<!--测试分页查询--> <selectid="selectUserByPage"resultMap="dao.base.userResultMap"> <![CDATA[ SELECT*FROMuser ]]> </select>
调用示例
@Override publicList<User>selectUserByPage(intoffset,intlimit){ RowBoundsrowBounds=newRowBounds(offset,limit); returngetSqlSession().selectList("dao.userdao.selectUserByPage",newObject(),rowBounds); }
另外,结合SpringMVC,编写翻页和生成页码代码
页码类
packageutil; /** *CreatedwithIntelliJIDEA. *User:zhenwei.liu *Date:13-8-7 *Time:上午10:29 *TochangethistemplateuseFile|Settings|FileTemplates. */ publicclassPagination{ privateStringurl;//页码url privateintpageSize=10;//每页显示记录数 privateintcurrentPage=1;//当前页码 privateintmaxPage=Integer.MAX_VALUE;//最大页数 //获取offset publicintgetOffset(){ return(currentPage-1)*pageSize; } //获取limit publicintgetLimit(){ returngetPageSize(); } publicStringgetUrl(){ returnurl; } publicvoidsetUrl(Stringurl){ this.url=url; } publicintgetPageSize(){ returnpageSize; } publicvoidsetPageSize(intpageSize){ this.pageSize=pageSize; } publicintgetCurrentPage(){ returncurrentPage; } publicvoidsetCurrentPage(intcurrentPage){ if(currentPage<1) currentPage=1; if(currentPage>maxPage) currentPage=maxPage; this.currentPage=currentPage; } publicintgetMaxPage(){ returnmaxPage; } publicvoidsetMaxPage(intmaxPage){ this.maxPage=maxPage; } }
为了计算最大页码,需要知道数据表的总记录数,查询SQL如下
<!--记录总数--> <selectid="countUser"resultType="Integer"> <![CDATA[ SELECTCOUNT(*)FROMuser ]]> </select>
@Override publicIntegercountTable(){ returngetSqlSession().selectOne("dao.userdao.countUser"); }
Controller中的使用
@RequestMapping("/getUserByPage") publicStringgetUserByPage(@RequestParam intpage,Modelmodel){ pagination.setCurrentPage(page); pagination.setUrl(getCurrentUrl()); pagination.setMaxPage(userDao.countTable()/pagination.getPageSize()+1); List<User>userList=userDao.selectUserByPage( pagination.getOffset(),pagination.getLimit()); model.addAttribute(pagination); model.addAttribute(userList); return"index"; }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。