mybatis pulsh : 关联分页查询

mysql:

-- 双表，外键，内连接查询

-- 分页查询
SELECT 
pcc.id,
pcb.commission_bureau_code  company_code, 
pcb.commission_bureau_name  company_name,
pcc.company_ratio company_ratio,
pcc.company_subscribe_number company_subscribe_number,
pcc.create_time,
pcc.update_time
FROM
per_commission_bureau pcb INNER JOIN pb_company_config pcc 
ON pcb.commission_bureau_code=pcc.company_code 
WHERE pcc.company_code='201' and pcb.commission_bureau_name LIKE '%单位名称%'
ORDER BY pcc.create_time DESC LIMIT 0,3;

-- 获取总条数, mybatis plush 会自动查询总条数
SELECT COUNT(1) 
 FROM (
 SELECT pcc.id, 
 pcb.commission_bureau_code company_code, 
 pcb.commission_bureau_name company_name, 
 pcc.company_ratio company_ratio, 
 pcc.company_subscribe_number company_subscribe_number, 
 pcc.create_time, pcc.update_time
 FROM per_commission_bureau pcb INNER JOIN pb_company_config pcc
 ON pcb.commission_bureau_code=pcc.company_code
 WHERE pcc.company_code='201' and pcb.commission_bureau_name LIKE '%单位名称%' 
 ORDER BY 'pcc.create_time' 'DESC' ) TOTAL;

 

ServiceImpl：

    @Override
    public Page<PbCompanyConfigVo> selectList(PbCompanyConfigQuery pbCompanyConfigQuery) {

        //创建分页对象
        Page<PbCompanyConfigVo> page = new Page<>(pbCompanyConfigQuery.getPageNo(), pbCompanyConfigQuery.getPageSize());
        Page<PbCompanyConfigVo> data = this.baseMapper.selectCompanyConfigList(page, pbCompanyConfigQuery);

        return data;
    }

Mapper：

    /**
     * @Author WangChangDian
     * @Description //TODO 分页查询列表
     * @Date 16:06 2021/5/12
     * @Param
     **/

    @Select({"<script>", "SELECT \n" +
            "        pcc.id,\n" +
            "        pcb.commission_bureau_code  company_code,\n" +
            "        pcb.commission_bureau_name  company_name,\n" +
            "        pcc.company_ratio company_ratio,\n" +
            "        pcc.company_subscribe_number company_subscribe_number,\n" +
            "        pcc.create_time,\n" +
            "        pcc.update_time\n" +
            "        FROM\n" +
            "        per_commission_bureau pcb INNER JOIN pb_company_config pcc ON pcb.commission_bureau_code=pcc.company_code\n" +
            "        WHERE 1=1\n" +
            "        <if test=\"params != null and params !=''\">\n" +
            "            <if test=\"params.companyCode != null and params.companyCode != ''\">\n" +
            "                AND pcc.company_code LIKE concat ('%',#{params.companyCode},'%')\n" +
            "            </if>\n" +
            "\n" +
            "            <if test=\"params.companyName != null and params.companyName != ''\">\n" +
            "                AND pcb.commission_bureau_name LIKE concat ('%',#{params.companyName},'%')\n" +
            "            </if>\n" +
            "        </if>\n" +
            "        ORDER BY #{params.orderBy} #{params.order}"
            , "</script>"})
    Page<PbCompanyConfigVo> selectCompanyConfigList(Page<PbCompanyConfigVo> page, @Param("params") PbCompanyConfigQuery pbCompanyConfigQuery);

PbCompanyConfigQuery：

@Data
@EqualsAndHashCode(callSuper = false)
public class PbCompanyConfigQuery implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * pageNo
     */
    private Long pageNo;
    /**
     * pageSize
     */
    private Long pageSize;
    /**
     * 单位编号
     */
    private String companyCode;
    /**
     * 单位名称
     */
    private String companyName;
    /**
     * 排序规则
     */
    private String order = "DESC";
    /**
     * 排序字段
     */
    private String orderBy = "pcc.create_time";

}

只贴部分关键代码，请开发者根据业务补充表和代码