Mybatis的自带分页方法只是逻辑分页,如果数据量很大,内存会溢出,不知道为什么开源组织不在里面实现类似Hibernate的物理分页处理方法。在不改动Mybatis源代码的情况下,怎么使Mybatis支持物理分页呢?下面我们来看看。
(1)新建一个Java类Dialect.java,该类的内容如下:
- package org.mybatis.extend.interceptor;
- public abstract class Dialect {
- public static enum Type{
- MYSQL,
- ORACLE
- }
- public abstract String getLimitString(String sql, int skipResults, int maxResults);
- }
(2)新建一个Java类OracleDialect.java,该类继承Dialect 类,具体的内容如下:
- package org.mybatis.extend.interceptor;
- public class OracleDialect extends Dialect{
- /* (non-Javadoc)
- * @see org.mybatis.extend.interceptor.IDialect#getLimitString(java.lang.String, int, int)
- */
- @Override
- public String getLimitString(String sql, int offset, int limit) {
- sql = sql.trim();
- StringBuffer pagingSelect = new StringBuffer(sql.length() + 100 );
- pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( " );
- pagingSelect.append(sql);
- pagingSelect.append(" ) row_ ) where rownum_ > " ).append(offset).append( " and rownum_ <= " ).append(offset + limit);
- return pagingSelect.toString();
- }
- }
(3)新建一个Mybaits的拦截器PaginationInterceptor.java,实现Interceptor接口,该类的内容如下:
- package org.mybatis.extend.interceptor;
- import java.sql.Connection;
- import java.util.Map;
- import java.util.Properties;
- import org.apache.ibatis.executor.parameter.DefaultParameterHandler;
- import org.apache.ibatis.executor.statement.StatementHandler;
- import org.apache.ibatis.mapping.BoundSql;
- import org.apache.ibatis.plugin.Interceptor;
- import org.apache.ibatis.plugin.Intercepts;
- import org.apache.ibatis.plugin.Invocation;
- import org.apache.ibatis.plugin.Plugin;
- import org.apache.ibatis.plugin.Signature;
- import org.apache.ibatis.reflection.MetaObject;
- import org.apache.ibatis.session.Configuration;
- import org.apache.ibatis.session.RowBounds;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- @Intercepts ({ @Signature (type=StatementHandler. class ,method= "prepare" ,args={Connection. class })})
- public class PaginationInterceptor implements Interceptor {
- //日志对象
- protected static Logger log = LoggerFactory.getLogger(PaginationInterceptor. class );
- /* (non-Javadoc)
- * @see org.apache.ibatis.plugin.Interceptor#intercept(org.apache.ibatis.plugin.Invocation)
- */
- @Override
- public Object intercept(Invocation invocation) throws Throwable {
- StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
- MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);
- RowBounds rowBounds = (RowBounds)metaStatementHandler.getValue("delegate.rowBounds" );
- if (rowBounds == null || rowBounds == RowBounds.DEFAULT){
- return invocation.proceed();
- }
- DefaultParameterHandler defaultParameterHandler = (DefaultParameterHandler)metaStatementHandler.getValue("delegate.parameterHandler" );
- Map parameterMap = (Map)defaultParameterHandler.getParameterObject();
- Object sidx = parameterMap.get("_sidx" );
- Object sord = parameterMap.get("_sord" );
- String originalSql = (String)metaStatementHandler.getValue("delegate.boundSql.sql" );
- if (sidx != null && sord != null ){
- originalSql = originalSql + " order by " + sidx + " " + sord;
- }
- Configuration configuration = (Configuration)metaStatementHandler.getValue("delegate.configuration" );
- Dialect.Type databaseType = null ;
- try {
- databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect" ).toUpperCase());
- } catch (Exception e){
- //ignore
- }
- if (databaseType == null ){
- throw new RuntimeException( "the value of the dialect property in configuration.xml is not defined : " + configuration.getVariables().getProperty( "dialect" ));
- }
- Dialect dialect = null ;
- switch (databaseType){
- case ORACLE:
- dialect = new OracleDialect();
- break ;
- case MYSQL: //需要实现MySQL的分页逻辑
- break ;
- }
- metaStatementHandler.setValue("delegate.boundSql.sql" , dialect.getLimitString(originalSql, rowBounds.getOffset(), rowBounds.getLimit()) );
- metaStatementHandler.setValue("delegate.rowBounds.offset" , RowBounds.NO_ROW_OFFSET );
- metaStatementHandler.setValue("delegate.rowBounds.limit" , RowBounds.NO_ROW_LIMIT );
- if (log.isDebugEnabled()){
- BoundSql boundSql = statementHandler.getBoundSql();
- log.debug("生成分页SQL : " + boundSql.getSql());
- }
- return invocation.proceed();
- }
- /* (non-Javadoc)
- * @see org.apache.ibatis.plugin.Interceptor#plugin(java.lang.Object)
- */
- @Override
- public Object plugin(Object target) {
- return Plugin.wrap(target, this );
- }
- /* (non-Javadoc)
- * @see org.apache.ibatis.plugin.Interceptor#setProperties(java.util.Properties)
- */
- @Override
- public void setProperties(Properties arg0) {
- // TODO Auto-generated method stub
- }
- }
(4)将Mybatis的拦截器配置到Mybatis的全局配置文件(mybatis.cfg.xml)中,具体如下:
- <?xml version= "1.0" encoding= "UTF-8" ?>
- <!DOCTYPE configuration PUBLIC
- "-//mybatis.org//DTD Config 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-config.dtd" >
- <configuration>
- <properties>
- <property name="dialect" value= "oracle" />
- </properties>
- <plugins>
- <plugin interceptor="org.mybatis.extend.interceptor.PaginationInterceptor" />
- </plugins>
- </configuration>
(5)使用方法同Mybatis逻辑分页一样,拦截器会自动拦截执行SQL的地方,加上分页代码:
- getSqlSession().selectList(sqlId, paramMap, new RowBounds(pageId, pageSize));