使用mybatisPlus注解方式多表关联查询带条件分页

本文记录使用mybatisPlus 多表关联查询，分页，条件查询

1、新建一个实体vo，这个实体可以不是数据库存在的表。

自由发挥

2、先看一下service层的代码

//获取page对象
        Page<MfuHealthPushRecordDto> page =pageConverter.pageCo2Page(pageCo);
        //新建查询对象
        QueryWrapper<MfuHealthPushRecordDto> queryWrapper = new QueryWrapper<>();
        //增加条件查询
        queryWrapper.apply(StringUtils.isNotBlank(mDto.getStartTime()),
                    " and record.DT_UPDATE_DATE >= to_date('" + mDto.getStartTime() + "','yyyy-mm-dd')");
        queryWrapper.apply(StringUtils.isNotBlank(mDto.getEndTime()),
                    " record.DT_UPDATE_DATE <= to_date('" + mDto.getEndTime() + "','yyyy-mm-dd')");

        queryWrapper.like(StringUtils.isNotBlank(mDto.getPublishOrg()),"record.C_PUBLISH_ORG",mDto.getPublishOrg());
        queryWrapper.eq(StringUtils.isNotBlank(mDto.getIllnessCode()),"ill.C_ILLNESS_ID",mDto.getIllnessCode());

        List<MfuHealthPushRecordDto> mfuHealthPushRecordList = mfuHealthPushRecordMapper.getMfuHealthPushRecordList(page, queryWrapper);

3、要准备一个page对象，这个pageCo其实很简单

import lombok.Data;

import java.io.Serializable;

/**
 * 分页条件类
 *
 * @author wuhuanjia
 */
@Data
public class PageCo implements Serializable {
    private Long page;
    private Long rows;
}

这边是利用mapstruct把pageCo转换为Page(其实就是属性复制)

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hyjk.platform.core.bean.PageBean;
import com.hyjk.platform.core.bean.PageParams;
import com.hyjk.platform.core.entity.PageCo;
import com.hyjk.platform.core.mapstruct.Converter;
import org.mapstruct.BeanMapping;
import org.mapstruct.Mapper;
import org.mapstruct.Mapping;
import org.mapstruct.Mappings;

import java.io.Serializable;

/**
 * 分页参数转化
 */
@Mapper(componentModel = "spring")
public interface PageConverter {

    /**
     * PageParams -> Page
     */
    @BeanMapping(resultType = Page.class)
    Page pageParms2Page(PageParams pageParams);

    /**
     * Page -> PageBean
     */
    @BeanMapping(resultType = PageBean.class)
    PageBean page2PageBean(IPage page);

    default <D extends Serializable, T extends Serializable> PageBean<T> page2PageBean(IPage<D> page, Converter<D, T> converter) {
        PageBean<T> pageBean = new PageBean<>();
        pageBean.setTotal(page.getTotal());
        pageBean.setCurrent(page.getCurrent());
        pageBean.setSize(page.getSize());
        pageBean.setRecords(converter.domain2Dto(page.getRecords()));
        return pageBean;
    }

    /**
     * @param pageCo
     * @return
     */
    @Mappings({
            @Mapping(source = "page", target = "current"),
            @Mapping(source = "rows", target = "size")
    })
    Page pageCo2Page(PageCo pageCo);
}
 

4、利用QueryWrapper对象进行条件查询，单表查询的网上有很多了，多表查询的使用居然不给用列别名，所以里面的条件查询都是用的表的列字段，更多的可以去看mybatisplus的官方文档有介绍api

5、因为我要分页 ，所以第一个参数传了page，后面做条件查询所以传了queryWrapper

6、到了mapper类，要实现basemapper

  @Select("select record.C_HEALTH_PUSH_ID as healthPushId, record.C_PUBLISH_TYPE as publishType,to_char(record.DT_UPDATE_DATE, 'yyyy-mm-dd hh24:mi:ss') as updateDate,record.C_RECIPIENT as recipient,record.C_PUBLISH_ORG_NAME as publishOrgName,record.C_PUBLISH_ORG as publishOrg,record.c_knowledge_id as knowledgeId,ill.C_ILLNESS_NAME as illnessName,ill.C_ILLNESS_ID as illnessCode, knowledge.c_theme as theme, knowledge.c_content as content from MFU_HEALTH_PUSH_RECORD record,MFU_RE_ILLNESS_KNOWLEDGE ill,MFU_HEALTH_KNOWLEDGE knowledge where record.c_knowledge_id = ill.c_knowledge_id and record.c_knowledge_id = knowledge.c_knowledge_id " + " ${ew.sqlSegment}")
    List<MfuHealthPushRecordDto> getMfuHealthPushRecordList(Page<MfuHealthPushRecordDto> page, @Param(Constants.WRAPPER) QueryWrapper<MfuHealthPushRecordDto> queryWrapper);

这里就是mapper类的写法，具体的包如下

完整的sql，精髓是后面拼接的那个参数，就是在上面代码动态拼接上去的