pagehelper库(https://github.com/pagehelper/Mybatis-PageHelper)是java后端开发领域比较常用的分页插件库,能够很好的实现分页需求。这次记录一下他的另外一个功能,就是在分页的同时传入自定义的排序条件,比如会遇到如下需求,分页查询某些表格数据后,再对某个字段进行升序或降序排序。
设置排序的源码如下(PageMethod):
/** * 开始分页 * * @param pageNum 页码 * @param pageSize 每页显示数量 * @param orderBy 排序 */ public static <E> Page<E> startPage(int pageNum, int pageSize, String orderBy) { Page<E> page = startPage(pageNum, pageSize); page.setOrderBy(orderBy); return page; }
此处传入的orderBy字符串需形如(假如按照id排序):"id asc"或"id desc"。
解析排序,组装sql的源码如下(PageInterceptor):
@Override public Object intercept(Invocation invocation) throws Throwable { try { Object[] args = invocation.getArgs(); MappedStatement ms = (MappedStatement) args[0]; ......//调用方言获取分页 sql String pageSql = dialect.getPageSql(ms, boundSql, parameter, rowBounds, pageKey); ...... } }
AbstractHelperDialect:
@Override public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) { String sql = boundSql.getSql(); Page page = getLocalPage(); //支持 order by String orderBy = page.getOrderBy(); if (StringUtil.isNotEmpty(orderBy)) { pageKey.update(orderBy); sql = OrderByParser.converToOrderBySql(sql, orderBy); } if (page.isOrderByOnly()) { return sql; } return getPageSql(sql, page, pageKey); }
OrderByParser:
/** * convert to order by sql * * @param sql * @param orderBy * @return */ public static String converToOrderBySql(String sql, String orderBy) { //解析SQL Statement stmt = null; try { stmt = CCJSqlParserUtil.parse(sql); Select select = (Select) stmt; SelectBody selectBody = select.getSelectBody(); //处理body-去最外层order by List<OrderByElement> orderByElements = extraOrderBy(selectBody); String defaultOrderBy = PlainSelect.orderByToString(orderByElements); if (defaultOrderBy.indexOf('?') != -1) { throw new RuntimeException("原SQL[" + sql + "]中的order by包含参数,因此不能使用OrderBy插件进行修改!"); } //新的sql sql = select.toString(); } catch (Throwable e) { e.printStackTrace(); } return sql + " order by " + orderBy; }
实际上最后的实现思路还是拼接sql,将order by的条件拼接到sql语句中。