Mybatis分页插件的实现

mybatis插件的使用，新增了两个类，一个是Interceptor，负责拦截并处理数据，一个是PageParams，作为入参

使用方式：

1.配置mybatis的插件

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource"></property>
		<property name="mapperLocations" value="classpath:sqlConfig/*.xml"></property><!-- 扫描所有的xml文件 -->
		<property name="plugins">
			<array>
				<bean class="com.*.*.*.PagePlugin"></bean>
			</array>
		</property>
	</bean>

2. 在dao中将入参设置为PageParams，例如

List<UserPwd> selectUserPwdByPage(PageParams<UserPwd> param);

3.调用

PageParams<UserPwd> pp = new PageParams<UserPwd>();
		pp.setPage(1);
		pp.setPageSize(10);
		UserPwd userPwd = new UserPwd();
		userPwd.setUserId("123");
		pp.setParam(userPwd);
		List<UserPwd> list = userPwdDao.selectUserPwdByPage(pp);
		
		System.out.println("查出数据"+list.size());
		System.out.println(pp);

查询结果为list，总数和总页数在pp中

4.sql

查询参数放在param中，sql中通过param取出

select <include refid="fields" /> from user_pwd 
		<where>
			<if test="param.userId != null">
				user_id = #{param.userId}
			</if>
		</where>

5. 代码

Interceptor

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

	private Integer defaultPage;
	private Integer defaultPageSize;
	private Boolean defaultUseFlag;
	private Boolean defaultCheckFlag;
	
	
	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		StatementHandler stmtHandler = getUnProxyObject(invocation);
		MetaObject metaStatementHandler = SystemMetaObject.forObject(stmtHandler);
		
		String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
		//不是select语句
		if(!checkSelect(sql)) {
			return invocation.proceed();
		}
		
		BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
		Object parameterObject = boundSql.getParameterObject();
		PageParams pageParams = getPageParams(parameterObject);
		//没有分页参数，不启用插件
		if(pageParams == null) {
			return invocation.proceed();
		}
		//获取分页参数，获取不到时候使用默认值
		Integer pageNum = pageParams.getPage() == null ? 
					this.defaultPage : pageParams.getPage();
		Integer pageSize = pageParams.getPageSize() == null ?
					this.defaultPageSize : pageParams.getPageSize();
		Boolean useFlag = pageParams.getUseFlag() == null ?
					this.defaultUseFlag : pageParams.getUseFlag();
		Boolean checkFlag = pageParams.getCheckFlag() == null ?
					this.defaultCheckFlag : pageParams.getCheckFlag();
		//不适用分页插件
		if(!useFlag) {
			return invocation.proceed();
		}
		
		int total = getTotal(invocation, metaStatementHandler, boundSql);
		//回填总数到分页参数里
		setTotalToPageParams(pageParams, total, pageSize);
		//检查当前页码的有效性
		checkPage(checkFlag, pageNum, pageParams.getTotalPage());
		//修改sql
		return changeSql(invocation, metaStatementHandler, boundSql, pageNum, pageSize);
	}
	@Override
	public Object plugin(Object statementHandler) {
		return Plugin.wrap(statementHandler, this);
	}

	@Override
	public void setProperties(Properties props) {
		String strDefaultPage = props.getProperty("default.page", "1");
		String strDefaultPageSize = props.getProperty("default.pageSize", "50");
		String strDefaultUseFlag = props.getProperty("default.useFlag", "false");
		String strDefaultCheckFlag = props.getProperty("default.checkFlag", "false");
		
		this.defaultPage = Integer.parseInt(strDefaultPage);
		this.defaultPageSize = Integer.parseInt(strDefaultPageSize);
		this.defaultUseFlag = Boolean.parseBoolean(strDefaultUseFlag);
		this.defaultCheckFlag = Boolean.parseBoolean(strDefaultCheckFlag);

	}
	
	private StatementHandler getUnProxyObject(Invocation ivt) {
		StatementHandler statementHandler = (StatementHandler) ivt.getTarget();
		MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
		Object object = null;
		while(metaStatementHandler.hasGetter("h")) {
			object = metaStatementHandler.getValue("h");
		}
		if(object == null) {
			return statementHandler;
		}
		return (StatementHandler) object;
	}
	
	private boolean checkSelect(String sql) {
		String trimSql = sql.trim();
		int idx = trimSql.toLowerCase().indexOf("select");
		
		return idx == 0;
	}
	/*
	 * **********分解分页参数，这里支持使用Map和@Param注解传递参数，或者pojo继承PageParams，这三种方式都可以
	 * 只拦截入参是PageParams的
	 */
	private PageParams getPageParams(Object parameterObject) {
		if(parameterObject == null) {
			return null;
		}
		PageParams pageParams = null;
//		if(parameterObject instanceof Map) {
//			Map<String, Object> paramMap = (Map<String, Object>) parameterObject;
//			Set<String> keySet = paramMap.keySet();
//			Iterator<String> it = keySet.iterator();
//			while(it.hasNext()) {
//				String key = it.next();
//				Object value = paramMap.get(key);
//				if(value instanceof PageParams) {
//					return (PageParams) value;
//				}
//			}
//		}else if(parameterObject instanceof PageParams) {
//			pageParams = (PageParams) parameterObject;
//		}
		if(parameterObject instanceof PageParams) {
			pageParams = (PageParams) parameterObject;
		}
		
		return pageParams;
		
	}
	
	private int getTotal(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql) throws SQLException {
		MappedStatement mappedStatement = (MappedStatement) 
				metaStatementHandler.getValue("delegate.mappedStatement");
		Configuration cfg = mappedStatement.getConfiguration();
		String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
		String countSql = "select count(*) as total from ("+sql+") a";
		Connection connection  = (Connection) invocation.getArgs()[0];
		PreparedStatement ps = null;
		int total = 0;
		try {
			ps = connection.prepareStatement(countSql);
			BoundSql countBoundSql = new BoundSql(cfg, countSql, 
					boundSql.getParameterMappings(), 
					boundSql.getParameterObject());
			ParameterHandler handler = new DefaultParameterHandler(mappedStatement, 
					boundSql.getParameterObject(), countBoundSql);
			handler.setParameters(ps);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				total = rs.getInt("total");
			}
		}finally {
			if(ps != null) {
				ps.close();
			}
		}
		return total;
	}
	private void setTotalToPageParams(PageParams pageParams, int total, Integer pageSize) {
		pageParams.setTotal(total);
		int totalPage = total % pageSize == 0 ? total / pageSize : total / pageSize +1;
		pageParams.setTotalPage(totalPage);
		
	}
	private void checkPage(Boolean checkFlag, Integer pageNum, Integer totalPage) throws Exception {
		if(checkFlag) {
			if(pageNum > totalPage) {
				//throw new Exception("查询时报，查询页码【"+pageNum+"】大于总页数【"+totalPage+"】！");
				pageNum = totalPage;
			}
		}
		
	}
	private Object changeSql(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql, Integer pageNum,
			Integer pageSize) throws Exception {
		String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
		String newSql = "select a.* from ("+sql+") a limit ?, ?";
		metaStatementHandler.setValue("delegate.boundSql.sql", newSql);
		PreparedStatement ps = (PreparedStatement) invocation.proceed();
		int count = ps.getParameterMetaData().getParameterCount();
		ps.setInt(count-1, (pageNum-1)*pageSize);
		ps.setInt(count, pageSize);
		return ps;
	}
}

