Use Mybatis-plus to realize joint query paging of two tables

Many-to-one mapping¶

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 = "Points table entity class")
public class ClientIntegration {

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

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

    /**
     * Create record time
     */
    @ApiModelProperty("Create record time")
    private String createTime;

    /**
     * Point change type 0 minus 1 plus
     */
    @ApiModelProperty("Point change type 0 minus 1 plus")
    private Integer changeType;

    /**
     * Change the number of points
     */
    @ApiModelProperty("Change the number of points")
    private Integer changeCount;

    /**
     * Historical points
     */
    @ApiModelProperty("Historical points")
    private Integer historyIntegration;

    /**
     * Existing points
     */
    @ApiModelProperty("Existing points")
    private Integer existingIntegration;

    /**
     * Points type
     */
    @ApiModelProperty("Points type")
    private String integrationType;

    /**
     * Customer entity class
     */
    @TableField(exist = false)
    @ApiModelProperty("Customer entity class")
    private ClientUser clientUser;

}

Important Service layer¶

/**
      * Get all points records
      * @return all data
      */
     @Override
     public R getAllIntegration(PageVo pageVo, IntegrationQueryVo integrationQueryVo) {

         // open paging
         Page<ClientIntegration> page = new Page<>(pageVo.getCurrentPage(), pageVo.getPageSize());

         // Pass the obtained page object and the parameters passed by the front desk to the sql query method written by yourself, note: the return value type must be Page<T>
         Page<ClientIntegration> clientIntegrationList =
                 clientIntegrationDao.getAllIntegration(page,integrationQueryVo);

         if (clientIntegrationList != null) {
             // Get the total number
             int total = (int) page.getTotal();
             return R.success().putObject(total).putListData(clientIntegrationList.getRecords());
         }
         return R.error();

     }

dao interface¶

/**
  * (ClientIntegration) table database access layer
  *
  * @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);

}

mapper mapping file¶

<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>