MybatisPlus CRUD扩展（四）

这是我参与11月更文挑战的第10天，活动详情查看：2021最后一次更文挑战

Select查询

//测试查询
@Test
public void testSelect() {
    //根据id查询
    User user = userMapper.selectById(1L);
    System.out.println(user);

    //批量查询
    List<User> users = userMapper.selectBatchIds(Arrays.asList(2L, 3L));
    users.forEach(System.out::println);

    //条件查询 map
    Map<String, Object> map = new HashMap<>();
    map.put("name", "zbc");
    List<User> users1 = userMapper.selectByMap(map);
    users1.forEach(System.out::println);
}

//根据id查询
==>  Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id=?
==> Parameters: 1(Long)

//批量查询
==>  Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id IN ( ? , ? )
==> Parameters: 2(Long), 3(Long)

//条件查询 map
==>  Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE name = ?
==> Parameters: zbc(String)

分页查询

实现方式

• 原始分页limit
• 第三方插件pageHelper
• MyBatisPlus自带分页功能

使用分页查询

• 增加分页插件，分页插件需要在乐观锁插件前面添加

@Configuration
@EnableTransactionManagement
public class MyBatisPlusConfig {

    /**
     * 用来配置mybatisPlus 插件
     *
     * @return 拦截器
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //分页插件，注意数据库的类型
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        //乐观锁插件
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

• 直接使用Page对象即可

//测试分页查询  
@Test  
public void testPage() {  
    //参数1：页码，参数2：每页记录数  
    Page<User> page = new Page<>(1, 5);  
    userMapper.selectPage(page, null);  
    page.getRecords().forEach(System.out::println);  
}

//先求总记录数
==>  Preparing: SELECT COUNT(*) AS total FROM user
==> Parameters: 
<==    Columns: total
<==        Row: 12
<==      Total: 1
//然后分页查询
==>  Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user LIMIT ?
==> Parameters: 5(Long)
//输出结果
User(id=1, name=test2, age=25, email=test1@baomidou.com, createTime=null, updateTime=Sun Nov 07 18:46:37 CST 2021, version=3)
User(id=2, name=Jack, age=20, email=test2@baomidou.com, createTime=null, updateTime=Sat Nov 06 22:28:53 CST 2021, version=1)
User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=Sat Nov 06 22:28:53 CST 2021, version=1)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com, createTime=null, updateTime=Sat Nov 06 22:28:53 CST 2021, version=1)
User(id=5, name=Billie, age=24, email=test5@baomidou.com, createTime=null, updateTime=Sat Nov 06 22:28:53 CST 2021, version=1)
12

delete删除

//测试删除
@Test
public void testDelete() {
    //单个id删除
    userMapper.deleteById(1456795944390905863L);

    //批量id删除
    userMapper.deleteBatchIds(Arrays.asList(1456795944390905861L, 1456795944390905862L));

    //条件删除 map
    Map<String, Object> map = new HashMap<>();
    map.put("name", "123");
    userMapper.deleteByMap(map);
}

//单个id删除
==>  Preparing: DELETE FROM user WHERE id=?
==> Parameters: 1456795944390905863(Long)
<==    Updates: 1
//批量id删除
==>  Preparing: DELETE FROM user WHERE id IN ( ? , ? )
==> Parameters: 1456795944390905861(Long), 1456795944390905862(Long)
<==    Updates: 2
//条件删除 map、
==>  Preparing: DELETE FROM user WHERE name = ?
==> Parameters: 123(String)
<==    Updates: 1

逻辑删除

• 物理删除：从数据库中直接删除
• 逻辑删除：从数据库中没有删除，而是通过一个变量来让它失效

应用场景：管理员可以查看被删除的记录，防止数据丢失，类似回收站。

使用

• 增加deleted字段

ALTER TABLE `table_name`  
ADD COLUMN `deleted` INT(1) DEFAULT 1 COMMENT '逻辑删除';

• 实体类增加属性

@TableLogic  
private Integer deleted;

• 增加逻辑删除配置

# 逻辑删除
# 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置@TableLogic)
mybatis-plus.global-config.db-config.logic-delete-field=flag
# 逻辑已删除值(默认为 1)
mybatis-plus.global-config.db-config.logic-delete-value=1
# 逻辑未删除值(默认为 0)
mybatis-plus.global-config.db-config.logic-not-delete-value=0

• 测试

//测试逻辑删除
@Test
public void testLogicDelete(){
    userMapper.deleteById(1456795944390905864L);
    User user = userMapper.selectById(1456795944390905864L);
    System.out.println(user);
}

==>  Preparing: UPDATE user SET deleted=1 WHERE id=? AND deleted=0
==> Parameters: 1456795944390905864(Long)
<==    Updates: 1


==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE id=? AND deleted=0
==> Parameters: 1456795944390905864(Long)
<==      Total: 0

null

本质上是更新操作并不是删除，而是修改操作。 查询的时候我们发现已经不能查找到了