PageParams

public class PageParams <T>{
	private Integer page;//当前页码
	private Integer pageSize;//每页条数
	private Boolean useFlag = true;//是否启用插件
	private Boolean CheckFlag = true;//是否检测当前页码的有效性
	private Integer total;//当前sql返回总数，插件回填
	private Integer totalPage;//sql以当前分页的总页数，插件回填
	
	private Object param;//查询参数
	private List<T> resultList;//返回参数，可以把结果直接设置进来，直接返回
	
	
	
	public Object getParam() {
		return param;
	}
	public void setParam(Object param) {
		this.param = param;
	}
	public List<T> getResultList() {
		return resultList;
	}
	public void setResultList(List<T> resultList) {
		this.resultList = resultList;
	}
	public Integer getPage() {
		return page;
	}
	public void setPage(Integer page) {
		this.page = page;
	}
	public Integer getPageSize() {
		return pageSize;
	}
	public void setPageSize(Integer pageSize) {
		this.pageSize = pageSize;
	}
	public Boolean getUseFlag() {
		return useFlag;
	}
	public void setUseFlag(Boolean useFlag) {
		this.useFlag = useFlag;
	}
	public Boolean getCheckFlag() {
		return CheckFlag;
	}
	public void setCheckFlag(Boolean checkFlag) {
		CheckFlag = checkFlag;
	}
	public Integer getTotal() {
		return total;
	}
	public void setTotal(Integer total) {
		this.total = total;
	}
	public Integer getTotalPage() {
		return totalPage;
	}
	public void setTotalPage(Integer totalPage) {
		this.totalPage = totalPage;
	}
	@Override
	public String toString() {
		return "PageParams [page=" + page + ", pageSize=" + pageSize + ", useFlag=" + useFlag + ", CheckFlag="
				+ CheckFlag + ", total=" + total + ", totalPage=" + totalPage + "]";
	}
}