& [Java][MyBatis]物理分页实现
[Java][MyBatis]物理分页实现
Mybatis3.0出来已有段时间了，其实自己挺喜欢这样的一个持久化框架的，因为它简单实用，学习成本低。Mybatis3.0在整体结构上和ibatis2.X差不多，改进特性如下：
1.解析xml引进了Xpath，不像ibatis2.x那样业余
2.动态sql用OGNL解析
3.加入注解配置sql，感觉没什么特别大的用途，我更喜欢xml方式，代码和配置分离，这也是ibatis的初衷
4.加强了缓存这块的功能。Mybatis3.0把缓存模块分得更细，分为“持久实现（prepetual）”和“资源回收策略实现（eviction）”，更好的对缓存功能进行自己组合和扩展
5.终于加入的plugin功能，就像struts一样，这样就可以很好的扩展内部的Executor,，StatementHandler….等内部对象功能。
MyBatis的分页功能还是基于内存分页（查找出所有记录再取出偏移量的记录，如果jdbc驱支持absolute定位或者rs.next()到指定偏移位置），其实这样的分页实现基本没用，特别是大量数据情况下。不过我们可以通过plugin功能来扩展MyBatis的分页功能、实现物理分页。具体做法如下：
1、编写分页插件类：
[java] view plaincopy
2、配置读取类
[java] view plaincopy
3、在mybatis全局配置文件设置
[html] view plaincopy
本文固定链接:
[上一篇][下一篇]
最新文章随机精彩热门排行
精彩内容获取超时，请稍候...
日志总数：3906 篇
评论总数：146 评
标签数量：4476 个
链接总数：4 条
建站日期：
运行天数：1629 天09:40 提问
org.apache.ibatis.reflection.ReflectionException:
这个报错什么意思？
十二月 23, :15 上午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [spring-dispatcher] in context with path [/JFWorkingHourSystem] threw exception [Reque nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'page' in 'class java.lang.Long'] with root cause
org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'page' in 'class java.lang.Long'
at org.apache.ibatis.reflection.Reflector.getGetInvoker(Reflector.java:377)
at org.apache.ibatis.reflection.MetaClass.getGetInvoker(MetaClass.java:167)
at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:149)
at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:45)
at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:113)
at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextMap.get(DynamicContext.java:94)
at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:113)
at ognl.OgnlRuntime.getProperty(OgnlRuntime.java:2317)
at ognl.ASTProperty.getValueBody(ASTProperty.java:114)
at ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
at ognl.SimpleNode.getValue(SimpleNode.java:258)
at ognl.ASTNotEq.getValueBody(ASTNotEq.java:50)
at ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
at ognl.SimpleNode.getValue(SimpleNode.java:258)
at ognl.Ognl.getValue(Ognl.java:494)
at ognl.Ognl.getValue(Ognl.java:644)
at ognl.Ognl.getValue(Ognl.java:619)
at org.apache.ibatis.scripting.xmltags.OgnlCache.getValue(OgnlCache.java:45)
at org.apache.ibatis.scripting.xmltags.ExpressionEvaluator.evaluateBoolean(ExpressionEvaluator.java:29)
at org.apache.ibatis.scripting.xmltags.IfSqlNode.apply(IfSqlNode.java:30)
at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:29)
at org.apache.ibatis.scripting.xmltags.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:37)
at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:275)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:79)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:62)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
at com.sun.proxy.$Proxy19.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:163)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
at com.sun.proxy.$Proxy30.selectByPrimaryKey(Unknown Source)
at com.bjhnd.Service.TiZhiCeDingJiYunDongChuFang.Impl.JiBenXinXiServiceImpl.GetJiBenXinXi(JiBenXinXiServiceImpl.java:41)
at com.bjhnd.Actions.TiZhiCeDingJiYunDongChuFang.JiBenXinXiAction.GetJiBenXinXi(JiBenXinXiAction.java:22)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.logging.log4j.core.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:66)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:617)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:668)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2503)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2492)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
我想要获取当前新用户的ID，查询他的信息。
按时间排序
错误：There is no getter for property named 'page' in 'class java.lang.Long'
如果传入类型为Long类型，则参数需统一修改为[_parameter],修改后的sql语句如下(不管你的参数是什么，都要改成"_parameter")
&select id="" parameterType="java.lang.Long" resultType=""&
SELECT * from c c
&if test="accountId!=null"&
c.accountId=#{_parameter} and c.type=2
其他相关推荐Mybatis 物理分页 - java-web-pagination - ITkeyowrd
Mybatis 物理分页
推荐：1、基本概念 1.1、Spring
Spring是一个开源框架，Spring是于2003 年兴起的一个轻量级的Java 开发框架，由Rod Johnson 在其著作Expert One-On-One J2EE D
一 方言 mybatis.pagination.dialect.Dialect.java package mybatis.pagination./** * Created by Administrator on . */public interface Dialect {public String getLimitString(String sql, int offset, int limit);} mybatis.pagination.dialect.MySqlDialect.java package mybatis.pagination. /** * Created by Administrator on . */public class MySqlDialect implements Dialect{@Overridepublic String getLimitString(String sql, int offset, int limit) {String newSql = &select * from (&+sql+&) e limit &+offset+&,&+return newS}} mybatis.pagination.dialect.DialectProvider.java package mybatis.pagination./** * Created by Administrator on . */public class DialectProvider {private static DialectProvider dialectProvider = new DialectProvider();private DialectProvider(){}public static DialectProvider getInstance(){return dialectP}public Dialect getDialect(String dialectName){if(&mysql&.equals(dialectName)){return new MySqlDialect();}throw new RuntimeException(&can't find the specified dialect : &+dialectName);}} 二 分页拦截器mybatis.pagination.interceptor.PaginationInterceptor.java package mybatis.pagination.import mybatis.pagination.dialect.Dimport mybatis.pagination.dialect.DialectPimport org.apache.ibatis.executor.statement.StatementHimport org.apache.ibatis.mapping.BoundSimport org.apache.ibatis.plugin.*;import org.apache.ibatis.reflection.MetaOimport org.apache.ibatis.reflection.factory.DefaultObjectFimport org.apache.ibatis.reflection.factory.ObjectFimport org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFimport org.apache.ibatis.reflection.wrapper.ObjectWrapperFimport org.apache.ibatis.session.Cimport org.apache.ibatis.session.RowBimport java.sql.Cimport java.util.P/** * 通过拦截&code&StatementHandler&/code&的&code&prepare&/code&方法，重写sql语句实现物理分页。 * 老规矩，签名里要拦截的类型只能是接口。 * */@Intercepts({@Signature(type = StatementHandler.class, method = &prepare&, args = {Connection.class})})public class PaginationInterceptor implements Interceptor {private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();@Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,DEFAULT_OBJECT_WRAPPER_FACTORY);RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue(&delegate.rowBounds&);// property在mybatis settings文件内配置Configuration configuration = (Configuration) metaStatementHandler.getValue(&delegate.configuration&);// 只重写需要分页的sql语句。通过MappedStatement的ID匹配，默认重写以Page结尾的MappedStatement的sqlBoundSql boundSql = (BoundSql) metaStatementHandler.getValue(&delegate.boundSql&);String sql = boundSql.getSql();// 重写sqlDialect dialect = getDialect(configuration);String newSql = dialect.getLimitString(sql,rowBounds.getOffset(),rowBounds.getLimit());metaStatementHandler.setValue(&delegate.boundSql.sql&, newSql);// 采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数metaStatementHandler.setValue(&delegate.rowBounds.offset&, RowBounds.NO_ROW_OFFSET);metaStatementHandler.setValue(&delegate.rowBounds.limit&, RowBounds.NO_ROW_LIMIT);// 将执行权交给下一个拦截器return invocation.proceed();}private Dialect getDialect(Configuration configuration){Properties properties = configuration.getVariables();if(properties==null){properties = new Properties();}String dialectName = properties.getProperty(&dialect&,&mysql&);return DialectProvider.getInstance().getDialect(dialectName);}@Overridepublic Object plugin(Object target) {// 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数if (target instanceof StatementHandler) {return Plugin.wrap(target, this);} else {}}@Overridepublic void setProperties(Properties properties) {//To change body of implemented methods use File | Settings | File Templates.}}三 Mybatis 配置 &properties&&property name=&dialect& value=&mysql&/&&/properties& &plugins&&plugin interceptor=&mybatis.pagination.interceptor.PaginationInterceptor&/&&/plugins& 四 pom.xml &dependency&&groupId&org.mybatis&/groupId&&artifactId&mybatis&/artifactId&&version&3.1.1&/version&&/dependency&
一 方言 mybatis.pagination.dialect.Dialect.java package mybatis.pagination./** * Created by Administrator on . */public interface Dialect {
public String
相关阅读排行
相关内容推荐
请激活账号
为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品，请激活账号。
您的注册邮箱：
如果您没有收到激活邮件，请注意检查垃圾箱。博客分类：
网上有很多MyBatis物理分页插件，基本都是围绕拦截指定分页对象来处理的；网上分页逻辑为，先查出总的列表数，使用分页插件查出分页后列表集合，封装总列表数和分页列表集合到Page结果对象；用起来还要自己封装结果类，相对比较麻烦；我想省去这些复杂的步骤，直接传递分页对象，返回结果对象。
这里我想像JPA一样传递Pagination分页对象进去，返回Page结果对象。
MyBatis预处理拦截器拦截指定的Pagination，并重写当前的SQL为分页SQL
MyBatis结果集拦截器拦截指定的Pagination，根据分页SQL得到查询总量的SQL执行得到总的列表数，得到原分页后的列表集合，封装为Page对象并返回
PaginationStatementInterceptor类为SQL预处理拦截类，拦截指定的分页类，这里我使用的是org.springframework.data.domain.Pageable分页类（Spring-data-commons包下的，如果用Spring-data应该会附带出来这个包的）；如果MyBatis中分页方法有这个分页类，则拦截并修改原来的执行SQL为想要的分页SQL；以MySQL为例比如：之前的SQL为 select * from user 修改后的SQL为 select * from user limit 100, 10利用反射替换原来SQL值的位置delegate.boundSql.sql
下面举例用的方言为MySQL对应的类MySql5Dialect继承了Dialect类，该类的getLimitString得到分页SQL，如果是别的数据库继承Dialect类，重写getLimitString即可
import com.mm.persist.expands.mybatis.dialect.D
import com.mm.persist.expands.mybatis.dialect.MySql5D
import com.mm.persist.expands.mybatis.dialect.OracleD
import com.mm.persist.expands.mybatis.dialect.SQLServer2005D
import mons.logging.L
import mons.logging.LogF
import org.apache.ibatis.binding.MapperM
import org.apache.ibatis.executor.parameter.ParameterH
import org.apache.ibatis.executor.statement.StatementH
import org.apache.ibatis.mapping.BoundS
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaO
import org.apache.ibatis.reflection.factory.DefaultObjectF
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperF
import org.apache.ibatis.session.C
import org.springframework.data.domain.P
import java.sql.C
import java.util.P
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class PaginationStatementInterceptor implements Interceptor {
private final static Log log = LogFactory
.getLog(PaginationStatementInterceptor.class);
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
ParameterHandler parameterHandler = statementHandler.getParameterHandler();
Object parameterObject = parameterHandler.getParameterObject();
Pageable pagination =
if(parameterObject instanceof MapperMethod.ParamMap){
MapperMethod.ParamMap paramMapObject = (MapperMethod.ParamMap)parameterO
if(paramMapObject != null){
for(Object key : paramMapObject.keySet()){
if(paramMapObject.get(key) instanceof
Pageable){
pagination = (Pageable) paramMapObject.get(key);
if (pagination != null) {
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, new DefaultObjectFactory(), new DefaultObjectWrapperFactory());
Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");
Dialect.Type databaseType =
databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect").toUpperCase());
} catch (Exception e) {
throw new Exception("Generate SQL: Obtain DatabaseType Failed!");
Dialect dialect =
switch (databaseType) {
case MYSQL:
dialect = new MySql5Dialect();
case ORACLE:
dialect = new OracleDialect();
case SQLSERVER:
dialect = new SQLServer2005Dialect();
String originalSql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
metaStatementHandler.setValue("delegate.boundSql.sql", dialect.getLimitString(originalSql, pagination.getPageNumber() * pagination.getPageSize(), pagination.getPageSize()));
if (log.isDebugEnabled()) {
BoundSql boundSql = statementHandler.getBoundSql();
log.debug("Generate SQL : " + boundSql.getSql());
return invocation.proceed();
return invocation.proceed();
public Object plugin(Object target) {
return Plugin.wrap(target, this);
public void setProperties(Properties properties) {
PaginationResultSetInterceptor类为结果拦截类，拦截指定的分页类，这里我使用的是org.springframework.data.domain.Pageable分页类（Spring-data-commons包下的，如果用Spring-data应该会附带出来这个包的）；如果MyBatis中分页方法有这个分页类，则拦截结果集进行重写得到最终想要的org.springframework.data.domain.Page结果类（Spring-data-commons包下的，如果用Spring-data应该会附带出来这个包的）；Page中会包含分页信息和分页后的列表信息。
下面举例用的方言为MySQL对应的类MySql5Dialect继承了Dialect类，该类的getCountString方法是根据分页SQL解析获取到对应的查询总记录数的SQL，如果是别的数据库继承Dialect类，重写getCountString即可
invocation.proceed()为原分页结果集，这里根据上面的查询总记录数的SQL执行获取总记录数结果，封装Page对象设置原结果集为分页结果列表，设置总记录数，并返回
import com.mm.persist.expands.mybatis.dialect.D
import com.mm.persist.expands.mybatis.dialect.MySql5D
import com.mm.persist.expands.mybatis.dialect.OracleD
import com.mm.persist.expands.mybatis.dialect.SQLServer2005D
import mons.logging.L
import mons.logging.LogF
import org.apache.ibatis.binding.MapperM
import org.apache.ibatis.executor.parameter.ParameterH
import org.apache.ibatis.executor.resultset.DefaultResultSetH
import org.apache.ibatis.executor.resultset.ResultSetH
import org.apache.ibatis.mapping.BoundS
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaO
import org.apache.ibatis.reflection.factory.DefaultObjectF
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperF
import org.apache.ibatis.session.C
import org.springframework.data.domain.P
import org.springframework.data.domain.PageI
import org.springframework.data.domain.P
import org.springframework.jdbc.support.JdbcU
import java.sql.*;
import java.util.ArrayL
import java.util.L
import java.util.P
@Intercepts({@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})})
public class PaginationResultSetInterceptor implements Interceptor {
private final static Log log = LogFactory.getLog(PaginationResultSetInterceptor.class);
public Object intercept(Invocation invocation) throws Throwable {
DefaultResultSetHandler resultSetHandler = (DefaultResultSetHandler) invocation.getTarget();
MetaObject metaResultSetHandler = MetaObject.forObject(resultSetHandler, new DefaultObjectFactory(), new DefaultObjectWrapperFactory());
ParameterHandler parameterHandler = (ParameterHandler) metaResultSetHandler.getValue("parameterHandler");
Object parameterObject = parameterHandler.getParameterObject();
Pageable pagination =
if(parameterObject instanceof MapperMethod.ParamMap){
MapperMethod.ParamMap paramMapObject = (MapperMethod.ParamMap)parameterO
if(paramMapObject != null){
for(Object key : paramMapObject.keySet()){
if(paramMapObject.get(key) instanceof
Pageable){
pagination = (Pageable) paramMapObject.get(key);
if (pagination != null) {
BoundSql boundSql = (BoundSql) metaResultSetHandler.getValue("parameterHandler.boundSql");
Configuration configuration = (Configuration) metaResultSetHandler.getValue("configuration");
Connection connection = (Connection) metaResultSetHandler.getValue("executor.delegate.transaction.connection");
String originalSql = boundSql.getSql();
Dialect.Type databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect").toUpperCase());
Dialect dialect =
switch (databaseType) {
case MYSQL:
dialect = new MySql5Dialect();
case ORACLE:
dialect = new OracleDialect();
case SQLSERVER:
dialect = new SQLServer2005Dialect();
// 修改sql，用于返回总记录数
String sql = dialect.getCountString(originalSql);
Long totalRecord = getTotalRecord(connection, sql, parameterHandler);
Object result = invocation.proceed();
Page page = new PageImpl((List)result, pagination, totalRecord);
// 设置返回对象类型
metaResultSetHandler.setValue("mappedStatement.resultMaps[0].type.name", Page.class.getName());
// 设置返回值
List&Page& pageList = new ArrayList&Page&();
pageList.add(page);
return pageL
} catch (Exception e) {
throw new Exception("Overwrite SQL : Fail!");
return invocation.proceed();
* 执行 count 操作
* @param connection
数据库连接
* @param sql
* @param parameterHandler
参数设置处理器
private Long getTotalRecord(Connection connection,String sql,ParameterHandler parameterHandler){
PreparedStatement preparedStatement =
ResultSet resultSet =
preparedStatement = connection.prepareStatement(sql);
parameterHandler.setParameters(preparedStatement);
resultSet = preparedStatement.executeQuery();
resultSet.next();
return (Long) JdbcUtils.getResultSetValue(resultSet, 1, Long.class);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(preparedStatement);
return 0l;
public Object plugin(Object target) {
return Plugin.wrap(target, this);
public void setProperties(Properties properties) {
这里我仅以MySQL为例，其他需要同学们自己写
public class MySql5Dialect extends Dialect {
public String getLimitString(String querySqlString, int offset, int limit) {
return querySqlString + " limit " + offset + " ," +
public String getCountString(String querySqlString) {
int limitIndex = querySqlString.lastIndexOf("limit");
if(limitIndex != -1){
querySqlString = querySqlString.substring(0, limitIndex != -1 ? limitIndex : querySqlString.length() - 1);
// 用的过程中会发现这里对原有sql进行包装一层select count会有SQL效率低的问题
// 等待优化
return "SELECT COUNT(*) FROM (" + querySqlString + ") tem";
public boolean supportsLimit() {
public abstract class Dialect {
public static enum Type {
MYSQL, ORACLE, SQLSERVER
public abstract String getLimitString(String querySqlString, int offset, int limit);
public abstract String getCountString(String querySqlString);
以上代码测试并在工作项目中运用，代码可能不全，有时间做个demo并上传
参考文档： http://mybatis.github.io/mybatis-3/zh/configuration.html#plugins
浏览: 6282 次