PageHelper 隐式分页排序

  PageHelper.startPage(1, 10);
  PageHelper.orderBy("id");

这是 PageHelper 平常分页排序的普遍用法,然而 PageHelper 还支持隐式分页和排序用法,开启 pagehelper.supportMethodsArguments即可,supportMethodsArguments默认是 false

下面以一个例子分析

  ....
  query.setOrderBy("id");
  query.setPageNum(1);
  query.setPageSize(10);
  itemMapper.selectItems(query); //myBatis mapper查询接口
  ....

itemMapper.selectItems 就是简单的一个 SQL,大概就是

  select * from item

下面进行代码跟踪,进入itemMapper.selectItems方法,首先跳转到的是ibatis的方法

  @Override
  public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    try {
      if (Object.class.equals(method.getDeclaringClass())) {
        return method.invoke(this, args);
      } else if (method.isDefault()) {
        return invokeDefaultMethod(proxy, method, args);
      }
    } catch (Throwable t) {
      throw ExceptionUtil.unwrapThrowable(t);
    }
    final MapperMethod mapperMethod = cachedMapperMethod(method);
    return mapperMethod.execute(sqlSession, args);
  }

一路跟下去会进到

  @Override
  public <E> List<E> selectList(String statement, Object parameter) {
    return this.sqlSessionProxy.selectList(statement, parameter);
  }
  @Override
  public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    try {
      Set<Method> methods = signatureMap.get(method.getDeclaringClass());
      if (methods != null && methods.contains(method)) {
        return interceptor.intercept(new Invocation(target, method, args));
      }
      return method.invoke(target, args);
    } catch (Exception e) {
      throw ExceptionUtil.unwrapThrowable(e);
    }
  }

之后进入interceptor.intercept方法,会跳到 PageHelper 包的intercept方法

  List resultList;
    //调用方法判断是否需要进行分页,如果不需要,直接返回结果
  if (!dialect.skip(ms, parameter, rowBounds)) {
      //判断是否需要进行 count 查询
      if (dialect.beforeCount(ms, parameter, rowBounds)) {
          //查询总数
          Long count = count(executor, ms, parameter, rowBounds, resultHandler, boundSql);
          //处理查询总数,返回 true 时继续分页查询,false 时直接返回
          if (!dialect.afterCount(count, parameter, rowBounds)) {
              //当查询总数为 0 时,直接返回空的结果
              return dialect.afterPage(new ArrayList(), parameter, rowBounds);
          }
      }
      resultList = ExecutorUtil.pageQuery(dialect, executor,
              ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
  } else {
      //rowBounds用参数值,不使用分页插件处理时,仍然支持默认的内存分页
      resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
  }

由此可见,查询方法被 PageHelper 给代理了,可是上面的业务代码并没有显式使用 PageHelper,就是说,PageHelper 是开箱即用了,只要引入了,就会拦截使用,然后再进去dialect.skip方法

  @Override
  public boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
      if (ms.getId().endsWith(MSUtils.COUNT)) {
          throw new RuntimeException("在系统中发现了多个分页插件,请检查系统配置!");
      }
      Page page = pageParams.getPage(parameterObject, rowBounds);
      if (page == null) {
          return true;
      } else {
          //设置默认的 count 列
          if (StringUtil.isEmpty(page.getCountColumn())) {
              page.setCountColumn(pageParams.getCountColumn());
          }
          autoDialect.initDelegateDialect(ms);
          return false;
      }
  }

