Mybatis-Plus中Mapper CRUD接口

- 日理万妓 2022-10-08 12:26 181阅读 0赞

### Mapper CRUD接口 ###

*  一、CRUD接口
 *   *  1.1 Insert
     *  1.2 Delete
     *  1.3 Update
     *  1.4 Select
 *  二、测试
 *   *  2.1 pom.xml
     *  2.2 Employee.java
     *  2.3 MybatisConfig.java
     *  2.4 EmployeeMapper.java
     *  2.5 application.yml
     *  2.6 测试
     *   *  2.6.1 插入测试
         *  2.6.2 更新测试
         *  2.6.3 删除测试
         *  2.6.4 查询测试

# 一、CRUD接口 #

> 说明:
> 
>  *  通用 CRUD 封装BaseMapper (opens new window)接口，为 Mybatis-Plus启动时自动解析实体表关系映射转换为 Mybatis 内部对象注入容器
>  *  泛型 T 为任意实体对象
>  *  参数 Serializable为任意类型主键 Mybatis-Plus 不推荐使用复合主键约定每一张表都有自己的唯一 id 主键
>  *  对象 Wrapper 为 条件构造器

## 1.1 Insert ##

// 插入一条记录
    int insert(T entity);

参数说明

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T</td> 
   <td>entity</td> 
   <td>实体对象</td> 
  </tr> 
 </tbody> 
</table>

## 1.2 Delete ##

// 根据 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);

参数说明

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>参数名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Wrapper</td> 
   <td>wrapper</td> 
   <td>实体对象封装操作类(可以为null)</td> 
  </tr> 
  <tr> 
   <td>Collection&lt;? extends Serializable&gt;</td> 
   <td>idList</td> 
   <td>主键ID列表(不能为 null 以及 empty)</td> 
  </tr> 
  <tr> 
   <td>Serializable</td> 
   <td>id</td> 
   <td>主键ID</td> 
  </tr> 
  <tr> 
   <td>Map&lt;String,Object&gt;</td> 
   <td>columnMap</td> 
   <td>表字段map对象</td> 
  </tr> 
 </tbody> 
</table>

## 1.3 Update ##

// 根据 whereWrapper 条件，更新记录
    int update(@Param(Constants.ENTITY) T updateEntity, @Param(Constants.WRAPPER) Wrapper<T> whereWrapper);
    // 根据 ID 修改
    int updateById(@Param(Constants.ENTITY) T entity);

参数说明

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>参数名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T</td> 
   <td>entity</td> 
   <td>实体对象(set条件值，可为null)</td> 
  </tr> 
  <tr> 
   <td>Wrapper</td> 
   <td>updateWrapper</td> 
   <td>实体对象封装操作类(可以为null,里面的entity用于生成where语句)</td> 
  </tr> 
 </tbody> 
</table>

## 1.4 Select ##

// 根据 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);

参数说明

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>参数名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Serializable</td> 
   <td>id</td> 
   <td>主键ID</td> 
  </tr> 
  <tr> 
   <td>Wrapper</td> 
   <td>queryWrapper</td> 
   <td>实体对象封装操作类(可以为null)</td> 
  </tr> 
  <tr> 
   <td>Collection&lt;? extends Serializable&gt;</td> 
   <td>idList</td> 
   <td>主键ID列表(不能为null以及empty)</td> 
  </tr> 
  <tr> 
   <td>Map&lt;String,Object&gt;</td> 
   <td>columnMap</td> 
   <td>表字段map对象</td> 
  </tr> 
  <tr> 
   <td>IPage</td> 
   <td>page</td> 
   <td>分页查询条件(可以为RowBounds.DEFAULT)</td> 
  </tr> 
 </tbody> 
</table>

# 二、测试 #

## 2.1 pom.xml ##

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.5.1</version>
            <relativePath/> 
        </parent>
        <groupId>com.baomidou.mybatisplus</groupId>
        <artifactId>mybatisplus01</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>mybatisplus01</name>
        <description>Demo project for Spring Boot</description>
        <properties>
            <java.version>1.8</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.4.3.1</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.2.6</version>
            </dependency>
    
    
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                    <configuration>
                        <excludes>
                            <exclude>
                                <groupId>org.projectlombok</groupId>
                                <artifactId>lombok</artifactId>
                            </exclude>
                        </excludes>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
    </project>

## 2.2 Employee.java ##

package com.baomidou.mybatisplus.bean;
    
    import com.baomidou.mybatisplus.annotation.IdType;
    import com.baomidou.mybatisplus.annotation.TableField;
    import com.baomidou.mybatisplus.annotation.TableId;
    import com.baomidou.mybatisplus.annotation.TableName;
    import lombok.Data;
    
    /** * @TableName * value:指定当前实体类所对应的表名 */
    @Data
    @TableName(value = "tbl_employee")
    public class Employee { 
        /** * @TableId: * value:指定表中的主键列的列名，如果实体属性名和列名一致，可以省略不指定 * type:指定主键策略 */
        @TableId(value = "id",type = IdType.AUTO)
        private Integer id;         //int
    
        @TableField(value = "last_name")
        private String lastName;
    
        @TableField(value = "email")
        private String email;
    
        @TableField(value = "gender")
        private Integer gender;
    
        @TableField(value = "age")
        private Integer age;
    
    // @TableField(exist = false)
    // private Double salary;
    }

## 2.3 MybatisConfig.java ##

package com.baomidou.mybatisplus.config;
    
    import com.baomidou.mybatisplus.annotation.DbType;
    import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
    import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    @Configuration
    public class MybatisPlusConfig { 
    
        @Bean
        public MybatisPlusInterceptor mybatisPlusInterceptor(){ 
            MybatisPlusInterceptor interceptor=new MybatisPlusInterceptor();
            interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
            return interceptor;
        }
    }

