笔记总结:Web后端——MyBatisPlus

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.枚举类型处理器

在数据库中填入枚举类型的实际值

  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;
        }
    }
    
  2. 配置全局枚举处理器

    mybatis-plus:
        # 修改 mybatis 的 DefaultEnumTypeHandler
        configuration:
            default-enum-type-handler: com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler
    
2.JSON处理器

数据库中以JSON格式存储的数据导出为实体对象

  1. 在实体对象属性上添加注解@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;
    
  2. 导入json依赖

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-json</artifactId>
    </dependency>
    
  3. 注意对象嵌套需要设置参数

    @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;
        });
    }
}
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值