Mybatis-plus 联表查询数据+分页展示+多条件搜索（模糊查询）--- 使用wrapper自定义sql

1.controller层：

    @GetMapping
    public Result<IPage<SelectRepositoryDto>> getAll(SelectRepositoryDto repositoryDto,PageVO pageVO){
        IPage<SelectRepositoryDto> all=eRepositoryService.getAll(repositoryDto,pageVO);
        return Result.success(all);
    }

其中SelectRepositoryDto中定义了页面需要展示的字段信息。
其中使用 @Transient 来定义数据库对应实体表中所没有的字段。

SelectRepositoryDto:

import org.springframework.data.annotation.Transient;
@Data
public class SelectRepositoryDto {

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @ApiModelProperty(value = "安全分类id")
    private Long safeTypeId;

    @ApiModelProperty(value = "题型(1:单选 2:多选 3:判断)")
    private Integer quesType;

    @ApiModelProperty(value = "题干")
    private String quesContent;

    @ApiModelProperty(value = "选项")
    private String options;

    @ApiModelProperty(value = "答案")
    private String answer;
	
    private Date createTime;
    
    @Transient
    private String typeName;
    @Transient
    private Date  startTime;
    @Transient
    private Date endTime;

}

就像上面那种写法， typeName、startTime、endTime不是数据库中repository表中的字段。

注意@Transient 的包不要导错
import org.springframework.data.annotation.Transient;

在导上面的@Transient相关包时，添加了有关redis的依赖：

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-redis</artifactId>
			<version>2.0.0.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>redis.clients</groupId>
			<artifactId>jedis</artifactId>
			<version>3.0.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-pool2</artifactId>
		</dependency>

PageVO中定义了分页所需要的信息字段：

@Data
@ApiModel(value = "分页", description = "orders传参实例：[{'column': 'create_time','asc': false},{'column': 'name','asc': " +
        "true}]")
public class PageVO {
    /**
     * 当前页
     */
    @ApiModelProperty(value = "当前页")
    private Integer current;

    /**
     * 当前页显示数据的条数
     */
    @ApiModelProperty(value = "当前页显示数据的条数")
    private Integer size;

    /**
     * 获取排序信息，排序的字段和正反序
     */
    @ApiModelProperty(value = "排序方式。(默认【创建时间倒序】:[{'column': 'create_time','asc': false}])。",
            notes = "例子：[{'column': 'create_time','asc': false},{'column':'name','asc': true}]"
    )
    private String orders;

    /**
     * 当前页默认值为1
     */
    public Integer getCurrent() {
        return current = (current == null || current <= 0) ? 1 : current;
    }

    /**
     * 每页大小默认为10
     */
    public Integer getSize() {
        return size = (size == null || size == 0) ? 10 : size;
    }

    /**
     * description:将orders（json数组字符串）转为List
     */
    public List<OrderItem> generateOrderList() {
        List<OrderItem> orderItemList = new ArrayList<>();
        if (StrUtil.isBlank(getOrders())) {
            orderItemList.add(OrderItem.desc("create_time"));
        } else {
            try {
                orderItemList = JSONArray.parseArray(orders, OrderItem.class);
            } catch (Exception e) {
                throw new BadRequestException("分页排序参数orders不合法，请传正确的参数格式——['column':'','asc':'true/false']");
            }
        }
        return orderItemList;
    }

    /**
     * description:根据pageVO构建分页查询IPage
     */
    public <K> IPage<K> buildPage() {
        Page<K> page = new Page<>(getCurrent(), getSize());
        page.addOrder(generateOrderList());
        return page;
    }
}

2.service层：

@Service
@RequiredArgsConstructor
public class ERepositoryServiceImpl extends BasicServiceImpl<ERepositoryMapper, ERepository> implements IERepositoryService {

    private final ERepositoryMapper eRepositoryMapper;

    @Override
    public IPage<SelectRepositoryDto> getAll(SelectRepositoryDto repositoryDto, PageVO pageVO) {
        QueryWrapper<Object> query = new QueryWrapper<>();
        System.out.println(repositoryDto);
        if(ObjectUtil.isNotNull(repositoryDto.getQuesContent())){
            query.and(e ->{
                e.like("er.ques_content",repositoryDto.getQuesContent());
            });
        }
        if(ObjectUtil.isNotNull(repositoryDto.getQuesType())){
               query.and(qw->qw.eq("er.ques_type",repositoryDto.getQuesType()));
        }
        if(ObjectUtil.isNotNull(repositoryDto.getSafeTypeId())){
            query.and(qw->qw.eq("er.safe_type_id",repositoryDto.getSafeTypeId()));
        }
        if(ObjectUtil.isNotNull(repositoryDto.getStartTime())){
            query.and(qw->qw.between("er.create_time",repositoryDto.getStartTime(),repositoryDto.getEndTime()));
        }
       query.eq("er.is_deleted",false);
        return eRepositoryMapper.getAll(pageVO.buildPage(),query);
    }
}

3.mapper层：

使用到了${ew.customSqlSegment}

注意：sqlSegment和customSqlSegment的区别：
（1）sqlSegment:

	< where>
    	${ew.sqlSegment}
	< /where>

（2）customSqlSegment:

	${ew.customSqlSegment} 

	省略了 < where>< /where>

eg: 
select * from a< where> ${ew.sqlSegment} < /where>

select * from a ${ew.customSqlSegment}

@Component
public interface ERepositoryMapper extends BasicMapper<ERepository> {

    /**
     * 按条件联表查询,使用 Wrapper自定义SQL
     * */
    @Select("select er.id,st.name as typeName,ques_type,ques_content,safe_type_id," +
            "options,answer,er.create_time,er.create_time " +
            "from e_repository er  " +
            "LEFT JOIN e_safe_type st on er.safe_type_id=st.id " +
            "${ew.customSqlSegment}  ")
    IPage<SelectRepositoryDto> getAll(IPage<Object> page, @Param(Constants.WRAPPER) QueryWrapper<Object> query);
}

LambdaQueryWrapper允许我们直接使用实体类::get字段的方式进行我们的字段映射，更方便代码的阅读

但是LambdaQueryWrapper更适合单表操作的情况下，比如我们同时操作两张表，两张表中都有is_deleted字段,那么我们条件判断时，它应该是谁的is_delete或者其他公共字段？

所以这种情况下，我们的LambdaQueryWrapper不在适宜了，所以我们可以结合mapper中定义的sql语句，来写自定义的字段。

它的缺点：不易阅读.