基础框架的持久化层使用的是MyBatis Plus 插件,一般我们的查询时单表查询,
针对一些业务复杂的操作,我们可能还需要进行多表查询,此时多表查询的分页是我们需要解决的问题:
[!TIP|label: 定义mapper]
其中 Page page 参数是最关键的,是MP会自动解析的,会在SQL后面自动加上分页语句
@Mapper
public interface IntentMapper extends AdminBaseMapper<IntentEntity> {
List<IntentEntity> queryPage(Page<IntentEntity> page, @Param("userId") Long userId,@Param("botId") Long botId);
}
[!TIP|label: 编写SQL]
将复杂的业务SQL可以自定义mapper.xml 文件编写
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ustcinfo.ishare.eip.chat.rest.mapper.IntentMapper">
<select id="queryPage" resultType="com.ustcinfo.ishare.eip.chat.rest.entity.IntentEntity">
select *
from chat_intent intent, chat_bot_skill_mapping skill_mapping
where intent.del_flag = 0 and skill_mapping.del_flag = 0 and
intent.user_id = #{userId} and skill_mapping.user_id = #{userId} and
intent.skill_id = skill_mapping.skill_id and skill_mapping.bot_id = #{botId}
</select>
</mapper>
[!TIP|label: 编写Service]
这里和单表查询有点区别
public PageUtils<IntentEntity> queryPage(MapUtils mapUtils) {
// 获取查询第几页
Integer currentPage = mapUtils.getInteger("page");
// 限制每页大小
Integer limit = mapUtils.getInteger("limit");
Page<IntentEntity> page = new Page();
page.setCurrent(currentPage);
page.setSize(limit);
String userId = UserUtils.getCurrentUserId();
Long botId = mapUtils.getLong("botId");
// 调用mapper自定义的方法
List<IntentEntity> list = baseMapper.queryPage(page, userId, botId);
// 设置结果大分页对象中
page.setRecords(list);
// 封装分页
return new PageUtils(page);
}
[!TIP|label: 说明]
上述进行完毕后,会发现执行的SQL后面自动加上了 limit currentPage,limit 【在MySQL数据库中】