import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 可分页标识
* <p>
* 详情请参照 - PageableAop
*/
@SuppressWarnings("all")
@Target({ElementType.METHOD, ElementType.ANNOTATION_TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface Pageable {
/**
* 自动触发分页 - 默认自动使用AOP进行分页
* <p>
* false表示不自动触发,
* 即aop跳过分页逻辑及导出逻辑,并将相关分页参数存入线程变量中,
* 同时,需要使用者显示调用AOP方法中的分页方法,在该方法中进行分页
* <p>
* 由于在复杂分页查询中,首次查询,一般不是目标查询,所以需要一种可手动调用的方式触发分页
*/
boolean autoTrigger() default true;
/**
* 排序类型
* <p>
* asc 正序
* <p>
* desc 倒序
*/
String sortType() default "asc";
/**
* 排序字段 - 可使用驼峰命名
*/
String sortBy() default "id";
/**
* 可导出标识,则需要显式指定导出的实体类是什么
*/
Excel exportable() default @Excel(Object.class);
/**
* Excel导出注解
*/
@interface Excel {
/**
* 导出表实体类型
*/
Class<?> value();
/**
* 设置文件名称
*/
String file() default "file";
/**
* 设置标题,空则不设置
*/
String title() default "";
/**
* 设置sheet名称
*/
String sheet() default "";
/**
* 导出表实体类型
*/
ExcelType excelType() default ExcelType.XSSF;
/**
* 是否需要表头,默认需要
*/
boolean needHeader() default true;
/**
* 列宽自适应
*/
boolean autoSize() default true;
}
}
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.hutool.core.lang.Pair;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageInfo;
import com.github.pagehelper.dialect.AbstractHelperDialect;
import com.ikas.ai.consts.Consts;
import com.ikas.ai.handle.JsonResult;
import com.ikas.ai.utils.NumUtil;
import com.ikas.ai.utils.PageUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Collections;
import java.util.List;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.stream.Collectors;
@Component
@Aspect
@Slf4j
public class PageableAop {
@Resource
private HttpServletRequest request;
@Resource
private HttpServletResponse response;
private static final ThreadLocal<Pair<HttpServletRequest, Pageable>> LOCAL = ThreadLocal.withInitial(() -> null);
/**
* 一次web请求中,手动显式调用分页方法
*
* @param result 需要分页的函数
* @return java.util.List<R>
* @author ly
*/
public static <T> List<T> page(@NotNull Supplier<List<T>> result) {
// 进行分页操作
Pair<HttpServletRequest, Pageable> pair = LOCAL.get();
if (pair == null || pair.getKey() == null || pair.getValue() == null) {
log.warn("Page aop method trigger is need page and size and @Pageable info, but it is empty");
return result.get();
}
HttpServletRequest rq = pair.getKey();
Pageable pageable = pair.getValue();
int page = getPageNumber(rq);
int size = getPageSize(rq);
configPageInfo(rq, pageable, page, size);
return result.get();
}
/**
* 一次web请求中,手动显式调用分页方法 - 可映射结果集
*
* @param result 需要分页的函数
* @return java.util.List<R>
* @author ly
*/
public static <T, R> List<R> page(@NotNull Supplier<List<T>> result, @NotNull Function<T, R> mapping) {
// 进行分页操作
List<T> list = page(result);
if (!(list instanceof Page)) {
return list.stream().map(mapping).collect(Collectors.toList());
}
Page<R> page = new Page<>();
list.forEach(t -> page.add(mapping.apply(t)));
Page<T> p = (Page<T>) list;
page.setTotal(p.getTotal());
page.setPageNum(p.getPageNum());
page.setPageSize(p.getPageSize());
page.setPages(p.getPages());
page.setStartRow(p.getStartRow());
page.setEndRow(p.getEndRow());
return page;
}
/**
* 定义切入点 - 可分页注解
*/
@Pointcut("@annotation(com.ikas.ai.server.aop.Pageable)")
public void pageable() {
}
/**
* 分页操作 -- 注意: 此处只支持get请求,post请求拿不到参数值,后续需要再改进下,支持post请求
*/
@Around("pageable()")
public Object doAround(ProceedingJoinPoint pjp) throws Throwable {
if (request == null) {
log.warn("Pageable error, because it's not a web side request");
return pjp.proceed();
}
// if it's export
String export = request.getParameter("export");
boolean isExported = StringUtils.isNotBlank(export);
// try to get page and size
int page = getPageNumber(request);
int size = getPageSize(request);
// get @Pageable
MethodSignature signature = ((MethodSignature) pjp.getSignature());
Pageable pageable = signature.getMethod().getAnnotation(Pageable.class);
// if it's not need auto trigger
if (!pageable.autoTrigger()) {
// set local val
LOCAL.set(Pair.of(request, pageable));
// to proceed
try {
return wrapResult(pjp.proceed(), page, false, pageable);
} finally {
LOCAL.remove();
}
}
if (isExported && pageable.exportable().value() == Object.class) {
isExported = false;
log.warn("Export error, @Pageable.Excel no setting");
}
// max limit is 10000
if (isExported) {
page = 1;
size = 10000;
}
// setting page info
configPageInfo(request, pageable, page, size);
// try to wrap
return wrapResult(pjp.proceed(), page, isExported, pageable);
}
/**
* 包装结果集
*
* @param result 结果集
* @param page 页码
* @param isExported 是否导出
* @param pageable 分页注解
* @return java.lang.Object
* @author ly
*/
private Object wrapResult(Object result, int page, boolean isExported, Pageable pageable) {
if (!(result instanceof JsonResult) || !(((JsonResult) result).getResult().get(JsonResult.TAG_DATA) instanceof List)) {
log.warn("Pageable warn, because it's not JsonResult or result is not instance of List");
if (isExported) {
log.warn("Export warn, because it's not JsonResult or result is not instance of List, it can't exportable");
return null;
}
return result;
}
@SuppressWarnings("unchecked")
List<Object> list = (List<Object>) ((JsonResult) result).getResult().get(JsonResult.TAG_DATA);
// export
if (isExported) {
tryExport(list, pageable.exportable());
return null;
}
// check page info
PageInfo<Object> info = new PageInfo<>(list);
if (isRightPage(info, page)) {
return JsonResult.ok(info);
}
// 此处仅对list/page/size/total进行数据正确 - 不保证其他PageInfo中的数据正确
info.setList(Collections.emptyList());
info.setPageNum(page);
return JsonResult.ok(info);
}
/**
* 根据请求参数获取页码
*
* @param request 请求参数
* @return int
* @author ly
*/
private static int getPageNumber(HttpServletRequest request) {
String pageStr = request.getParameter(Consts.PAGE);
if (!NumUtil.isInteger(pageStr)) {
pageStr = request.getParameter("pageNum");
}
if (!NumUtil.isInteger(pageStr)) {
pageStr = request.getParameter("pageNumber");
}
if (!NumUtil.isInteger(pageStr)) {
pageStr = "1";
}
return Integer.parseInt(pageStr);
}
/**
* 根据请求参数获取条数
*
* @param request 请求参数
* @return int
* @author ly
*/
private static int getPageSize(HttpServletRequest request) {
String sizeStr = request.getParameter(Consts.SIZE);
if (!NumUtil.isInteger(sizeStr)) {
sizeStr = request.getParameter("pageSize");
}
if (!NumUtil.isInteger(sizeStr)) {
sizeStr = "10";
}
return Integer.parseInt(sizeStr);
}
/**
* check page info
* <p>
* 由于框架本身会在查询total后会放入Page中
* <p>
* 但,page中的setTotal方法会对原有的pages进行修改
* <p>
* 导致下一步结果集查询会未按预期进行
* <p>
* 如:查询只有4条结果集的表,1页分10条查询
* <p>
* 预期:第一页返回数据,第二页不返回数据
* <p>
* 实际:第一页返回数据,第二页返回数据同时Page对象的PageNumber被修改成1
* <p>
* 所以需要手动检查分页数据
*
* @return boolean
* @author ly
* @see AbstractHelperDialect#afterCount(long, Object, org.apache.ibatis.session.RowBounds)
* @see Page#setTotal(long)
*/
private boolean isRightPage(PageInfo<Object> page, int pageNum) {
// 判断原分页参数是否被修改
return page.getPageNum() == pageNum;
}
/**
* 设置分页属性
*
* @param pageable 分页设置
* @param page 页码
* @param size 条数
* @author ly
*/
private static void configPageInfo(HttpServletRequest request, Pageable pageable, Integer page, Integer size) {
String sortType = StringUtils.defaultIfBlank(request.getParameter("sortType"), pageable.sortType());
String sortBy = StringUtils.defaultIfBlank(request.getParameter("sortBy"), pageable.sortBy());
// setting page helper
PageUtil.configPageHelper(page, size, sortBy, sortType, pageable.sortBy(), pageable.sortType());
}
/**
* 尝试导出文件
*
* @param list 结果集
* @param export 导出设置
* @author ly
*/
private void tryExport(List<Object> list, Pageable.Excel export) {
// setting params
ExportParams params = new ExportParams();
params.setType(export.excelType());
//params.setAutoSize(export.autoSize());
if (StringUtils.isNotBlank(export.sheet())) {
params.setSheetName(export.sheet());
}
if (StringUtils.isNotBlank(export.title())) {
params.setTitle(export.title());
}
if (!export.needHeader()) {
params.setCreateHeadRows(false);
}
// write stream
writeExcel(export.file(), () -> ExcelExportUtil.exportExcel(params, export.value(), list));
}
/**
* 导出文件
*
* @param file 文件名称
* @param excelGetter excel获取器
* @author ly
*/
private void writeExcel(String file, Supplier<Workbook> excelGetter) {
String fileName = file;
try {
fileName = URLEncoder.encode(file + "." + "xlsx", StandardCharsets.UTF_8.displayName());
} catch (Exception e) {
log.warn("Export error, file name can't encode", e);
}
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
Workbook excel = excelGetter.get();
// write data
try (OutputStream out = response.getOutputStream()) {
excel.write(out);
excel.close();
} catch (IOException e) {
log.warn("Export error, can not write data to out put stream", e);
}
}
}
@Pageable(sortType = "desc", sortBy = "generate_time")
@GetMapping("/getMeteByEquipmentOrSystem")
public JsonResult getMeteByEquipmentOrSystem(
String machineNo,
String equipmentCode,
Integer meteType,
PageInfo pageInfo
) {
List<DorisYxData> list = cockpitService.page(machineNo, equipmentCode, meteType);
return JsonResult.ok(list);
}
public List<MeteDataCockpitDTO> getMeteByEquipmentOrSystem(String machineNo, String equipmentCode, Integer meteType) {
List<DorisYxData> newestYxList = dorisYxDataDao.getYxDataInMaxReportTimeByWindow(machineNo,equipmentCode);
List<MeteDataCockpitDTO> resultCollect = newestYxList.stream()
.map(data -> {
MeteDataCockpitDTO meteDataCockpitDTO = new MeteDataCockpitDTO();
meteDataCockpitDTO.setMeteName(data.getMeteName());
meteDataCockpitDTO.setMeteType(MeteTypeEnum.YX.getNum());
meteDataCockpitDTO.setMeteCode(data.getMeteCode());
meteDataCockpitDTO.setMeteId(data.getMeteId());
meteDataCockpitDTO.setReportTime(data.getReportTime());
);
return meteDataCockpitDTO;
})
.collect(Collectors.toList());
return resultCollect;
}
再用组件的时候发现返回的数据始终有问题,引起的思考。
list无值,则total数值不对,而真实数据库是有数据的。
因此对PageHelper和Mybatis的关联源码进行了分析,总结出在使用PageHelper的几个注意点。
1、PageHelper.startPage(pageNum, pageSize);,底层逻辑是用
ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal()
2、PageHelper提供Page拦截器针对SQL做包装。
3、Page拦截器只会拦截第一个SQL执行的方法。
4、所以这里需要封装一层
PageInfo<DorisYxData> pageInfo=new PageInfo<>(newestYxList);
Page<DorisYxData> page=new Page<>();
page.setTotal(pageInfo.getTotal());
page.setPageNum(pageInfo.getPageNum());
page.setPageSize(pageInfo.getPageSize());
page.addAll(list);
return page;