Mybatis简单查询
前言
好记性不如烂笔头，记录内容，方便日后复制。
一、时间区间查询
简单记录两种时间查询的方式：
1.xml中实现
controller 文件
@ApiOperation(value="查询用户的通知列表",notes = "查询用户的通知列表")
@PostMapping("/queryUserNoticeList")
public Result<IPage<NcNoticeDeliverVo>> queryUserNoticeList(
@ApiParam(name = "startTime", value = "开始时间") @RequestParam(required = false) String startTime,
@ApiParam(name = "endTime", value = "结束时间") @RequestParam(required = false) String endTime,
@ApiParam(name = "page", value = "页码") @RequestParam(defaultValue = "0") Integer page,
@ApiParam(name = "size", value = "数量") @RequestParam(defaultValue = "10") Integer size) {
// 开始时间
Date startDate = null;
Date endDate = null;
try {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if(StringUtils.isNotBlank(startTime)){
startDate = simpleDateFormat.parse(startTime);
}
if(StringUtils.isNotBlank(endTime)){
endDate = simpleDateFormat.parse(endTime);
}
} catch (ParseException e) {
throw new ServiceException("时间转换异常！");
}
Page<NcNoticeDeliverVo> queryPage = new Page<>(page,size);
return new Result(CommonCode.SUCCESS, "查询成功！",
ncNoticeDeliverService.queryUserNoticeList(queryPage, startDate, endDate));
}
xml 文件
<if test='startDate != null'>
AND <![CDATA[ DATE_FORMAT(a.CREATED_TIME,'%Y-%m-%d') >= DATE_FORMAT(#{startDate},'%Y-%m-%d')]]>
</if>
<if test='endDate != null'>
AND <![CDATA[ DATE_FORMAT(a.CREATED_TIME,'%Y-%m-%d') <= DATE_FORMAT(#{endDate},'%Y-%m-%d')]]>
</if>
注意：
%Y-%m-%d %H:%i:%s 年月日 时分秒，可根据实际情况进行使用
< <
<= <=
> >
>= >=
& &
' '
" "
2. Mybatis Plus 方式
Controller 文件
@ApiOperation(value="列表(分页,没有筛选条件)",notes = "列表(分页,没有筛选条件)")
@PostMapping("/queryList")
public Result<IPage<DpRecord>> queryList(
@ApiParam(name = "startTime", value = "开始时间") @RequestParam(required = false) String startTime,
@ApiParam(name = "endTime", value = "结束时间") @RequestParam(required = false) String endTime,
@ApiParam(name = "page", value = "页码") @RequestParam(defaultValue = "0") Integer page,
@ApiParam(name = "size", value = "数量") @RequestParam(defaultValue = "10") Integer size) {
// 时间校验
try {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if(StringUtils.isNotBlank(startTime)){
simpleDateFormat.parse(startTime);
}
if(StringUtils.isNotBlank(endTime)){
simpleDateFormat.parse(endTime);
}
} catch (ParseException e) {
throw new ServiceException("时间转换异常！");
}
Page<DpRecord> queryPage = new Page<DpRecord>(page,size);
return new Result(CommonCode.SUCCESS, "查询成功！",
dpRecordService.queryList(queryPage, startTime, endTime));
}
serviceImpl 文件
@Override
public IPage<DpRecord> queryList(Page<DpRecord> iPage, String startTime, String endTime){
// 查询条件拼装
LambdaQueryWrapper<DpRecord> lambdaQueryWrapper = Wrappers.<DpRecord>query().lambda();
if(StringUtils.isNotBlank(startTime)){
lambdaQueryWrapper.apply("date_format ("+DpRecord.START_TIME+",'%Y-%m-%d') >= date_format('" + startTime + "','%Y-%m-%d')");
}
if(StringUtils.isNotBlank(endTime)){
lambdaQueryWrapper.apply("date_format ("+DpRecord.END_TIME+",'%Y-%m-%d') <= date_format('" + endTime + "','%Y-%m-%d')");
}
lambdaQueryWrapper.orderByDesc(DpRecord::getStartTime);
return this.page(iPage, lambdaQueryWrapper);
}
model文件
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("dp_record")
@ApiModel(value="DpRecord对象", description="数据处理记录 ")
public class DpRecord implements Serializable {
@TableField(exist = false)
public static final String START_TIME = "START_TIME";
@TableField(exist = false)
public static final String END_TIME = "END_TIME";
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "ID")
@TableId(value = "ID", type = IdType.ASSIGN_ID)
private String id;
}
二、模糊查询
简单记录两种模糊查询的方式：
1.xml中实现
Controller 文件
@ApiOperation(value="列表(分页,没有筛选条件)",notes = "列表(分页,没有筛选条件)")
@PostMapping("/queryList")
public Result<IPage<DpProcess>> queryList(
@ApiParam(name = "searchParam", value = "模糊搜索条件：支持名称、代码、影响集合等条件的查询") @RequestParam(required = false) String searchParam,
@ApiParam(value = "页码",name = "page") @RequestParam(defaultValue = "0") Integer page,
@ApiParam(value = "数量",name = "size") @RequestParam(defaultValue = "10") Integer size) {
Page<DpProcess> iPage = new Page<>(page, size);
return new Result(CommonCode.SUCCESS, "查询成功！", dpProcessService.queryList(iPage, enabled, searchParam, category));
}
xml 文件
<if test = 'searchParam != null and searchParam != ""'>
and CONCAT_WS('|',a.PY_CODE, '|',a.WB_CODE, '|',a.THIRD_CODE, '|',a.ROLE_NAME ) LIKE CONCAT('%',#{searchParam},'%')
</if>
2. Mybatis Plus 方式
Controller 文件
@ApiOperation(value="列表(分页,没有筛选条件)",notes = "列表(分页,没有筛选条件)")
@PostMapping("/queryList")
public Result<IPage<DpProcess>> queryList(
@ApiParam(name = "searchParam", value = "模糊搜索条件：支持名称、代码、影响集合等条件的查询") @RequestParam(required = false) String searchParam,
@ApiParam(value = "页码",name = "page") @RequestParam(defaultValue = "0") Integer page,
@ApiParam(value = "数量",name = "size") @RequestParam(defaultValue = "10") Integer size) {
Page<DpProcess> iPage = new Page<>(page, size);
return new Result(CommonCode.SUCCESS, "查询成功！", dpProcessService.queryList(iPage, enabled, searchParam, category));
}
serviceImpl 文件
@Override
public IPage<DpProcess> queryList(Page<DpProcess> iPage, String searchParam){
// 查询条件进行封装
LambdaQueryWrapper<DpProcess> lambdaQueryWrapper = new LambdaQueryWrapper<>();
if(StringUtils.isNotBlank(searchParam)){
lambdaQueryWrapper.and(i -> i.like(DpProcess::getName, searchParam)
.or().like(DpProcess:: getCode, searchParam)
.or().like(DpProcess::getOutCols, searchParam));
}
return this.page(iPage, lambdaQueryWrapper);
}
