mybatis配置分页插件

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

高版本SpringBoot中，会提示这种写法已过时, 所以采用另一种写法MybatisPlusInterceptor：

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

MybatisPlusInterceptor

该插件是核心插件，目前代理了如下方法：

• Executor#query

• Executor#update

• StatementHandler#prepare

属性

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

InnerInterceptor，我们提供的插件都将基于此接口来实现功能 目前已有的功能：

• 自动分页: PaginationInnerInterceptor

• 多租户:TenantLineInnerInterceptor

• 动态表名: DynamicTableNameInnerInterceptor

• 乐观锁：OptimisticLockerInnerInterceptor

• sql性能规范: IllegalSQLInnerInterceptor

• 防止全表更新与删除: BlockAttackInnerInterceptor

使用多个功能需要注意顺序关系，建议使用如下顺序

• 多租户

• 动态表名

• 分页

• 乐观锁

• sql性能规范

• 防止全表更新与删除

对sql进行单次改造的优先放入，不对sql进行改造的最后放入

Mapper及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>

测试类

简单的分页查询

 @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);
     }
 }

运行结果:

 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

带查询条件的分页查询

此方法是使用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);
 }

运行结果:

 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)

Page

简单分页模型, 有如下几个主要属性

 /**
  * 查询数据列表
  */
 protected List<T> records = Collections.emptyList();
 ​
 /**
  * 总数
  */
 protected long total = 0;
 ​
 /**
  * 每页显示条数，默认 10
  */
 protected long size = 10;
 ​
 /**
  * 当前页
  */
 protected long current = 1;

注意事项

在编写mapper.xml中的SQL语句的时候, 语句末尾不能使用 ; 结尾，因为做分页时，会在编写的SQL语句后面拼接Limit语句，导致出现SQL语法错误(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 syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 3' at line 1