使用Mybatis-plus实现两个表联合查询分页

1、多对一的映射关系

package com.second.client.pojo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * (ClientIntegration)表实体类
 * @author lvyongqi
 * @since 2021-07-24 09:04:24
 */
@Data
@TableName(value = "client_integration")
@ApiModel(value = "积分表实体类")
public class ClientIntegration {

    @TableId(value = "id",type = IdType.AUTO)
    @ApiModelProperty("主键Id")
    private Integer id;

    /**
     * 会员ID
     */
    @ApiModelProperty("会员ID")
    private Integer clientId;

    /**
     * 创建记录时间
     */
    @ApiModelProperty("创建记录时间")
    private String createTime;

    /**
     * 积分变化类型0减1加
     */
    @ApiModelProperty("积分变化类型,0减1加")
    private Integer changeType;

    /**
     * 积分改变数量
     */
    @ApiModelProperty("积分改变数量")
    private Integer changeCount;

    /**
     * 历史积分
     */
    @ApiModelProperty("历史积分")
    private Integer historyIntegration;

    /**
     * 现有积分
     */
    @ApiModelProperty("现有积分")
    private Integer existingIntegration;

    /**
     * 积分类型
     */
    @ApiModelProperty("积分类型")
    private String integrationType;

    /**
     * 客户实体类
     */
    @TableField(exist = false)
    @ApiModelProperty("客户实体类")
    private ClientUser clientUser;

}

多条积分记录对应一个客户实体

2、重要的Service层

     /**
     * 获取所有的积分记录
     * @return 所有数据
     */
    @Override
    public R getAllIntegration(PageVo pageVo, IntegrationQueryVo integrationQueryVo) {

        // 开启分页
        Page<ClientIntegration> page = new Page<>(pageVo.getCurrentPage(), pageVo.getPageSize());

        // 将获取到的page对象和前台传递的参数传递到自己写的sql查询方法，注意：返回值类型一定要是Page<T>
        Page<ClientIntegration> clientIntegrationList =
                clientIntegrationDao.getAllIntegration(page,integrationQueryVo);

        if (clientIntegrationList != null ) {
            // 获取总条数
            int total = (int) page.getTotal();
            return R.success().putObject(total).putListData(clientIntegrationList.getRecords());
        }
        return R.error();

    }

3、dao接口

/**
 * (ClientIntegration)表数据库访问层
 *
 * @author lvyongqi
 * @since 2021-07-24 09:02:24
 */
public interface ClientIntegrationDao extends BaseMapper<ClientIntegration> {

    Page<ClientIntegration> getAllIntegration(Page<ClientIntegration> page,
                                              @Param("integrationQueryVo") IntegrationQueryVo integrationQueryVo);
    
}

4、mapper映射文件

<select id="getAllIntegration" resultMap="getAll" >
         select ci.id, client_id, create_time, change_type, change_count, history_integration, existing_integration, integration_type,cu.username
         from client_integration ci join client_user cu
         on ci.client_id = cu.id
         <where>
             <if test="integrationQueryVo.username != '' and integrationQueryVo.username != null ">
                  cu.username like concat('%',#{integrationQueryVo.username},'%')
             </if>
             <if test="integrationQueryVo.integrationType != '' and integrationQueryVo.integrationType != null">
                  and ci.integration_type like concat('%',#{integrationQueryVo.integrationType},'%')
             </if>
             <if test="integrationQueryVo.changeType==0 or integrationQueryVo.changeType==1">
                  and ci.change_type = #{integrationQueryVo.changeType}
             </if>
         </where>

    </select>
    <resultMap id="getAll" type="com.second.client.pojo.entity.ClientIntegration"
               autoMapping="true">
        <id column="id" property="id"/>
        <association property="clientUser" javaType="com.second.client.pojo.entity.ClientUser"
                     autoMapping="true" >
            <id column="id" property="id"/>
        </association>
    </resultMap>