Mybatis技术（三） 实现物理分页

Mybatis技术（三） 实现物理分页

博客分类：?
• Mybatis技术内幕Mybatis物理分页

  Mybatis的自带分页方法只是逻辑分页，如果数据量很大，内存会溢出，不知道为什么开源组织不在里面实现类似Hibernate的物理分页处理方法。在不改动Mybatis源代码的情况下，怎么使Mybatis支持物理分页呢？下面我们来看看。

  ?

  （1）新建一个Java类Dialect.java，该类的内容如下：

  Java代码??
  1. package?org.mybatis.extend.interceptor;??
  2. ??
  3. public?abstract?class?Dialect?{??
  4. ??
  5. ????public?static?enum?Type{??
  6. ????????MYSQL,??
  7. ????????ORACLE??
  8. ????}??
  9. ??????
  10. ????public?abstract?String?getLimitString(String?sql,?int?skipResults,?int?maxResults);??
  11. ??????
  12. }??

  ?

  ?

  （2）新建一个Java类OracleDialect.java，该类继承Dialect 类，具体的内容如下：

  Java代码??
  1. package?org.mybatis.extend.interceptor;??
  2. ??
  3. public?class?OracleDialect?extends?Dialect{??
  4. ??
  5. ????/*?(non-Javadoc)?
  6. ?????*?@see?org.mybatis.extend.interceptor.IDialect#getLimitString(java.lang.String,?int,?int)?
  7. ?????*/??
  8. ????@Override??
  9. ????public?String?getLimitString(String?sql,?int?offset,?int?limit)?{??
  10. ??
  11. ????????sql?=?sql.trim();??
  12. ????????StringBuffer?pagingSelect?=?new?StringBuffer(sql.length()?+?100);??
  13. ??????????
  14. ????????pagingSelect.append("select?*?from?(?select?row_.*,?rownum?rownum_?from?(?");??
  15. ??????????
  16. ????????pagingSelect.append(sql);??
  17. ??????????
  18. ????????pagingSelect.append("?)?row_?)?where?rownum_?>?").append(offset).append("?and?rownum_?<=?").append(offset?+?limit);??
  19. ??????????
  20. ????????return?pagingSelect.toString();??
  21. ????}??
  22. ??
  23. }??

  ?

  （3）新建一个Mybaits的拦截器PaginationInterceptor.java，实现Interceptor接口，该类的内容如下：

  Java代码??
  1. package?org.mybatis.extend.interceptor;??
  2. ??
  3. import?java.sql.Connection;??
  4. import?java.util.Map;??
  5. import?java.util.Properties;??
  6. ??
  7. import?org.apache.ibatis.executor.parameter.DefaultParameterHandler;??
  8. import?org.apache.ibatis.executor.statement.StatementHandler;??
  9. import?org.apache.ibatis.mapping.BoundSql;??
  10. import?org.apache.ibatis.plugin.Interceptor;??
  11. import?org.apache.ibatis.plugin.Intercepts;??
  12. import?org.apache.ibatis.plugin.Invocation;??
  13. import?org.apache.ibatis.plugin.Plugin;??
  14. import?org.apache.ibatis.plugin.Signature;??
  15. import?org.apache.ibatis.reflection.MetaObject;??
  16. import?org.apache.ibatis.session.Configuration;??
  17. import?org.apache.ibatis.session.RowBounds;??
  18. import?org.slf4j.Logger;??
  19. import?org.slf4j.LoggerFactory;??
  20. ??
  21. ??
  22. @Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})??
  23. public?class?PaginationInterceptor?implements?Interceptor?{??
  24. ????//日志对象??
  25. ????protected?static?Logger?log?=?LoggerFactory.getLogger(PaginationInterceptor.class);??
  26. ????/*?(non-Javadoc)?
  27. ?????*?@see?org.apache.ibatis.plugin.Interceptor#intercept(org.apache.ibatis.plugin.Invocation)?
  28. ?????*/??
  29. ????@Override??
  30. ????public?Object?intercept(Invocation?invocation)?throws?Throwable?{??
  31. ????????StatementHandler?statementHandler?=?(StatementHandler)invocation.getTarget();??
  32. ????????MetaObject?metaStatementHandler?=?MetaObject.forObject(statementHandler);??
  33. ??????????
  34. ????????RowBounds?rowBounds?=?(RowBounds)metaStatementHandler.getValue("delegate.rowBounds");??
  35. ????????if(rowBounds?==?null?||?rowBounds?==?RowBounds.DEFAULT){??
  36. ????????????return?invocation.proceed();??
  37. ????????}??
  38. ??????????
  39. ????????DefaultParameterHandler?defaultParameterHandler?=?(DefaultParameterHandler)metaStatementHandler.getValue("delegate.parameterHandler");??
  40. ????????Map?parameterMap?=?(Map)defaultParameterHandler.getParameterObject();??
  41. ????????Object?sidx?=?parameterMap.get("_sidx");??
  42. ????????Object?sord?=?parameterMap.get("_sord");??
  43. ??????????
  44. ????????String?originalSql?=?(String)metaStatementHandler.getValue("delegate.boundSql.sql");??
  45. ??????????
  46. ????????if(sidx?!=?null?&&?sord?!=?null){??
  47. ????????????originalSql?=?originalSql?+?"?order?by?"?+?sidx?+?"?"?+?sord;??
  48. ????????}??
  49. ??????????
  50. ????????Configuration?configuration?=?(Configuration)metaStatementHandler.getValue("delegate.configuration");??
  51. ??????????
  52. ????????????????????????????Dialect.Type?databaseType??=?null;??
  53. ????????try{??
  54. ????????????databaseType?=?Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect").toUpperCase());??
  55. ????????}?catch(Exception?e){??
  56. ????????????//ignore??
  57. ????????}??
  58. ????????if(databaseType?==?null){??
  59. ????????????throw?new?RuntimeException("the?value?of?the?dialect?property?in?configuration.xml?is?not?defined?:?"?+?configuration.getVariables().getProperty("dialect"));??
  60. ????????}??
  61. ????????Dialect?dialect?=?null;??
  62. ????????switch(databaseType){??
  63. ????????????case?ORACLE:??
  64. ????????????????dialect?=?new?OracleDialect();??
  65. ????????????????break;??
  66. ????????????case?MYSQL://需要实现MySQL的分页逻辑??
  67. ????????????????break;??
  68. ??????????????????
  69. ????????}??
  70. ??????????
  71. ??????????
  72. ????????metaStatementHandler.setValue("delegate.boundSql.sql",?dialect.getLimitString(originalSql,?rowBounds.getOffset(),?rowBounds.getLimit())?);??
  73. ????????metaStatementHandler.setValue("delegate.rowBounds.offset",?RowBounds.NO_ROW_OFFSET?);??
  74. ????????metaStatementHandler.setValue("delegate.rowBounds.limit",?RowBounds.NO_ROW_LIMIT?);??
  75. ????????if(log.isDebugEnabled()){??
  76. ????????????BoundSql?boundSql?=?statementHandler.getBoundSql();??
  77. ????????????log.debug("生成分页SQL?:?"?+?boundSql.getSql());??
  78. ????????}??
  79. ????????return?invocation.proceed();??
  80. ????}??
  81. ??
  82. ????/*?(non-Javadoc)?
  83. ?????*?@see?org.apache.ibatis.plugin.Interceptor#plugin(java.lang.Object)?
  84. ?????*/??
  85. ????@Override??
  86. ????public?Object?plugin(Object?target)?{??
  87. ????????return?Plugin.wrap(target,?this);??
  88. ????}??
  89. ??
  90. ????/*?(non-Javadoc)?
  91. ?????*?@see?org.apache.ibatis.plugin.Interceptor#setProperties(java.util.Properties)?
  92. ?????*/??
  93. ????@Override??
  94. ????public?void?setProperties(Properties?arg0)?{??
  95. ????????//?TODO?Auto-generated?method?stub??
  96. ??????????
  97. ????}??
  98. ??
  99. }??

  ?

  ?（4）将Mybatis的拦截器配置到Mybatis的全局配置文件(mybatis.cfg.xml)中，具体如下：

  Java代码??
  1. <?xml?version="1.0"?encoding="UTF-8"??>??
  2. ??
  3. <!DOCTYPE?configuration?PUBLIC???
  4. ????"-//mybatis.org//DTD?Config?3.0//EN"??
  5. ????"http://mybatis.org/dtd/mybatis-3-config.dtd">??
  6. ??
  7. <configuration>??
  8. ????<properties>??
  9. ??????????????<property?name="dialect"?value="oracle"/>??
  10. ????</properties>??
  11. ??????????
  12. ????<plugins>??
  13. ????????????<plugin?interceptor="org.mybatis.extend.interceptor.PaginationInterceptor"/>??
  14. ????</plugins>??
  15. ?????????
  16. </configuration>??

  ?

  （5）使用方法同Mybatis逻辑分页一样，拦截器会自动拦截执行SQL的地方，加上分页代码：

  Java代码??
  1. getSqlSession().selectList(sqlId,?paramMap,new?RowBounds(pageId,?pageSize)); ?

  ?

  1 楼 azrael6619 2012-06-13 Object sidx = parameterMap.get("_sidx");
  Object sord = parameterMap.get("_sord");
  这两个参数是哪里放进去的？