MyBatisPlus系列三：增删改查

阳光穿透心脏的1/2处 2022-04-25 04:40 278阅读 0赞

创建Mapper接口

/**
     * Mapper接口
     * 基于Mybatis:  在Mapper接口中编写CRUD相关的方法  提供Mapper接口所对应的SQL映射文件 以及 方法对应的SQL语句.
     * 基于MP:  让XxxMapper接口继承 BaseMapper接口即可.
     * 		   BaseMapper<T> : 泛型指定的就是当前Mapper接口所操作的实体类类型
     */
    public interface EmployeeMapper extends BaseMapper<Employee> {
    
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70]  
1、新增测试：

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

控制台输出：

insert方法：
     Preparing: INSERT INTO tbl_employee ( last_name, email, gender ) VALUES ( ?, ?, ? )    
     Parameters: MP(String), mp@atguigu.com(String), 1(Integer)
     Updates: 1
     insertAllColumn方法：
     Preparing: INSERT INTO tbl_employee ( last_name,email,gender,age ) VALUES ( ?,?,?,? )   
     Parameters: MP(String), mp@atguigu.com(String), 1(Integer), null 
     Updates: 1

报错1：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: Could not set property 'id' of 'class com.atguigu.mp.beans.Employee' with value '1123462688618409986' Cause: java.lang.IllegalArgumentException: argument type mismatch

原因：没有指定主键生成策略  
解决办法：

/**
     * @TableId:
     * 	 value: 指定表中的主键列的列名， 如果实体属性名与列名一致，可以省略不指定.
     *   type: 指定主键策略.
     */
    @TableId(value="id" , type =IdType.AUTO)
    private Integer id ;

报错2：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mybatisplus.employee' doesn't exist

原因：MybatisPlus会默认使用实体类的类名到数据中找对应的表employee  
解决办法：

/**
     * MybatisPlus会默认使用实体类的类名到数据中找对应的表.
     */
    @TableName(value="tbl_employee")
    public class Employee {

也可以在spring配置文件中配置MybatisPlus的全局策略

**@TableField注解**：

/**
     * 可以不开启驼峰命名。用@TableField注解指定属性对应的数据库字段名
     */
    @TableField(value = "last_name")
    private String  lastName;
    private String  email ;
    private Integer gender;
    private Integer age ;
    /**
     * 指定属性是否在数据库有对应的字段。默认是true
     * 设置为false，在数据库交互时就不会包含在内
     */
    @TableField(exist=false)
    private Double salary ;

**支持主键自增的数据库插入数据获取主键值**  
 Mybatis: 需要通过 useGeneratedKeys 以及 keyProperty 来设置  
 MybatisPlus: 自动将主键值回写到实体类中  
2、修改测试：

@Test
    public void testCommonUpdate() {
    	//初始化修改对象
    	Employee employee = new Employee();
    	employee.setId(7);
    	employee.setLastName("王五");
    	employee.setEmail("xz@sina.com");
    	employee.setGender(0);
    	//updateById方法在修改时，会根据实体类的每个属性进行非空判断，只有非空的属性对应的字段才会出现到SQL语句中
    	Integer result = employeeMapper.updateById(employee);
    	System.out.println("result: " + result );
    	//updateAllColumnById方法在修改时，不管属性是否非空， 属性所对应的字段都会出现到SQL语句中.
    	Integer result1 = employeeMapper.updateAllColumnById(employee);
    	System.out.println("result: " + result1 );
    }

控制台输出：

updateById方法：
    Preparing: UPDATE tbl_employee SET last_name=?, email=?, gender=? WHERE id=? 
    Parameters: 王五(String), xz@sina.com(String), 0(Integer), 7(Integer)  (JakartaCommonsLoggingImpl.java:54) 
    Updates: 1 
    updateAllColumnById方法：
    Preparing: UPDATE tbl_employee SET last_name=?,email=?,gender=?,age=? WHERE id=?   
    Parameters: 王五(String), xz@sina.com(String), 0(Integer), null, 7(Integer)  
    Updates: 1

3、查询测试：

@Test
    public void  testCommonSelect() {
    	//1. 通过id查询
    	Employee employee = employeeMapper.selectById(7);
    	System.out.println(employee);
    
    	//2. 通过多个列进行查询。selectOne查询结果只能是一条，否则报错
    	Employee  employee1 = new Employee();
    	employee.setLastName("王五");
    	employee.setGender(0);
    	Employee result = employeeMapper.selectOne(employee1);
    	System.out.println("result: " +result );
    
    	//3. 通过多个id进行查询
    	List<Integer> idList = new ArrayList<>();
    	idList.add(4);
    	idList.add(5);
    	idList.add(6);
    	idList.add(7);
    	List<Employee> emps = employeeMapper.selectBatchIds(idList);
    	System.out.println(emps);
    
        //4. 通过Map封装条件查询。map中的key是数据库中字段名
    	Map<String,Object> columnMap = new HashMap<>();
    	columnMap.put("last_name", "Tom");
    	columnMap.put("gender", 1);
    	List<Employee> emps1 = employeeMapper.selectByMap(columnMap);
    	System.out.println(emps1);
    
    	//5. 分页查询
    	List<Employee> emps2 = employeeMapper.selectPage(new Page<>(3, 2), null);
    	System.out.println(emps2);
    }

控制台输出：

selectById方法：
    Preparing: SELECT id AS id,last_name AS lastName,email,gender,age FROM tbl_employee WHERE id=?  
    Parameters: 7(Integer)
    Total: 1
    Employee [id=7, lastName=王五, email=xz@sina.com, gender=0, age=null]
    selectOne方法：
    Preparing: SELECT id AS id,last_name AS lastName,email,gender,age FROM tbl_employee WHERE last_name=? AND gender=?
    Parameters: 王五(String), 0(Integer)
    Total: 1
    result: Employee [id=7, lastName=王五, email=xz@sina.com, gender=0, age=null]
    selectBatchIds方法：
    Preparing: SELECT id AS id,last_name AS lastName,email,gender,age FROM tbl_employee WHERE id IN ( ? , ? , ? , ? )
    Parameters: 4(Integer), 5(Integer), 6(Integer), 7(Integer) 
    Total: 4
    [Employee [id=4, lastName=White, email=white@atguigu.com, gender=0, age=35], Employee [id=5, lastName=MP, email=mp@atguigu.com, gender=1, age=22], Employee [id=6, lastName=MP, email=mp@atguigu.com, gender=1, age=22], Employee [id=7, lastName=王五, email=xz@sina.com, gender=0, age=null]]
    selectByMap方法：
    Preparing: SELECT id AS id,last_name AS lastName,email,gender,age FROM tbl_employee WHERE gender = ? AND last_name = ?
    Parameters: 1(Integer), Tom(String)
    Total: 1
    [Employee [id=1, lastName=Tom, email=tom@atguigu.com, gender=1, age=22]]
    selectPage方法：
    Preparing: SELECT id AS id,last_name AS lastName,email,gender,age FROM tbl_employee   
    Parameters:
    [Employee [id=5, lastName=MP, email=mp@atguigu.com, gender=1, age=22], Employee [id=6, lastName=MP, email=mp@atguigu.com, gender=1, age=22]]

4、删除测试：

@Test
    public void testCommonDelete() {
    	//1 .根据id进行删除
    	Integer result = employeeMapper.deleteById(13);
    	System.out.println("result: " + result );
    
    	//2. 根据条件进行删除
    	Map<String,Object> columnMap = new HashMap<>();
    	columnMap.put("last_name", "MP");
    	columnMap.put("email", "mp@atguigu.com");
    	Integer result1 = employeeMapper.deleteByMap(columnMap);
    	System.out.println("result: " + result1 );
    
    	//3. 批量删除
    	List<Integer> idList = new ArrayList<>();
    	idList.add(3);
    	idList.add(4);
    	idList.add(5);
    	Integer result2 = employeeMapper.deleteBatchIds(idList);
    	System.out.println("result: " + result2 );
    }

控制台输出：

deleteById方法：
    Preparing: DELETE FROM tbl_employee WHERE id=? 
    Parameters: 13(Integer)
    Updates: 1
    result: 1
    deleteByMap方法：
    Preparing: DELETE FROM tbl_employee WHERE last_name = ? AND email = ?   
    Parameters: MP(String), mp@atguigu.com(String) 
    Updates: 7
    result: 7
    deleteBatchIds方法：
    Preparing: DELETE FROM tbl_employee WHERE id IN ( ? , ? , ? ) 
    Parameters: 3(Integer), 4(Integer), 5(Integer)  
    Updates: 2
    result: 2

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70]: /images/20220210/eb4104c4cb0a4810bc92b8f5a4102b4b.png