MyBatisPlus笔记01 CRUD
coconutnut
 2020-04-21 11:38:49      

https://www.bilibili.com/video/BV1rE41197jR

p1~25

数据库准备

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建测试表
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `user_name` varchar(20) NOT NULL COMMENT '用户名', `password` varchar(20) NOT NULL COMMENT '密码',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 插入测试数据
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('1', 'zhangsan', '123456', '张三', '18', 'test1@itcast.cn');
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('2', 'lisi', '123456', '李四', '20', 'test2@itcast.cn');
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('3', 'wangwu', '123456', '王五', '28', 'test3@itcast.cn');
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('4', 'zhaoliu', '123456', '赵六', '21', 'test4@itcast.cn');
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('5', 'sunqi', '123456', '孙七', '24', 'test5@itcast.cn');

创建工程

导入依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<dependencies>
  <!--mybatis-plus的springboot支持-->
  <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.1</version>
  </dependency>
  <!--mysql驱动-->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
  </dependency>
  <!--简化bean代码的工具包-->
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
  </dependency>

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    <exclusions>
      <exclusion>
        <groupId>org.junit.vintage</groupId>
        <artifactId>junit-vintage-engine</artifactId>
      </exclusion>
    </exclusions>
  </dependency>
</dependencies>

配置文件

log4j.properties

1
2
3
4
log4j.rootLogger=DEBUG,A1
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=[%t] [%c]-[%p] %m%n

application.properties

1
2
3
4
5
spring.application.name = itcast-mp-springboot
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/groot? useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=iamgroot

实体类

使用lombok注解生成get、set方法

使用MybatisPlus注解指定表名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.coconutnut.demo.pojo;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("tb_user")
public class User {
  	@TableId(type = IdType.AUTO)
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String email;
}

用@TableId指定id的增长策略

@TableField

可解决的问题：

  1. 对象中的属性名和字段名不一致的问题(非驼峰)

    1
    2
    @TableField(value = "email")
    private String mail;

  2. 对象中的属性字段在表中不存在的问题

    1
    2
    @TableField(exist = false)
    private String address;

  3. 查询时不返回该字段的值

    1
    2
    @TableField(select = false)
    private String password;

Mapper接口

1
2
3
4
5
6
7
package com.coconutnut.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.coconutnut.demo.pojo.User;

public interface UserMapper extends BaseMapper<User> {
}

SpringBoot启动类

使用@MapperScan设置mapper接口的扫描包

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com.coconutnut.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.coconutnut.demo.mapper") 
@SpringBootApplication
public class DemoApplication {

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

}

项目结构

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.coconutnut.demo;

import com.coconutnut.demo.mapper.UserMapper;
import com.coconutnut.demo.pojo.User;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
class DemoApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testSelect() {
        List<User> userList = userMapper.selectList(null);
        for (User user : userList) {
            System.out.println(user);
        }
    }

}

输出

DEBUG 依赖重复

1
SLF4J: Class path contains multiple SLF4J bindings.

删除依赖中的

1
2
3
4
<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-log4j12</artifactId>
</dependency>

CRUD

查看UserMapper继承的BaseMapper源码

CRUD方法都有了

官网文档

https://mybatis.plus/guide/crud-interface.html#mapper-crud-%E6%8E%A5%E5%8F%A3

直接测试

插入

1
2
// 插入一条记录
int insert(T entity);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.coconutnut.demo;

import com.coconutnut.demo.mapper.UserMapper;
import com.coconutnut.demo.pojo.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestUserMapper {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testInsert(){
        User user = new User();
        user.setAge(20);
        user.setUserName("zhangsi");
        user.setName("张四");
        user.setEmail("zhangsi@163.com");
        user.setPassword("123456");

        int result = this.userMapper.insert(user); // 返回的result是受影响的行数，并不是自增后的id
        System.out.println("result = " + result);
        System.out.println(user.getId()); // 自增后的id会回填到对象中
    }
}

运行后，数据库变化

输出

更新

1
2
3
4
// 根据 whereEntity 条件，更新记录
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);
// 根据 ID 修改
int updateById(@Param(Constants.ENTITY) T entity);

根据id更新

1
2
3
4
5
6
7
8
@Test
public void testUpdateById() {
  User user = new User();
  user.setId(1L);
  user.setAge(21);
  //根据id更新，更新不为null的字段
  this.userMapper.updateById(user);
}

执行后

根据条件更新

QueryWrapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void testUpdate() {
  User user = new User();
  user.setAge(22);

  // 更新的条件
  QueryWrapper<User> wrapper = new QueryWrapper<>();
  // wrapper.eq("id", 6);
  wrapper.eq("user_name","zhangsan");

  // 执行更新操作
  int result = this.userMapper.update(user, wrapper);
  System.out.println("result = " + result);
}

wrapper中设置属性用的是mysql表中的名称

执行后

UpdateWrapper

