2.1.3 删除操作

deleteById

直接上实例，因为这部分都是api的调用

@Test
public void testDelete01() {
    int i = userMapper.deleteById(6L);
    System.out.println(i);
}
==>  Preparing: DELETE FROM user WHERE id=? 
==> Parameters: 6(Long)
<==    Updates: 1
deleteByMap
接受一个map，mao中的元素会被设置为删除的条件，多个之间为and关系
源码：
int deleteByMap(@Param("cm") Map<String, Object> var1);

@Test
public void testDelete02() {
    HashMap<String, Object> map = new HashMap<>();
    map.put("age",14);
    map.put("name","CCC");
    int i = userMapper.deleteByMap(map);
    System.out.println(i);
}
==>  Preparing: DELETE FROM user WHERE name = ? AND age = ? 
==> Parameters: CCC(String), 14(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@767191b1]
1

delete
@Test
public void testDelete03() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("name","Jone")
        .eq("age",18);
    userMapper.delete(wrapper);
}
==>  Preparing: DELETE FROM user WHERE name = ? AND age = ? 
    ==> Parameters: Jone(String), 18(Integer)
        <==    Updates: 1

第二种方式

@Test
public void testDelete03() {
    User user = new User();
    user.setAge(20);
    user.setName("Jack");
    //        将实体对象进行包装，包装为操作条件
    QueryWrapper<User> wrapper = new QueryWrapper<>(user);
    userMapper.delete(wrapper);
    }
==>  Preparing: DELETE FROM user WHERE name=? AND age=? 
==> Parameters: Jack(String), 20(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6eb17ec8]

deleteBatchIds

根据id集合批量删除

@Test
    public void testDelete04() {
      userMapper.deleteBatchIds(Arrays.asList(1L, 11L, 12L));
    }
==>  Preparing: DELETE FROM user WHERE id IN ( ? , ? , ? ) 
==> Parameters: 1(Long), 11(Long), 12(Long)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@767191b1]

2.1.4 查询操作

和上面类型，简单的API调用会用即可

selectById
package com.pyy.mp;
import com.pyy.mp.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class MpApplicationTests02 {
    @Autowired
    private UserMapper userMapper;
    @Test
    public void testSelectById(){
        userMapper.selectById(2L);
    }
}
==>  Preparing: SELECT id,name,age,email FROM user WHERE id=? 
==> Parameters: 2(Long)
<==    Columns: id, name, age, email
<==        Row: 2, Jack, 20, test2@baomidou.com
<==      Total: 1

selectBatchIds

根据id集合批量查询

@Test
public void testSelectBatchIds(){
    userMapper.selectBatchIds(Arrays.asList(3L,4L,66L));
}
==>  Preparing: SELECT id,name,age,email FROM user WHERE id IN ( ? , ? , ? ) 
==> Parameters: 3(Long), 4(Long), 66(Long)
<==    Columns: id, name, age, email
<==        Row: 3, Tom, 28, test3@baomidou.com
<==        Row: 4, Sandy, 21, test4@baomidou.com
<==      Total: 2

selectOne
@Test
public void testSelectOne(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper.eq("name","Jack");
    userMapper.selectOne(userQueryWrapper);
}
==>  Preparing: SELECT id,name,age,email FROM user WHERE name = ? 
==> Parameters: Jack(String)
<==    Columns: id, name, age, email
<==        Row: 2, Jack, 20, test2@baomidou.com
<==      Total: 1

selectCount

根据Wrapper条件，查询总记录数

@Test
public void testSelectCount(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper.gt("age",23);
    Integer integer = userMapper.selectCount(userQueryWrapper);
    System.out.println(integer);
}
==>  Preparing: SELECT COUNT(1) FROM user WHERE age > ? 
==> Parameters: 23(Integer)
<==    Columns: COUNT(1)
<==        Row: 2
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5c77ba8f]
2

selectList

gt = greater than

lt = little than

其他的类似

@Test
public void testSelectList(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper.lt("age",30);
    List<User> users = userMapper.selectList(userQueryWrapper);
    users.forEach(x-> System.out.println(x));
}
==>  Preparing: SELECT id,name,age,email FROM user WHERE age < ? 
==> Parameters: 30(Integer)
<==    Columns: id, name, age, email
<==        Row: 2, Jack, 20, test2@baomidou.com
<==        Row: 3, Tom, 28, test3@baomidou.com
<==        Row: 4, Sandy, 21, test4@baomidou.com
<==        Row: 5, Billie, 24, test5@baomidou.com
<==      Total: 4
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@25290bca]
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=3, name=Tom, age=28, email=test3@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)

