MybatisPlus入门（中）+https://developer.aliyun.com/article/1492262
如何使用！
1、配置拦截器组件即可
/** * 注册分页 * @return */ @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); }
2、测试
/** * 分页 */ @Test void testPage(){ // 参数1：当前页；参数2：页面大小; 以5条记录作为一页查询数据 Page<User> page = new Page<>(1, 5); userMapper.selectPage(page, null); page.getRecords().forEach(System.out::println); System.out.println(page.getTotal()); }
3、测试结果
底层还是使用的limit
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7da31a40] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection@805536380 wrapping com.mysql.cj.jdbc.ConnectionImpl@29b40b3] will not be managed by Spring JsqlParserCountOptimize sql=SELECT id,name,age,email,version,create_time,update_time FROM user ==> Preparing: SELECT COUNT(1) FROM user ==> Parameters: <== Columns: COUNT(1) <== Row: 9 ==> Preparing: SELECT id,name,age,email,version,create_time,update_time FROM user LIMIT 0,5 ==> Parameters: <== Columns: id, name, age, email, version, create_time, update_time <== Row: 1, lpp, 220, test1@baomidou.com, 3, 2021-09-24 13:49:18, 2021-09-24 15:04:20 <== Row: 2, Jack, 20, test2@baomidou.com, 1, 2021-09-24 13:49:18, null <== Row: 3, Tom, 28, test3@baomidou.com, 1, 2021-09-24 13:49:18, null <== Row: 4, Sandy, 21, test4@baomidou.com, 1, 2021-09-24 13:49:18, null <== Row: 5, Billie, 24, test5@baomidou.com, 1, 2021-09-24 13:49:18, null <== Total: 5 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7da31a40] User(id=1, name=lpp, age=220, email=test1@baomidou.com, version=3, createTime=Fri Sep 24 13:49:18 CST 2021, updateTime=Fri Sep 24 15:04:20 CST 2021) User(id=2, name=Jack, age=20, email=test2@baomidou.com, version=1, createTime=Fri Sep 24 13:49:18 CST 2021, updateTime=null) User(id=3, name=Tom, age=28, email=test3@baomidou.com, version=1, createTime=Fri Sep 24 13:49:18 CST 2021, updateTime=null) User(id=4, name=Sandy, age=21, email=test4@baomidou.com, version=1, createTime=Fri Sep 24 13:49:18 CST 2021, updateTime=null) User(id=5, name=Billie, age=24, email=test5@baomidou.com, version=1, createTime=Fri Sep 24 13:49:18 CST 2021, updateTime=null) 9
删除操作
基本删除操作
/** * 根据ID删除 */ @Test void deleteById(){ userMapper.deleteById(1L); } /** * 批量删除 */ @Test void deleteBatch(){ userMapper.deleteBatchIds(Arrays.asList(2, 3)); } /** * 根据多条件删除 */ @Test void deleteByMap(){ Map<String, Object> objectMap = new HashMap<>(); objectMap.put("name", "Billie"); objectMap.put("age", 24); userMapper.deleteByMap(objectMap); }
逻辑删除
物理删除：从数据库中直接移除
逻辑删除：在数据库中没有被移除，而是通过一个变量让他失效，deleted = 0 / deleted = 1
测试：
1、增加字段 deleted
2、实体类中增加属性
/** * 逻辑删除 */ @TableLogic private Integer deleted;
3、注册逻辑删除组件
/** * 逻辑删除组件 * @return */ @Bean public ISqlInjector sqlInjector(){ return new LogicSqlInjector(); }
4、增加配置
mybatis-plus: global-config: db-config: logic-delete-value: 1 # 逻辑已删除值(默认为 1) logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
5、测试
/** * 根据ID删除 */ @Test void deleteById(){ userMapper.deleteById(6L); }
删除时，只是更新了deleted的状态
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5762658b] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection@1328705686 wrapping com.mysql.cj.jdbc.ConnectionImpl@1d61a348] will not be managed by Spring ==> Preparing: UPDATE user SET deleted=1 WHERE id=? AND deleted=0 ==> Parameters: 6(Long) <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5762658b]
搜索时，自动忽略deleted=1的数据
性能分析插件
作用
性能分析拦截器，用于输出每条SQL语句及其执行时间。
MP提供性能分析插件，如果超过指定的时间就停止运行、抛出异常。
如何使用
1、导入插件
@Bean @Profile({"dev"}) // 设置dev环境开启 public PerformanceInterceptor performanceInterceptor() { PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor(); performanceInterceptor.setMaxTime(1); performanceInterceptor.setFormat(true); return performanceInterceptor; }
注意匹配对应的环境信息，如下为dev
2、测试使用
@Test void contextLoads() { // wrapper是条件构造器 List<User> users = userMapper.selectList(null); users.forEach(System.out::println); }
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6dc9da2d] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection@1637821180 wrapping com.mysql.cj.jdbc.ConnectionImpl@df921b1] will not be managed by Spring ==> Preparing: SELECT id,name,age,email,version,deleted,create_time,update_time FROM user WHERE deleted=0 ==> Parameters: <== Columns: id, name, age, email, version, deleted, create_time, update_time <== Row: 4, Sandy, 21, test4@baomidou.com, 1, 0, 2021-09-24 13:49:18, null <== Row: 1441249447918751745, 断言, 24, Jay@, 1, 0, 2021-09-24 13:49:18, null <== Row: 1441249447918751746, 断言, 24, Jay@, 1, 0, 2021-09-24 13:49:18, null <== Row: 1441249447918751747, 断言1, 24, Jay@, 1, 0, 2021-09-24 14:22:13, 2021-09-24 14:22:13 <== Total: 4 Time：27 ms - ID：com.jay.mybatisplus.mapper.UserMapper.selectList Execute SQL： SELECT id, name, age, email, version, deleted, create_time, update_time FROM user WHERE deleted=0 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6dc9da2d] org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: The SQL execution time is too large, please optimize ! ### The error may exist in com/jay/mybatisplus/mapper/UserMapper.java (best guess) ### The error may involve com.jay.mybatisplus.mapper.UserMapper.selectList ### The error occurred while handling results ### SQL: SELECT id,name,age,email,version,deleted,create_time,update_time FROM user WHERE deleted=0 ### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: The SQL execution time is too large, please optimize !
条件构造器
测试1:
@Test void contextLoads() { QueryWrapper<User> queryWrapper = new QueryWrapper(); queryWrapper.isNotNull("name").isNotNull("email").ge("age",24); List<User> users = userMapper.selectList(queryWrapper); users.forEach(System.out::println); }
测试2:
@Test void test1() { QueryWrapper queryWrapper = new QueryWrapper(); queryWrapper.eq("name", "Sandy"); User user = userMapper.selectOne(queryWrapper); System.out.println(user); }
测试3:
@Test void test2() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.between("age",20,24); Integer count = userMapper.selectCount(queryWrapper); System.out.println(count); }
测试4:
@Test void test3() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); // name不包含J，email以t开头 queryWrapper.notLike("name", "J").likeRight("email","t"); // 查询结果 userMapper.selectMaps(queryWrapper).forEach(System.out::println); }
测试5:
@Test void test4() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); // id 子查询 queryWrapper.inSql("id", "select id from user where age < 22"); // 查询结果 userMapper.selectMaps(queryWrapper).forEach(System.out::println); }