1
2
3
4
5
6
7
8
9
10
@Test
public void testUpdate2() {
  //更新的条件以及字段
  UpdateWrapper<User> wrapper = new UpdateWrapper<>();
  wrapper.eq("id", 6).set("age", 23);

  //执行更新操作
  int result = this.userMapper.update(null, wrapper);
  System.out.println("result = " + result);
}

执行后

删除

1
2
3
4
5
6
7
8
// 根据 entity 条件，删除记录
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);
// 删除（根据ID 批量删除）
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
// 根据 ID 删除
int deleteById(Serializable id);
// 根据 columnMap 条件，删除记录
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

deleteById

1
2
3
4
5
@Test
public void testDeleteById() {
  int result = this.userMapper.deleteById(6L);
  System.out.println("result = " + result);
}

执行后

deleteByMap

1
2
3
4
5
6
7
8
9
10
@Test
public void testDeleteByMap() {
  // 将columnMap中的元素设置为删除的条件，多个之间为and关系
  Map<String, Object> columnMap = new HashMap<>();
  columnMap.put("age",20);
  columnMap.put("name","张三");

  int result = this.userMapper.deleteByMap(columnMap);
  System.out.println("result = " + result);
}

执行后

没有符合条件的

delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
public void testDelete() {
	// 用法1
//        QueryWrapper<User> wrapper = new QueryWrapper<>();
//        wrapper.eq("user_name","zhangsan");
  
  // 用法2
  User user = new User();
  user.setAge(20);
  user.setName("张三");
  // 将实体对象进行包装，包装为操作条件
  QueryWrapper<User> wrapper = new QueryWrapper<>(user);

  int result = this.userMapper.delete(wrapper);
  System.out.println("result = " + result);
}

推荐使用用法2

deleteByIds

1
2
3
4
5
@Test
public void testDeleteByIds() {
  int result = this.userMapper.deleteBatchIds(Arrays.asList(1L,10L,20L)); 
  System.out.println("result = " + result);
}

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 根据 ID 查询
T selectById(Serializable id);
// 根据 entity 条件，查询一条记录
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

// 查询（根据ID 批量查询）
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
// 根据 entity 条件，查询全部记录
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// 查询（根据 columnMap 条件）
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
// 根据 Wrapper 条件，查询全部记录
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// 根据 Wrapper 条件，查询全部记录。注意： 只返回第一个字段的值
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

// 根据 entity 条件，查询全部记录（并翻页）
IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// 根据 Wrapper 条件，查询全部记录（并翻页）
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// 根据 Wrapper 条件，查询总记录数
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

selectById

1
2
3
4
5
@Test
public void testSelectById() { 
  User user = this.userMapper.selectById(2L);
  System.out.println("result = " + user);
}

执行后

没查到为null

selectOne

不存在返回null

查到多条报错

1
2
3
4
5
6
7
@Test
public void testSelectOne() {
  QueryWrapper<User> wrapper = new QueryWrapper<User>();
  wrapper.eq("name", "李四");
  User user = this.userMapper.selectOne(wrapper);
  System.out.println(user);
}

输出

1
User(id=2, userName=lisi, password=123456, name=李四, age=20, email=test2@itcast.cn)

selectBatchIds

1
2
3
4
5
6
7
@Test
public void testSelectBatchIds() {
  List<User> users = this.userMapper.selectBatchIds(Arrays.asList(2L, 3L, 100L));
  for (User user : users) {
    System.out.println(user);
  }
}

执行后

selectCount

1
2
3
4
5
6
7
@Test
public void testSelectCount() {
  QueryWrapper<User> wrapper = new QueryWrapper<User>();
  wrapper.gt("age", 23); // 年龄大于23岁
  Integer count = this.userMapper.selectCount(wrapper);
  System.out.println("count = " + count);
}

输出

1
count = 2

selectList

1
2
3
4
5
6
7
8
9
10
@Test
public void testSelectList() {
  QueryWrapper<User> wrapper = new QueryWrapper<User>();
  wrapper.gt("age", 23); // 年龄大于23岁
  //根据条件查询数据
  List<User> users = this.userMapper.selectList(wrapper);
  for (User user : users) {
    System.out.println("user = " + user);
  }
}

执行后

selectPage

配置分页插件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.coconutnut.demo.mapper") // 设置mapper接口的扫描包
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
    
}

有了这个配置类之后，DemoApplication中的@MapperScan可以去掉

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void testSelectPage() {
  QueryWrapper<User> wrapper = new QueryWrapper<User>();
  wrapper.gt("age", 20); // 年龄大于20岁
  Page<User> page = new Page<>(2,2);
  IPage<User> iPage = this.userMapper.selectPage(page, wrapper);

  System.out.println("数据总条数:" + iPage.getTotal());
  System.out.println("总页数:" + iPage.getPages());
  List<User> users = iPage.getRecords();
  for (User user : users) {
    System.out.println("user = " + user);
  }
}

执行后