MyBatis Plus入门学习

MyBatis Plus学习

1. 入门案例

1、建立maven工程导入依赖

	<!-- 引入mybatisplus依赖-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2</version>
 </dependency>

注:如果遇到maven仓库中有,但是导入不进去,在版本出爆红,则maven本地仓库中删除,重新下载即可。

2、建立实体类和数据库

实体类:

@Data
@TableName("user")
public class Account { 
    private Long id;
    private String username;
    private String password;
}

数据库:

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1271723566315397123 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'zhangsan', '123');
INSERT INTO `user` VALUES (2, 'lisi', '456');
INSERT INTO `user` VALUES (3, 'wangwu', '789');

3、Mapper接口

//@Component 如果使用component就要在springboot启动类处加上MapperScan
@Mapper
public interface UserMapper extends BaseMapper<Account> { 
	
}
@SpringBootApplication
//@MapperScan("com.liuzeyu.mapper")
public class MybatisplusApplication { 

    public static void main(String[] args) { 
        SpringApplication.run(MybatisplusApplication.class, args);
    }

}

4、application.yml

配置数据源和SQL日志输出

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT
    password: 809080
    username: root
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

注意:username,password不能写成data-username和data-password,否则连接不到数据库。

5、测试函数

@SpringBootTest
public class MapperTest { 
    @Autowired
    private UserMapper userMapper;
    @Test
    public void test01(){ 
        userMapper.selectList(null).forEach(account -> System.out.println(account));
    }
    @Test
    public void test02(){ 
        Account account = new Account();
        account.setUsername("lizueyu");
        account.setPassword("809080");
        userMapper.insert(account);
    }
}

2. MyBatis-plus常用注解

1、 @TableName

作用是当我们的实体类名和数据库表名不一致时,可以做到映射数据库表名、

@Data
@TableName("user")
public class Account { 
    private Long id;
    private String username;
    private String password;
}

2、@TableId

mybatis-plus-annotation-3.3.2.jar包下,是一个注解,作用于主键上面。

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.FIELD, ElementType.ANNOTATION_TYPE})
public @interface TableId { 
    String value() default "";

    IdType type() default IdType.NONE;
}

value:属性也是与数据库主键字段的映射。

其中IdType是一个枚举,可以为type取值

    AUTO(0),
    NONE(1),
    INPUT(2),
    ASSIGN_ID(3),
    ASSIGN_UUID(4),
    /** @deprecated */
    @Deprecated
    ID_WORKER(3),
    /** @deprecated */
    @Deprecated
    ID_WORKER_STR(3),
    /** @deprecated */
    @Deprecated
    UUID(4);

看似有8个,其实也就前面5个,后面3个已经不建议使用了。

描述
AUTO 雪花算法实现数据库自增
NONE MP set主键,雪花算法实现,没有自增
INPUT 需要开发者手动赋值,如果没有,雪花算法实现,自增
ASSIGN_ID MP 分配 ID,Integer,Long,String
ASSIGN_UUID MP 分配 UUID,String类型,也可手动赋值
  • AUTO
  1. 当type设置为AUTO
@Data
@TableName("user")
public class Account { 
    @TableId(type = IdType.AUTO,value = "id")
    private Long uid;
    private String username;
    private String password;
}
  1. 测试函数
    @Test
    public void test02(){ 
        Account account = new Account();
        account.setUsername("lizueyu222");
        account.setPassword("809080");
        userMapper.insert(account);
    }
  1. 运行结果:

可见主键ID是根据雪花算法得到的一连串数字,此时实体类的id要使用Long类型,否则会报错。

  • NONE
  1. 当type设置为NONE
@Data
@TableName("user")
public class Account { 
    @TableId(type = IdType.NONE,value = "id")
    private Long uid;
    private String username;
    private String password;
}

  1. 测试函数
@Test
public void test02(){ 
    Account account = new Account();
    account.setUsername("lizueyu222");
    account.setPassword("809080");
    userMapper.insert(account);
}
  1. 运行结果
  • INPUT
  1. 当type设置为INPUT
@Data
@TableName("user")
public class Account { 
    @TableId(type = IdType.INPUT,value = "id")
    private Long uid;
    private String username;
    private String password;
}
  1. 测试函数
