2020-05-18

分页插件

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.ibatis.binding.BindingException;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
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.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.SystemMetaObject;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
/**

• 拦截查询语句，对查询语句中有参数PageRequest的查询处理，其他处理直接返回；
• 对参数中有PageRequest的请求，封装两条查询语句，一句是查询总数，一句是分页和排序后的数据，最后返回PageResponse对象
• @author Orc

*/
@Intercepts({ @Signature(method = “prepare”, type = StatementHandler.class, args = { Connection.class, Integer.class }),
@Signature(method = “query”, type = Executor.class, args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) })
@Component
public class PageInterceptor implements Interceptor{

private static final Logger LOGGER =  LoggerFactory.getLogger(PageInterceptor.class);  

protected ThreadLocal<PageRequest> pageRequsetThreadLocal = new ThreadLocal<PageRequest>();
protected ThreadLocal<PageResponse<?>> pageResponseThreadLocal = new ThreadLocal<PageResponse<?>>();

@Override
public Object intercept(Invocation invocation) throws Throwable {
	if (invocation.getTarget() instanceof StatementHandler) { // 控制SQL和查询总数的地方
		PageRequest pageRequest = pageRequsetThreadLocal.get();
		if (pageRequest == null) { //不是分页查询
			return invocation.proceed();
		}
		PageResponse<?> pageResponse = pageResponseThreadLocal.get();
		
		StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); 
		MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);  
        // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环  
        // 可以分离出最原始的的目标类)  
        while (metaStatementHandler.hasGetter("h")) {  
            Object object = metaStatementHandler.getValue("h");  
            metaStatementHandler = SystemMetaObject.forObject(object);  
        }  
        // 分离最后一个代理对象的目标类  
        while (metaStatementHandler.hasGetter("target")) {  
            Object object = metaStatementHandler.getValue("target");  
            metaStatementHandler = SystemMetaObject.forObject(object);  
        }  
        MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
        BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");  
        // 分页参数作为参数对象parameterObject的一个属性  
        String sql = boundSql.getSql();  
        // 重写sql  
        String pageSql = buildPageSql(sql, pageRequest);  
        //重写分页sql  
        metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
		Connection connection = (Connection) invocation.getArgs()[0];
		// 请求总数据行数，重设分页参数里的总页数等  
        setPageParameter(sql, connection, mappedStatement, boundSql, pageResponse);  
		return invocation.proceed();
	} else { // 查询结果的地方
		// 获取是否有分页Page对象
		PageRequest pageRequest = findPageRequestObject(invocation.getArgs()[1]);
		if (pageRequest == null) {
			return invocation.proceed();
		}
		//设置真正的parameterObj
		invocation.getArgs()[1] = extractRealParameterObject(invocation.getArgs()[1]);
		pageRequsetThreadLocal.set(pageRequest);
		PageResponse<?> pageResponse = new PageResponse();
		pageResponse.setPageNum(pageRequest.getPageNum());
		pageResponse.setPageSize(pageRequest.getPageSize());
		pageResponseThreadLocal.set(pageResponse);
		try {
			Object resultObj = invocation.proceed(); // Executor.query(..)
			if (resultObj instanceof List) {
				pageResponse.setDataList((List) resultObj);
			}
			return Arrays.asList(pageResponse);
		} finally {
			pageRequsetThreadLocal.remove();
			pageResponseThreadLocal.remove();
		}
	}
}

/** 
 * 修改原SQL为分页SQL 
 * @param sql 
 * @param page 
 * @return 
 */  
private String buildPageSql(String sql, PageRequest page) {  
    StringBuilder pageSql = new StringBuilder(200);  
    pageSql.append("select temp.* from ( ");  
    pageSql.append(sql);  
    pageSql.append(" ) temp  ");

// if(StringUtils.isNotBlank(page.getOrderField())){
// pageSql.append(“order by “).append(page.getOrderField()).append(” “).append(page.getOrderWay());
// }
pageSql.append(” limit “).append(page.getLimitBegin()).append(”,”).append(page.getPageSize());
return pageSql.toString();
}

/** 
 * 获取总记录数 
 * @param sql 
 * @param connection 
 * @param mappedStatement 
 * @param boundSql 
 * @param page 
 */  