selectpage

什么是分页查询？

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

要实现分页功能，实际上就是从结果集中显示第1100条记录作为第1页，显示第101200条记录作为第2页，以此类推。

举例？

SELECT id, name, gender, score

FROM students

ORDER BY score DESC

LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。

Page page = new Page<>(1,1);

查询第一页，查询1条数据

getTotal() 数据总条数

getPages() 数据总页数

getCurrent 当前页数

getRecords 得到数据

@Test
public void testSelectList(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper.lt("age",30);
    Page<User> page = new Page<>(1,1);
    IPage<User> iPage = userMapper.selectPage(page, userQueryWrapper);
    System.out.println(iPage.getTotal());
    System.out.println(iPage.getPages());
    List<User> users = iPage.getRecords();
    users.forEach(x-> System.out.println(x));
}
==>  Preparing: SELECT COUNT(1) FROM user WHERE age < ? 
==> Parameters: 30(Integer)
<==    Columns: COUNT(1)
<==        Row: 4
==>  Preparing: SELECT id,name,age,email FROM user WHERE age < ? LIMIT 0,1 
==> Parameters: 30(Integer)
<==    Columns: id, name, age, email
<==        Row: 2, Jack, 20, test2@baomidou.com
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46c269e0]
4
4
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
2022-02-18 20:31:03.877  INFO 19944 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-02-18 20:31:03.899  INFO 19944 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
Process finished with exit code 0

2.2 条件构造器

Wrapper就是条件构造器

下面我们看它的继承结构

QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类

用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件

2.2.1 allEq

params : key为数据库字段名,value为字段值

null2IsNull : 为true则在map的value为null时调用 isNull方法,为false时则忽略value为null的

@Test
public void testSelectAllEq(){
    Map<String, Object> map = new HashMap<>();
    map.put("name","Jack");
    map.put("age",20);
    map.put("password",null);
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.allEq(map,false);
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(x-> System.out.println(x));
}
==>  Preparing: SELECT id,name,age,email FROM user WHERE name = ? AND age = ? 
==> Parameters: Jack(String), 20(Integer)
<==    Columns: id, name, age, email
<==        Row: 2, Jack, 20, test2@baomidou.com
<==      Total: 1

2.2.2 基本比较操作

e:equals

n:not

g:greater

l:little

组合的话意思也组合

🌔🌔🌔🌔🌔🌔🌔🌔🌔🌔🌔🌔

:冒号加英文单词可以打出符号哦🐮(:cow)

eq 等于

ne 不等于

gt 大于

ge 大于等于

It 小于

le 小于等于

between between value1 and value2

notBetween not between value1 and value2

in 字段IN（value.get(0),value.get(1),…）

2.2.3 模糊查询

LIKE ‘%值%’

例: like("name", "王")—>name like '%王%'

@Test
public void testLike(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.like("name","J");
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(x-> System.out.println(x));
}
==>  Preparing: SELECT id,name,age,email FROM user WHERE name LIKE ? 
==> Parameters: %J%(String)
<==    Columns: id, name, age, email
<==        Row: 2, Jack, 20, test2@baomidou.com
<==        Row: 9, Jam, 18, test6@qq.cn
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46c269e0]
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=9, name=Jam, age=18, email=test6@qq.cn)

2.2.4 排序

orderBy

排序：ORDER BY 字段, …

例: orderBy(true, true, "id", "name")—>order by id ASC,name ASC

orderByAsc

排序：ORDER BY 字段, … ASC(升序)

例: orderByAsc("id", "name")—>order by id ASC,name ASC

orderByDesc

排序：ORDER BY 字段, … DESC（降序）

例: orderByDesc("id", "name")—>order by id DESC,name DESC