@Test
public void test02(){ 
    Account account = new Account();
    account.setUsername("lizueyu222");
    account.setPassword("809080");
    userMapper.insert(account);
}
  1. 运行结果
  • ASSIGN_ID
  1. 当type设置为ASSIGN_ID
@Data
@TableName("user")
public class Account {
    @TableId(type = IdType.ASSIGN_ID,value = "id")
    private Long uid;
    private String username;
    private String password;
}
  1. 测试函数
@Test
public void test02(){ 
    Account account = new Account();
    account.setUsername("lizueyu555");
    account.setPassword("809080");
    userMapper.insert(account);
}
  1. 运行结果

和NONE类似,也可以自己手动赋值,但ASSIGN_ID支持的类型更多,有Integer,Long,String。

  • ASSIGN_UUID
  1. 当type设置为ASSIGN_UUID
@Data
@TableName("user")
public class Account { 
    @TableId(type = IdType.ASSIGN_ID,value = "id")
    private Long uid;
    private String username;
    private String password;
}
  1. 测试函数
@Test
public void test02(){ 
    Account account = new Account();
    account.setUsername("lizueyu555");
    account.setPassword("809080");
    userMapper.insert(account);
}
  1. 运行结果

出现了这个问题是因为类型不匹配,数组库的主键为bitint类型,不会支持String类型,所有要使用ASSIGN_UUID就必须先把主键类型改为varchar(1024)

需要把主键自增勾取消掉,因为String是不支持自增。

3、@TableField

  1. value:非主键字段映射实体类属性

  2. exist:表示是否实体类为数据库字段,不是的话标注false

@Data
@TableName("user")
public class Account { 
    @TableId(type = IdType.NONE,value = "id")
    private Long uid;
    private String username;

    @TableField(exist = false)
    private String password;
}

此时结果集将不会封装password属性

@Data
@TableName("user")
public class Account { 
    @TableId(type = IdType.NONE,value = "id")
    private Long uid;
    private String username;

    @TableField(exist = false)
    private String password;

    @TableField(exist = true)
    private String nickname;
}

此时将爆出异常,因为数据库表中并未存在nickname字段

3. select:表示是否查询该字段,默认为true
4. fill表示给数据库字段设置自动填充,常见的自动填充字段有create_Time,update_Time

如给实体类添加两个字段createTime,updateTime

@Data
@TableName("user")
public class Account { 
    @TableId(type = IdType.NONE,value = "id")
    private Long uid;
    private String username;
    private String password;

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

创建一个自动填充工具类:

/** * 自动填充处理器 */
@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);
    }
}

为数据库添加字段

类型均为datetime,这样可以精确到秒,其中createTime对自动与create_Time做映射

测试类:

   @Test
    public void test02(){ 
        Account account = new Account();
        account.setUid(3333L);
        account.setUsername("lizueyu444");
        account.setPassword("809080");
        userMapper.insert(account);
    }

    @Test
    public void test03(){ 
        Account account = userMapper.selectById(3333);
        account.setUsername("xxxxxxxxxxx");
        userMapper.updateById(account);
    }

运行结果:

4、@Version

注解@Version的作用利用了乐观锁解决多线程并发。

# 线程1:
update user set username = liuzeyu, version =  x where id = 3,version = 1;
# 线程2:
update user set username = duyangting, version x where id = 3,version = 1;

此时,线程1如果先执行,会将version改为2,然后线程2的条件就失效了,解决了并发数据的一致性问题。

为数据库添加字段version

添加乐观锁拦截器:

@Configuration
public class MyOptimisticLockConfig { 
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){ 
        return new OptimisticLockerInterceptor();
    }
}

测试函数:

    @Test
    public void test04(){ 
        //线程1
        Account account = userMapper.selectById(3);
        //线程2
        Account account1 = userMapper.selectById(3);
        account.setUsername("xyz");
        account1.setUsername("opq");
        userMapper.updateById(account);
    }

运行结果:

可见此时version并没有改成3,实现了乐观锁解决并发的特点。

回顾乐观锁和悲观锁

  1. 乐观锁:总是以乐观心态,对数据也是安全也是很乐观的,每次去拿数据的时候都认为别人不会加锁,所以也都不会上锁,只要在更新的时候会判读数据有没有被修改过,所以乐观锁使用与多读的场景。

  2. 悲观锁:总是悲观的,对操作的数据也是悲观的,每次去拿数据的时候都认为有人会去修改它,所以都会加上锁,在此期间,其它线程若想访问,就必须进入堵塞状态,多用于更新数据的场合,这样才可以保证数据的安全性。例如:sychronized或同步代码块用的都是悲观锁。

