Mybatis-plus构造条件器wapper结合sql

1.service

public JsonPageResult<UserQueryVO> queryByPage(UserDTO userDTO){
        LambdaQueryWrapper<User> wrapper = getWrapper(userDTO);
        IPage<UserQueryVO> page = userMapper
                .getUserByRoleTag(new Page<>(userDTO.getPageNo(), userDTO.getPageSize()), wrapper,ObjectUtils.isEmpty(userDTO.getRoleIds())?0:Long.valueOf(userDTO.getRoleIds()));
        return new JsonPageResult<UserQueryVO>(page.getRecords(),page.getTotal(),(int)page.getCurrent(),(int)page.getSize());
    }

    private LambdaQueryWrapper<User> getWrapper(UserDTO userDTO) {
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        if(!ObjectUtils.isEmpty(userDTO.getId())){
            wrapper.eq(User::getId, userDTO.getId());
        }
        if (ObjectUtils.isNotEmpty(userDTO.getAccount())){
            Pattern pattern = Pattern.compile("[0-9]*");
            boolean matches = pattern.matcher(userDTO.getAccount()).matches();
            if(!matches){
                wrapper.like(User::getAccount, userDTO.getAccount());
            }
            if(matches){
                wrapper.and(queryWrapper->queryWrapper
                        .like(User::getAccount, userDTO.getAccount())
                        .or()
                        .like(User::getId, userDTO.getAccount())) ;
            }
        }
        if(!ObjectUtils.isEmpty(userDTO.getUnitName())){
            wrapper.like(User::getUnitName, userDTO.getUnitName());
        }


        if(!ObjectUtils.isEmpty(userDTO.getBeginDate())){
            wrapper.ge(User::getCreatedTime,userDTO.getBeginDate());
        }
        if(!ObjectUtils.isEmpty(userDTO.getEndDate())){
            wrapper.le(User::getCreatedTime,userDTO.getEndDate());
        }
        if(!ObjectUtils.isEmpty(userDTO.getBeginTime())){
            wrapper.ge(User::getCreatedTime,userDTO.getBeginTime());
        }
        if(!ObjectUtils.isEmpty(userDTO.getEndTime())){
            wrapper.le(User::getCreatedTime,userDTO.getEndTime());
        }
        if (ObjectUtils.isNotEmpty(userDTO.getKeyword())) {
            wrapper.and(queryWrapper -> queryWrapper
                    .like(User::getName, userDTO.getKeyword())
                    .or()
                    .like(User::getAccount, userDTO.getKeyword()));
        }
        wrapper.orderByDesc(User::getCreatedTime);
        return wrapper;

    }

2.mapper.java

public interface UserMapper extends BaseMapper<User> {

    IPage<UserQueryVO> getUserByRoleTag(Page<Map<String, Object>> page,@Param(Constants.WRAPPER) LambdaQueryWrapper<User> wrapper,@Param("roleIds") Long roleIds);
}

3.mapper.xml

<select id="getUserByRoleTag" resultType="com.zyjournals.lib.module.account.domain.AccountApp">
        SELECT t.* from
        (SELECT a.id,a.user_id,a.mobile,a.online_hours,a.online_hours_sum,a.visits,a.download_times,a.created_time
        FROM
        t_account_app a
        LEFT JOIN t_user_role ur ON a.user_id = ur.user_id
        LEFT JOIN t_role r ON r.id = ur.role_id
        <where>
            r.tag = '3'
            and a.is_delete = 1
            and ur.is_delete = 1
            and r.is_delete = 1
            <if test="roleIds != 0">
                and r.id = #{roleIds}
            </if>
        </where>
        GROUP BY
        a.id,
        a.user_id,
        a.mobile,
        a.online_hours,
        a.online_hours_sum,
        a.visits,
        a.download_times,
        a.created_time) t
        ${ew.customSqlSegment}
    </select>

还可以用${ew.SqlSelect}
笔记：
1.关闭count查询

        Page p = new Page(messageNewsDTO.getPageNo(), messageNewsDTO.getPageSize(),false);
        Page<News> page = mapper.selectPage(p, wrapper);
        page.setTotal(Long.valueOf(mapper.selectCount(wrapper)));

2.sqlserver如果一个表设置主键自增，调用saveBatch会报错，重写mapper的saveBatch方法或改为input类型
3.更新空值

@Test
public void updateUserTest(){
    UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
    userUpdateWrapper.set("address", null);
    userUpdateWrapper.lambda().eq(User::getId, 1);
    userService.update(userUpdateWrapper);
}