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";
}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。