5、@EnumeValue

@EnumeValue:做枚举类型与数据库字段名的映射关系。

  1. 实现方式1:使用配置

实体类添加枚举类型:

private StatusEnum status;

枚举类:

public enum  StatusEnum { 

    Work(1,"工作"),
    REST(0,"休息");

     StatusEnum(Integer code, String msg) { 
        this.code = code;
        this.msg = msg;
    }

    @EnumValue
    private Integer code;
    private String msg;
}

数据库添加status int字段:

测试函数:

@Test
public void test01(){ 
	userMapper.selectList(null).forEach(account -> System.out.println(account));
}

运行结果:

2. 实现方式2:使用IEnum接口

为数据库添加age字段和实体类的age对应上

private AgeEnum age;
public enum AgeEnum implements IEnum<Integer> { 

    ONE(1,"一岁"),TWO(2,"两岁"),THREE(3,"三岁");
    private Integer code;
    private String msg;

    AgeEnum(Integer code, String msg) { 
        this.code = code;
        this.msg = msg;
    }

    @Override
    public Integer getValue() { 
        return code;
    }
}

测试函数:

@Test
public void test01(){ 
	userMapper.selectList(null).forEach(account -> System.out.println(account));
}

运行结果:

6、@TableLoginc

@TableLoginc作用是可以 “假删除”,怎么说呢,就可以可以做到数据库中有数据但是查不出来。

  1. 数据表添加字段deleted

  1. 实体类添加属性
@TableLogic
private Integer deleted;
  1. yam配置
  global-config:
    db-config:
      logic-not-delete-value: 0 # 没有被删除的时候为
      logic-delete-value: 1 # 已经被删除了
  1. 测试函数
@Test
public void test11(){ 
	userMapper.deleteById(2);
}
  1. 测试结果:

可见此时deleted在数据库中被修改为1了,2这条数据字段就是被标记为 “删除”,已经不能被查询出来了

3. MyBatis-plus CRUD操作

1、 C (create)创建

@Test
public void test02(){ 
    Account account = new Account();
    account.setUid(3333L);
    account.setUsername("lizueyu444");
    account.setPassword("809080");
    userMapper.insert(account);
}

2、 R (retrieval)查询

//1.无条件查询
	//userMapper.selectList(null).forEach(account -> System.out.println(account));
//2.添加普通条件查询
    QueryWrapper wrapper = new QueryWrapper();
    //wrapper.eq("id",1);  id = 1
    //wrapper.lt("age",5);  //小于 le:小于等于
    //wrapper.gt("age",2);  //大于 ge:大于等于
    //        Map<String,Object> map = new HashMap<>();
    //        map.put("age",2);
    //        map.put("id",2);
    //        wrapper.allEq(map);  //取并集
    //        userMapper.selectList(wrapper).forEach(account-> 				System.out.println(account));
//3.模糊查询
    //wrapper.like("username","wang");
    //wrapper.likeLeft("username","liu");  %liu:尾部有liu
    //wrapper.likeLeft("username","wang");  wang%:开头wang
    //userMapper.selectList(wrapper).forEach(account-> System.out.println(account));

//4.通过map查找,map只能做等值操作
    //       Map<String,Object> map = new HashMap<>();
    //       map.put("age",2);
    //       map.put("id",2);
    //       userMapper.selectByMap(map).forEach(account-> System.out.println(account));

//5.将查询结果封装到map集合里
    //userMapper.selectMaps(null).forEach(account-> System.out.println(account));

//6.in SQL查询
    wrapper.inSql("age","select age from user where age > 1");
    wrapper.inSql("id","select id from user where id < 4");
    //userMapper.selectList(wrapper).forEach(account-> System.out.println(account));
    //SQL拼接后:SELECT id AS uid,username,password,create_time,update_time,version,status,age FROM user
    // WHERE (age IN (select age from user where age > 1) AND id IN (select id from user where id < 4))

//7.分页查询
    Page<Account> page = new Page<>(1,2);  //第1页2条数据
    Page<Account> result = userMapper.selectPage(page, null);
    //        System.out.println(result.getSize());
    //        System.out.println(result.getTotal());
    //        result.getRecords().forEach(account -> System.out.println(account));
    //将查询结果封装成map集合
    Page<Map<String,Object>> pageMap = new Page<>(2,2);
    //userMapper.selectMapsPage(pageMap,null).getRecords().forEach(accout-> System.out.println(accout));

