mybatis-plus基本操作

mybatis-plus基本操作

（只针对稍微有难度的，部分未涉及到的简单操作可以参考官方文档）

创建类User

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("user")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 主键ID
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    /**
     * 姓名
     */
    @TableField("name")
    private String name;

    /**
     * 年龄
     */
    @TableField("age")
    private Integer age;

    /**
     * 邮箱
     */
    @TableField("email")
    private String email;

    @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;

    @TableField(value = "creat_time", fill = FieldFill.INSERT)
    private Date creatTime;

    @TableField("deleted")
    @TableLogic
    private Integer deleted;


}

Mapper操作

一.插入

1.插入对象，返回id

User user=new User();
user.setName("tom");
user.setAge(12);
user.setEmail("123@qq.com");
//result是返回的数据库受影响的行数，不是id
int result = userMapper.insert(user);
System.out.println(result);

//获取自增后的id,自增后的id会回填回到对象中
System.out.println(user.getId());

二.更新

1.通过条件进行更新

UpdateWrapper<User> userUpdateWrapper=new UpdateWrapper<>();
userUpdateWrapper.set("name","小草").set("age",22).eq("id",15l);
int update = userMapper.update(null, userUpdateWrapper);

三.删除

1.deleteByMap删除

  Map<String, Object> columnMap=new HashMap<>();
        columnMap.put("name","小米");
        columnMap.put("age",15);
//        columnMap之间是and关系
        int result = userMapper.deleteByMap(columnMap);
        System.err.println(result);

运行的SQL语句：

2.通过包装条件删除

QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.eq("age",22);
int result = userMapper.delete(queryWrapper);
System.err.println(result);

3.根据id批量删除数据

int result = userMapper.deleteBatchIds(Arrays.asList(11l, 14l));
System.out.println(result);

四.查询

1.根据id列表批量查询

List<User> userList = userMapper.selectBatchIds(Arrays.asList(1l, 3l, 5l));

2.根据条件查询一条数据，返回结果超过一条时会报错

QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.eq("name","tom");
User user = userMapper.selectOne(queryWrapper);
System.err.println(user);

3.根据条件查询条数

QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.ge("age",22);//年龄大于等于22
 Integer count = userMapper.selectCount(queryWrapper);
 System.err.println(count);

4.根据条件查询对象列表

QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.like("name","小");//模糊查询
 List<User> userList = userMapper.selectList(queryWrapper);
 for (User user : userList) {
     System.err.println(user);
 }

5.分页查询

Page<User> page=new Page<>(2,3);
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.like("name","小");
        Page<User> userPage = userMapper.selectPage(page, queryWrapper);
        long total = userPage.getTotal();//数据总条数
        long pages = userPage.getPages();//数据总页数
        long current = userPage.getCurrent();//当前页数
        System.out.println(total);
        System.out.println(pages);
        System.out.println(current);
        List<User> records = userPage.getRecords();//当前页数记录
        for (User record : records) {
            System.out.println(record);
        }

运行的SQL语句：

五.springboot 常用配置

spring:
  profiles:
#    开发环境
    active: dev
  #  数据库的配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost/mybatis-plus?useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
#    视图解析器
  mvc:
    view:
      prefix: /WEB-INF/jsp/
      suffix: .jsp

#  mybatis-plus自动开启驼峰命名和二级缓存
mybatis-plus:
  configuration:
#    日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#    别名配置
  type-aliases-package: com.yong.user.pojo
  global-config:
    db-config:
      #  全局id生成策略
      id-type: auto
#      逻辑未删除值
      logic-not-delete-value: 0
#      逻辑删除值
      logic-delete-value: 1
#      枚举扫描包
  type-enums-package: com.yong.user.enums
#  mapper-locations: classpath*:/mapper/**/*.xml
#      全局的表名前缀（自动生成代码时）
#      table-prefix: tb_

六.条件构造器

allEq方法的使用

1.allEq只有一个map参数，null2IsNull（null值是否要作为条件）默认是true

Map<String,Object> params=new HashMap<>();
        params.put("name","tom");
        params.put("age",21);
        params.put("email",null);

        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.allEq(params);
        List<User> userList = userMapper.selectList(queryWrapper);
        for (User user : userList) {
            System.out.println(user);
        }

运行的SQL语句：

2.将上述null2IsNull设置为false，即null不作为条件

Map<String,Object> params=new HashMap<>();
params.put("name","tom");
params.put("age",21);
params.put("email",null);

QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.allEq(params,false);
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
    System.out.println(user);
}

运行的SQL语句

3.设置对条件的过滤

 Map<String,Object> params=new HashMap<>();
        params.put("name","tom");
        params.put("age",23);
        params.put("email","3497903@qq.com");

        QueryWrapper<User> queryWrapper=new QueryWrapper<>();

        //上述 params.put("age",23);起作用，params.put("name","tom");params.put("email",null);不起作用
        queryWrapper.allEq((k,v)->(k.equals("id")||(k.equals("age"))),params);

        List<User> userList = userMapper.selectList(queryWrapper);
        for (User user : userList) {
            System.out.println(user);
        }

运行的SQL语句