Mybatis簡單查詢

Fire♪一度發表於2021-09-15
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   年月日 時分秒，可根據實際情況進行使用

<	&lt;
<=	&lt;=
>	&gt;
>=	&gt;=
&	&amp;
'	&apos;
"	&quot;

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);
}

相關文章