3、 U (update)更新

@Test
public void test06(){ 
    Account account = userMapper.selectById(5);
    account.setUsername("liuzeyu");
    userMapper.updateById(account);

    QueryWrapper wrapper = new QueryWrapper();
    wrapper.eq("username","liuzeyu");
    account.setUsername("wangwu ");
    userMapper.update(account,wrapper);
}

4 、D (delete)删除

@Test
public void test05(){ 
    Map<String,Object> map = new HashMap<>();
    map.put("id",3);
    System.out.println(userMapper.deleteByMap(map));
}

4. MyBatis-plus 多表关联查询

  1. 创建产品表,添加外键

    添加user_id为user表外键,注意的是user_id和user表的id必须类型和长度一致,否则将创建外键失败!

  2. 创建产品VO类

@Data
public class ProductVO { 

    private String category;
    private Integer count;
    private String description;
    private String userId;
    private String username;
}
  1. UserMapper自定义SQL
@Select("select product.*,user.username from user,product " +
"where user.id = product.user_id and id=#{id}")
public List<ProductVO> listProduct(Integer id);
  1. 测试函数
@Test
public void test10(){ 
    List<ProductVO> productVOS = userMapper.listProduct(3);
    System.out.println(productVOS;
}
  1. 测试结果

5. MyBatis-plus自动生成

  1. 建立springboot项目导入MyBatis-plus自动生成依赖
<!-- 添加mybatis-plus自动生成-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity</artifactId>
    <version>1.7</version>
</dependency>
</dependencies>
  1. 编写生产类
/** * 自动生成类 */
public class Main { 
    public static void main(String[] args) { 
        //1.创建autoGenerator
        AutoGenerator autoGenerator = new AutoGenerator();
        //2.配置数据源
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver");
        dataSourceConfig.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&charactEncoding=UTF-8&serverTimezone=UTC");
        dataSourceConfig.setUsername("root");
        dataSourceConfig.setPassword("809080");
        autoGenerator.setDataSource(dataSourceConfig);
        //3.全局配置
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setAuthor("liuzeyu12a");
        globalConfig.setOpen(false);
        globalConfig.setServiceImplName("%sService");
        globalConfig.setOutputDir(System.getProperty("user.dir")+"/src/main/java");//生成地方
        autoGenerator.setGlobalConfig(globalConfig);
        //4.包信息
        PackageConfig packageConfig = new PackageConfig();
        packageConfig.setEntity("entity");
        packageConfig.setModuleName("generator");
        packageConfig.setService("service");
        packageConfig.setServiceImpl("service.impl");
        packageConfig.setMapper("mapper");
        packageConfig.setController("controller");
        packageConfig.setParent("com.liuzeyu");
        //5.配置策略
        StrategyConfig strategyConfig = new StrategyConfig();
        strategyConfig.setEntityLombokModel(true); //Lombok
        strategyConfig.setInclude("city");   //生成部分表
        strategyConfig.setNaming(NamingStrategy.underline_to_camel); //驼峰转换
        strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel); //驼峰转换
        autoGenerator.setStrategy(strategyConfig);

        autoGenerator.execute();  //执行自动生成
    }
}
  1. 运算结果
  2. yml的配置
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT
    password: 809080
    username: root
  thymeleaf:
    suffix: .html
    prefix: classpath:/templates/
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  1. 编写controller层
@Controller
@RequestMapping("/city")
public class CityController { 

    @Autowired
    private ICityService service;
    @GetMapping("findAll")
    public ModelAndView findAll(){ 
        ModelAndView modelAndView = new ModelAndView();
        modelAndView.setViewName("index");
        modelAndView.addObject("list",service.list());
        return modelAndView;
    }
}
  1. 在templates下新建index.html
<!DOCTYPE html>
<html lang="en">
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

<table>
    <tr th:each="item : ${list}">
        <td th:text="${item.cityId}"></td>
        <td th:text="${item.cityName}"></td>
        <td th:text="${item.countryId}"></td>
    </tr>
</table>
</body>
</html>
  1. 测试从后台接收到数据

参考B站:https://www.bilibili.com/video/BV1yA411t782

页面下部广告