快捷導(dǎo)航

SpringBoot使用Mybatis-Plus中分頁插件PaginationInterceptor詳解

更新時(shí)間：2025年07月15日 09:49:44 作者：秋分的秋刀魚

文章介紹SpringBoot高版本中使用MyBatisPlusInterceptor替代舊分頁插件,需配置多個(gè)InnerInterceptor功能模塊（如分頁、多租戶、動(dòng)態(tài)表名等）,并強(qiáng)調(diào)插件順序和mapper.xml中SQL語句不能以分號結(jié)尾,以避免分頁語法錯(cuò)誤

1 配置分頁插件

@Configuration
public class MybatisConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

在高版本的SpringBoot中, 會(huì)提示這種寫法已過時(shí), 所以采用另一種寫法 MybatisPlusInterceptor , 如下:

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
    return interceptor;
}

1.1 MybatisPlusInterceptor

該插件是核心插件，目前代理了 Executor#query 和 Executor#update 和 StatementHandler#prepare 方法

1.1.1 屬性

private List<InnerInterceptor> interceptors = new ArrayList<>();

1.1.2 InnerInterceptor

我們提供的插件都將基于此接口來實(shí)現(xiàn)功能目前已有的功能: 自動(dòng)分頁: PaginationInnerInterceptor 多租戶:
TenantLineInnerInterceptor 動(dòng)態(tài)表名: DynamicTableNameInnerInterceptor 樂觀鎖:
OptimisticLockerInnerInterceptor sql性能規(guī)范: IllegalSQLInnerInterceptor
防止全表更新與刪除: BlockAttackInnerInterceptor 注意: 使用多個(gè)功能需要注意順序關(guān)系，建議使用如下順序多租戶
動(dòng)態(tài)表名分頁，樂觀鎖 sql性能規(guī)范，防止全表更新與刪除總結(jié): 對sql進(jìn)行單次改造的優(yōu)先放入，不對sql進(jìn)行改造的最后放入

2 編寫Mapper及其對應(yīng)的mapper.xml文件

@Mapper
public interface UserMapper extends BaseMapper<User> {
    List<User> findPageUsers(Page<User> page);
}

<select id="findPageUsers" resultType="org.wxmx.mybatis_plus_study.entity.User">
    select *
    from `user`
</select>

3 編寫測試類

3.1 簡單的分頁查詢

@SpringBootTest
class MybatisPlusStudyApplicationTests {
    @Resource
    UserMapper userMapper;
    @Test
    void contextLoads() {
        Page<User> page = new Page<>(1, 3);
        List<User> pageUsers = userMapper.findPageUsers(page);
        page.setRecords(pageUsers);
        System.out.println(page);
    }
}

運(yùn)行結(jié)果：

JsqlParserCountOptimize sql=select *
        from `user`
==>  Preparing: SELECT COUNT(1) FROM `user`
==> Parameters: 
<==    Columns: COUNT(1)
<==        Row: 9
==>  Preparing: select * from `user` LIMIT ?
==> Parameters: 3(Long)
<==    Columns: id, name, age
<==        Row: 39a773890a1b12b8a072c1be02ff3cdc, aaa, 12
<==        Row: 3b25fb904548c28b7ac6882d86c7ae5f, wdh, 12
<==        Row: 8b0397fcdfebe37d1d26175c17ed3725, wdh, 12
<==      Total: 3

3.2 帶查詢條件的分頁查詢

此方法是使用PaginationInterceptor 作為分頁插件.

@Test
void contextLoads() {
    Page<User> page = new Page<>(1, 2);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("name", "wdh");
    Page<User> page1 = userMapper.selectPage(page, queryWrapper);
   	page.setRecords(page1.getRecords()).getRecords().forEach(System.out::println);
}

運(yùn)行結(jié)果:

JsqlParserCountOptimize sql=SELECT  id,name,age  FROM user 
 
 WHERE (name = ?)
==>  Preparing: SELECT COUNT(1) FROM user WHERE (name = ?)
==> Parameters: wdh(String)
<==    Columns: COUNT(1)
<==        Row: 5
==>  Preparing: SELECT id,name,age FROM user WHERE (name = ?) LIMIT ?
==> Parameters: wdh(String), 2(Long)
<==    Columns: id, name, age
<==        Row: 3b25fb904548c28b7ac6882d86c7ae5f, wdh, 12
<==        Row: 8b0397fcdfebe37d1d26175c17ed3725, wdh, 12
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6a0f2853]
User(id=3b25fb904548c28b7ac6882d86c7ae5f, name=wdh, age=12)
User(id=8b0397fcdfebe37d1d26175c17ed3725, name=wdh, age=12)

3.3 Page

簡單分頁模型, 有如下幾個(gè)主要屬性

/**
 * 查詢數(shù)據(jù)列表
 */
protected List<T> records = Collections.emptyList();

/**
 * 總數(shù)
 */
protected long total = 0;

/**
 * 每頁顯示條數(shù)，默認(rèn) 10
 */
protected long size = 10;

/**
 * 當(dāng)前頁
 */
protected long current = 1;

4 注意事項(xiàng)

在編寫mapper.xml中的SQL語句的時(shí)候, 語句末尾不能使用 ; 結(jié)尾, 原因是在做分頁的時(shí)候會(huì)在編寫的SQL語句后面拼接上limit語句, 導(dǎo)致出現(xiàn)SQL語法錯(cuò)誤(SQLSyntaxErrorException). 如下所示:

JsqlParserCountOptimize sql=select *
        from `user`;
==>  Preparing: SELECT COUNT(1) FROM `user`
==> Parameters: 
<==    Columns: COUNT(1)
<==        Row: 9
==>  Preparing: select * from `user`; LIMIT ?
==> Parameters: 3(Long)
org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 3' at line 1
### The error may exist in org/wxmx/mybatis_plus_study/mapper/UserMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select *         from `user`; LIMIT ?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 3' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL