【mybatis】使用Plugin查询分页_public list> querypageusers();-优快云博客

本文链接：https://blog.youkuaiyun.com/blackbeauty1024/article/details/43987323

本文详细介绍了如何通过MyBatis的RowBounds上下文和自定义拦截器来优化分页查询性能，避免内存溢出问题。通过设置RowBounds在数据库端分页查询，实现更高效的分页处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds);

上面代码是mybatis SqlSession接口分页查询的方法，但底层默认的实现方式执行效率非常低，原因是因为selectList方法是一次性将所有的记录从数据库中查询出来，然后在java内存中分页，在数据量较大的情况下还容易出现内存溢出问题。

mybatis3提供了类似struts2的拦截器，我们可以使用mybatis的拦截器对Executor接口的query方法进行拦截，结合ThreadLocal实现在数据库端查询分页。

RowBoundsContext：RowBounds上下文

final public class RowBoundsContext {
	private RowBoundsContext(){}
	private final static ThreadLocal<RowBounds> ROWBOUNDS_CONTEXT = new ThreadLocal<RowBounds>();
	public static void setRowBounds( RowBounds rowBounds ){
		ROWBOUNDS_CONTEXT.set(rowBounds);
	}
	public static RowBounds getRowBounds() {
		return ROWBOUNDS_CONTEXT.get();
	}
	public static void removeRowBounds(){
		ROWBOUNDS_CONTEXT.remove();
	}
}

Dao类：

public class UserDao extends BaseDao {
	public List<User> queryPage(User user){
		try{
			RowBoundsContext.setRowBounds(new RowBounds(20, 10));
			return getSqlSession().selectList("queryForList", user);
		}finally{
			RowBoundsContext.removeRowBounds();//切记一定要清除线程中的RowBounds对象
		}
	}
}

PagePlugin 分页拦截器：

@Intercepts({ @Signature(type = Executor.class, method = "query", args = {
		MappedStatement.class, Object.class, RowBounds.class,
		ResultHandler.class }) })
public class PagePlugin implements Interceptor {
	private static final String PAGESQL_START = "SELECT * FROM ( ";
	private static final String PAGESQL_END = " ) _PAGE_TABLE LIMIT ";
	public Object intercept(Invocation invocation) throws Throwable {
		Object result = null;
		RowBounds _rowBounds = RowBoundsContext.getRowBounds();
		Executor executor = (Executor) invocation.getTarget();
		if (!(executor instanceof ReuseExecutor)||_rowBounds==null) {//笔者配置文件中defaultExecutorType配置为REUSE，只对ReuseExecutor进行处理

			return invocation.proceed();
		}
		Object[] args = invocation.getArgs();
		MappedStatement ms = (MappedStatement) args[0];
		Object parameter = args[1];
		RowBounds rowBounds = (RowBounds) args[2];
		ResultHandler resultHandler = (ResultHandler) args[3];
		BoundSql boundSql = ms.getBoundSql(parameter);
		String originalSql = boundSql.getSql();
		MetaObject boundSqlMetaObject = SystemMetaObject.forObject(boundSql);
		StringBuffer pageSqlBuffer = new StringBuffer(originalSql);
		pageSqlBuffer.insert(0, PAGESQL_START);
		pageSqlBuffer.append(PAGESQL_END).append(_rowBounds.getOffset()).append(",").append(_rowBounds.getLimit());
		boundSqlMetaObject.setValue("sql", pageSqlBuffer.toString());
		CacheKey key = executor.createCacheKey(ms, parameter, rowBounds,boundSql);
		result = executor.query(ms, parameter, rowBounds, resultHandler, key,boundSql);
		return result;
	}

	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}
	public void setProperties(Properties properties) {
	}

}

配置文件：

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">    
         <property name="configLocation" value="classpath:config/mybatis/sqlMapConfig.xml" />
        <property name="mapperLocations" value="classpath*:config/mybatis/mapping/*.xml"></property>
        <property name="typeAliasesPackage" value="com.myproject"/>
        <property name="typeAliasesSuperType" value="com.myproject.support.mybatis.TypeAliases"/>  
		<property name="dataSource" ref="dataSource"/> 
 		<property name="plugins">
			<array>
				 <bean class="com.myproject.support.mybatis.page.PagePlugin"></bean> 
			</array>
		</property>  
    </bean>