mybatisplus多条件查询，包含（eq()，and()，or()，notIn()，like()，分页），or()避坑

场景说明

业务需求是查询没有绑定车辆的司机列表，查询条件是根据司机姓名或司机手机号模糊查询。

mybatisplus的Maven依赖

		<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.2</version>
        </dependency>

司机实体类

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.xxx.xxx.xxx.SuperEntity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.math.BigDecimal;

/**
 * @author 
 * @since 
 */
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = true)
@TableName(value = "dms_driver", autoResultMap = true)
@ApiModel(value = "Driver对象", description = "司机信息表")
public class Driver extends SuperEntity<Driver> {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty("司机手机号")
    private String phone;

    @ApiModelProperty("司机账号")
    private String username;

    @ApiModelProperty("司机姓名")
    private String name;

    @ApiModelProperty("司机头像")
    private String headUrl;

}

司机实体类继承的公共类，SuperEntity.java

import com.baomidou.mybatisplus.annotation.*;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * @author 
 * @date 
 * @describe
 */
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = true)
public abstract class SuperEntity<T extends Model<?>> extends Model<T> implements Serializable {

    @TableId(value = "id", type = IdType.ASSIGN_ID)
    @ApiModelProperty("主键id")
    private Long id;

    @TableField(fill = FieldFill.INSERT)
    @ApiModelProperty("创建时间")
    private LocalDateTime createTime;

    @TableField(fill = FieldFill.UPDATE)
    @ApiModelProperty("更新时间")
    private LocalDateTime updateTime;

    @TableLogic
    @TableField(fill = FieldFill.INSERT_UPDATE)
    @ApiModelProperty("是否删除")
    private Boolean del;
}

请求参数

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @author
 * @date 
 * @describe
 */
@Data
@ApiModel(value = "查询司机分页参数-绑定车辆时用")
public class QueryDriverPageParam {

	@ApiModelProperty("当前页")
    @NotNull
    @Min(1)
    private int pageNumber;

    @ApiModelProperty("查询数量")
    @NotNull
    private int pageSize;
    
    @ApiModelProperty(value = "司机手机号或司机姓名")
    private String phoneOrName;
}

方式一：直接使用mybatisplus查询

代码展示

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

public Page<Driver> queryDriverPageForBinding(QueryDriverPageParam param) {
       // 排除以下列表中的司机
       List<Long> driverIds = new ArrayList<>();
        driverIds.add(1L);
        driverIds.add(2L);
        driverIds.add(3L);
        Page<Driver> pageInfo = new Page<>(param.getPageNumber(), param.getPageSize());
        LambdaQueryWrapper<Driver> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(Driver::getDel, 0); // 默认del为0，可以不用写
        if (null != driverIds && driverIds.size() > 0) {
            wrapper.notIn(Driver::getId, driverIds);
        }
        if (!StringUtils.isEmpty(param.getPhoneOrName())) {
            wrapper.and(s -> s.like(Driver::getPhone, param.getPhoneOrName()).or().like(Driver::getName, param.getPhoneOrName()));
        }

        Page<Driver> page = this.page(pageInfo, wrapper);
        return page;
    }

注意：这里使用this.page()要满足两个条件
条件一：DriverService接口继承

条件二：DriverServiceImpl实现类

方式二：手写SQL查询

代码展示

	@Resource
    private DriverMapper driverMapper;
    
	public Page<Driver> queryDriverPageForBinding(QueryDriverPageParam param) {
		// 排除以下列表中的司机
       	List<Long> driverIds = new ArrayList<>();
        driverIds.add(1L);
        driverIds.add(2L);
        driverIds.add(3L);
        Page<Driver> pageInfo = new Page<>(param.getPageNumber(), param.getPageSize());
        Page<Driver> page = driverMapper.queryDriverPage(pageInfo, driverIds, param.getPhoneOrName());
        return page;
        }

DriverMapper类代码展示

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xxx.xxx.xxx.dms.entity.Driver;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author AUTO
 * @since 2024-01-04
 */
@Mapper
public interface DriverMapper extends BaseMapper<Driver> {

    Page<Driver> queryDriverPage(@Param("page") Page<Driver> page, @Param("driverIds") List<Long> driverIds, @Param("phoneOrName") String phoneOrName);

}

DriverMapper.xml代码展示

<select id="queryDriverPage" resultType="com.xxx.xxx.xxx.xxx.entity.Driver">
        select
        <include refid="Base_Column_List"/>
        from dms_driver
        where del=0

        <if test="null != driverIds">
            and id not in
            <foreach collection="driverIds" item="driverId" open="(" close=")" separator=",">
                #{driverId}
            </foreach>
        </if>
        <if test="phone != null and phone != ''">
            and ( phone LIKE CONCAT('%',#{phoneOrName},'%')
            or name LIKE CONCAT('%',#{phoneOrName},'%'))
        </if>
    </select>