开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 2 天，点击查看活动详情
每日英语：
The past cannot be cured.
过去是无法疗愈的。 -伊丽莎白一世
复杂查询实现
DruidSQL主要用于对Apache Druid实现查询操作，所以我们把各种可能用到的查询都实现一遍，比如查询所有、查询总记录数、分页查询、条件查询、排序等。
1 查询所有
查询所有其实只需要执行select * from msitemslog就可以了。
修改com.xz.mall.dw.controller.HotGoodsController添加查询所有方法：
@Autowired
private HotGoodsService hotGoodsService;
/***
* 集合查询
* @return
*/
@GetMapping
public RespResult<List<HotGoods>> list(){
//集合查询
List<HotGoods> goods = hotGoodsService.list();
return RespResult.ok(goods);
}
2 查询前N条
查询前N条记录使用limit关键词，但limit关键词并不能单独实现分页。
1.Dao
修改com.xz.mall.dw.mapper.HotGoodsMapper
/***
* 查询前N条记录
* @param size
* @return
*/
@Select("select uri,__time as accesstime,ip from msitemslog limit #{size}")
List<HotGoods> topNum(Integer size);
2.Service
接口：修改com.xz.mall.dw.service.HotGoodsService添加查询前N条记录方法
List<HotGoods> topNum(Integer size);
实现类：修改com.xz.mall.dw.service.impl.HotGoodsServiceImpl实现接口方法
/***
* 查询前N条记录
* @param size
* @return
*/
@Override
public List<HotGoods> topNum(Integer size) {
return hotGoodsMapper.topNum(size);
}
3.Controller
修改com.xz.mall.dw.controller.HotGoodsController调用查询方法
/***
* 查询前N条记录
* @return
*/
@GetMapping("/top/{size}")
public RespResult<List<HotGoods>> topNum(@PathVariable(value = "size")Integer size){
//集合查询前N条
List<HotGoods> hotGoods = hotGoodsService.topNum(size);
return RespResult.ok(hotGoods);
}
3 分页查询
Druid分页查询需要用到offset实现，offset表示查询偏移量，说白了就是从指定下标数据查询，结合limit可以实现分页功能。
1)分页对象
创建com.xz.mall.dw.util.DruidPage，在该类中实现分页offset计算和分页基本功能：
@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class DruidPage<T> {
//中记录数
private Integer total;
//每页显示条数
private Integer size;
//当前页
private Integer page;
//偏移量
private Long offset;
//总页数
private Integer totalPages;
//数据集合
private T data;
//排序字段
private String sort;
//排序类型
private String sortType;
/***
* 初始化并计算偏移量
* @param sort
* @param size
* @param sortType
* @param page
*/
public DruidPage(Integer page,Integer size,String sort,String sortType) {
this.size = size;
this.page = page;
this.sort = sort;
this.sortType = sortType;
if(page<=0){
this.page = 1;
}
//计算偏移量
this.offset =Long.valueOf((this.page-1)*size);
}
/***
* 初始化并计算偏移量
* @param size
* @param page
*/
public DruidPage(Integer page,Integer size) {
this.size = size;
this.page = page;
if(page<=0){
this.page = 1;
}
//计算偏移量
this.offset =Long.valueOf((this.page-1)*size);
}
/***
* 计算分页参数
*/
public DruidPage<T> pages(T data,Integer total){
//总记录数
this.total = total;
//数据
this.data = data;
//总页数
if(this.total>0){
this.totalPages = this.total%this.size==0? this.total/this.size : (this.total/this.size)+1;
}else{
this.totalPages = 0;
}
return this;
}
}
2)Dao
修改com.xz.mall.dw.mapper.HotGoodsMapper添加分页查询方法，代码如下：
/**
* 分页查询
* @return
*/
@Select("select uri,__time as accesstime,ip from msitemslog limit #{size} offset #{offset}")
List<HotGoods> pageList(DruidPage druidPage);
3)Service
接口：修改com.xz.mall.dw.service.HotGoodsService添加分页查询方法
DruidPage<List<HotGoods>> pageList(Integer page, Integer size);
实现类：修改com.xz.mall.dw.service.impl.HotGoodsServiceImpl实现接口方法
//分页查询
@Override
public DruidPage<List<HotGoods>> pageList(Integer page, Integer size) {
//创建分页
DruidPage<List<HotGoods>> pageInfo = new DruidPage<List<HotGoods>>(page,size);
//总记录数查询
Integer total = hotGoodsMapper.selectCount(null);
//集合查询
List<HotGoods> hotGoods = hotGoodsMapper.pageList(pageInfo);
return pageInfo.pages(hotGoods,total);
}
4)Controller
修改com.xz.mall.dw.controller.HotGoodsController调用分页查询
/***
* 分页查询
* @return
*/
@GetMapping("/{page}/{size}")
public RespResult<DruidPage<List<HotGoods>>> page(@PathVariable(value = "page")Integer page,
@PathVariable(value = "size")Integer size){
//集合查询
DruidPage<List<HotGoods>> pageInfo = hotGoodsService.pageList(page,size);
return RespResult.ok(pageInfo);
}
4 分页+排序
排序的时候，我们会选择指定列和排序方式，因此需要传递2个参数过来，但是执行的SQL语句需要将他们拼接进去而不是作为一个变量，因此在MyBatis中可以考虑使用${}拼接，使用#{}传递变量。
1)Dao
修改com.xz.mall.dw.mapper.HotGoodsMapper添加排序分页方法
/***
* 排序+分页
* @param pageInfo
* @return
*/
@Select("select uri,__time as accesstime,ip from msitemslog order by ${sort} ${sortType} limit #{size} offset #{offset}")
List<HotGoods> pageListSort(DruidPage<List<HotGoods>> pageInfo);
2)Service
接口：修改com.xz.mall.dw.service.HotGoodsService添加分页排序方法：
DruidPage<List<HotGoods>> pageListSort(Integer page, Integer size, String sort, String sortType);
实现类：修改com.xz.mall.dw.service.impl.HotGoodsServiceImpl添加实现方法
/***
* 分页+排序
* @param page
* @param size
* @param sort
* @param sortType
* @return
*/
@Override
public DruidPage<List<HotGoods>> pageListSort(Integer page, Integer size, String sort, String sortType) {
//创建分页
DruidPage<List<HotGoods>> pageInfo = new DruidPage<List<HotGoods>>(page,size,sort,sortType);
//总记录数查询
Integer total = hotGoodsMapper.selectCount(null);
//集合查询
List<HotGoods> hotGoods = hotGoodsMapper.pageListSort(pageInfo);
return pageInfo.pages(hotGoods,total);
}
3)Controller
修改com.xz.mall.dw.controller.HotGoodsController添加排序查询方法
/***
* 分页排序查询
* @return
*/
@GetMapping("/{page}/{size}/{sort}/{type}")
public RespResult<DruidPage<List<HotGoods>>> page(@PathVariable(value = "page")Integer page,
@PathVariable(value = "size")Integer size,
@PathVariable(value = "sort")String sort,
@PathVariable(value = "type")String sortType){
//集合查询
DruidPage<List<HotGoods>> pageInfo = hotGoodsService.pageListSort(page,size,sort,sortType);
return RespResult.ok(pageInfo);
}
5 时间查询
我们查询1小时前或者1天前的数据，__time时间格式是TIMESTAMP，SQL中可以使用TIMESTAMP指定时间格式，比如我们要查询1小时前的数据，只需要计算出1小时前的时间，然后使用TIMESTAMP即可，例如：TIMESTAMP 2022-2-3 23:00:00。
1)时间工具类
我们首先创建一个时间工具类com.xz.mall.dw.util.TimeUtil，用时间工具类实现时间格式化：
public class TimeUtil {
public static final String format1 = "yyyy-MM-dd HH:mm:ss";
public static final String format2 = "yyyy-MM-dd";
public static final String format3 = "yyyy年MM月dd日 HH时mm分ss秒";
public static final String format4 = "yyyy年MM月dd日";
public static final String unit_hour = "hour";
public static final String unit_day = "day";
/***
* 当前时间增加N Unit
*/
public static String beforeTime(String unit,Integer num){
//1小时为单位
long times = 3600000;
if(unit.equalsIgnoreCase(unit_hour)){
times=times*num;
}else if(unit.equalsIgnoreCase(unit_day)){
times=times*24*num;
}
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format1);
return simpleDateFormat.format( new Date(System.currentTimeMillis()-times));
}
}
2)Dao
修改com.xz.mall.dw.mapper.HotGoodsMapper添加时间查询方法：
/***
* 搜索数据
* @param size
* @param time
* @return
*/
@Select("select uri,__time as accesstime,ip from msitemslog where __time>=TIMESTAMP '${time}' limit #{size}")
List<HotGoods> search(@Param("size") Integer size,@Param("time") String time);
3)Service
接口：修改com.xz.mall.dw.service.HotGoodsService添加时间查询方法
List<HotGoods> search(Integer size, Integer hour);
实现类：修改com.xz.mall.dw.service.impl.HotGoodsServiceImpl实现接口方法
/***
* 查询历史数据
* @param size
* @param hour
* @return
*/
@Override
public List<HotGoods> search(Integer size, Integer hour) {
return hotGoodsMapper.search(size, TimeUtil.beforeTime(TimeUtil.unit_hour,hour));
}
4)Controller
修改com.xz.mall.dw.controller.HotGoodsController实现时间查询调用
/****
* 指定时间的历史数据查询
*/
@GetMapping("/search/{size}/{hour}")
public RespResult<List<HotGoods>> history(@PathVariable(value = "size")Integer size,
@PathVariable(value = "hour")Integer hour){
//集合查询前N条
List<HotGoods> hotGoods = hotGoodsService.search(size,hour);
return RespResult.ok(hotGoods);
}
6 NOT IN
我们如果想排除某些数据可以使用not in，比如我们想排除部分uri就可以这么做。
1)Dao
修改com.xz.mall.dw.mapper.HotGoodsMapper添加排除部分数据方法
/***
* 数据搜索
* @param size
* @param time
* @param urls
* @return
*/
@Select("select uri,__time as accesstime,ip from msitemslog where __time>=TIMESTAMP '${time}' and uri not in('${urls}') limit #{size}")
List<HotGoods> searchExclude(@Param("size") Integer size,@Param("time") String time,@Param("urls")String urls);
2)Service
接口：修改com.xz.mall.dw.service.HotGoodsService添加排除部分数据方法：
List<HotGoods> search(Integer size, Integer hour, String[] ids);
实现类：修改com.xz.mall.dw.service.impl.HotGoodsServiceImpl实现接口方法
/***
* 排除指定uri数据
* @param size
* @param hour
* @param urls
* @return
*/
@Override
public List<HotGoods> search(Integer size, Integer hour, String[] urls) {
String urlsJoin = StringUtils.join(urls, "','");
return hotGoodsMapper.searchExclude(size, TimeUtil.beforeTime(TimeUtil.unit_hour,hour),urlsJoin);
}
3)Controller
修改com.xz.mall.dw.controller.HotGoodsController添加方法调用
/****
* 指定时间的历史数据查询,排除指定数据
*/
@PostMapping("/search/{size}/{hour}")
public RespResult<List<HotGoods>> history(@PathVariable(value = "size")Integer size,
@PathVariable(value = "hour")Integer hour,@RequestBody String[] ids){
//集合查询前N条
List<HotGoods> hotGoods = hotGoodsService.search(size,hour,ids);
return RespResult.ok(hotGoods);
}
总结
本篇主要讲述了一下MyBatis查询Apache Druid实战，实战了查询所有、查询前N条、分页查询、分页+排序、时间查询、NOT IN 这六种情况。
