MyBatisPlus 第一天

数据库创建表

CREATE DATABASE `mybatis_plus` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; use `mybatis_plus`;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名 ',
`age` int(11) DEFAULT NULL COMMENT '年龄 ',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱 ',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加数据

INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, '[e-mail]'),
(2, 'Jack', 20, '[e-mail]'),
(3, 'Tom', 28, '[e-mail]'),
(4, 'Sandy', 21, '[e-mail]'),
(5, 'Billie', 24, '[e-mail]');

 

创建SpringBoot项目后进行以下操作

1 pom.xml中导入依赖

  <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency>
<!--    SpringBoot整合测试的Start--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!--        SpringBoot整合MyBatisPlus的依赖--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.1</version></dependency>
<!--    插件使用 为实体类提供set get以及相关方法    --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency>
<!--        mysql驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.30</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><scope>test</scope></dependency>

 补充: mysql 8.0和mysql5.0版本的区别: 1)mysql常用版本5.5 5.6 5.7以及8.0

                                                               2) 5.x的版本可以使用8.0的驱动 具有向上兼容  5.x的没                                                                        有 cj包,8.0有cj包(关系到驱动项和URL)

                                                               3) 你的电脑安装的是8.0的mysql 需要在URL后面加上时                                                                        区,5.x的版本不需要

 2 添加lombok的插件

 文件->设置->插件 搜索LomBok进行安装

 3  application.yml中配置数据源信息

spring:
# 配置数据源信息
datasource:
# 配置数据源类型
type: com.zaxxer.hikari.HikariDataSource
# 配置连接数据库信息
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf- 8&useSSL=false
username: root
password: 123456

4 在SpringBoot启动类中 记得加上@MapperScan的注解来扫描mapper包

5 创建Mapper接口

@Repository
//自己写的BaseMapper就是基础的CRUD
public interface UserMapper extends BaseMapper<User> {
}

6 创建实体类

@Data //lombok注解  提供所有set get方法
@AllArgsConstructor  //有参构造器方法
@NoArgsConstructor  //无参构造器方法
public class User {private Long id;private String name;private Integer age;private String email;}

关于LomBok注解的补充

三种方式使SpringBoot测试类正常运行

 

关于BaseMapper中方法的测试

插入

 @Testpublic void insert(){User user=new User(null,"张三",23,"[e-mail]");int count=userMapper.insert(user);System.out.println(user.getId());}

输出结果

此处的id是通过雪花算法自动生成的

如果想要主键正常生成 自增的效果可以改动两个地方

1）application.yml中  

global-config:db-config:# 配置MyBatis-Plus操作表的默认前缀table-prefix:# 配置MyBatis-Plus的主键策略id-type: auto

2）数据库中的表中，一定要勾选自动递增

 

