MybatisPlus实例

• mp配置

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.0</version>
</dependency>

spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://localhost:3306/plus?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

• mp-CRUD

@SpringBootTest
class MybatisPlusApplicationTests {

    @Autowired
    private UserMapper mapper;

    @Test
    void contextLoads() {
        List<User> users = mapper.selectList(null);
        for (User user : users) {
            System.out.println(user);
        }
    }
    @Test
    void insert(){
        User user = new User();
        user.setUsername("yoho");
        user.setSex("男");
        user.setAddress("西安");
        mapper.insert(user);
    }
    @Test
    void Update(){
        User user = new User();
        user.setId(54L);
        user.setUsername("xixi");
        user.setSex("神");
        mapper.updateById(user);
    }
    @Test
    void select(){
        User user = mapper.selectById(50L);
        System.out.println(user);
    }
    @Test
    void selectByBatchId(){
        List<User> users = mapper.selectBatchIds(Arrays.asList(48L, 49L, 50L));
        System.out.println(users);
    }
    @Test
    void selectByBatchMap(){
        HashMap<String, Object> map = new HashMap<>();
        //可以实现动态sql,模糊查询
        map.put("username","谈雨儿");
//        map.put("username","yoho");
//        map.put("address","西安");
        mapper.selectByMap(map);
    }
    @Test
        //分页查询
    void testPageSelected(){
        Page<User> page = new Page<>(1, 5);
//        page.hasNext();
//        page.hasPrevious();
        mapper.selectPage(page,null);
        for (User user : page.getRecords()) {
            System.out.println(user);
        }
    }
    @Test
    void delete(){
        HashMap<String, Object> map = new HashMap<>();
        map.put("username","yoyo");
        //只能删一个
        //还可通过id批量删除
//        map.put("username","xixi");
//        map.put("username","yo");
//        map.put("username","yoho");
        mapper.deleteByMap(map);
    }
    @Test
    void deleteByID(){
       mapper.deleteBatchIds(Arrays.asList(41L,53L,54L,55L));
    }
    @Test
    void deleteone(){
        mapper.deleteById(50L);
    }
}



@Test
    void selectUserBywrapper() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("address","西安石油大学");
        List<User> users = mapper.selectList(wrapper);
        System.out.println(users);
    }
    @Test
    void selectCountsBywrapper() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.between("id","45","50");
        Integer integer = mapper.selectCount(wrapper);
        System.out.println(integer);
    }
    @Test
    //模糊查询
    void selectLikeBywrapper() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("address","西安");
        wrapper.likeRight("username","l");
        List<Map<String, Object>> users = mapper.selectMaps(wrapper);
        users.forEach(System.out::println);
    }
    @Test
    //子查询
    void selectInByWrapper(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.inSql("id","select id from user where id<50");
        List<Object> users = mapper.selectObjs(wrapper);
        users.forEach(System.out::println);
    }
    @Test

    void selectInnByWrapper(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
//        wrapper.le("id",50);//小于等于
        wrapper.ne("id",50);//不等于
        //ge大于等于
//        wrapper.gt()大于
//        wrapper.lt() 小于
        List<User> users = mapper.selectList(wrapper);
        users.forEach(System.out::println);
    }
    @Test
    void testdesc(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.orderByDesc("id");
        List<Map<String, Object>> users = mapper.selectMaps(wrapper);
        users.forEach(System.out::println);
    }

• mp更新操作，自动填充

添加字段，添加注解

@TableField(fill = FieldFill.INSERT)
private Date createTime;
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;

在handler包下创建

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        this.setFieldValByName("createTime", new Date(), metaObject);
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }
}

• mp分页查询 config的MyBatisPlusConfig

@Bean    //实例化这个类，交给Spring容器进行管理
    高版本
//    public MybatisPlusInterceptor PaginationInterceptor() {
//        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
//        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
//        paginationInnerInterceptor.setMaxLimit(500L);
//        paginationInnerInterceptor.setOverflow(false);
//        mybatisPlusInterceptor.addInnerInterceptor(paginationInnerInterceptor);
//        return mybatisPlusInterceptor;
//    
}

-------------------------------------------------
@Bean
public PaginationInterceptor paginationInterceptor() {
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
    // paginationInterceptor.setOverflow(false);
    // 设置最大单页限制数量，默认 500 条，-1 不受限制
    // paginationInterceptor.setLimit(500);
    // 开启 count 的 join 优化,只针对部分 left join
    return paginationInterceptor;
}

 @Test
    //分页查询
    void testPageSelected(){
        Page<User> page = new Page<>(1, 5);
//        page.hasNext();
//        page.hasPrevious();
        mapper.selectPage(page,null);
        for (User user : page.getRecords()) {
            System.out.println(user);
        }
    }
    
    
    @Test
void test() {
    QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
    queryWrapper.ge("age", 25);// greaterEquals大于等于25
    Page<User> page=new Page<>(1,2);//当前页是1，每页2条记录
    //传入要进行的分页以及查询构造条件
    IPage<User> userIPage = userMapper.selectPage(page, queryWrapper);
    
    System.out.println("总页数"+userIPage.getPages());//打印总页数
    System.out.println("总记录数数"+userIPage.getTotal());//总记录数
    List<User> userList=userIPage.getRecords();//获取当前页的内容，返回的是一个List集合
    userList.forEach(System.out::println);//遍历打印
 
}

• 逻辑删除

添加字段

@TableLogic
//逻辑删除
private Integer deleted;

在config中MyBatisPlusConfig添加

@Bean
//逻辑删除
//高版本不用配置
public ISqlInjector sqlInjector() {
    return new LogicSqlInjector();
}

在application.properties

mybatis-plus.global-config.db-config.logic-not-delete-value=0
mybatis-plus.global-config.db-config.logic-delete-value=1

• 性能分析插件

@Bean
//高版本配置不了
//性能插件
public PerformanceInterceptor performanceInterceptor() {
    PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
    // maxTime 指的是 sql 最大执行时长
    performanceInterceptor.setMaxTime(5000);
    //SQL是否格式化 默认false
    performanceInterceptor.setFormat(true);
    return new PerformanceInterceptor();
}

在application.properties

#执行效率
spring.profiles.active=dev

• 乐观锁

配置数据库字段

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Rr7waqzK-1622870002805)(C:\Users\优小熊Xx\AppData\Roaming\Typora\typora-user-images\1622786166907.png)]

配置pojo

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xdEsB08Y-1622870002808)(C:\Users\优小熊Xx\AppData\Roaming\Typora\typora-user-images\1622786186451.png)]

配置插件

//乐观锁
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor(){
    return new OptimisticLockerInterceptor();
}

测试

@Test
void versionTest() {
    User user = mapper.selectById(42L);
    user.setAddress("xian");
    User user2 = mapper.selectById(42L);
    user2.setAddress("xianyang");
    mapper.updateById(user2);
    mapper.updateById(user);
}

user的地址会被改为xianyang

运行流程：取出记录时，获取当前version后更新时带上这个version，执行更新时如果old version==version则成功执行version=version+1否则执行失败

• 配置日志输出

mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

2L);
user2.setAddress(“xianyang”);
mapper.updateById(user2);
mapper.updateById(user);
}

user的地址会被改为xianyang

运行流程：取出记录时，获取当前version后更新时带上这个version，执行更新时如果old version==version则成功执行version=version+1否则执行失败

- 配置日志输出

mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl