iBatis3 Dialect 分页

参考以前论坛中关于ibatis2 Dialect 分页思路，实现iBatis3 Dialect 分页，由于ibatis3结构重新设计，提供了interceptor功能，有几个切入点，使得我们可以修改运行sql语句和处理返回结果

1：修改sql语句

import java.sql.Connection;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
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;

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

	private Properties properties;

	private static final String DIALECT = "dialect";

	public Object intercept(Invocation invocation) throws Throwable {
		RoutingStatementHandler statement = (RoutingStatementHandler)invocation.getTarget();

		PreparedStatementHandler handler = (PreparedStatementHandler) ReflectUtil.getField(statement, "delegate", StatementHandler.class);

		Integer rowOffset = (Integer)ReflectUtil.getField(handler, "rowOffset", int.class);
		Integer rowLimit = (Integer)ReflectUtil.getField(handler, "rowLimit", int.class);

		if(rowLimit>0 && rowLimit < Executor.NO_ROW_LIMIT) {
			BoundSql boundSql = statement.getBoundSql();
			String sql = boundSql.getSql();
			String dialectStr = properties.getProperty(DIALECT);

			Dialect dialect = (Dialect) Class.forName(dialectStr).newInstance();
			sql = dialect.getLimitString(sql, rowOffset, rowLimit);

			ReflectUtil.setFieldValue(boundSql, "sql", String.class, sql);
		}
		return invocation.proceed();
	}

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

	public void setProperties(Properties properties) {
		this.properties = properties;
	}
}

2： 修改返回数据

import java.sql.Statement;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.resultset.DefaultResultSetHandler;
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;

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


	public Object intercept(Invocation invocation) throws Throwable {
		DefaultResultSetHandler resultSet = (DefaultResultSetHandler)invocation.getTarget();

		Integer rowLimit = (Integer)ReflectUtil.getField(resultSet, "rowLimit", int.class);

		if(rowLimit>0 && rowLimit < Executor.NO_ROW_LIMIT) {
			ReflectUtil.setFieldValue(resultSet, "rowOffset", Integer.class, Executor.NO_ROW_OFFSET);
			ReflectUtil.setFieldValue(resultSet, "rowLimit", Integer.class, Executor.NO_ROW_LIMIT);
		}
		return invocation.proceed();
	}

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

	public void setProperties(Properties properties) {
	}
}

3：mysql分页，很方便添加其他数据库的

public class MySQLDialect implements Dialect{

	protected static final String SQL_END_DELIMITER = ";";

	public String getLimitString(String sql, boolean hasOffset) {
		return new StringBuffer(sql.length() + 20).append(trim(sql)).append(
				hasOffset ? " limit ?,?" : " limit ?")
				.append(SQL_END_DELIMITER).toString();
	}

	public String getLimitString(String sql, int offset, int limit) {
		sql = trim(sql);
		StringBuffer sb = new StringBuffer(sql.length() + 20);
		sb.append(sql);
		if (offset > 0) {
			sb.append(" limit ").append(offset).append(',').append(limit)
					.append(SQL_END_DELIMITER);
		} else {
			sb.append(" limit ").append(limit).append(SQL_END_DELIMITER);
		}
		return sb.toString();
	}

	public boolean supportsLimit() {
		return true;
	}

	private String trim(String sql) {
		sql = sql.trim();
		if (sql.endsWith(SQL_END_DELIMITER)) {
			sql = sql.substring(0, sql.length() - 1
					- SQL_END_DELIMITER.length());
		}
		return sql;
	}

}

4：MapperConfig.xml中添加配置

  <plugins>
    <plugin interceptor="DiclectStatementHandlerInterceptor">
      <property name="dialect" value="MySQLDialect"/>
    </plugin>
    <plugin interceptor="DiclectResultSetHandlerInterceptor">
    </plugin>
  </plugins>