## 2.4 EmployeeMapper.java ##

package com.baomidou.mybatisplus.mapper;
    
    import com.baomidou.mybatisplus.bean.Employee;
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    
    public interface EmployeeMapper extends BaseMapper<Employee> { 
    
    }

## 2.5 application.yml ##

# DataSource Config
    spring:
      datasource:
        url: jdbc:mysql://localhost:3306/mp?&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
    # mybatis-plus配置控制台打印sql语句:
    mybatis-plus:
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

## 2.6 测试 ##

数据库数据如下：  
![在这里插入图片描述][202106211650505.png]

### 2.6.1 插入测试 ###

@Test
        public void testCommonInsert(){ 
            //初始化Eployee对象
            Employee employee=new Employee();
            employee.setLastName("MP");
            employee.setEmail("mp@guigu.com");
            employee.setGender(1);
            employee.setAge(22);
            //插入到数据库
            //insert方法在插入时，会根据实体类的每个属性进行非空判断，只有非空的属性对应
            //的字段才会出现到sql语句中
            Integer result = employeeMapper.insert(employee);
            System.out.println("result:"+result);
    
            //获取当前数据在数据库中的主键值
            Integer key=employee.getId();
            System.out.println("key="+key);
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70]

> 截图中得transactional这些错误是我没开启事务，先忽略这个问题。

![在这里插入图片描述][20210621170539720.png]

### 2.6.2 更新测试 ###

@Test
        public void testCommonUpdate(){ 
            //初始化修改对象
            Employee employee=new Employee();
            employee.setId(6);
            employee.setLastName("汤姆老师");
            employee.setEmail("tom@sina.com");
            employee.setGender(1);
            employee.setAge(22);
    
            Integer result = employeeMapper.updateById(employee);
            System.out.println("result="+result);
        }

![在这里插入图片描述][20210621170709272.png]  
![在这里插入图片描述][20210621170717235.png]

### 2.6.3 删除测试 ###

(1) 根据id进行删除

Integer result = employeeMapper.deleteById(8);
            System.out.println("result="+result);

![在这里插入图片描述][20210621170935648.png]  
![在这里插入图片描述][20210621170944316.png]  
(2)根据条件进行删除

Map<String,Object> columnMap=new HashMap<>();
            columnMap.put("last_name","Tom");
            columnMap.put("email","tom@atguigu.com");
            Integer result = employeeMapper.deleteByMap(columnMap);
            System.out.println("result="+result);

![在这里插入图片描述][20210621171132375.png]  
![在这里插入图片描述][20210621171139388.png]

### 2.6.4 查询测试 ###

(1)查询所有

@Test
        public void testEmployee(){ 
            List<Employee> employeeList=employeeMapper.selectList(null);
            employeeList.forEach(System.out::println);
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][20210621171242267.png]  
（2）通过id查询

Employee employee = employeeMapper.selectById(6);
            System.out.println(employee);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 2]  
（3）通过多个列查询(selectOne若查出多条数据会报错)

QueryWrapper<Employee> employeeQueryWrapper= Wrappers.query();
            employeeQueryWrapper.eq("id",6);
            employeeQueryWrapper.eq("last_name","汤姆老师");
            employeeQueryWrapper.eq("gender",1);
            Employee employee = employeeMapper.selectOne(employeeQueryWrapper);
            System.out.println(employee);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 3]  
(4)通过多个id进行查询

List<Integer> idList=new ArrayList<>();
            idList.add(2);
            idList.add(5);
            idList.add(6);
            List<Employee> employeeList = employeeMapper.selectBatchIds(idList);
            employeeList.forEach(System.out::println);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 4]  
(5)通过Map封装条件查询

Map<String,Object> columnMap=new HashMap<>();
            columnMap.put("last_name","汤姆老师");
            columnMap.put("gender",1);
            List<Employee> employeeList = employeeMapper.selectByMap(columnMap);
            employeeList.forEach(System.out::println);

![在这里插入图片描述][20210621173211633.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 5]

[202106211650505.png]: /images/20221005/db03738ac42b49d4ab64a73beb53d398.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70]: /images/20221005/322633aa66e1427c8c54af83059c31e1.png
[20210621170539720.png]: /images/20221005/2ba944b69cc2435098cd7d46de25c571.png
[20210621170709272.png]: /images/20221005/727e885ff44d41cea8884093d7503392.png
[20210621170717235.png]: /images/20221005/5726e4d9a66e4181b716c5497a102da5.png
[20210621170935648.png]: /images/20221005/5f2307f18c8d4a8c8b7d5e78a6f06b46.png
[20210621170944316.png]: /images/20221005/27b1042b5b5c470f9d32ba30876d719f.png
[20210621171132375.png]: /images/20221005/a1d1991ea33a4aefb9b21ac98f4f2e78.png
[20210621171139388.png]: /images/20221005/c3a59c0726af491f93bcf98b2376be2d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 1]: /images/20221005/7aaf3411a7fe41c69ca3ed64ba14d6cf.png
[20210621171242267.png]: /images/20221005/ed38d852ef8c4ea681f2af511dc44bba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 2]: /images/20221005/2df875b8a66c4847a01e0cd2b9014ca4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 3]: /images/20221005/29fe580cf5e741d3b30c4b0eac261ef5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 4]: /images/20221005/bc893095d39747d183aa664a60dd5d9d.png
[20210621173211633.png]: /images/20221005/0b5278f3c7404c12b81beba027b46ef3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 5]: /images/20221005/53409f7609474cc2b35a2602a4841f16.png