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语句中。