mybatis 分页插件的实现（xml映射配置九）

封装基础bean

public class PageParams {
	
	// 当前页码
	private Integer page;
	// 每页数量
	private Integer pageSize;
	// 总数量
	private Integer total;
	// 总页数
	private Integer pageTotal;
	
	// 是否启动插件（开关）
	private Boolean useFlag;
	// 是否检测页码下标的有效性
	private Boolean checkFlag;
	// 是否清楚order by后面的语句
	private Boolean cleanOrderBy;
	
	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 Integer getTotal() {
		return total;
	}
	public void setTotal(Integer total) {
		this.total = total;
	}
	public Integer getPageTotal() {
		return pageTotal;
	}
	public void setPageTotal(Integer pageTotal) {
		this.pageTotal = pageTotal;
	}
	public Boolean getUseFlag() {
		return useFlag;
	}
	public void setUseFlag(Boolean useFlag) {
		this.useFlag = useFlag;
	}
	public Boolean getCheckFlag() {
		return checkFlag;
	}
	public void setCheckFlag(Boolean checkFlag) {
		this.checkFlag = checkFlag;
	}
	public Boolean getCleanOrderBy() {
		return cleanOrderBy;
	}
	public void setCleanOrderBy(Boolean cleanOrderBy) {
		this.cleanOrderBy = cleanOrderBy;
	}
}

插件的实现

@Intercepts({ @Signature(
		type = StatementHandler.class, 
		method = "prepare", 
		args = { Connection.class, Integer.class }) })
public class PagePlugin implements Interceptor{
	
	// private static final Logger logger = LoggerFactory.getLogger(MyPlugin.class);
	
	// 当前页码
	private Integer defaultPage;
	// 每页数量
	private Integer defaultPageSize;

	// 是否启动插件（开关）
	private Boolean defaultUseFlag;
	// 是否检测页码下标的有效性
	private Boolean defaultCheckFlag;
	// 是否清楚order by后面的语句
	private Boolean defaultCleanOrderBy;

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		StatementHandler statementHandler =  getNonProxyObject(invocation);
		MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
		
		// 获取StatementHandler中的变量
		BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
		String sql = boundSql.getSql();
		
		// 判断是否为select语句, 如果不是，结束本次处理
		if (!isSelect(sql)) {
			invocation.proceed();
		}
		
		// 获取StatementHandler中的变量
		// MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
		Object parameterObject = boundSql.getParameterObject();
		PageParams pageParam = getPageParamsByObject(parameterObject);
		
		// 判断是否入参中是否包含分页信息, 如果没有，结束本次处理
		if (pageParam == null) {
			return invocation.proceed();
		}
		
		// 判断是否启用分页
		boolean isUse = pageParam.getUseFlag() == null ? defaultUseFlag : pageParam.getUseFlag();
		if (!isUse) {
			return invocation.proceed();
		}
		
		// 分页参数相关
		Integer page = pageParam.getPage() == null ? defaultPage : pageParam.getPage();
		Integer pageSize = pageParam.getPageSize() == null ? defaultPageSize : pageParam.getPageSize();
		boolean checkFlag = pageParam.getCheckFlag() == null ? defaultCheckFlag : pageParam.getCheckFlag();
		boolean cleanOrderBy = pageParam.getCleanOrderBy() == null ? defaultCleanOrderBy : pageParam.getCleanOrderBy();
		
		// 总条数
		int total = getTotal(invocation, metaStatementHandler, boundSql, cleanOrderBy);
		// 总页数
		int pageTotal = (total % pageSize == 0) ? (total / pageSize) : (total / pageSize) + 1;
		pageParam.setTotal(total);
		pageParam.setPageTotal(pageTotal);
		
		// 检查当前页码
		checkCurrentPage(checkFlag, page, pageTotal);
		
