MyBatis Plus 实现多表分页查询_骑驴的小牧童的博客-CSDN博客_mybatis-plus多表分页
最简单的 MyBatis Plus 的多表联接、分页查询实现方法_IT小村的博客-CSDN博客_mybatisplus多表联查
项目中有用到 mybatis-plus(mbp) 进行分页的需求,所以笔记记录下。
一.声明配置类
@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
public class MybatisPlusConfiguration {
/**
* 分页插件
*/
@Bean
public MybatisPlusInterceptor paginationInnerInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//声明针对分页的拦截器
PaginationInnerInterceptor pageInterceptor = new PaginationInnerInterceptor();
//声明数据库类型
pageInterceptor.setDbType(DbType.MYSQL);
//添加到拦截器链中
interceptor.addInnerInterceptor(pageInterceptor);
return interceptor;
}
}
二.实体类
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class RuleConfiguration implements Serializable {
private static final long serialVersionUID=1L;
/**
* 自增主键ID
*/
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 规则名称
*/
@TableField("name")
private String name;
public static final String ID = "id";
public static final String NAME = "name";
}
实体类中须包含 @TableField 等注解
三.mapper
public interface RuleConfigurationMapper extends BaseMapper<RuleConfiguration> {
/**
* 规则分页
*
* @param page 分页对象
* @param name 规则名称
* @return
*/
List<ScheduleRulePageDTO> scheduleRulePage(Page page, @Param("name") String name);
}
方法参数须带上 Page 对象
四.Service
@Service
@Slf4j
public class RuleConfigurationServiceImpl implements RuleConfigurationService {
@Autowired
private RuleConfigurationMapper RuleConfigurationMapper;
@Override
public void page(PageReq req) {
Page<RuleConfiguration> page = new Page<>(req.getPageNum(), req.getPageSize());
List<RulePageDTO> RulePage = RuleConfigurationMapper.scheduleRulePage(page, req.getName());
}
}
声明 Page 对象并且设置 分页数 和 分页大小 属性,分页的方法传入 page 和查询条件即可完成分页。
五.结果
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6d5a95f6] was not registered for synchronization because synchronization is not active
//拦截器对sql进行相关拦截
2022-04-08 10:17:30.441 WARN 27080 --- [nio-8300-exec-5] c.b.m.e.p.i.PaginationInnerInterceptor : optimize this sql to a count sql has exception, sql:"SELECT
t1.id,
t1.name
FROM
rule_configuration t1
ORDER BY t1.updated_at DESC", exception:
null
//拼接 Limit 参数
2022-04-08 10:17:30.450 DEBUG 27080 --- [nio-8300-exec-5] c.m.s.m.s.I.scheduleRulePage : ==> Preparing: SELECT t1.id, t1.name FROM rule_configuration t1 ORDER BY t1.updated_at DESC LIMIT ?
2022-04-08 10:17:30.450 DEBUG 27080 --- [nio-8300-exec-5] c.m.s.m.s.I.scheduleRulePage : ==> Parameters: 10(Long)
2022-04-08 10:17:30.456 DEBUG 27080 --- [nio-8300-exec-5] c.m.s.m.s.I.scheduleRulePage : <== Total: 1
从日志可以看出,PaginationInnerInterceptor会对包含page的参数的sql进行拦截并重新拼接Limit参数
不过手动分页也值得考虑~