Mybatis3.0.6 结合hibernate3.6.8 Dialect 进行物理物理分页

mybatis插件配置：

 

	<plugins>
		<plugin interceptor="com.xuanwu.sms.smstask.webmanager.common.crud.plugins.PaginationInterceptor">
			<property name="dialectClass" value="org.hibernate.dialect.SQLServer2005Dialect"/>
		</plugin>
	</plugins>

 

 

插件处理类：

package com.xuanwu.sms.smstask.webmanager.common.crud.plugins;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import org.apache.ibatis.builder.SqlSourceBuilder;
import org.apache.ibatis.builder.xml.dynamic.DynamicContext;
import org.apache.ibatis.builder.xml.dynamic.SqlNode;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.DefaultParameterHandler;
import org.apache.ibatis.mapping.MappedStatement;
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.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.hibernate.dialect.Dialect;

import com.xuanwu.sms.smstask.webmanager.common.crud.entity.IPage;
import com.xuanwu.sms.smstask.webmanager.common.crud.entity.Pagination;

@Intercepts( { @Signature(type = Executor.class, method = "query", args = {
		MappedStatement.class, Object.class, RowBounds.class,
		ResultHandler.class }) })
public class PaginationInterceptor implements Interceptor {

	Dialect dialect;
	
	public Object intercept(Invocation invocation) throws Throwable {

		Object parameterObject = invocation.getArgs()[1];
		
		if(parameterObject != null && parameterObject instanceof IPage){
			IPage page =(IPage)parameterObject;
			if (page.getPagination() == null|| page.getPagination().isAvailable()) {
				return pageIntercept(invocation);
			}
		}
		return invocation.proceed();
	}
	
	
	
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	public void setProperties(Properties properties) {
		String dialectClass = properties.getProperty("dialectClass");
		try {
			 dialect = (Dialect) Class.forName(dialectClass).newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private Object pageIntercept(Invocation invocation) throws Throwable{
		int rowCount = 0;
		MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
		Object parameterObject = invocation.getArgs()[1];
		SqlSource sqlSource = getCountSqlSource(mappedStatement, parameterObject);
		MappedStatement newMappedStatement = copyMappedStatementBySqlSource(mappedStatement, sqlSource);
		Connection connection = newMappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
		DefaultParameterHandler dp = new DefaultParameterHandler(mappedStatement,parameterObject,sqlSource.getBoundSql(parameterObject));
		PreparedStatement countStmt = connection.prepareStatement(sqlSource.getBoundSql(parameterObject).getSql());    
		dp.setParameters(countStmt);
		ResultSet rs= countStmt.executeQuery();
		if(rs.next()){
			rowCount =rs.getInt(1);
		}
		rs.close();    
		countStmt.close();
		connection.close();
		
		IPage page =(IPage)parameterObject;
		
		if (page.getPagination() == null) {
			page.setPagination(new Pagination());
		}
		
		page.getPagination().setRowCount(rowCount);
		page.getPagination().pagination();
		sqlSource = getPageLimitSqlSource(mappedStatement, parameterObject);
		newMappedStatement = copyMappedStatementBySqlSource(mappedStatement, sqlSource);
		invocation.getArgs()[0] = newMappedStatement;
		return invocation.proceed();
	}
	

	private String getMapperSQL(MappedStatement mappedStatement,Object parameterObject) throws Throwable{
		SqlSource nowSqlSource = mappedStatement.getSqlSource();
		Class<?> sqlSourceType = nowSqlSource == null ? Object.class : nowSqlSource.getClass();
		Field rootSqlNodeField = sqlSourceType.getDeclaredField("rootSqlNode");
		rootSqlNodeField.setAccessible(true);
		SqlNode sqlNode = (SqlNode) rootSqlNodeField.get(nowSqlSource);
		DynamicContext context = new DynamicContext(mappedStatement.getConfiguration(), parameterObject);
		sqlNode.apply(context);
		return context.getSql();
	}
	
	private SqlSource getCountSqlSource(MappedStatement mappedStatement,Object parameterObject) throws Throwable{
		SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(mappedStatement.getConfiguration());
		String mapperSQL = getMapperSQL(mappedStatement,parameterObject);
		Class<?> parameterType = parameterObject == null ? Object.class: parameterObject.getClass();
		String newSql = "select count(1) from ( "+ mapperSQL+" ) t";
		SqlSource sqlSource = sqlSourceParser.parse(newSql, parameterType);
		return sqlSource;
	}
	
	private SqlSource getPageLimitSqlSource(MappedStatement mappedStatement,Object parameterObject) throws Throwable {
		SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(mappedStatement.getConfiguration());
		String mapperSQL = getMapperSQL(mappedStatement,parameterObject);
		Class<?> parameterType = parameterObject == null ? Object.class: parameterObject.getClass();
		IPage page =(IPage)parameterObject;
		String newSql = getLimitString(mapperSQL, page.getPagination().getCurrentMinRow(), page.getPagination().getCurrentMaxRow());
		SqlSource sqlSource = sqlSourceParser.parse(newSql, parameterType);
		return sqlSource;
	}
	
	private MappedStatement copyMappedStatementBySqlSource(MappedStatement mappedStatement,SqlSource sqlSource){
		MappedStatement.Builder builder = new MappedStatement.Builder(mappedStatement.getConfiguration(), mappedStatement.getId(),sqlSource, mappedStatement.getSqlCommandType());
		builder.resource(mappedStatement.getResource());     
		builder.fetchSize(mappedStatement.getFetchSize());     
		builder.statementType(mappedStatement.getStatementType());     
		builder.keyGenerator(mappedStatement.getKeyGenerator());     
		builder.keyProperty(mappedStatement.getKeyProperty());     
		builder.timeout(mappedStatement.getTimeout());     
		builder.parameterMap(mappedStatement.getParameterMap());     
		builder.resultMaps(mappedStatement.getResultMaps());     
		builder.cache(mappedStatement.getCache());     
		MappedStatement newMappedStatement = builder.build();
		return newMappedStatement;
	}
	
	private String getLimitString(String sql, int offset, int limit) {
		
		String whereLowerCase = null;
		String finalSql = null;
		String sqlLowerCase = sql.trim().toLowerCase();
	
		int whereIndex = sqlLowerCase.indexOf("where");
		if (whereIndex > 0) {
			whereLowerCase = sqlLowerCase.substring(whereIndex);
		}
		
		String limitArgs[] = new String[] { String.valueOf(offset),String.valueOf(limit)};
		String newSql = dialect.getLimitString(sqlLowerCase, offset, limit);
		String sqls[] = newSql.split("\\?");
		String querySQL = "";
		for (int i = 0; i < 2; i++) {
			querySQL = querySQL + " " + sqls[i] + limitArgs[i];
		}

		querySQL = querySQL.trim();
		
		if (whereLowerCase == null || "".endsWith(whereLowerCase)) {
			return querySQL;
		}
		
		int finalWhereIndex = querySQL.indexOf(whereLowerCase);

		if (finalWhereIndex > 0) {
			String begin = querySQL.substring(0, finalWhereIndex);
			String end = querySQL.substring(finalWhereIndex + whereLowerCase.length());
			finalSql = begin + sql.substring(whereIndex) + end;
		}
		return finalSql;
		
	}

}

 

分页接口：

package com.xuanwu.sms.smstask.webmanager.common.crud.entity;

import java.io.Serializable;

public interface IPage extends Serializable {
	
	public Pagination getPagination();

	public void setPagination(Pagination pagination);
}

 

分页实体类：

package com.xuanwu.sms.smstask.webmanager.common.crud.entity;

import java.io.Serializable;


public class Pagination implements Serializable{

	private static final long serialVersionUID = -6579435031214235862L;

	private int rowCount;// 总记录数
	private int pageSize = 15;// 每页记录数
	private int pageCount;// 总页数
	private int currentPage;// 当前页数
	private boolean next;// 是否能下一页
	private boolean previous;// 是否能上一页
	private boolean available = true;//是否分页
	
	public Pagination(){
		
	}
	
	public Pagination(String currentPage, int rowCount) {
		if (currentPage == null) {
			this.currentPage = 1;
		} else {
			this.currentPage = Integer.parseInt(currentPage);
		}
		this.rowCount = rowCount;
		// 计算总页数
		this.pageCount = (int) Math.ceil(this.rowCount / (double) this.pageSize);
		// 计算是否能上一页和下一页
		this.next = this.currentPage < this.pageCount;
		this.previous = this.currentPage > 1;
		
		
	}
	
	public Pagination pagination() {
		if (currentPage == 0) {
			this.currentPage = 1;
		}
		// 计算总页数
		this.pageCount = (int) Math
				.ceil(this.rowCount / (double) this.pageSize);
		// 计算是否能上一页和下一页
		this.next = this.currentPage < this.pageCount;
		this.previous = this.currentPage > 1;
		return this;
	} 

	public int getPageCount() {
		return pageCount;
	}

	public int getCurrentPage() {
		return currentPage;
	}

	public int getRowCount() {
		return rowCount;
	}

	public void setRowCount(int rowCount) {
		this.rowCount = rowCount;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getCurrentMinRow() {
		return (currentPage-1) * pageSize;
	}

	public int getCurrentMaxRow() {
		return currentPage * pageSize - 1;
	}

	public int getNextPage() {
		if (next) {
			return currentPage + 1;
		}
		return currentPage;
	}

	public int getPreviousPage() {
		if (previous) {
			return currentPage - 1;
		}
		return currentPage;
	}

	public boolean isNext() {
		return next;
	}

	public boolean isPrevious() {
		return previous;
	}

	public int nextPage() {
		if (next) {
			currentPage = currentPage + 1;
		}
		return currentPage;
	}

	public int previousPage() {
		if (previous) {
			currentPage = currentPage - 1;
		}
		return currentPage;
	}

	public boolean isAvailable() {
		return available;
	}

	public void setAvailable(boolean available) {
		this.available = available;
	}
}

 

pom.xml

 

mybatis版本：

  <dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis</artifactId>
   <version>3.0.6</version>
   <scope>compile</scope>
  </dependency>

 

hibernate版本

  <dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-core</artifactId>
   <version>3.6.8.Final</version>
  </dependency>

高级查询的分页条件要保存在哪里呢？

看写的代码是查询条件的bean需要继承IPage 接口的!

贴一段我写的，MyBatis版本3.0.5，方言参考Hibernate3.2.5。用了2个拦截器。

Statement部分拦截：

package com.xxx.common.page;

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();
		PreparedStatementHandler handler = (PreparedStatementHandler) ReflectUtil.getClassField(statement, "delegate");
		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) {

		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 );
		if (offset > 0) {
			pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
		}
		else {
			pagingSelect.append("select * from ( ");
		}
		pagingSelect.append(sql);
		if (offset > 0) {
			pagingSelect.append(" ) row_ ) where rownum_ <= " + limit + " and rownum_ > " + offset);
		}
		else {
			pagingSelect.append(" ) where rownum <= " + limit);
		}
		if ( isForUpdate ) {
			pagingSelect.append( " for update" );
		}
		return pagingSelect.toString();
	}
}

ResultSet部分拦截：

package com.xxx.common.page;

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) {
	}
}

插件配置略，反射工具类略。