@Test
public void testOrder(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.orderByDesc("age");
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(x-> System.out.println(x));
}
==>  Preparing: SELECT id,name,age,email FROM user ORDER BY age DESC 
==> Parameters: 
<==    Columns: id, name, age, email
<==        Row: 3, Tom, 28, test3@baomidou.com
<==        Row: 5, Billie, 24, test5@baomidou.com
<==        Row: 4, Sandy, 21, test4@baomidou.com
<==        Row: 2, Jack, 20, test2@baomidou.com
<==        Row: 9, Jam, 18, test6@qq.cn
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5fa23c]
User(id=3, name=Tom, age=28, email=test3@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=9, name=Jam, age=18, email=test6@qq.cn)
2022-02-18 21:38:58.700  INFO 1588 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-02-18 21:38:58.725  INFO 1588 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
Process finished with exit code 0

2.2.5 逻辑查询

主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)

例: eq("id",1).or().eq("name","老王")—>id = 1 or name = '小王

and

AND 嵌套

例: and(i -> i.eq("name", "李四").ne("status", "平凡"))—>and (name = '李四' and status <> '平凡')

@Test
public void testOrder(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.orderByDesc("age");
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(x-> System.out.println(x));
}
==>  Preparing: SELECT id,name,age,email FROM user WHERE name = ? OR age = ? 
==> Parameters: Jack(String), 20(Integer)
<==    Columns: id, name, age, email
<==        Row: 2, Jack, 20, test2@baomidou.com
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46c269e0]
User(id=2, name=Jack, age=20, email=test2@baomidou.com)

2.2.6 select

在MP查询中，默认查询所有的字段，如果有需要也可以通过select方法进行指定字段。

@Test
public void testSelect(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("name","Jack")
            .or()
            .eq("age",20)
            .select("id","name","age");
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(x-> System.out.println(x));
}
==>  Preparing: SELECT id,name,age FROM user WHERE name = ? OR age = ? 
==> Parameters: Jack(String), 20(Integer)
<==    Columns: id, name, age
<==        Row: 2, Jack, 20
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@558756be]
User(id=2, name=Jack, age=20, email=null)

2.3 主键策略

@TableId

描述：主键注解

使用位置：实体类主键字段

属性类型必须指定默认值描述值

value String 否 “” 主键字段名

type Enum 否 IdType.NONE 指定主键类型

主键类型：

AUTO(0) 数据库id自增

NONE(1) 未设置主键

INPUT(2) 手动输入

ID_WORKER(3) 默认的全局唯一id

UUID(4) 全局唯一id uuid

ID_WORKER_STR(5) ID_WORKER 字符串表示法

2.3.1 AUTO

数据库设置id字段自动递增

实体类添加注解设置自增

package com.pyy.mp.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

测试

@SpringBootTest
class MpApplicationTests {
//    继承了BaseMapper，所有的方法都来自自己的父类
    @Autowired
    private UserMapper userMapper;
    //测试插入
    @Test
    public void testInsert(){
        User pyy = new User();
        pyy.setName("a");
        pyy.setAge(3);
        pyy.setEmail("a@aa.cn");
        int result = userMapper.insert(pyy);
        System.out.println(result);
        System.out.println(pyy);
    }
}
.........
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@14823f76] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1509506488 wrapping com.mysql.cj.jdbc.ConnectionImpl@1aabf50d] will not be managed by Spring
==>  Preparing: INSERT INTO user ( name, age, email ) VALUES ( ?, ?, ? ) 
==> Parameters: a(String), 3(Integer), a@aa.cn(String)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@14823f76]
1
User(id=6, name=a, age=3, email=a@aa.cn)

2.3.2 INPUT

实体类

package com.pyy.mp.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    @TableId(type = IdType.INPUT)
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

测试类

@SpringBootTest
class MpApplicationTests {
//    继承了BaseMapper，所有的方法都来自自己的父类
    @Autowired
    private UserMapper userMapper;
    //测试插入
@Test
    public void testInsert(){
        User pyy = new User();
        pyy.setId(7L);
        pyy.setName("a");
        pyy.setAge(3);
        pyy.setEmail("a@aa.cn");
        int result = userMapper.insert(pyy);
        System.out.println(result);
        System.out.println(pyy);
    }
}
...........
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@48840594] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2028088629 wrapping com.mysql.cj.jdbc.ConnectionImpl@2e8a1ab4] will not be managed by Spring
==>  Preparing: INSERT INTO user ( id, name, age, email ) VALUES ( ?, ?, ?, ? ) 
==> Parameters: 7(Long), a(String), 3(Integer), a@aa.cn(String)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@48840594]
1
User(id=7, name=a, age=3, email=a@aa.cn)
2022-02-17 20:59:30.554  INFO 2152 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-02-17 20:59:30.567  INFO 2152 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
Process finished with exit code 0