删除

 //通过ID删除@Testpublic void deleteById(){int result = userMapper.deleteById(1);System.out.println(result);}//通过map删除@Testpublic void deleteByMap(){Map<String,Object> map=new HashMap<>();map.put("name","张三");map.put("email","[e-mail]");int result = userMapper.deleteByMap(map);System.out.println(result);}//ID批量删除@Testpublic void deleteIds(){List<Long> list=Arrays.asList(1L,2L,3L);int result=userMapper.deleteBatchIds(list);}

 修改

  @Testpublic void testUpdateById(){User user = new User(4L, "admin", 22, null);//UPDATE user SET name=?, age=? WHERE id=?int result = userMapper.updateById(user);System.out.println("受影响行数："+result);}

查询

  @Testpublic void testSelectById(){//根据id查询用户信息//SELECT id,name,age,email FROM user WHERE id=?User user = userMapper.selectById(4L);System.out.println(user);}@Testpublic void testSelectBatchIds(){//根据多个id查询多个用户信息//SELECT id,name,age,email FROM user WHERE id IN ( ? , ? )List<Long> idList = Arrays.asList(4L, 5L);List<User> list = userMapper.selectBatchIds(idList);list.forEach(System.out::println);}@Testpublic void testSelectByMap(){//通过map条件查询用户信息//SELECT id,name,age,email FROM user WHERE name = ? AND age = ?Map<String, Object> map = new HashMap<>();map.put("age", 22);map.put("name", "admin");List<User> list = userMapper.selectByMap(map);list.forEach(System.out::println);}

四种条件构造器:updatewrapper  querywrapper  lambdaquerywrapper  lambdaupdatewrapper

以下为示例

  @Autowiredprivate UserMapper userMapper;//测试selectList()用wrapper做条件构造器来查询@Testpublic void test1(){//查询用户名包含a，年龄在20到30之间,邮箱信息不为null的用户信息//创建条件构造器对象//==>  Preparing: SELECT uid AS id,user_name AS name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (user_name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)// 执行的sqlQueryWrapper<User> queryWrapper=new QueryWrapper<User>();queryWrapper.like("name","a").between("age",20,30).isNotNull("email");userMapper.selectList(queryWrapper).forEach(System.out::println);}//测试条件加上排序@Testpublic void test2(){//查询用户信息，按照年龄的降序排序  如年龄相同，则按照id升序排序//创建条件构造器对象//==>  Preparing: SELECT uid AS id,user_name AS name,age,email,is_deleted FROM user WHERE is_deleted=0 ORDER BY age DESC,uid ASC// 执行sqlQueryWrapper<User> queryWrapper=new QueryWrapper<User>();queryWrapper.orderByDesc("age").orderByAsc("id");userMapper.selectList(queryWrapper).forEach(System.out::println);}//使用wrapper进行删除@Testpublic void test3(){//删除有邮箱地址为null的用户信息//创建条件构造器对象//==>  Preparing: UPDATE user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)//        // 执行sqlQueryWrapper<User> queryWrapper=new QueryWrapper<User>();queryWrapper.isNull("email");userMapper.delete(queryWrapper);}@Testpublic void test4(){//修改将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改//注意2点  1.使用QueryWrapper<User>目的是查询符合条件的数据修改  2.使用userMapper的update方法去修改(user,wrapper)//参数1  修改的内容   参数2 查找需改数据的条件//==>  Preparing: UPDATE user SET user_name=?, email=? WHERE is_deleted=0 AND (age > ? ANDUser user=new User();user.setName("小明");user.setEmail("[e-mail]");QueryWrapper<User> queryWrapper=new QueryWrapper<User>();queryWrapper.gt("age",20).like("name","a").or().isNull("email");int result=userMapper.update(user,queryWrapper);}@Testpublic void test5(){//修改//将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改//条件构造器注意  AND和or的优先级以及使用   此时的AND和or需要加还要lambda表达式QueryWrapper<User> wrapper=new QueryWrapper<>();wrapper.like("user_name","a" ).and(i->i.gt("age", 20).or().isNull("email"));User user=new User();user.setName("小红");user.setEmail("[e-mail]");int update = userMapper.update(user, wrapper);System.out.println("满足条件修改的值个数："+update);}@Testpublic void test6(){
//        select查询指定的字段而不是所有字段QueryWrapper<User> queryWrapper=new QueryWrapper<User>();queryWrapper.select("name","age");List<Map<String,Object>> mapList=userMapper.selectMaps(queryWrapper);mapList.forEach(System.out::println);}@Testpublic void test7(){//查询id小于等100的用户信息QueryWrapper<User> queryWrapper=new QueryWrapper<User>();//首先使用insql来构建子查询语句queryWrapper.inSql("age","select age from user where age<20");//在使用userMapper的selectList查询结果List<User> list=userMapper.selectList(queryWrapper);}@Testpublic void test8(){//修改将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改UpdateWrapper<User> updateWrapper=new UpdateWrapper<User>();updateWrapper.gt("age",20).and(i->i.like("name","a")).or().isNull("email");updateWrapper.set("name","张三").set("email","qcby@2021");userMapper.update(null,updateWrapper);}/*** MP中的组装的条件情况* 实现前端用户选择条件然后进行判断* 满足加入到构造器  不满足不加入* 第一种方式：使用字符串工具类判断* sql* ==>  Preparing: SELECT uid AS id,user_name AS name,age,email,is_deleted FROM user* WHERE is_deleted=0 AND (age >= ? AND age <= ?* Parameters: 20(Integer), 30(Integer)*///第一种方式@Testpublic  void test9(){String username="";Integer start=20;Integer end=10;QueryWrapper<User> queryWrapper=new QueryWrapper<User>();if(com.baomidou.mybatisplus.core.toolkit.StringUtils.isNotBlank(username)){queryWrapper.like("name","username");}if(start!=null){queryWrapper.ge("age",start);}if(end!=null){queryWrapper.le("age",end);}List<User> list=userMapper.selectList(queryWrapper);}//第二种方式@Testpublic  void test10(){String username="";Integer start=20;Integer end=10;QueryWrapper<User> queryWrapper=new QueryWrapper<User>();queryWrapper.like(com.baomidou.mybatisplus.core.toolkit.StringUtils.isNotBlank(username),"name","username");queryWrapper.ge(start!=null,"age",start);queryWrapper.le(end!=null,"age",end);List<User> list=userMapper.selectList(queryWrapper);}/*** MP演示避免字段写错的方式* 可以根据当前实体类的属性找到对应的字段名* 使用LambdaQueryWrapper的构造器* sql* ==>  Preparing: SELECT uid AS id,user_name AS name,age,email,is_deleted FROM user* WHERE is_deleted=0 AND (user_name LIKE ? AND age >= ? AND age <= ?)*/@Testpublic  void test11(){String username="";Integer start=20;Integer end=10;LambdaQueryWrapper<User> lambdaQueryWrapper=new LambdaQueryWrapper<>();lambdaQueryWrapper.like(com.baomidou.mybatisplus.core.toolkit.StringUtils.isNotBlank(username),User::getName,"username").le(start!=null,User::getAge,start).ge(end!=null,User::getAge,end);List<User> list=userMapper.selectList(lambdaQueryWrapper);list.forEach(System.out::println);}/*** MP演示避免字段写错的方式* 可以根据当前实体类的属性找到对应的字段名* 使用LambdaUpdateWrapper的构造器* sql* ==>  Preparing: UPDATE user SET user_name=?,email=?* WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ?) OR email IS NULL)*/@Testpublic void test12(){LambdaUpdateWrapper<User> lambdaUpdateWrapper=new LambdaUpdateWrapper<>();lambdaUpdateWrapper.ge(User::getAge,20).and(i->i.like(User::getName,"a")).or().isNull(User::getEmail);lambdaUpdateWrapper.set(User::getName, "张亮").set(User::getEmail, "[e-mail]");int update = userMapper.update(null, lambdaUpdateWrapper);System.out.println(update);}

最后讲解下 @Mapper和@Repository的区别

相同点:都作用在mapper层接口 生成对象Bean 交给spring容器管理

不同点: @Mapper可以单独使用 不需要配置扫描地址  @Repository需要配置扫描地址 和@MapperScan联用 否则没有任何用处