[ MybatisPlus ] (二)SpringBoot 整合 MybatisPlus 实现增删改查

SpringBoot 整合 MybatisPlus 增删改查


一、注解的简单使用

// 指定表明
@TableName("user")
public class User {
	// 指定主键
    @TableId
    private String id;
    // 指定数据库字段
    @TableField("username")
    private String username;
    private String password;
    // 该字段数据库中不存在
    @TableField(exist = false)
    private String remark;
    private Integer age;
}

二、获取 UserMapper 对象


	@Autowired(required = false)
    private UserMapper userMapper;

required = false
不加这个条件时会报错

三、插入数据


// 插入
        User user = new User();
        user.setId("4");
        user.setUsername("4");
        // 当某个字段为null时,该字段不会插入
        // INSERT INTO user ( id, username ) VALUES ( ?, ? ) 
        user.setPassword("3");
        int result = userMapper.insert(user);
        System.out.println(result);

四、查询数据


1.普通查询

  • 查询全部
// 查询全部
        List<User> list = userMapper.selectList(null);
        Assert.assertEquals(2,list.size());
        for(User user : list){
            System.out.println(user);
        }
  • 根据 id 查询
// 查询单个
        User user = userMapper.selectById("2");
        System.out.println(user);
  • 根据 idList 查询多个
// 查询多个
        List<String> idList = Arrays.asList("1","2","3");
        List<User> list = userMapper.selectBatchIds(idList);
        for(User user : list){
            System.out.println(user);
        }
  • 多个 where 条件查询
// 多个where条件查询
        Map<String,Object> map = new HashMap<>();
        // k:数据库中字段名
        map.put("id","2");
        map.put("username","2");
        // SELECT id,username,password FROM user WHERE id = ? AND username = ?
        List<User> list = userMapper.selectByMap(map);
        for(User user : list){
            System.out.println(user);
        }

2.条件构造器查询

  • 查询姓名中含有‘文’,年龄小于60的
// 查询姓名中含有‘文’,年龄小于60的
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//        QueryWrapper<User> queryWrapper = Wrappers.<User>query();
        queryWrapper.like("username","文").lt("age",60);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • 查询姓名中含有‘文’,年龄小于60大于40的,password不为空
// 查询姓名中含有‘文’,年龄小于60大于40的,password不为空
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("username","文").between("age",40,60).isNotNull("password");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • name like ‘王%’ or age>=25 order by age desc,id asc
// name like '王%' or age>=25 order by age desc,id asc
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name","王").or().ge("age",25)
                .orderByDesc("age").orderByAsc("id");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • data_format(create_time,’%Y-%m-%d’) and manager_id in (select id from user where name like ‘王%’)
// data_format(create_time,'%Y-%m-%d') and manager_id in (select id from user where name like '王%')
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.apply("data_format(create_time,'%Y-%m-%d')={0}","2019-02-04")
            .inSql("manager_id","select id from user where username like '王%'");
        // WHERE (data_format(create_time,'%Y-%m-%d')=? AND
        // manager_id IN (select id from user where username like '王%'))
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • name like ‘王%’ and (age<40 or email is not null)
// name like '王%' and (age<40 or email is not null)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name","王")
                .and(wq->wq.lt("age",40).or().isNotNull("email"));
        // WHERE (name LIKE ? AND (age < ? OR email IS NOT NULL)) 
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);

  • name like ‘王%’ or (age < 40 and age >20 and email is not null)
// name like '王%' or (age < 40 and age >20 and email is not null)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name","王")
                .or(wq->wq.lt("age",40).gt("age",20).isNotNull("email"));
        // WHERE (name LIKE ? OR (age < ? AND age > ? AND email IS NOT NULL))
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);

  • (age < 40 or email is not null) name like ‘王%’
// (age < 40 or email is not null) name like '王%'
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.nested(wq->wq.lt("age",40).or().isNotNull("email"))
            .likeRight("name","王");
        // WHERE ((age < ? OR email IS NOT NULL) AND name LIKE ?) 
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);

  • age in (13,14,15,16) limit 1
// age in (13,14,15,16) limit 1
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age",Arrays.asList(13,14,15,16)).last("limit 1");
        // WHERE (age IN (?,?,?,?)) limit 1
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • 查询指定字段
// select id
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • 字段为空时,不加入查询
String name = "aowei";
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(name.equals(null),"name",name);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • 实体作为条件构造器构造方法的参数
User user = new User();
        user.setId("2");
        user.setUsername("2");
        QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
        // WHERE id=? AND username=? 
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • Map 类型参数
Map<String,Object> map = new HashMap<>();
        map.put("id","2");
        map.put("username","2");
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.allEq(map);
        // WHERE (id = ? AND username = ?)
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::print);
  • 返回 Map 类型结果集
	QueryWrapper<User> queryWrapper = new QueryWrapper<>();
      queryWrapper.select("id","username");
      List<Map<String,Object>> list = userMapper.selectMaps(queryWrapper);
      list.forEach(System.out::print);
  • 查询总记录数
	QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id");
        int result = userMapper.selectCount(queryWrapper);
        System.out.println(result);

3.Lambda 条件构造器查询

  • 仿误写
//        LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
//        LambdaQueryWrapper<User> lambda = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<User> lambda = Wrappers.<User>lambdaQuery();
        lambda.like(User::getUsername,"文");
        List<User> list = userMapper.selectList(lambda);
        list.forEach(System.out::print);

4.分页查询

  • 新建配置类
//Spring boot方式
@EnableTransactionManagement
@Configuration
@MapperScan("com.baomidou.cloud.service.*.mapper*")
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量,默认 500 条,-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }
}

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        Page<User> page = new Page<>(1,2);
        IPage<User> iPage = userMapper.selectPage(page,queryWrapper);
        System.out.println("总页数:"+iPage.getPages());
        System.out.println("总记录:"+iPage.getTotal());
        List<User> list = iPage.getRecords();
        list.forEach(System.out::println);

五、更新数据


1.普通更新

  • 根据 id 更新
User user = new User();
        user.setId("2");
        user.setUsername("22222");
        int result = userMapper.updateById(user);
        System.out.println(result);

2.条件构造器更新

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("id","2");
        User user = new User();
        user.setAge(22);
        int result = userMapper.update(user,updateWrapper);
        System.out.println(result);
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("id","2").set("age",23);
        int result = userMapper.update(null,updateWrapper);
        System.out.println(result);

3.Lambda 条件构造器更新

 LambdaUpdateWrapper<User> lambdaUpdate = Wrappers.<User>lambdaUpdate();
        lambdaUpdate.eq(User::getId,"2").set(User::getAge,23);
        int result = userMapper.update(null,lambdaUpdate);
        System.out.println(result);

六、删除数据


  • 删除数据时,同样有条件构造器删除
  • 使用的是 QueryWrapper
int result = userMapper.deleteById("2");

Map<String,Object> map = new HashMap<>();
// k:数据库中字段名
map.put("id","2");
map.put("username","2");
int result = userMapper.deleteByMap(map);

int result = userMapper.deleteBatchIds(Arrays.asList("1","2","3"));

上一篇[ MybatisPlus ] (一)SpringBoot 整合 MybatisPlus


在这里插入图片描述

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页