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";
}
只贴部分关键代码,请开发者根据业务补充表和代码