MyBatisPlus

1、引入依赖

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId>
</dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope><exclusions><exclusion><groupId>org.junit.vintage</groupId><artifactId>junit-vintage-engine</artifactId></exclusion></exclusions>
</dependency><!--mybatis-plus-->
<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.0.5</version>
</dependency><!--mysql-->
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId>
</dependency><!--lombok用来简化实体类-->
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId>
</dependency>

2、编写代码

@SpringBootApplication
@MapperScan("com.achang.mybatisplus.mapper")
public class MybatisPlusApplication {
......
}

entity

@Data
public class User {private Long id;private String name;private Integer age;private String email;
}

mapper

@Repository
public interface UserMapper extends BaseMapper<User> {
}

test

@SpringBootTest
class MybatisPlusApplicationTests {@Autowiredprivate UserMapper userMapper;@Testvoid testSelectList(){System.out.println(("----- selectAll method test ------"));//UserMapper 中的 selectList() 方法的参数为 MP 内置的条件封装器 Wrapper//所以不填写就是无任何条件List<User> list = userMapper.selectList(null);list.forEach(System.out::println);}}

insert

        int result = userMapper.insert(user);

update

        int result = userMapper.updateById(user);

select

        User user = userMapper.selectById(1);

        List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));

        List<User> users = userMapper.selectByMap(map);

delete

        int result = userMapper.deleteById(1364080977348956166L);

        int result = userMapper.deleteBatchIds(Arrays.asList(8, 9, 10));

        int result = userMapper.deleteByMap(map);

主键策略

MyBatis-Plus默认的主键策略是： ID_WORKER 全局唯一 ID

主键自增注解 

        @TableId(type = IdType.AUTO)

        private Long id;

#全局设置主键生成策略 mybatis-plus.global-config.db-config.id-type=auto

自动填充字段

添加datetime类型的新的字段 create_time、update_time

实体上添加注解

        @TableField(fill = FieldFill.INSERT)

        private Date createTime;

        @TableField(fill = FieldFill.INSERT_UPDATE)

        private Date updateTime;

实现元对象处理器接口MetaObjectHandler

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {private static final Logger LOGGER = LoggerFactory.getLogger(MyMetaObjectHandler.class);//使用mp实现添加操作，这个方法执行@Overridepublic void insertFill(MetaObject metaObject) {LOGGER.info("start insert fill。。。");this.setFieldValByName("createTime", new Date(), metaObject);this.setFieldValByName("updateTime", new Date(), metaObject);}//使用mp实现修改操作，这个方法执行@Overridepublic void updateFill(MetaObject metaObject) {LOGGER.info("start update fill ....");this.setFieldValByName("updateTime", new Date(), metaObject);}
}

乐观锁

数据库中添加version字段

实体类添加version字段

        @Version

        @TableField(fill = FieldFill.INSERT)

        private Integer Version;

元对象处理器接口添加version的insert默认值

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {//使用mp实现添加操作，这个方法执行@Overridepublic void insertFill(MetaObject metaObject) {...this.setFieldValByName("version",1,metaObject);}}

在 MybatisPlusConfig 中注册 Bean，使用乐观锁插件

@EnableTransactionManagement
@Configuration
@MapperScan("com.achang.mybatisplus.mapper")
public class MybatisPlusConfig {//乐观锁插件@Beanpublic OptimisticLockerInterceptor optimisticLockerInterceptor() {return new OptimisticLockerInterceptor();}
}

测试乐观锁

 @Testvoid testOptimisticLocker(){//查询User user = userMapper.selectById(1364080977348956166L);//修改数据user.setName("Helen Yao");user.setEmail("helen@qq.com");//执行更新userMapper.updateById(user);}@Testpublic void testOptimisticLockerFail() {//查询User user = userMapper.selectById(1L);//修改数据user.setName("Helen Yao1");user.setEmail("helen@qq.com1");//模拟取出数据后，数据库中version实际数据比取出的值大，即已被其它线程修改并更新了versionuser.setVersion(user.getVersion() - 1);//执行更新userMapper.updateById(user);}

分页

创建配置类

//分页插件
@Bean
public PaginationInterceptor paginationInterceptor() {return new PaginationInterceptor();
}

测试selectPage分页

//分页查询
@Test
void testPage(){//1、创建page对象//传入参数：当前页 和 每页显示记录数Page<User> userPage = new Page<>(1,3);//调用mp分页查询方法//调用mp分页查询过程中，底层会封装，把所有分页数据分装到page对象中userMapper.selectPage(userPage,null);//通过page对象获取数据userPage.getRecords().forEach(System.out::println);//遍历查询的分页数据System.out.println(userPage.getCurrent());//获取当前页System.out.println(userPage.getSize());//每页显示记录数System.out.println(userPage.getTotal());//总记录数System.out.println(userPage.getPages());//总页数System.out.println(userPage.hasNext());//判断是否有下一页System.out.println(userPage.hasPrevious());//判断是否有上一页
}

测试selectMapsPage分页

//测试selectMapsPage分页：结果集是Map
@Test
void testSelectMapsPage(){//1、创建page对象//传入参数：当前页 和 每页显示记录数Page<User> page = new Page<>(1,3);//调用mp分页查询方法//调用mp分页查询过程中，底层会封装，把所有分页数据分装到page对象中IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(page, null);//注意：此行必须使用 mapIPage 获取记录列表，否则会有数据类型转换错误mapIPage.getRecords().forEach(System.out::println);System.out.println(page.getCurrent());System.out.println(page.getPages());System.out.println(page.getSize());System.out.println(page.getTotal());System.out.println(page.hasNext());System.out.println(page.hasPrevious());
}

逻辑删除

数据库中添加 deleted字段

实体类添加deleted 字段

        @TableLogic

        @TableField(fill = FieldFill.INSERT)

        private Integer deleted;

元对象处理器接口添加deleted的insert默认值

@Override
public void insertFill(MetaObject metaObject) {
......
this.setFieldValByName("deleted", 0, metaObject);
}

application.properties 加入配置，指定删除为1，没删为0，此为默认值

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

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

在 MybatisPlusConfig 中注册 Bean

@Bean
public ISqlInjector sqlInjector() {return new LogicSqlInjector();
}

性能分析拦截器

开发环境使用，超过指定时间，停止运行。

在 MybatisPlusConfig 中配置

//性能分析插件
/*** SQL 执行性能分析插件* 开发环境使用，线上不推荐。 maxTime 指的是 sql 最大执行时长*/
@Bean
@Profile({"dev","test"})// 设置 dev test 环境开启
public PerformanceInterceptor performanceInterceptor() {PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();performanceInterceptor.setMaxTime(100);//ms，超过此处设置的ms则sql不执行performanceInterceptor.setFormat(true);//SQL是否格式化，默认falsereturn performanceInterceptor;
}

Spring Boot 中设置dev环境

#环境设置：dev、test、prod

        spring.profiles.active=dev

条件构造器 wapper

1、ge、gt、le、lt、isNull、isNotNull

@Test
public void testDelete() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.isNull("name")
.ge("age", 12)
.isNotNull("email");
int result = userMapper.delete(queryWrapper);
System.out.println("delete return count = " + result);
}

UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL

2、eq、ne

@Test
public void testSelectOne() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.eq("name", "Tom");User user = userMapper.selectOne(queryWrapper);System.out.println(user);
}

SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ?

3、between、notBetween

@Test
public void testSelectCount() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.between("age", 20, 30);Integer count = userMapper.selectCount(queryWrapper);System.out.println(count);
}

SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ?

4、allEq

@Test
public void testSelectList() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();Map<String, Object> map = new HashMap<>();map.put("id", 2);map.put("name", "Jack");map.put("age", 20);queryWrapper.allEq(map);List<User> users = userMapper.selectList(queryWrapper);users.forEach(System.out::println);
}

SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ? AND id = ? AND age = ?

5、like、notLike、likeLeft、likeRight

@Test
public void testSelectMaps() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.notLike("name", "e").likeRight("email", "t");List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表maps.forEach(System.out::println);
}

SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name NOT LIKE ? AND email LIKE ?

6、in、notIn、inSql、notinSql、exists、notExists

@Test
public void testSelectObjs() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();//queryWrapper.in("id", 1, 2, 3);queryWrapper.inSql("id", "select id from user where id < 3");List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表objects.forEach(System.out::println);
}

SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND id IN (select id from user where id < 3)

7、or、and

@Test
public void testUpdate1() {//修改值User user = new User();user.setAge(99);user.setName("Andy");//修改条件UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();userUpdateWrapper.like("name", "h").or().between("age", 20, 30);int result = userMapper.update(user, userUpdateWrapper);System.out.println(result);
}

UPDATE user SET name=?, age=?, update_time=? WHERE deleted=0 AND name LIKE ? OR age BETWEEN ? AND ?   

8、嵌套or、嵌套and

@Test
public void testUpdate2() {//修改值User user = new User();user.setAge(99);user.setName("Andy");//修改条件UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();userUpdateWrapper.like("name", "h").or(i -> i.eq("name", "李白").ne("age", 20));int result = userMapper.update(user, userUpdateWrapper);System.out.println(result);
}

UPDATE user SET name=?, age=?, update_time=? WHERE deleted=0 AND name LIKE ? OR ( name = ? AND age <> ? )

9、orderBy、orderByDesc、orderByAsc

@Test
public void testSelectListOrderBy() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.orderByDesc("id");List<User> users = userMapper.selectList(queryWrapper);users.forEach(System.out::println);
}

SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 ORDER BY id DESC

10、last    //只能调用一次,多次调用以最后一次为准 有sql注入的风险

@Test
public void testSelectListLast() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.last("limit 1");List<User> users = userMapper.selectList(queryWrapper);users.forEach(System.out::println);
}

SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 limit 1

11、指定要查询的列

@Test
public void testSelectListColumn() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.select("id", "name", "age");List<User> users = userMapper.selectList(queryWrapper);users.forEach(System.out::println);
}

SELECT id,name,age FROM user WHERE deleted=0

12、set、setSql

@Test
public void testUpdateSet() {//修改值User user = new User();user.setAge(99);//修改条件UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();userUpdateWrapper.like("name", "h").set("name", "老李头")//除了可以查询还可以使用set设置修改的字段.setSql(" email = '123@qq.com'");//可以有子查询int result = userMapper.update(user, userUpdateWrapper);
}

UPDATE user SET age=?, update_time=?, name=?, email = '123@qq.com' WHERE deleted=0 AND name LIKE ?