		return preparedSQL(invocation, metaStatementHandler, boundSql, page, pageSize);
	}

	private Object preparedSQL(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql, Integer page,
			Integer pageSize) throws InvocationTargetException, IllegalAccessException, SQLException {
		String sql = boundSql.getSql();
		
		// 重置分页sql
		String pageSql = String.format("select * from (%s) temp_table limit ?, ?", sql);
		metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
		
		Object proceed = invocation.proceed();
		// 设置分页参数
		preparePageDataParams((PreparedStatement) proceed, page, pageSize);
		return proceed;
	}

	// 最后两位
	private void preparePageDataParams(PreparedStatement ps, Integer page, Integer pageSize) throws SQLException {
		int count = ps.getParameterMetaData().getParameterCount();
		ps.setInt(count - 1, (page - 1) * pageSize);
		ps.setInt(count, pageSize);
	}

	// 校验合法性，不然查询不出信息
	private void checkCurrentPage(boolean checkFlag, Integer page, int pageTotal) throws Exception {
		if (!checkFlag) {
			return ;
		}
		
		if (page > pageTotal) {
			throw new Exception(String.format("分页失败，当前页码【%s】，大于总页码【%s】", page, pageTotal));
		}
	}

	private int getTotal(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql,
			boolean cleanOrderBy) throws SQLException {
		MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
		Configuration configuration = mappedStatement.getConfiguration();
		String sql = boundSql.getSql();
		
		if (cleanOrderBy) {
			sql = cleanOrderByForSql(sql);
		}
		
		// 查询count
		String countSql = String.format("select count(*) as total from (%s) temp_total", sql);
		Connection connection = (Connection) invocation.getArgs()[0];
		
		PreparedStatement pst = null;
		int total = 0;
		try {
			pst = connection.prepareStatement(countSql);
			
			// 构建新的 BoundSql
			BoundSql countBoundSql = new BoundSql(configuration, countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
			// 构造参数到prepareStatement对象的映射
			ParameterHandler handler = new DefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), countBoundSql);
			handler.setParameters(pst);
			// 执行sql
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				total = rs.getInt("total");
			}
			
		} finally {
			if (pst != null) {
				pst.close();
			}
		}
		
		return total;
	}

	private String cleanOrderByForSql(String sql) {
		String lowerCase = sql.trim().toLowerCase();
		// 判断是否还有order by 关键字
		int index = lowerCase.lastIndexOf("order");
		if (index == -1) {
			return sql;
		}
		return sql.substring(0, index);
	}

	private PageParams getPageParamsByObject(Object parameterObject) throws Exception {
		if (parameterObject == null) {
			return null;
		}

		// 多个入参，未使用@Param注解，系统封装成一个map
		if (parameterObject instanceof Map) {
			Map<String, Object> paramMap = (Map<String, Object>) parameterObject;
			Collection<Object> values = paramMap.values();
			for (Object object : values) {
				if (object instanceof PageParams) {
					return (PageParams) object;
				}
 			}
		} else if (parameterObject instanceof PageParams) { // 使用@Param注解
			return (PageParams) parameterObject;
		} else { // bean
			Field[] fields = parameterObject.getClass().getDeclaredFields();
			for (Field field : fields) {
				if (field.getType() == PageParams.class) {
					PropertyDescriptor pd = new PropertyDescriptor(field.getName(), parameterObject.getClass());
					Method readMethod = pd.getReadMethod();
					PageParams invoke = (PageParams) readMethod.invoke(parameterObject);
					return invoke;
				}
			}
			return null;
		}
		return null;

配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<plugins>
		<plugin interceptor="priv.dengjl.ns.page.PagePlugin">
			<!-- 默认页码 -->
			<property name="default.page" value="1" />
			<!-- 默认每页条数 -->
			<property name="default.pageSize" value="20" />
			<!-- 是否启动分页插件功能 -->
			<property name="default.useFlag" value="true" />
			<!-- 是否检查页码有效性，如果非有效，则抛出异常. -->
			<property name="default.checkFlag" value="false" />
			<!-- 针对哪些含有order by的SQL，是否去掉最后一个order by 以后的SQL语句，提高性能 -->
			<property name="default.cleanOrderBy" value="false" />
		</plugin>
	</plugins>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.cj.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/test" />
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>

	<!-- 使用注解方式 -->
	<mappers>
		<mapper resource="priv/dengjl/ns/day0001/mapper/RoleMapper.xml" />
	</mappers>
</configuration>

测试

• 测试类型为PageParams
• 测试Map对象中，包含PageParams
• 测试bean对象中包含PageParams
• 测试拥有多个@Param注解中，至少有一个PageParams