private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement,  
                              BoundSql boundSql, PageResponse<?> pageResponse) {  
    // 记录总记录数  
    String countSql = "select count(0) from (" + sql + ") t";  
    PreparedStatement countPreparedStatement = null;  
    ResultSet rs = null;  
    try {  
    	countPreparedStatement = connection.prepareStatement(countSql);  
        BoundSql countBS = getCountBoundSql(mappedStatement.getConfiguration(), countSql, boundSql);
        setParameters(countPreparedStatement, mappedStatement, countBS, boundSql.getParameterObject());  
        rs = countPreparedStatement.executeQuery();  
        int totalCount = 0;  
        if (rs.next()) {  
            totalCount = rs.getInt(1);  
        }  
        pageResponse.setTotalLine(totalCount);  
        int totalPage = totalCount / pageResponse.getPageSize() + ((totalCount % pageResponse.getPageSize() == 0) ? 0 : 1);  
        pageResponse.setTotalPage(totalPage);  
    } catch (SQLException e) {  
    	LOGGER.error("Ignore this exception", e);  
    }catch(BindingException be){
    	LOGGER.error("bind parameter error" , be);
    }
    finally {  
        try {  
            rs.close();  
        } catch (SQLException e) {  
        	LOGGER.error("Ignore this exception", e);  
        }  
        try {  
        	countPreparedStatement.close();  
        } catch (SQLException e) {  
        	LOGGER.error("Ignore this exception", e);  
        }  
    }  
} 

/**
 * 生成count用的BoundSql
 * @param configuration
 * @param countSql
 * @param boundSql
 * @return
 */
private BoundSql getCountBoundSql(Configuration configuration,String countSql,BoundSql boundSql){
	BoundSql countBS = new BoundSql(configuration, countSql,  
            boundSql.getParameterMappings(), boundSql.getParameterObject());
	for (ParameterMapping mapping : boundSql.getParameterMappings()) {  
        String prop = mapping.getProperty();  
        if (boundSql.hasAdditionalParameter(prop)) {  
        	countBS.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));  
        }  
    }  
	return countBS;
}

/** 
 * 代入参数值 
 * @param ps 
 * @param mappedStatement 
 * @param boundSql 
 * @param parameterObject 
 * @throws SQLException 
 */  
private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,  
                           Object parameterObject) throws SQLException {  
    ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);  
    parameterHandler.setParameters(ps);
}  

private PageRequest findPageRequestObject(Object parameterObj) {
	if (parameterObj instanceof PageRequest) {
		return (PageRequest) parameterObj;
	} else if (parameterObj instanceof Map) {
		for (Object val : ((Map<?, ?>) parameterObj).values()) {
			if (val instanceof PageRequest) {
				return (PageRequest) val;
			}
		}
	}
	return null;
}

/**
 * <pre>
 * 把真正的参数对象解析出来
 * Spring会自动封装对个参数对象为Map<String, Object>对象
 * 对于通过@Param指定key值参数我们不做处理，因为XML文件需要该KEY值
 * 而对于没有@Param指定时，Spring会使用0,1作为主键
 * 对于没有@Param指定名称的参数,一般XML文件会直接对真正的参数对象解析，
 * 此时解析出真正的参数作为根对象
 * </pre>
 */
private Object extractRealParameterObject(Object parameterObj) {
	if (parameterObj instanceof Map<?, ?>) {
		Map<?, ?> parameterMap = (Map<?, ?>) parameterObj;
		if (parameterMap.size() == 2) {
			boolean springMapWithNoParamName = true;
			for (Object key : parameterMap.keySet()) {
				if (!(key instanceof String)) {
					springMapWithNoParamName = false;
					break;
				}
				String keyStr = (String) key;
				if (!"0".equals(keyStr) && !"1".equals(keyStr)) {
					springMapWithNoParamName = false;
					break;
				}
			}
			if (springMapWithNoParamName) {
				for (Object value : parameterMap.values()) {
					if (!(value instanceof PageRequest)) {
						return value;
					}
				}
			}
		}
	}
	return parameterObj;
}

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

@Override
public void setProperties(Properties arg0) {
	// TODO Auto-generated method stub
	
}

}