MyBatis分页拦截

一、MyBatis配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="  
          http://www.springframework.org/schema/beans  
          http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
          http://www.springframework.org/schema/context
          http://www.springframework.org/schema/context/spring-context-2.5.xsd
          http://www.springframework.org/schema/tx  
          http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
          http://www.springframework.org/schema/aop  
          http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
          http://www.springframework.org/schema/mvc
          http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd"
	default-autowire="byName" default-lazy-init="false">

	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation" value="classpath:mybatis-config.xml" />
		<property name="mapperLocations" value="classpath*:/com/**/model/**/*Mapper.xml" />
	</bean>

	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>

</beans> 

mybatis-config

<?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>
		<!-- mybatis分页拦截器 -->
		<plugin interceptor="com.shareinfo.framework.pagination.mybatis.PageInterceptor" />
	</plugins>

</configuration>

二、分页封装类

public interface Dialect
{
	public static enum Type
	{
		MYSQL
		{
			public String getValue()
			{
				return "mysql";
			}
		},
		SQLSERVER
		{
			public String getValue()
			{
				return "sqlserver";
			}
		},
		ORACLE
		{
			public String getValue()
			{
				return "oracle";
			}
		};

		public abstract String getValue();
	}

	// 获取分页的查询SQL
	public String getPaginationSql(String sql, int offset, int limit);
}

三、分页方言

1.oracle

public class OracleDialect implements Dialect
{
	public String getPaginationSql(String sql, int offset, int limit)
	{
		return "select * from (select rownum rn, t.* from (" + sql + ") t where rownum <= " + (offset + limit) + ") t1 where t1.rn > " + offset;
	}
}

2. mysql

public class MySQL5Dialect implements Dialect
{
	public String getPaginationSql(String sql, int offset, int limit)
	{
		return sql + " limit " + offset + "," + limit;
	}
}

3.SqlServer

public class SqlServerDialect implements Dialect
{
	public String getPaginationSql(String sql, int pageNo, int pageSize)
	{
		return "select top " + pageSize + " from (" + sql + ") t where t.id not in (select top " + (pageNo - 1) * pageSize + " t1.id from (" + sql + ") t1)";
	}
}

四、MyBatis分页拦截器

@Intercepts({ @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) })
public class PageInterceptor implements Interceptor
{
	static int MAPPED_STATEMENT_INDEX = 0;
	static int PARAMETER_INDEX = 1;
	static int ROWBOUNDS_INDEX = 2;
	static int RESULT_HANDLER_INDEX = 3;

	public Object intercept(Invocation invocation) throws Throwable
	{
		processIntercept(invocation.getArgs());
		return invocation.proceed();
	}

	public void processIntercept(Object[] queryArgs) throws ConfigurationException
	{
		// 当前环境 MappedStatement，BoundSql，及sql取得
		MappedStatement mappedStatement = (MappedStatement)queryArgs[MAPPED_STATEMENT_INDEX];
		// 请求的对象
		Object parameter = queryArgs[PARAMETER_INDEX];
		// 分页信息
		RowBounds rowBounds = (RowBounds)queryArgs[ROWBOUNDS_INDEX];
		int offset = rowBounds.getOffset();
		int limit = rowBounds.getLimit();
		//如果分页信息为null,则rowBounds默认为rowBounds.offset=0,rowBounds.limit=Integer.MAX_VALUE,所以这里作下判断
		if (offset != 0 || limit != Integer.MAX_VALUE)
		{
			Dialect dialect = getDialect(mappedStatement.getConfiguration());
			BoundSql boundSql = mappedStatement.getBoundSql(parameter);
			String sql = boundSql.getSql().trim();
			sql = dialect.getPaginationSql(sql, offset, limit);
			offset = 0; // 这里没有增加的话导致后面分页查询不出来
			limit = Integer.MAX_VALUE;
			queryArgs[ROWBOUNDS_INDEX] = new RowBounds(offset, limit);
			BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), sql, boundSql.getParameterMappings(), boundSql.getParameterObject());
			MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql));
			queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
		}
	}

	private Dialect getDialect(Configuration configuration) throws ConfigurationException
	{
		Dialect.Type databaseType = null;
		try
		{
			databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect").toUpperCase());
		}
		catch (Exception e)
		{
			throw new ConfigurationException("the value of the dialect property in mybatis-config.xml is not defined : " + configuration.getVariables().getProperty("dialect"));
		}

		Dialect dialect = null;
		switch (databaseType)
		{
			case MYSQL:
				dialect = new MySQL5Dialect();
			case SQLSERVER:
				dialect = new SqlServerDialect();
			case ORACLE:
				dialect = new OracleDialect();
		}

		return dialect;
	}

	private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource)
	{
		Builder builder = new Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());

		builder.resource(ms.getResource());
		builder.fetchSize(ms.getFetchSize());
		builder.statementType(ms.getStatementType());
		builder.keyGenerator(ms.getKeyGenerator());
		// builder.keyProperty(ms.getKeyProperty());

		builder.timeout(ms.getTimeout());

		builder.parameterMap(ms.getParameterMap());

		builder.resultMaps(ms.getResultMaps());
		builder.resultSetType(ms.getResultSetType());

		builder.cache(ms.getCache());
		builder.flushCacheRequired(ms.isFlushCacheRequired());
		builder.useCache(ms.isUseCache());

		return builder.build();
	}

	public class BoundSqlSqlSource implements SqlSource
	{
		BoundSql boundSql;

		public BoundSqlSqlSource(BoundSql boundSql)
		{
			this.boundSql = boundSql;
		}

		public BoundSql getBoundSql(Object parameterObject)
		{
			return boundSql;
		}
	}

	public Object plugin(Object arg0)
	{
		return Plugin.wrap(arg0, this);
	}

	public void setProperties(Properties properties)
	{
	}
}

说明:本人于ITEYE创建于2014年,现转移到优快云