SUM、AVG、COUNT
sum、avg、count加一起试试吧。
定义返回体
import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; /** @Author huyi @Date 2021/10/26 14:50 @Description: 聚合平均总和返回体 */ @Data @AllArgsConstructor @NoArgsConstructor @Builder public class AggregateAgeSumAvgAndCountRsp { private String name; private Integer sum; private Integer avg; private Integer count; }
方法实现
@Override public List<AggregateAgeSumAvgAndCountRsp> getAgeSumAvgCountByName() { List<Map<String, Object>> result = testFluentMybatisMapper.listMaps( new TestFluentMybatisQuery() .select .name() .sum .age("sum") .avg .age("avg") .count("count") .end() .groupBy .name() .end()); if (result != null && result.size() != 0) { List<AggregateAgeSumAvgAndCountRsp> list = new ArrayList<>(); result.forEach( x -> list.add( BeanUtil.fillBeanWithMapIgnoreCase( x, new AggregateAgeSumAvgAndCountRsp(), false))); return list; } else { return null; } }
控制层代码
@ApiOperation(value = "根据年龄分组并获取年龄和、平均年龄、数量", notes = "根据年龄分组并获取年龄和、平均年龄、数量") @RequestMapping(value = "/getAgeSumAvgCountByName", method = RequestMethod.GET) @ResponseBody public Result<List<AggregateAgeSumAvgAndCountRsp>> getAgeSumAvgCountByName() { try { return Result.ok(aggregateService.getAgeSumAvgCountByName()); } catch (Exception exception) { return Result.error(ErrorCode.BASE_ERROR_CODE.getCode(), exception.getMessage(), null); } }
调试代码
OK，完美。
apply方法使用
官方提供了显示自由指定字段.apply语法功能。我们测试一下好不好用。
返回体定义
import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import java.util.Date; /** @Author huyi @Date 2021/10/26 15:10 @Description: 聚合应用返回体 */ @Data @AllArgsConstructor @NoArgsConstructor @Builder public class AggregateApplyRsp { private String name; private Date createTime; private Integer minAge; private Date maxTime; }
方法实现
@Override public List<AggregateApplyRsp> getApply() { List<Map<String, Object>> result = testFluentMybatisMapper.listMaps( new TestFluentMybatisQuery() .select .apply("name") .createTime("createTime") .apply("min(age) as minAge", "max(create_time) as maxTime") .end() .groupBy .name() .createTime() .end()); if (result != null && result.size() != 0) { List<AggregateApplyRsp> list = new ArrayList<>(); result.forEach( x -> list.add(BeanUtil.fillBeanWithMapIgnoreCase(x, new AggregateApplyRsp(), false))); return list; } else { return null; } }
控制层代码
@ApiOperation(value = "根据名字获取最小年龄，使用语句", notes = "根据名字获取最小年龄，使用语句") @RequestMapping(value = "/getApply", method = RequestMethod.GET) @ResponseBody public Result<List<AggregateApplyRsp>> getApply() { try { return Result.ok(aggregateService.getApply()); } catch (Exception exception) { return Result.error(ErrorCode.BASE_ERROR_CODE.getCode(), exception.getMessage(), null); } }
调试代码
OK，完美。
总结
在调试完聚合查询代码后给我直观感受是，这和写sql没啥区别，十分好理解。只是需要掌握fm的select语法，特别是end方法的理解，可以追一下源码看一下具体实现过程。
