mybatis在mysql中的分页扩展

时间：2022-09-30 14:32:07

applicationContext.xml

	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

		<property name="dataSource" ref="dataSource" />

		<property name="configLocation" value="classpath:/mybatis-config.xml" />

	</bean>

mybatis-config.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>

	<settings>

		<!-- 设置成 true就可以启动缓存，一般的修改要1分钟才能生效（因为设置的flushInterval="60000" ）

			 如果是在一个xxxxMapper.xml里面修改的数据库的数据,就直接清空本mapper的缓存 -->

		<setting name="cacheEnabled" value="false"/>

		<setting name="useGeneratedKeys" value="false"/>

	</settings>

	<plugins>

		<plugin interceptor="com.system.util.DiclectStatementHandlerInterceptor" />

		<plugin interceptor="com.system.util.DiclectResultSetHandlerInterceptor" />

	</plugins>

</configuration>

DiclectResultSetHandlerInterceptor.java

package com.system.util;

import java.sql.Statement;

import java.util.Properties;

import org.apache.ibatis.executor.resultset.FastResultSetHandler;

import org.apache.ibatis.executor.resultset.NestedResultSetHandler;

import org.apache.ibatis.executor.resultset.ResultSetHandler;

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.session.RowBounds;

@Intercepts({ @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = { Statement.class }) })

public class DiclectResultSetHandlerInterceptor implements Interceptor {

	public Object intercept(Invocation invocation) throws Throwable {

		FastResultSetHandler resultSet = (FastResultSetHandler) invocation.getTarget();

		if(!(resultSet instanceof NestedResultSetHandler)) {

			RowBounds rowBounds = (RowBounds) ReflectUtil.getClassField(resultSet, "rowBounds");

			if (rowBounds.getLimit() > 0 && rowBounds.getLimit() < RowBounds.NO_ROW_LIMIT) {

				ReflectUtil.setClassField(resultSet, "rowBounds", new RowBounds());

			}

		}

		return invocation.proceed();

	}

	public Object plugin(Object target) {

		return Plugin.wrap(target, this);

	}

	public void setProperties(Properties properties) {}

}

　

DiclectStatementHandlerInterceptor.java

package com.system.util;

import java.sql.Connection;

import java.util.Properties;

import org.apache.ibatis.executor.statement.PreparedStatementHandler;

import org.apache.ibatis.executor.statement.RoutingStatementHandler;

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.session.RowBounds;

@Intercepts( { @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })

public class DiclectStatementHandlerInterceptor implements Interceptor {

	public Object intercept(Invocation invocation) throws Throwable {

		RoutingStatementHandler statement = (RoutingStatementHandler) invocation.getTarget();

		StatementHandler handler = (StatementHandler) ReflectUtil.getClassField(statement, "delegate");

		if (handler instanceof PreparedStatementHandler){

			RowBounds rowBounds = (RowBounds) ReflectUtil.getSuperClassField(handler, "rowBounds");

			if (rowBounds.getLimit() > 0 && rowBounds.getLimit() < RowBounds.NO_ROW_LIMIT) {

				BoundSql boundSql = statement.getBoundSql();

				String sql = boundSql.getSql();

				sql = getLimitString(sql, rowBounds.getOffset(), rowBounds.getLimit());

				ReflectUtil.setClassField(boundSql, "sql", sql);

			}

		}

		return invocation.proceed();

	}

	public Object plugin(Object target) {

		return Plugin.wrap(target, this);

	}

	public void setProperties(Properties properties) {

	}

	public String getLimitString(String sql, int offset, int limit) {

		limit = offset+limit;

		sql = sql.trim();

		boolean isForUpdate = false;

		if ( sql.toLowerCase().endsWith(" for update") ) {

			sql = sql.substring( 0, sql.length()-11 );

			isForUpdate = true;

		}

		StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );

		pagingSelect.append(sql);

		pagingSelect.append(" limit "+offset+","+limit);

		if ( isForUpdate ) {

			pagingSelect.append( " for update" );

		}

		return pagingSelect.toString();

	}

}

　　

相关文章