再进去Page page = pageParams.getPage(parameterObject, rowBounds);这一行

  Page page = PageHelper.getLocalPage();
  if (page == null) {
      if (rowBounds != RowBounds.DEFAULT) {
          if (offsetAsPageNum) {
              page = new Page(rowBounds.getOffset(), rowBounds.getLimit(), rowBoundsWithCount);
          } else {
              page = new Page(new int[]{rowBounds.getOffset(), rowBounds.getLimit()}, rowBoundsWithCount);
              //offsetAsPageNum=false的时候,由于PageNum问题,不能使用reasonable,这里会强制为false
              page.setReasonable(false);
          }
          if(rowBounds instanceof PageRowBounds){
              PageRowBounds pageRowBounds = (PageRowBounds)rowBounds;
              page.setCount(pageRowBounds.getCount() == null || pageRowBounds.getCount());
          }
      } else if(parameterObject instanceof IPage || supportMethodsArguments){
          try {
              page = PageObjectUtil.getPageFromObject(parameterObject, false);
          } catch (Exception e) {
              return null;
          }
      }
      if(page == null){
          return null;
      }
      PageHelper.setLocalPage(page);
  }

else if(parameterObject instanceof IPage || supportMethodsArguments) 看出,假如supportMethodsArguments开启了,会进到PageObjectUtil.getPageFromObject方法

  Object orderBy = getParamValue(paramsObject, "orderBy", false);
  boolean hasOrderBy = false;
  if (orderBy != null && orderBy.toString().length() > 0) {
      hasOrderBy = true;
  }
  try {
      Object _pageNum = getParamValue(paramsObject, "pageNum", required);
      Object _pageSize = getParamValue(paramsObject, "pageSize", required);
      if (_pageNum == null || _pageSize == null) {
          if(hasOrderBy){
              Page page = new Page();
              page.setOrderBy(orderBy.toString());
              page.setOrderByOnly(true);
              return page;
          }
          return null;
      }
      pageNum = Integer.parseInt(String.valueOf(_pageNum));
      pageSize = Integer.parseInt(String.valueOf(_pageSize));
  } catch (NumberFormatException e) {
      throw new PageException("分页参数不是合法的数字类型!", e);
  }
  Page page = new Page(pageNum, pageSize);
  //count查询
  Object _count = getParamValue(paramsObject, "count", false);
  if (_count != null) {
      page.setCount(Boolean.valueOf(String.valueOf(_count)));
  }
  //排序
  if (hasOrderBy) {
      page.setOrderBy(orderBy.toString());
  }

从上面可以看到,代码会从接口参数里获取orderBypageNumpageSize的值,然后设置到当前线程的page

回到 PageHelper 包的intercept方法,进入ExecutorUtil.pageQuery方法,最后看到封装sql的方法

  @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);
  }

从上面可以看出,假如当前线程的pageorderBy值,那么就会拼接到执行sql上去,也就是一开始的query里的orderBy参数的值就这样用起来了,假如还有pageNumpageSize,就进入到getPageSql(sql, page, pageKey)

  @Override
  public String getPageSql(String sql, Page page, CacheKey pageKey) {
      StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
      sqlBuilder.append("SELECT * FROM ( ");
      sqlBuilder.append(" SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ( ");
      sqlBuilder.append(sql);
      sqlBuilder.append(" ) TMP_PAGE)");
      sqlBuilder.append(" WHERE ROW_ID <= ? AND ROW_ID > ?");
      return sqlBuilder.toString();
  }

这里用的数据库是Oracle,所以上面是相对应的sql拼接实现,可以看出这里拼接了分页的相关sql,当然之后还有一系列的赋值操作,进入对应的分页参数操作方法

  @Override
  public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {
      paramMap.put(PAGEPARAMETER_FIRST, page.getEndRow());
      paramMap.put(PAGEPARAMETER_SECOND, page.getStartRow());
      //处理pageKey
      pageKey.update(page.getEndRow());
      pageKey.update(page.getStartRow());
      //处理参数配置
      handleParameter(boundSql, ms);
      return paramMap;
  }

这里就把page里的分页相关参数值设置到了paramMapPAGEPARAMETER_FIRSTPAGEPARAMETER_SECOND就是对应上面sql最后的那两个分页占位符

总结:pagehelper.supportMethodsArguments设置为 true,那么 Mybatis 的 mapper 接口参数对象里的orderBypageNumpageSize这三个关键参数值会被使用到具体的执行sql里。

上一篇:提升工作效率 终端复用神器Tmux


下一篇:vue使用filterBy,orderBy实现搜索筛选功能