背景和配置方法在此:
springboot-bootstrapTable+PageHelper+mybatis服务端分页
只显示一页内容
就算数据库中有100条数据,但PageSize=10时也只显示1页,共计10条。把PageSize改大到50时,也只显示1页,共计50条。
经过排查,发现是bean.setTotal()这句。不需要限制,让PageHelper自己确定就可以了。
// vo存储了PageNumber和PageSize,可以看上一篇配置方法
PageHelper.startPage(vo.getPageNumber(), vo.getPageSize());
List<SysOrder> sysOrders = this.orderMapper.listOrders(vo);
// order类做了一些修改,是真正要展示在页面上的类。有的blog说这里最好不要修改,其实包一下是完全不影响的
for (SysOrder sysOrder : sysOrders) {
Order o = new Order(sysOrder);
o.setStrCreateTime();
orders.add(o);
}
PageInfo bean = new PageInfo<SysOrder>(sysOrders);
bean.setList(orders);
//bean.setTotal(sysOrders.size());
return bean;
xml中orderby失效
我的mapper中使用了类似 ORDER BY q.create_time DESC 的语句,但是发现并没有排序。看下面的测试结果:
mysql> (select order_id, create_time from A order by create_time desc limit 10) union (select order_id, create_time from B order by create_time desc limit 10);
+------------+---------------------+
| order_id | create_time |
+------------+---------------------+
| Q*******03 | 2023-02-10 13:25:43 |
| Q*******02 | 2023-02-10 10:57:41 |
...
| S******* | 2023-01-05 16:27:19 |
| C******* | 2023-01-05 16:20:59 |
| C******* | 2022-12-22 13:15:19 |
| Q*******06 | 2022-12-22 13:12:31 |
+------------+---------------------+
20 rows in set (0.01 sec)
mysql> (select order_id, create_time from A order by create_time desc ) union (select order_id, create_time from B order by create_time desc ) limit 10;
+--------------+---------------------+
| order_id | create_time |
+--------------+---------------------+
| S********1 | 2020-06-01 14:38:54 |
| C******** | 2020-06-01 14:49:20 |
...
| C******** | 2020-08-07 15:37:20 |
| C******** | 2020-08-07 15:52:44 |
+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> (select order_id, create_time from A ) union (select order_id, create_time from B ) order by create_time limit 10;
+--------------+---------------------+
| order_id | create_time |
+--------------+---------------------+
| S********* | 2020-06-01 14:38:54 |
| C****** | 2020-06-01 14:49:20 |
...
| C****** | 2020-08-07 15:37:20 |
| C****** | 2020-08-07 15:52:44 |
+--------------+---------------------+
10 rows in set (0.02 sec)
PageHelper实际是自动修改mysql语句,添加limi语句以实现分页效果,所以实际输出类似后两个,但我希望的输出是第一个。检查后发现,PageHelper的排序需要使用单独语句,也就是要在PageHelper.startPage后添加一句orderBy,类似下面这种格式(字段名 DESC或ASC):
PageHelper.startPage(vo.getPageNumber(), vo.getPageSize());
PageHelper.orderBy("create_time DESC");