1 配置分页插件
@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;
}
1.1 mybatisplusinterceptor
该插件是核心插件，目前代理了 executor#query 和 executor#update 和 statementhandler#prepare 方法
1.1.1 属性
private list<innerinterceptor> interceptors = new arraylist<>();
1.1.2 innerinterceptor
- 我们提供的插件都将基于此接口来实现功能 目前已有的功能: 自动分页: paginationinnerinterceptor 多租户:
- tenantlineinnerinterceptor 动态表名: dynamictablenameinnerinterceptor 乐观锁:
- optimisticlockerinnerinterceptor sql性能规范: illegalsqlinnerinterceptor
- 防止全表更新与删除: blockattackinnerinterceptor 注意: 使用多个功能需要注意顺序关系，建议使用如下顺序 多租户
- 动态表名 分页，乐观锁 sql性能规范，防止全表更新与删除 总结: 对sql进行单次改造的优先放入，不对sql进行改造的最后放入
2 编写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>
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);
}
}
运行结果：
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);
}
运行结果:
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
简单分页模型, 有如下几个主要属性
/** * 查询数据列表 */ protected list<t> records = collections.emptylist(); /** * 总数 */ protected long total = 0; /** * 每页显示条数，默认 10 */ protected long size = 10; /** * 当前页 */ protected long current = 1;
4 注意事项
在编写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
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。