mybatis-plus-联表-分页查询-带条件

准备工作

        1.数据库创建两张有关联关系的表pms_disp_ord与pms_disp_ord_rls表。（

        2.引入mybaits-plus依赖于lombok（lombok用于提供get，set方法）

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

开工

1.创建两个表的实体类

@Data
@TableName(value = "pms_disp_ord")
public class DispOrd {
    /** 主键 */
    @ApiModelProperty(name = "主键",notes = "")
    @TableId(type = IdType.ASSIGN_ID)
    private String id ;
    /** 名称 */
    @ApiModelProperty(name = "名称",notes = "")
    private String name ;
    /** 专业类型 */
    @ApiModelProperty(name = "专业类型",notes = "")
    private Integer type ;
    /** 优先级 */
    @ApiModelProperty(name = "优先级",notes = "")
    private Integer priority ;
    /** 指令描述 */
    @ApiModelProperty(name = "指令描述",notes = "")
    private String orderDesc ;
    /** 创建时间 */
    @ApiModelProperty(name = "创建时间",notes = "")
    @TableField(value = "gmt_create",fill = FieldFill.INSERT)
    private Date gmtCreate ;
    /** 更新时间 */
    @ApiModelProperty(name = "更新时间",notes = "")
    @TableField(value = "gmt_modified",fill = FieldFill.INSERT_UPDATE)
    private Date gmtModified ;

}

@Data
@TableName(value = "pms_disp_ord_rls")
public class DispOrdRls {
    /** 主键 */
    @ApiModelProperty(name = "主键",notes = "")
    private String id ;
    /** 调度指令库外键 */
    @ApiModelProperty(name = "调度指令库外键",notes = "")
    private String dispordId ;
    /** 调度指令编码 */
    @ApiModelProperty(name = "调度指令编码",notes = "")
    private String dispordrlsCode ;
    /** 指令下达人 */
    @ApiModelProperty(name = "指令下达人",notes = "")
    private String instructionIssuer ;
    /** 指令传达人 */
    @ApiModelProperty(name = "指令传达人",notes = "")
    private String instructionCommunicator ;
    /** 指令接收方 */
    @ApiModelProperty(name = "指令接受方",notes = "")
    private String instructionRecipient ;
    /** 下达时间 */
    @ApiModelProperty(name = "下达时间",notes = "")
    private Date releaseTime ;
    /** 指令下达状态 */
    @ApiModelProperty(name = "指令下达状态",notes = "")
    private Integer instIssStatus ;
    /** 指令状态 */
    @ApiModelProperty(name = "指令状态",notes = "")
    private Integer instructionStatus ;
    /** 受理情况 */
    @ApiModelProperty(name = "受理情况",notes = "")
    private Integer acceptanceStatus ;
    /** 执行情况 */
    @ApiModelProperty(name = "执行情况",notes = "")
    private Integer implementation ;
    /** 下达指令描述 */
    @ApiModelProperty(name = "下达指令描述",notes = "")
    private String instDesc ;
    /** 创建时间 */
    @ApiModelProperty(name = "创建时间",notes = "")
    @TableField(value = "gmt_create",fill = FieldFill.INSERT)
    private Date gmtCreate ;
    /** 更新时间 */
    @ApiModelProperty(name = "更新时间",notes = "")
    @TableField(value = "gmt_modified",fill = FieldFill.INSERT_UPDATE)
    private Date gmtModified ;

    @TableField(exist = false)
    private DispOrd dispOrd;
}

DispOrdRls对象包含DispOrd对象，并告诉数据库它不是其中的字段

2.创建dao层（数据层）并书写联表分页查询带条件的方法，page是由逻辑层传入的，用的是mybatisplas的page

public interface DispOrdRlsMapper extends BaseMapper<DispOrdRls> {
      Page<DispOrdRls> findByPage(Page<DispOrdRls> page,@Param("dispOrdRlsQuery") DispOrdRlsQuery dispOrdRlsQuery);
}

2.1书写mapper层xml（sql）

<resultMap id="findByPageResultMap" type="com.javakc.pms.dispordrls.entity.DispOrdRls" autoMapping="true">
    <id column="id" jdbcType="VARCHAR" property="id"/>
    <association property="dispOrd" javaType="com.javakc.pms.dispord.entity.DispOrd" autoMapping="true"/>
</resultMap>

<select id="findByPage" parameterType="com.javakc.pms.dispordrls.vo.DispOrdRlsQuery" resultMap="findByPageResultMap">
    SELECT
        a.id,
        a.dispordrls_code,
        a.instruction_issuer,
        a.instruction_communicator,
        a.instruction_recipient,
        a.release_time,
        a.inst_iss_status,
        a.instruction_status,
        a.acceptance_status,
        a.implementation,
        a.inst_desc,
        b.id,
        b.NAME,
        b.type,
        b.priority,
        b.order_desc
    FROM
        pms_disp_ord_rls a,
        pms_disp_ord b
    WHERE
        a.dispord_id = b.id
        <if test="dispOrdRlsQuery.dispordrlsCode != '' and null !=dispOrdRlsQuery.dispordrlsCode">
            and a.dispordrls_code like concat(concat("%",#{dispOrdRlsQuery.dispordrlsCode}),"%")
        </if>
</select>

3.书写逻辑层

3.1书写逻辑层接口

public interface DispOrdRlsService extends IService<DispOrdRls> {
    /**
     * 多表的联合查询
     * @param pageNo
     * @param pageSize
     * @param dispOrdRlsQuery
     */
    Page<DispOrdRls> queryByPageAll(Long pageNo, Long pageSize, DispOrdRlsQuery dispOrdRlsQuery);
}

3.2书写逻辑层实现类

@Service
public class DispOrdRlsServiceImpl extends ServiceImpl<DispOrdRlsMapper, DispOrdRls> implements DispOrdRlsService {

    /**
     * 多表的联合查询
     * @param pageNo
     * @param pageSize
     * @param dispOrdRlsQuery
     */
    @Override
    public Page<DispOrdRls> queryByPageAll(Long pageNo, Long pageSize, DispOrdRlsQuery dispOrdRlsQuery) {
        Page<DispOrdRls> page = new Page<>(pageNo,pageSize);
        return baseMapper.findByPage(page,dispOrdRlsQuery);
    }
}

4.书写表现层

@PostMapping("queryByPageAll/{pageNo}/{pageSize}")
public R queryByPageAll(
        @PathVariable("pageNo")
        Long pageNo,
        @PathVariable("pageSize")
        Long pageSize,
        @RequestBody
        DispOrdRlsQuery dispOrdRlsQuery){

    if (Objects.isNull(pageNo) || Objects.equals(pageNo,"")){
        pageNo = 1L;
    }
    if (Objects.isNull(pageSize) || Objects.equals(pageSize,"")){
        pageSize = 10L;
    }

    Page<DispOrdRls> page = dispOrdRlsService.queryByPageAll(pageNo, pageSize, dispOrdRlsQuery);
    long total = page.getTotal();
    List<DispOrdRls> dispOrdRlsList = page.getRecords();
    return R.OK().data("total",total).data("dispOrdRlsList",dispOrdRlsList);
}