mybatis分页练手
最近碰到个需求,要做个透明的mybatis分页功能,描述如下:
目标:搜索列表的Controller action要和原先保持一样,并且返回的json需要有分页信息,如:
@ResponseBody @RequestMapping(value="/search", method={RequestMethod.POST}) public List<ProjectInfo> search(@RequestBody SearchProjectCommand command) { List<ProjectInfo> projects=projectFetcher.search(command.getKey(), command.getFrom(), command.getTo()); return projects; }
返回信息:
{ "successful": true, "message": null, "messages": null, "dateTime": 1505651777350, "body": { "totalCount": 2, "totalPage": 1, "records": [ { "projectId": "1111", "projectName": "11111111111111", "title": "11111111111111" }, { "projectId": "22222", "projectName": "222222", "title": "222222" } ] } }
关键点:
- 针对Controller方法的aop
- Mybatis interceptor && PagingContext保存分页信息
- ResponseBodyAdvice(用于在输出json之前加入通用格式)
开始之前,先来看看消息格式,以及某些限制,主要是针对分页pageIndex这种参数的传递:
public abstract class PagingCommand { private int pageSize; private int pageIndex; public PagingCommand getPagingInfo() { return this; } public int getPageSize() { if(pageSize<=0) return Integer.MAX_VALUE; return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public int getPageIndex() { if(pageIndex<0) return 0; return pageIndex; } public void setPageIndex(int pageIndex) { this.pageIndex = pageIndex; } } public class PagingResponse { private int totalCount; private int totalPage; private List<Object> records; public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public List<Object> getRecords() { return records; } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public void setRecords(List<Object> records) { this.records = records; } }
PagingCommand是抽象类,所有的具体Command必须继承这个Command
PagingResponse是分页结果
先来看看横切入口AOP类:
1 @Aspect 2 @Component 3 public class PagingAop { 4 private static final Logger logger = LoggerFactory.getLogger(PagingAop.class); 5 6 @Pointcut("@annotation(org.springframework.web.bind.annotation.RequestMapping)") 7 public void controllerMethodPointcut() { 8 } 9 10 @Around("controllerMethodPointcut()") 11 public Object Interceptor(ProceedingJoinPoint pjp) throws Throwable { 12 13 logger.info("Paging..."); 14 15 //找到是否具有PagingCommand的class作为输入参数 16 //有,则放入PagingContext中 17 for(Object arg:pjp.getArgs()) 18 { 19 if(arg==null) 20 continue; 21 22 logger.info(arg.getClass().toString()); 23 if(PagingCommand.class.isAssignableFrom(arg.getClass())) 24 { 25 logger.info("需要分页行为"); 26 PagingContext.setPagingCommand((PagingCommand)arg); 27 } 28 else 29 { 30 logger.info("不需要分页行为"); 31 } 32 } 33 34 return pjp.proceed(); 35 } 36 }
代码很容易识别,判断参数是否是继承自PagingCommand,只要有1个继承自PagingCommand就会设置相应参数到PagingContext来标识需要分页处理,下面看看这个Context类:
1 public final class PagingContext { 2 private static ThreadLocal<PagingCommand> pagingCommand=new ThreadLocal<PagingCommand>(); 3 private static ThreadLocal<Integer> totalCount=new ThreadLocal<Integer>(); 4 private static ThreadLocal<Integer> totalPage=new ThreadLocal<Integer>(); 5 6 public static void setPagingCommand(PagingCommand cmd) 7 { 8 pagingCommand.set(cmd); 9 } 10 11 public static PagingCommand getPagingCommand() 12 { 13 return pagingCommand.get(); 14 } 15 16 public static boolean isPagingCommandEmpty() 17 { 18 if(pagingCommand.get()==null) 19 return true; 20 21 return false; 22 } 23 24 25 public static int getTotalCount() { 26 return totalCount.get(); 27 } 28 29 public static void setTotalCount(int count) { 30 totalCount.set(count); 31 } 32 33 public static boolean isTotalCountEmpty() 34 { 35 if(totalCount.get()==null) 36 return true; 37 38 return false; 39 } 40 41 42 public static int getTotalPage() { 43 return totalPage.get(); 44 } 45 46 public static void setTotalPage(int pages) { 47 totalPage.set(pages); 48 } 49 }
针对各个线程的ThreadLocal变量,但是目前只支持普通的httprequest线程才能正常工作,ThreadPool的有问题,等以后再解决。
下面是核心的mybatis分页插件了:
1 @Intercepts({@Signature(type=Executor.class,method="query",args={ MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class/*, CacheKey.class, BoundSql.class*/})}) 2 public class PagingInterceptor implements Interceptor { 3 private static final Logger logger = LoggerFactory.getLogger(PagingInterceptor.class); 4 5 @Override 6 public Object intercept(Invocation invocation) throws Throwable { 7 8 9 logger.info("intercept............."); 10 11 //判断是否需要分页行为, from PagingContext中 12 if(PagingContext.isPagingCommandEmpty()) 13 return invocation.proceed(); 14 15 MappedStatement mappedStatement=(MappedStatement)invocation.getArgs()[0]; 16 Object parameter = invocation.getArgs()[1]; 17 BoundSql boundSql = mappedStatement.getBoundSql(parameter); 18 String originalSql = boundSql.getSql().trim(); 19 20 //生成count sql,然后执行 21 int totalCount = getTotalCount(mappedStatement, boundSql, originalSql); 22 //set totalCount value to context 23 PagingContext.setTotalCount(totalCount); 24 25 int totalPages=calculateTotalPagesCount(totalCount, PagingContext.getPagingCommand().getPageSize()); 26 PagingContext.setTotalPage(totalPages); 27 28 //生成分页limit sql,然后执行 29 MappedStatement newMs = wrapPagedMappedStatement(mappedStatement, boundSql, originalSql); 30 invocation.getArgs()[0]= newMs; 31 32 return invocation.proceed(); 33 } 34 35 private int calculateTotalPagesCount(int totalCount, int pageSize) { 36 int pageCount=totalCount/pageSize; 37 38 if(pageCount==0) 39 return 1; 40 41 if(pageCount*pageSize<=totalCount) 42 return pageCount; 43 44 return pageCount+1; 45 } 46 47 private MappedStatement wrapPagedMappedStatement(MappedStatement mappedStatement, BoundSql boundSql, String originalSql) { 48 PagingCommand page= PagingContext.getPagingCommand(); 49 int offset = (page.getPageIndex()) * page.getPageSize(); 50 StringBuffer sb = new StringBuffer(); 51 sb.append(originalSql).append(" limit ").append(offset).append(",").append(page.getPageSize()); 52 BoundSql newBoundSql = MyBatisUtils.copyFromBoundSql(mappedStatement, boundSql, sb.toString()); 53 return MyBatisUtils.copyFromMappedStatement(mappedStatement,new BoundSqlSqlSource(newBoundSql)); 54 } 55 56 private int getTotalCount(MappedStatement mappedStatement, BoundSql boundSql, String originalSql) throws SQLException { 57 Object parameterObject = boundSql.getParameterObject(); 58 String countSql = getCountSql(originalSql); 59 Connection connection=mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection() ; 60 PreparedStatement countStmt = connection.prepareStatement(countSql); 61 BoundSql countBS = MyBatisUtils.copyFromBoundSql(mappedStatement, boundSql, countSql); 62 DefaultParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBS); 63 parameterHandler.setParameters(countStmt); 64 ResultSet rs = countStmt.executeQuery(); 65 int totalCount=0; 66 if (rs.next()) { 67 totalCount = rs.getInt(1); 68 } 69 rs.close(); 70 countStmt.close(); 71 connection.close(); 72 return totalCount; 73 } 74 75 private String getCountSql(String sql) { 76 return "SELECT COUNT(1) FROM (" + sql + ") Mybatis_Pager_TBL_ALIAS"; 77 } 78 79 @Override 80 public Object plugin(Object o) { 81 return Plugin.wrap(o, this); 82 } 83 84 @Override 85 public void setProperties(Properties properties) { 86 87 } 88 }
最后就一步了,就是写一个ResponseBodyAdvice来根据判断是否分页输出,来返回json:
1 @ControllerAdvice 2 public class GlobalMessageResponseBodyAdvice implements ResponseBodyAdvice { 3 4 @Override 5 public boolean supports(MethodParameter methodParameter, Class aClass) { 6 return true; 7 } 8 9 @Override 10 public Object beforeBodyWrite(Object o, MethodParameter methodParameter, MediaType mediaType, Class aClass, ServerHttpRequest serverHttpRequest, ServerHttpResponse serverHttpResponse) { 11 12 Object payload = o; 13 14 //判断是否需要分页 15 if (isNeedPagingResponse()) { 16 PagingResponse response = new PagingResponse(); 17 18 response.setTotalCount(PagingContext.getTotalCount()); 19 response.setTotalPage(PagingContext.getTotalPage()); 20 response.setRecords((List<Object>) payload); 21 22 payload = response; 23 } 24 25 NormalMessage msg = new NormalMessage(); 26 msg.setSuccessful(true); 27 msg.setMessage(null); 28 msg.setBody(payload); 29 return msg; 30 31 } 32 33 public boolean isNeedPagingResponse() { 34 if(PagingContext.isPagingCommandEmpty()) 35 return false; 36 37 return true; 38 } 39 }
完成。
自省推动进步,视野决定未来。
心怀远大理想。
为了家庭幸福而努力。
商业合作请看此处:https://www.magicube.ai
心怀远大理想。
为了家庭幸福而努力。
商业合作请看此处:https://www.magicube.ai