MyBatisPlus
一、配置文件
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>
注意:尽量不要与mybatis配置文件同时出现
二、用法
1.mapper
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
继承接口,自动编写所有CRUD方法
2.service
public interface IUserService extends IService<User> {
}
实现类:
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
-
批处理
for循环每次都预编译SQL语句,而MP批处理只预编译了一次
//mp批处理 @Test void testSaveBatch(){ long b = System.currentTimeMillis(); List<User> list = new ArrayList<>(1000); for(int i = 1; i <= 100000; ++i){ list.add(buildUser(i)); //每次批处理1000条记录 if(i % 1000 == 0){ userService.saveBatch(list); list.clear(); } } long e = System.currentTimeMillis(); System.out.println("耗时:" + (e - b) + "ms"); }
每次为SQL语句添加参数
配置JDBC参数,在数据库链接后添加:
rewriteBatchedStatements=true
修改了SQL语句,使得语句从添加参数变为values(xxx),(xxx)
-
LambdaQuery
@Test void testLambdaQuery(){ User user = userService.lambdaQuery().eq(User::getUsername, "Rose").one(); System.out.println(user); List<User> users = userService.lambdaQuery().like(User::getUsername, "o").list(); users.forEach(System.out::println); Long count = userService.lambdaQuery().like(User::getUsername, "o").count(); System.out.println(count); }
动态SQL
public List<User> queryUsers(String username, Integer status, Long min, Long max){ return userService.lambdaQuery().like(username != null, User::getUsername, username) .eq(status != null, User::getStatus, status) .gt(min != null, User::getBalance, min) .lt(max != null, User::getBalance, max) .list(); }
-
LambdaUpdate
public void updateBalance(Long balance, Long id, String username){ if(id == null && username == null){ throw new RuntimeException("更新条件不能为空"); } userService.lambdaUpdate() .set(User::getBalance, balance) //当余额为0时,冻结账户 .set(balance == 0, User::getStatus, 2) .eq(id != null, User::getId, id) .eq(username != null, User::getUsername, username) .update(); //执行update语句 }
-
Db静态工具
- 解决注入Service时发生循环依赖的问题,使用静态工具不需要注入Bean对象
- Db可以通过传入的实体类型,自动查找对应实体,完成数据库操作
- Service获取Mapper还可以通过getBaseMapper方法获取,此办法适用于需要自定义SQL的场景
@Test public void testUpdate(){ User user = new User(); //动态SQL user.setId(6L); user.setName("狂神说"); //参数是一个对象 int i = Db.updateById(user); System.out.println(i); }
三、常用注解
1.@TableName
- 类注解,将类名映射数据库表名
2.@TableId
- 主键注解,标注属性主键
- 设置主键生成策略
3.@TableField
- 属性注解,属性名映射数据库字段名
- 使用场景:
- 变量名与字段名不一致
- isXXX成员变量,MP识别自动删除is,需要手动映射
- 变量名与数据库关键字冲突,手动添加转义
三、CRUD扩展
1.insert
@Test
void testInsert(){
User user = new User();
user.setName("小明");
user.setAge(18);
user.setEmail("123456@qq.com");
int result = userMapper.insert(user); //自动生成id,默认雪花算法
System.out.println(result); //返回受影响的行数
System.out.println(user); //自动回填
}
-
主键生成策略:uuid、自增id、雪花算法、redis、zookeeper
-
雪花算法:分布式系统唯一id生成。
使用41bit作为毫秒数,10bit作为机器ID(5bit数据中心、5bit机器ID),12bit作为毫秒内的流水号,最后为符号位0。
可以保证几乎全球唯一。
-
@TableId注解
设置主键生成策略
public class User { @TableId(type = IdType.ASSIGN_ID) private Long id; private String name; private Integer age; private String email; }
public enum IdType { AUTO(0), //主键自增,数据库对应也一定是自增 NONE(1), //未设置主键 INPUT(2), //手动输入,不会自动添加 ASSIGN_ID(3), //分配Id,默认雪花算法 ASSIGN_UUID(4); //UUID }
2.update
@Test
public void testUpdate(){
User user = new User();
//动态SQL
user.setId(6L);
user.setName("狂神说");
//参数是一个对象
int i = userMapper.updateById(user);
System.out.println(i);
}
-
自动填充create_time、modified_time
-
注解@TableField
@TableField(fill = FieldFill.INSERT) private LocalDateTime createTime; @TableField(fill = FieldFill.INSERT_UPDATE) private LocalDateTime updateTime;
-
设置自动填充处理器
@Slf4j //把处理器加入到IOC容器中 @Component public class MyMetaObjectHandler implements MetaObjectHandler { @Override public void insertFill(MetaObject metaObject) { log.info("start insert fill..."); this.setFieldValByName("createTime", LocalDateTime.now(), metaObject); this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject); } @Override public void updateFill(MetaObject metaObject) { log.info("start update fill.."); this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject); } }
-
乐观锁
乐观锁:总是认为不会出现问题,无论干什么都不去上锁,当出现了问题,再次更新值测定。
悲观锁:总是认为会出现问题,无论干什么都上锁。
用于线程安全处理
# A线程 update user set name = "aaa", version = version + 1 where id = 2 and version = 1; # B线程 update user set name = "aaa", version = version + 1 where id = 2 and version = 1;
当B线程抢先对version+1,A线程将更新失败。
MP乐观锁插件:
//添加乐观锁注解 @Version private Integer version;
注册乐观锁插件:
//自动管理事物 @EnableTransactionManagement @Configuration public class MyBatisPlusConfig { //注册乐观锁插件(MP内置拦截器,用于在与数据库交互前拦截数据,执行逻辑) @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor()); return interceptor; } }
测试模拟多线程:
//测试乐观锁 @Test public void testOptimisticLockerInnerInterceptor(){ //线程1 User user = userMapper.selectById(1); user.setName("张无忌"); //另外一个线程进行插队 User user2 = userMapper.selectById(1); user2.setName("刘邦"); userMapper.updateById(user2); //被乐观锁阻断了 userMapper.updateById(user); }
3.select
//测试查询
@Test
public void testSelectById(){
User user = userMapper.selectById(1L);
System.out.println(user);
}
//批量查询
@Test
public void testSelectByBatchIds(){
List<User> users = userMapper.selectBatchIds(Arrays.asList(1,2,3));
users.forEach(System.out::println);
}
//按条件查询
@Test
public void testSelectByMap(){
HashMap<String, Object> map = new HashMap<>();
map.put("name", "Jack");
List<User> users = userMapper.selectByMap(map);
users.forEach(System.out::println);
}
-
分页查询
插件配置:
@Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL); //设置最大分页数 paginationInnerInterceptor.setMaxLimit(1000L); interceptor.addInnerInterceptor(paginationInnerInterceptor); return interceptor; }
分页查询测试:
@Test void testPage(){ int pageNo = 1, pageSize = 5; //分页条件 Page<User> p = Page.of(pageNo, pageSize); //排序条件,true升序,false降序 p.addOrder(new OrderItem("balance", false));、 //分页查询 Page<User> page = userService.page(p); //分页结果 long total = page.getTotal(); System.out.println("total = " + total); long pages = page.getPages(); System.out.println("pages = " + pages); List<User> records = page.getRecords(); records.forEach(System.out::println); }
4.delete
//测试删除
@Test
public void testDeleteById(){
userMapper.deleteById(1703042369650462724L);
}
//批量删除
@Test
public void testDeleteByBatchIds(){
userMapper.deleteBatchIds(Arrays.asList(2, 3));
}
//条件删除
@Test
public void testDeleteMap(){
HashMap<String, Object> hashMap = new HashMap<>();
hashMap.put("name", "张三");
userMapper.deleteByMap(hashMap);
}
-
逻辑删除
物理删除:从数据库直接移除
逻辑删除:在数据库中没有移除,而是通过变量来让他失效,查询不到他,设置deleted = 1
管理员可以查看被删除的数据,类似于回收站
-
配置application.yml
mybatis-plus: global-config: db-config: logic-delete-field: flag # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置步骤2) logic-delete-value: 1 # 逻辑已删除值(默认为 1) logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
-
为实体类的逻辑删除属性添加@TableLogic注解
-
查询操作会自动拼接上delete条件
SELECT id,name,age,email,version,deleted,create_time,update_time FROM user WHERE id=? AND deleted=0
四、条件构造器Wrapper
@Test
void contextLoads() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//设置查询的字段和条件
queryWrapper.isNotNull("name").isNotNull("email").ge("age", 12).select("name", "email", "age");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
void test1(){
//查询一个记录
userMapper.selectOne(new QueryWrapper<User>().eq("name", "Jack"));
}
@Test
void test2(){
//查询结果数
Long count = userMapper.selectCount(new QueryWrapper<User>().between("age", 20, 30));
System.out.println(count);
}
//模糊查询
@Test
void test3(){
List<Map<String, Object>> maps = userMapper.selectMaps(new QueryWrapper<User>().notLike("name", "e")
//右边以t开头
.likeRight("email", "t"));
maps.forEach(System.out::println);
}
@Test
void test4(){
//使用sql语句进行联表子查询
List<Object> objects = userMapper.selectObjs(new QueryWrapper<User>().inSql("id", "select id from user where id < 3"));
objects.forEach(System.out::println);
}
@Test
void test5(){
//通过id进行降序排序
List<Object> objects = userMapper.selectObjs(new QueryWrapper<User>().orderByDesc("id"));
objects.forEach(System.out::println);
}
@Test
void test6(){
User user = new User();
user.setAge(64);
//更新记录
userMapper.update(user, new QueryWrapper<User>().eq("name", "赵四"));
}
//基于UpdateWrapper
@Test
void test7(){
//设置set语句
userMapper.update(null, new UpdateWrapper<User>().setSql("age = age - 1")
.in("id", Arrays.asList(1, 2, 3)));
}
//基于LambdaQueryWrapper
@Test
void test8(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
//防止直接写入属性名的硬编码,利用方法反射获取属性名
wrapper.lambda().select(User::getId, User::getName, User::getAge)
.like(User::getName, "赵");
userMapper.selectList(wrapper);
}
自定义sql语句
@Test
void testCustomSql(){
int amount = 2;
QueryWrapper<User> wrapper = new QueryWrapper<User>().in("id", Arrays.asList(1, 2, 3)).eq("deleted", 0);
//注意:逻辑删除失效了!手动添加
userMapper.updateAgeByWrapper(amount, wrapper);
}
//注入wrapper生成的条件语句
@Update("UPDATE user set age = age - #{amount} ${ew.customSqlSegment}")
void updateAgeByWrapper(@Param("amount") int amount, @Param("ew") QueryWrapper<User> wrapper);
联表查询:
查询给定用户id值,且地址在北京的记录
@Test
void testMultiTableQuery() {
List<Long> ids = List.of(1L, 2L, 4L);
String city = "北京";
QueryWrapper<User> queryWrapper = new QueryWrapper<User>().in("u.id", ids)
.eq("a.city", city);
List<User> users = userMapper.queryUsersByWrapper(queryWrapper);
users.forEach(System.out::println);
}
List<User> queryUsersByWrapper(@Param("ew") QueryWrapper<User> wrapper);
xml配置文件
<select id="queryUsersByWrapper" resultType="com.itheima.po.User">
select u.*
from user u
inner join address a on u.id = a.user_id
${ew.customSqlSegment}
</select>
五、其他处理器
1.枚举类型处理器
在数据库中填入枚举类型的实际值
-
在枚举类型中为值变量添加注解@EnumValue
@Getter public enum UserStatus { NORMAL(1, "正常"), FREEZE(2, "冻结"), ; @EnumValue private final int value; private final String decs; //私有的 UserStatus(int value, String decs) { this.value = value; this.decs = decs; } }
-
配置全局枚举处理器
mybatis-plus: # 修改 mybatis 的 DefaultEnumTypeHandler configuration: default-enum-type-handler: com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler
2.JSON处理器
数据库中以JSON格式存储的数据导出为实体对象
-
在实体对象属性上添加注解@TableField(typeHandler = JacksonTypeHandler.class)
@Data @NoArgsConstructor @AllArgsConstructor(staticName = "of") public class UserInfo { private Integer age; private String intro; private String gender; }
@TableField(typeHandler = JacksonTypeHandler.class) private UserInfo info;
-
导入json依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-json</artifactId> </dependency>
-
注意对象嵌套需要设置参数
@TableName(autoResultMap = true)
六、配置加密
MP拥有基于AES算法的加密工具
1.获取密钥与加密
@Test
void contextLoads() {
// 生成 16 位随机 AES 密钥
String randomKey = AES.generateRandomKey();
System.out.println("randomKey = " + randomKey);
// 利用密钥对用户名加密
String username = AES.encrypt("root", randomKey);
System.out.println("username = " + username);
// 利用密钥对密码加密
String password = AES.encrypt("MySQL123", randomKey);
System.out.println("password = " + password);
}
2.修改配置
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/mp?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: mpw:QWWVnk1Oal3258x5rVhaeQ== # 密文要以 mpw:开头
password: mpw:EUFmeH3cNAzdRGdOQcabWg== # 密文要以 mpw:开头
3.配置参数
@SpringBootTest(args = "--mpw.key=6234633a66fb399f")
七、分页查询高级应用实例
1.实体类
/**
* 分页查询请求实体类
*/
//贫血模型:无方法
//充血模型:有方法
@Data
public class PageQuery {
private Integer pageNo = 1;
private Integer pageSize = 5;
private String sortBy;
private Boolean isAsc = false;
//根据前端的请求实体自动生成Page对象,手动传入默认排序方法
public <T> Page<T> toMpPage(OrderItem ... defaultOrderItem){
//分页条件
Page<T> p = Page.of(pageNo, pageSize);
//排序条件,true升序,false降序
//既不能为null,也不能为空
if(StrUtil.isNotBlank(sortBy)){
p.addOrder(new OrderItem(sortBy, isAsc));
}
else {
for (OrderItem orderItem : defaultOrderItem) {
p.addOrder(orderItem);
}
}
return p;
}
}
/**
* 返回前端的User实体类
*/
@Data
public class UserVO {
private Long id;
private String username;
private UserInfo info;
private UserStatus status;
private Long balance;
}
/**
* 通用分页查询结果类
* @param <T>
*/
@Data
@AllArgsConstructor(staticName = "of")
@NoArgsConstructor
public class PageVo<T> {
private Long total;
private Long pages;
private List<T> list;
//自动查询转换器,将mp的page实体转换为分页查询结果类封装
public <P> PageVo(Page<P> page, Class<T> clazz) {
this.total = page.getTotal();
this.pages = page.getPages();
List<P> records = page.getRecords();
if(CollUtil.isEmpty(records)){
this.list = Collections.emptyList();
return;
}
this.list = BeanUtil.copyToList(records, clazz);
}
public <P> PageVo(Page<P> page, Function<P, T> convertor) {
this.total = page.getTotal();
this.pages = page.getPages();
List<P> records = page.getRecords();
if(CollUtil.isEmpty(records)){
this.list = Collections.emptyList();
return;
}
this.list = records.stream().map(convertor).collect(Collectors.toList());
}
}
2.controller
@RestController
@RequestMapping("/users")
//自动生成参数构造器
@RequiredArgsConstructor
public class UserController {
//final必须加入构造器
private final IUserService userService;
private final IAddressService addressService;
@GetMapping("/page")
public PageVo<UserVO> queryUserByPage(@RequestBody PageQuery pageQuery){
return userService.queryUserByPage(pageQuery);
}
}
3.service
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
@Override
public PageVo<UserVO> queryUserByPage(PageQuery pageQuery) {
Page<User> p = pageQuery.toMpPage(new OrderItem("updateTime", false));
//分页查询
Page<User> page = page(p);
//分页结果,将用户名的最后四个字符隐藏
return new PageVo<>(page, user -> {
UserVO userVO = BeanUtil.copyProperties(user, UserVO.class);
String username = userVO.getUsername();
userVO.setUsername((username.substring(0, username.length() - 4) + "****"));
return userVO;
});
}
}