三、扩展

3.1 自动填充

插入数据时进行填充，也就是为email添加自动填充功能，在新增数据时有效

3.1.1 实体类添加注解

让email字段插入时自动填充数据

@TableField(fill = FieldFill.INSERT)
private String email;

3.1.2 自定义实现类 MyMetaObjectHandler

package com.pyy.mp.handle;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    //插入数据时填充
    @Override
    public void insertFill(MetaObject metaObject) {
        Object email = getFieldValByName("email", metaObject);
        if (null == email){
            setFieldValByName("email","111111",metaObject);
        }
    }
    //更新数据时填充
    @Override
    public void updateFill(MetaObject metaObject) {
    }
}

3.1.3 测试

@Test
public void testInsert() {
    User pyy = new User();
    pyy.setId(6L);
    pyy.setName("B");
    pyy.setAge(3);
    int result = userMapper.insert(pyy);
    System.out.println(result);
    System.out.println(pyy);
}
==>  Preparing: INSERT INTO user ( name, age, email ) VALUES ( ?, ?, ? ) 
==> Parameters: B(String), 3(Integer), 111111(String)
<==    Updates: 1
 Time：33 ms - ID：com.pyy.mp.mapper.UserMapper.insert
Execute SQL：
    INSERT 
    INTO
        user
        ( name, age, email ) 
    VALUES
        ( 'B', 3, '111111' )
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@a66e580]
1
User(id=11, name=B, age=3, email=111111, version=null)

3.2 逻辑删除

开发系统时，有时候在实现功能时，删除操作需要实现逻辑删除，所谓逻辑删除就是将数据标记为删除，而并非真正的物理删除（非DELETE操作），查询时需要携带状态条件，确保被标记的数据不被查询到。这样做的目的就是避免数据被真正的删除。

就像用户删除购买的订单一样，用户看到实际上是删除了的，但实际上只是逻辑上的删除后台依旧还有数据存在。

3.2.1 修改表结构

3.2.2 修改实体类

@TableLogic
private Integer deleted;

3.2.3 配置文件

新版本的mp可以直接修改yml文件即可

逻辑删除为1，反之为0

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      logic-delete-value: 1
      logic-not-delete-value: 0

我用的是3.0的，所以我要在配置类中添加逻辑删除组件

//删除组件
@Bean
public ISqlInjector sqlInjector(){
    return new LogicSqlInjector();
}

3.2.4 测试

@Test
public void testDelete01() {
    userMapper.deleteById(12L);
}
==>  Preparing: UPDATE user SET deleted=1 WHERE id=? AND deleted=0 
==> Parameters: 12(Long)
<==    Updates: 1
 Time：0 ms - ID：com.pyy.mp.mapper.UserMapper.deleteById
Execute SQL：
    UPDATE
        user 
    SET
        deleted=1 
    WHERE
        id=12 
        AND deleted=0

再次查看

12号已被删除

@Test
    void contextLoads() {
        List<User> users = userMapper.selectList(null);
        users.forEach(a -> {
            System.out.println(a);
        });
    }
==>  Preparing: SELECT id,name,age,email,version,deleted FROM user WHERE deleted=0 
==> Parameters: 
<==    Columns: id, name, age, email, version, deleted
<==        Row: 2, Jack, 30, test2@baomidou.com, 2, 0
<==        Row: 3, Tom, 28, test3@baomidou.com, 1, 0
<==        Row: 4, Sandy, 21, test4@baomidou.com, 1, 0
<==        Row: 5, Billie, 24, test5@baomidou.com, 1, 0
<==      Total: 4
 Time：37 ms - ID：com.pyy.mp.mapper.UserMapper.selectList
Execute SQL：
    SELECT
        id,
        name,
        age,
        email,
        version,
        deleted 
    FROM
        user 
    WHERE
        deleted=0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@22a6e998]
User(id=2, name=Jack, age=30, email=test2@baomidou.com, version=2, deleted=0)
User(id=3, name=Tom, age=28, email=test3@baomidou.com, version=1, deleted=0)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com, version=1, deleted=0)
User(id=5, name=Billie, age=24, email=test5@baomidou.com, version=1, deleted=0)

实际上只进行了逻辑删除

Mybatis-Plus快速入门（二）