24.01.01 MyBatis

对sql拆分简化之后,更适合使用MyBatisPlus

搭建项目

• 引入依赖

        <!--Mysql 驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><!--Druid数据库连接池--><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.8</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.2</version></dependency>

• 配置文件

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/qingqing?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=trueusername: rootpassword: rootdruid:initial-size: 5max-active: 100min-idle: 10
mybatis-plus:mapper-locations: classpath:mapper/*.xmlconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

• 启动类

@SpringBootApplication
@MapperScan("com.javasm.mp.*.dao")
public class MpApplication {public static void main(String[] args) {SpringApplication.run(MpApplication.class, args);}}

测试

常用注解

@MapperScan		全局包扫描
@Mapper			扫描当前dao的,当全局扫描不到的dao需要加入Spring容器,使用

@TableName		实体类的名字和类名不一致的时候,配置数据库表名

@TableId		主键,在主键的属性上添加type =  IdType.AUTO		自增type =  IdType.INPUT	手动输入type =  IdType.ASSIGN_ID	雪花算法生成ID(一长串数字)(默认策略)(1874289430391369729)type =  IdType.ASSIGN_UUID	UUID,随机字符串(2333b9ea0b5da4b30e8739e6e0a8ea32)如果主键的名字,是id,会自动识别为主键,但是,默认的策略依然是雪花算法

@TableField	只要写了的属性,都认为是数据库的字段.排除当前属性,不是数据库的字段@TableField(exist = false)private SkillModel skill;数据库存在,但是不查询这一列,@TableField(select = false)字段不一致,绑定数据库的列名@TableFleld("game_id")private Integer gid;whereStrategy = FieldStrategy.NOT_EMPTY  (针对select语句)       //这个非空是指在生成 WHERE 条件时，只有当字段的值不为空（对于字符串类型来说，既不是 null 也不是空字符串 ""）时，该字段才会被包含在 WHERE 条件中。@TableField(whereStrategy = FieldStrategy.NOT_EMPTY,condition = SqlCondition.LIKE)非空+ 模糊查询updateStrategy = FieldStrategy.NOT_NULL(针对update语句)  @TableField(updateStrategy = FieldStrategy.NOT_NULL , update = "%s + 100")更新的时候,如果更新了当前属性,在当前属性原有值的基础上+100    

逻辑删除

物理删除(真删除):执行的是delete操作
逻辑删除(假删除):执行update操作,通过一个字段来表示数据是否删除了@TableLogic(value = "正常的值",delval = "删除的值")后续所有的查询中,都会自动加上 当前的条件

条件构建器

QueryWrapper<Room> queryWrapper = new QueryWrapper<>();
查询的条件构建器
后续的所有查询条件,都要在这里进行设置LambdaQueryWrapper<Room> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(Room::getRoomName,"大厅");
list(queryWrapper);
Lambda表示条件构建器
里面所有的方法,都和QueryWrapper是一模一样的
找列的时候,不需要写列名,只需要写属性名就可以了

查询

//单条查询
//roomService.getById(100);  //这种自带的根据ID查询的语句要在实体类当中@TableID出来那个是表的ID
LambdaQueryWrapper<Room> lambdaQueryWrapper = new LambdaQueryWrapper<>();
//预计查询条件查询出来的结果是1条,使用getOne 如果是多条,使用list
//lambdaQueryWrapper.eq(Room::getRoomId,10001);
lambdaQueryWrapper.like(Room::getRname,"大厅1212121212");
Room one = roomService.getOne(lambdaQueryWrapper);
//多条查询
List<Room> list = roomService.list(lambdaQueryWrapper);

• 等值查询

lambdaQueryWrapper.eq(Room::getRoomId,10001);

• 模糊查询

lambdaQueryWrapper.like(Room::getRname,"大厅");

• 多条件查询

        QueryWrapper<Room> queryWrapper = new QueryWrapper<>();queryWrapper.eq("room_speech_status",1).like("room_name","厅");queryWrapper.eq("room_push",1);List<Room> list = roomService.list(queryWrapper);

• 排序

        LambdaQueryWrapper<Room> queryWrapper = new LambdaQueryWrapper<>();queryWrapper.orderByDesc(Room::getRoomId);roomService.list(queryWrapper);

• 子查询

        LambdaQueryWrapper<SkillModel> queryWrapper = new LambdaQueryWrapper<>();queryWrapper.inSql(SkillModel::getGid,"select game_id from skill_game where game_name like '%黑%'");skillService.list(queryWrapper);

SELECT skill_id,uid,game_id AS gid,game_username,skill_price FROM skill WHERE (game_id IN (select game_id from skill_game where game_name like '%黑%'))

• apply

        LambdaQueryWrapper<Room> queryWrapper = new LambdaQueryWrapper<>();queryWrapper.apply("date_format(ctime,'%Y-%m-%d') = {0}","2025-01-02"); //ctime属性在数据库当中日期格式是  年月日 时分秒，在这里用apply根据年月日查询roomService.list(queryWrapper);