项目时原来写的逻辑分页(及在代码里对数据进行分页,但是存在一个问题,当数据量大时会导致卡死,这种情况使用物理分页(及在sql数据库中进行物理分页))
拼装page (Current Size PageSize Total Records)
int start = PageUtil.getStart(meterRecordsHoursDTO.getCurrentPage() - 1, meterRecordsHoursDTO.getPageSize());
Integer count = meterRecordsHoursMapper.pageListGroupByHourCount(spaceCode, meterRecordsHoursDTO.getMacs(), meterRecordsHoursDTO.getStartDate(), meterRecordsHoursDTO.getEndDate(), SessionUtil.getCorpId(),
start, meterRecordsHoursDTO.getPageSize());
select count(1) from (
select id
from meter_records_hours mrh
<where>
<if test="null != spaceCode and spaceCode != ''">
mrh.space_code=#{spaceCode}
</if>
<if test="null != macs and macs.size > 0">
and mrh.mac in
<foreach collection="macs" open="(" separator="," close=")" item="mac" index="i">
#{mac}
</foreach>
</if>
and mrh.record_date between #{startDate} and #{endDate}
and mrh.corpid =#{corpid}
</where>
group by record_date,mac
) a
List<MeterRecordsHoursVO> meterRecordsHoursVOS = meterRecordsHoursMapper.pageListGroupByHour(spaceCode, meterRecordsHoursDTO.getMacs(), meterRecordsHoursDTO.getStartDate(), meterRecordsHoursDTO.getEndDate(), SessionUtil.getCorpId(),
start, meterRecordsHoursDTO.getPageSize());
IPage<MeterRecordsHoursVO> page = new Page<>();
page = page.setCurrent(start + 1).setSize(meterRecordsHoursDTO.getPageSize()).setTotal(count).setRecords(list);
return PageInfoHelperUtils.convert(page);
public static <T> LitePageInfo<T> convert(IPage<T> iPage) {
if (iPage == null) {
return null;
} else {
List<T> records = iPage.getRecords();
return LitePageInfo.<T>builder()
.currentPage(iPage.getCurrent())
.pageSize(iPage.getSize())
.size(CollectionUtil.isEmpty(records) ? 0L : records.size())
.totalCount(iPage.getTotal())
.pages(iPage.getPages())
.data(records)
.build();
}
在物理分页之后 ;导出该表格的话是不能分页的 ;从新写该接口
解决方法就是在传currentpage pageSize 页数的时候传null
//按小时查询
List<MeterRecordsHoursVO> meterRecordsHoursVOS = meterRecordsHoursMapper.pageListGroupByHour(spaceCode, meterRecordsHoursDTO.getMacs(),
meterRecordsHoursDTO.getStartDate(), meterRecordsHoursDTO.getEndDate(), SessionUtil.getCorpId(),
null, null);
select mrh.id,
mrh.space_code as spaceCode,
mrh.mac as mac,
sum(mrh.use_energy) as useEnergy,
mrh.record_date as recordDate,
date_format(record_date, '%Y-%m-%d %H:%i') recordDateStr
from meter_records_hours mrh
<where>
<if test="null != spaceCode and spaceCode != ''">
mrh.space_code=#{spaceCode}
</if>
<if test="null != macs and macs.size > 0">
and mrh.mac in
<foreach collection="macs" open="(" separator="," close=")" item="mac" index="i">
#{mac}
</foreach>
</if>
and mrh.record_date between #{startDate} and #{endDate}
and mrh.corpid =#{corpid}
</where>
group by record_date,mac
order by useEnergy desc
<if test="start != null and pageSize != null">
limit #{start},#{pageSize}
</if>