MyBatis拦截器分页与动态修改SQL及其参数值

package XXXX.mybatis.plugin; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Properties; import java.util.regex.Matcher; import java.util.regex.Pattern; import javax.xml.bind.PropertyException; import org.apache.ibatis.builder.SqlSourceBuilder; import org.apache.ibatis.executor.ErrorContext; import org.apache.ibatis.executor.ExecutorException; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.mapping.ParameterMode; import org.apache.ibatis.mapping.ResultMap; import org.apache.ibatis.mapping.ResultMapping; import org.apache.ibatis.mapping.SqlSource; 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.reflection.factory.DefaultObjectFactory; import org.apache.ibatis.reflection.factory.ObjectFactory; import org.apache.ibatis.reflection.property.PropertyTokenizer; import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory; import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory; import org.apache.ibatis.scripting.xmltags.DynamicContext; import org.apache.ibatis.scripting.xmltags.ForEachSqlNode; import org.apache.ibatis.scripting.xmltags.SqlNode; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.type.TypeHandler; import org.apache.ibatis.type.TypeHandlerRegistry; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import XXXX.easyui.EasyUiPageInfo; import XXXX.mybatis.dialect.Dialect; import XXXX.mybatis.dialect.ExampleOracleDialect; import XXXX.mybatis.dialect.MySql5Dialect; import XXXX.mybatis.dialect.OracleDialect; import XXXX.mybatis.paging.impl.PagingByCount; import XXXX.mybatis.paging.impl.PagingByExampl; import XXXX.mybatis.paging.itf.MybatisPaging; /******************************************************************************* * MYBATIS拦截器 * * 实现mybatis物理分页,解决数据量很大，内存溢出问题。 * 参考:http://www.linuxso.com/architecture/19205.html * * @author LUOYT * * 2012-03-15 * */ @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) }) public class PaginationInterceptor implements Interceptor { protected static Logger log = LoggerFactory .getLogger(PaginationInterceptor.class); private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory(); private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory(); private static final ObjectFactory DEFAULT_OBJECT_FACTORY2 = new DefaultObjectFactory(); private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY2 = new DefaultObjectWrapperFactory(); private static String dialect = ""; private static String pageSqlId = ""; private static String exampleDialect = ""; private static String examplePageSqlId = ""; public Object intercept(Invocation invocation) throws Throwable { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,DEFAULT_OBJECT_WRAPPER_FACTORY); MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); MetaObject metaMappedStatement = MetaObject.forObject(mappedStatement, DEFAULT_OBJECT_FACTORY2,DEFAULT_OBJECT_WRAPPER_FACTORY2); Pattern pattern = Pattern.compile(pageSqlId,Pattern.CASE_INSENSITIVE); Matcher matcher = pattern.matcher(mappedStatement.getId()); MybatisPaging mybatisPaging = null; Pattern examplePattern = Pattern.compile(examplePageSqlId,Pattern.CASE_INSENSITIVE); Matcher exampleMatcher = examplePattern.matcher(mappedStatement.getId()); String dialectName = ""; if(matcher.find()){ mybatisPaging = new PagingByCount(); dialectName = dialect; }else if(exampleMatcher.find()){ mybatisPaging = new PagingByExampl(); dialectName = exampleDialect; }else{ return invocation.proceed(); } Dialect.Type databaseType = Dialect.Type.valueOf(dialectName .toUpperCase()); if (dialect == null) { throw new RuntimeException( "the value of the dialect property in configuration.xml is not defined : dialect"); } Dialect dialect = null; switch (databaseType) { case MYSQL: dialect = new MySql5Dialect(); break; case ORACLE: dialect = new OracleDialect(); break; case EXAMPLEORACLE: dialect = new ExampleOracleDialect(); break; default: throw new RuntimeException("the dialect of '" + databaseType + "' cannot be recognized"); } mybatisPaging.intercept(metaStatementHandler, statementHandler , metaMappedStatement, mappedStatement, dialect, invocation); // //if (mappedStatement.getId().matches(pageSqlId)) { // if (matcher.find()) // { // EasyUiPageInfo page = (EasyUiPageInfo) metaStatementHandler // .getValue("delegate.boundSql.parameterObject.page"); // //String originalSql = (String) metaStatementHandler.getValue("delegate.boundSql.sql"); // BoundSql boundSql = statementHandler.getBoundSql(); // //修改参数值 // SqlNode sqlNode = (SqlNode) metaMappedStatement.getValue("sqlSource.rootSqlNode"); // boundSql=getBoundSql(mappedStatement.getConfiguration(), boundSql.getParameterObject(),sqlNode); // // // 所属分页的命名设置 // if (page == null) { // if (log.isDebugEnabled()) { // // String formattedSQL = new // // BasicFormatterImpl().format(boundSql.getSql()); // log.debug("生成SQL : " + boundSql.getSql()); // } // return invocation.proceed(); // } // Dialect.Type databaseType = Dialect.Type.valueOf(dialect // .toUpperCase()); // if (dialect == null) { // throw new RuntimeException( // "the value of the dialect property in configuration.xml is not defined : dialect"); // } // Dialect dialect = null; // switch (databaseType) { // case MYSQL: // dialect = new MySql5Dialect(); // break; // case ORACLE: // dialect = new OracleDialect(); // break; // default: // throw new RuntimeException("the dialect of '" + databaseType // + "' cannot be recognized"); // } // // 计算总数量 // Connection connection = (Connection) invocation.getArgs()[0]; // Object parameterObject = boundSql.getParameterObject(); // // String sql = boundSql.getSql(); // if(sql.toUpperCase().indexOf("ORDER BY")==-1){ // ResultMap resultMap = mappedStatement.getResultMaps().get(0); // //判断如果sql里面有order by那么界面的order by就没有效果 // if (page.getSort() != null) { // // 替换orderby 的column为数据库字段 // List<ResultMapping> list = resultMap.getResultMappings(); // for (ResultMapping resultMapping : list) { // if (resultMapping.getProperty().equalsIgnoreCase(page.getSort())) { // page.setSort(resultMapping.getColumn()); // } // } // }else{ // //如果排序为空，默认加上一个排序 // List<ResultMapping> list = resultMap.getResultMappings(); // ResultMapping resultMapping = list.get(0); // page.setSort(resultMapping.getColumn()); // page.setOrder(""); // // } // }else{ // page.setSort(""); // page.setOrder(""); // } // // String count