7
mybatis配置分页插件
source link: https://blog.51cto.com/u_11440114/5235156
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
mybatis配置分页插件
原创@Configuration
public class MybatisConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
}
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;
}
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);
}
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>
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);
}
}
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
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);
}
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)
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)
简单分页模型, 有如下几个主要属性
/**
* 查询数据列表
*/
protected List<T> records = Collections.emptyList();
/**
* 总数
*/
protected long total = 0;
/**
* 每页显示条数，默认 10
*/
protected long size = 10;
/**
* 当前页
*/
protected long current = 1;
* 查询数据列表
*/
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
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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK