springboot mybatisplus 动态查询

一、说明

最近做的项目中用到了mp，以前以为只能单表查呢，现在发现多表也可以，下面使用注解型sql做示例说明

二、单表推荐使用LambdaQueryWrapper，不用写sql

  LambdaQueryWrapper<Menu> queryWrapper = new LambdaQueryWrapper<>();
        if (ObjectUtil.isNotNull(dto)) {
            //模糊查询
            if (ObjectUtil.isNotEmpty(dto.getCode())) {
                queryWrapper.like(Menu::getCode, dto.getCode());
            }
        }
        Page<Menu> page = new Page<>(dto.getPageNum(),dto.getPageSize());
        return this.page(page,queryWrapper);

二、多表使用QueryWrapper

 Page<UserBean> page = new Page<>(dto.getPageNum(),dto.getPageSize());
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        if (ObjectUtil.isNotNull(dto)) {
            //模糊查询
            if (ObjectUtil.isNotEmpty(dto.getAccount())) {
                queryWrapper.like("a.account", dto.getAccount());
            }
            if (ObjectUtil.isNotEmpty(dto.getUserName())) {
                queryWrapper.like("a.user_name", dto.getUserName());
            }
            if(ObjectUtil.isNotEmpty(dto.getCreateTime())){
                queryWrapper.like("a.create_time", dto.getCreateTime());
            }
            if(ObjectUtil.isNotEmpty(dto.getStartTime())){
                queryWrapper.gt("a.create_time", dto.getCreateTime());
            }
            if(ObjectUtil.isNotEmpty(dto.getEndTime())){
                queryWrapper.lt("a.create_time", dto.getEndTime());
            }
        }
        return baseMapper.getUserPage(page,queryWrapper);

mapper

 @Select("select a.id,a.account,a.create_by,a.create_time,a.department_id,a.status," +
            "a.modify_by,a.modify_time,a.phone_num,a.user_name,a.phone_num,b.user_name createUserName " +
            "from sys_user_info a left join sys_user_info b on a.create_by = b.id ${ew.customSqlSegment} " )
    IPage<UserBean> getUserPage(Page<UserBean> page, @Param(Constants.WRAPPER) QueryWrapper<UserBean> orgWrapper);

-end-