java 索取了多表联合查询分页_Mybatis Plus 多表关联分页查询

Mybaits Plus 做单表分页直接用实体类的方式就可以了，简单到连SQL语句都省了，这里介绍下关联表查询的方式结合Mybatis Plus 中的分页插件对多表查询进行分页。

我的SQL语句示例:

SELECT

COUNT(1) total,

u. NAME,

u.job,

date_sub(

date_sub(

date_format(now(), '%y-%m-%d'),

INTERVAL extract(DAY FROM now()) DAY

),

INTERVAL 0 MONTH

) AS lastDay

FROM

sys_tttt m

LEFT JOIN sys_xxx u ON m.receiver = u.id

WHERE

PERIOD_DIFF(

date_format(now(), '%Y%m'),

date_format(m.end_time, '%Y%m')

) = 1

AND m. STATUS = '3'

AND u. NAME LIKE '%%'

GROUP BY

m.receiver

ORDER BY

total DESC;

直接将SQL语句写到XML文件中

SELECT

COUNT(1) total,

u. NAME,

u.job,

date_sub(

date_sub(

date_format(now(), '%y-%m-%d'),

INTERVAL extract(DAY FROM now()) DAY

),

INTERVAL 0 MONTH

) AS lastDay

FROM

sys_ttt m

LEFT JOIN sys_xxx u ON m.receiver = u.id

WHERE

PERIOD_DIFF(

date_format(now(), '%Y%m'),

date_format(m.end_time, '%Y%m')

) = 1

AND m. STATUS = '3'

AND u. NAME LIKE CONCAT('%',#{userName},'%')

GROUP BY m.receiver ORDER BY total DESC

Dao 层

List> selectXXX(IPage> page , @Param("userName") String userName);

Service 层

public PageUtils xxxPage(Map params) {

//名称 String userName = (String)params.get("userName");

IPage> page = new Query>().getPage(params);

List> list = baseMapper.selectXXX(page , userName);

page.setRecords(list);

return new PageUtils(page);

}

Controller层

@ApiOperation(value = "接口描述")

@RequestMapping(value = "/xxxTest")

public ResultData findXxxTest(@RequestParam Map params){

try { PageUtils page = testService.xxxPage(params);

return ResultData.ok().put("data", page);

}catch (Exception e){

if(e.getMessage() == null) {

return ResultData.ok().put("data",new HashMap<>());

}

return ResultData.error();

}

}