Mybatis Plus 查询注解,复杂的查询业务需求通过QO + Query注解优雅的实现,提升开发效率(源码已开源,文章最后)。
1. 支持多表关联查询;
2. 支持 常用类型:EQ、LK(LIKE)、LLK(LEFT LIKE)、RLK(RIGHT LIKE)、GT、LT、GE、LE、IN、NIN(NOT IN)、ES(EXISTS)、NES(NOT EXISTS)、BT(BETWEEN)、NBT(NOT BETWEEN)、INL(IS NULL)、NNL(NOT NULL) 等等;
3. 支持AND、OR 嵌套;
4. 支持自定义排序;
5. 支持前端控制条件(new 快速开发,解放生产力);
6. 支持后端自定义查询(new);
一、支持注解实现复杂查询逻辑,使用示例:
1.1. 注解查询类 GoodsQO (关键代码)
import com.example.czy.annotation.Query;
import com.example.czy.annotation.QueryOR;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @Author czy
* @Date 2023-07-09
**/
@Data
public class GoodsQO {
/**
* 多表关联查询时可以使用别名 如:g.id
* 默认查询类型为 equal
* id =
*/
@ApiModelProperty(value = "商品ID")
@Query(field = "g.id")
private Long id;
/**
* like可以指定为:left like、like、right like
* name like '1%'
*/
@ApiModelProperty(value = "商品名称")
@Query(type = Query.Type.RIGHT_LIKE)
private String name;
/**
* between 支持集合、数组、逗号分割
* create_Time between '2021-07-09' and '2023-07-09'
*/
@ApiModelProperty(value = "商品创建时间区间逗号分割")
@Query(field = "g.createTime", type = Query.Type.BETWEEN)
private String createTime;
/**
* 多字段模糊匹配,适合一键搜索场景
* (name like '%1%' or description like '%1%')
*/
@ApiModelProperty(value = "商品或描述")
@Query(likes = "name,description")
private String keyword;
/**
* 嵌套查询注解@QueryOR 相当于 and (store_id is not null or goods_id is not null)
*/
@QueryOR
private StoreQO storeQO;
/**
* 排序如:createTime desc,id
* (order by create_time desc,id asc)
*/
@ApiModelProperty(value = "排序,逗号分割")
private String sort;
}
1.2. OR条件类 StoreQO(拓展)
import com.example.czy.annotation.Query;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @Author czy
* @Date 2023-07-09
**/
@Data
public class StoreQO {
@ApiModelProperty(value = "门店ID", hidden = true)
@Query(type = Query.Type.NOT_NULL)
private String storeId;
@ApiModelProperty(value = "商品ID", hidden = true)
@Query(type = Query.Type.NOT_NULL)
private String goodsId;
}
1.3 . Service业务实现类
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.czy.entity.Goods;
import com.example.czy.mapper.GoodsMapper;
import com.example.czy.qo.GoodsQO;
import com.example.czy.qo.base.PageQO;
import com.example.czy.service.GoodsService;
import com.example.czy.util.PageUtil;
import com.example.czy.util.QueryUtils;
import com.example.czy.vo.GoodsVO;
import org.springframework.stereotype.Service;
/**
* @Author czy
* @Date 2023-07-09
**/
@Service
public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods> implements GoodsService {
@Override
public IPage<GoodsVO> pageByQO(PageQO page, GoodsQO qo) {
QueryWrapper queryWrapper = QueryUtils.buildQueryWrapper(qo);
return baseMapper.selectByQO(PageUtil.getPage(page), queryWrapper);
}
}
1.4. Mapper 实现类
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.czy.entity.Goods;
import com.example.czy.vo.GoodsVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
/**
* 商品
*
* @author czy
* @date 2022-02-25
*/
@Mapper
public interface GoodsMapper extends BaseMapper<Goods> {
/**
* 根据QO查询
*
* @param page
* @param wrapper
* @return
*/
@Select("select g.*,gs.store_id from goods g join goods_store gs on g.id = gs.goods_id ${ew.customSqlSegment}")
IPage<GoodsVO> selectByQO(Page page, @Param(Constants.WRAPPER) Wrapper wrapper);
}
1.5. 接口测试
1.6. 最终SQL
SELECT g.*, gs.store_id
FROM goods g JOIN goods_store gs ON g.id = gs.goods_id
WHERE
(
name LIKE '1%'
AND g.create_time BETWEEN '2021-07-011' AND '2023-07-11'
AND ( name LIKE '%1%' OR description LIKE '%1%' )
AND ( store_id IS NOT NULL OR goods_id IS NOT NULL)
)
ORDER BY create_time DESC, id ASC
LIMIT 10
二. 支持前端控制查询条件,使用示例:
public Page<GoodsVO> pageByJson(PageQO page, String json) {
// 前端传过来的json条件
// String json = "[{\"name\":\"lk 张三\"},{\"id\":\"in 1,3,4,5\"}]";
// 设置别名,all表示默认别名,即前端传过来所有字段默认别名为g
Map<String, String> alialsMap = new HashMap();
alialsMap.put("all", "g");
QueryWrapper queryWrapper = QueryUtils.buildQueryWrapperJson(json, alialsMap);
return baseMapper.selectByQO(PageUtil.getPage(page), queryWrapper);
}
// 翻译SQL
// SELECT * FROM goods WHERE (name LIKE '%张三%' AND id IN ('1', '2', '3', '4', '5') )
三. 支持后端自定义控制查询条件,使用实例:
public Page<GoodsVO> pageExample(PageQO page) {
Map<String, String> param = new HashMap<>();
// 创建时间在 2024-06-06 00:00:00 到 2024-06-06 23:49:22之间
param.put("g.createTime", "bt 2024-06-06 00:00:00,2024-06-06 23:49:22");
// 名称包含 张或李或王
param.put("name", "lk 张 or 李 or 王");
// 名称或简介包含 1314
param.put("name or description", "lk 1314");
// id等于 1
param.put("g.id", "1");
QueryWrapper queryWrapper = QueryUtils.buildQueryWrapperJson(param);
return baseMapper.selectByQO(PageUtil.getPage(page), queryWrapper);
}
// 翻译SQL
/** SELECT * from goods g JOIN goods_store gs ON g.id = gs.goods_id
WHERE
(
( name LIKE '%1314%' OR description LIKE '%1314%' )
AND ( name LIKE '%张%' OR name LIKE '%李%' OR name LIKE '%王%' )
AND g.id = '1'
AND g.create_time BETWEEN '2024-06-06 00:00:00' AND '2024-06-06 23:49:22'
)
*/