MyBatisPlus03_MyBatisPlus的增删改查CRUD

不念不忘少年蓝@ 2023-02-27 12:27 45阅读 0赞

**MyBatisPlus03\_MyBatisPlus的增删改查CRUD**

--------------------

#### 查询 ####

无条件查询

List<User> list = mapper.selectList(null);

eq查询

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.eq("name", "BLU");
    List<User> list = mapper.selectList(wrapper);

alleq 查询  
多条件查询

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    Map<String,Object> map = new HashMap<>();
    map.put("name","BLU");
    map.put("age",22);
    wrapper.allEq(map);
    List<User> list = mapper.selectList(wrapper);

lt 查询  
查询年龄小于22的数据

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.lt("age", 22);
    List<User> list = mapper.selectList(wrapper);

gt 查询  
查询年龄大于22的数据

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.gt("age", 22);
    List<User> list = mapper.selectList(wrapper);

le 或 ge 查询  
查询年龄大于等于22的数据

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.ge("age", 22);
    List<User> list = mapper.selectList(wrapper);

ne 查询  
不等于查询

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.ne("name", "BLU");
    List<User> list = mapper.selectList(wrapper);

like 查询  
查询姓名带小字的数据

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.like("name", "小");
    List<User> list = mapper.selectList(wrapper);

likeLeft 或 likeRight 查询  
查询姓名以小结尾的数据

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.likeLeft("name", "小");
    List<User> list = mapper.selectList(wrapper);

inSql 查询

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.inSql("id","select id from user where id < 10");
    wrapper.inSql("age","select age from user where age > 3");
    List<User> list = mapper.selectList(wrapper);

排序查询  
逆序查询

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.orderByDesc("age");
    List<User> list = mapper.selectList(wrapper);

正序查询且id>8

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.orderByAsc("age");
    wrapper.having("id > 8");
    List<User> list = mapper.selectList(wrapper);

selectCount 查询  
统计个数

Integer count = mapper.selectCount(null);

带条件的 selectCount 查询  
统计id>1的个数

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.gt("id", 1);
    Integer count = mapper.selectCount(wrapper);

将查询结果封装到 Map 中

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.gt("id", 1);
    List<Map<String, Object>> maps = mapper.selectMaps(wrapper);
    System.out.println(maps);

查询结果：

[{name=zhangsan, id=2, age=21}, {name=lisi, id=3, age=23}]

分页查询

//参数1为页数，参数2为每页记录数
    Page<User> page = new Page<>(1,2);
    Page<User> result = mapper.selectPage(page, null);
    System.out.println(result.getSize());
    System.out.println(result.getTotal());
    List<User> list = result.getRecords();
    for (User user : list) { 
    	System.out.println(user);
    }

**注：分页查询需要配置 PaginationInterceptor 拦截器**

package com.blu.config;
    
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
    
    @Configuration
    public class MyBatisPlusConfig { 
    
    	@Bean
    	public PaginationInterceptor paginationInterceptor() { 
    		return new PaginationInterceptor();
    	}
    
    }

查询结果：

2
    8
    User(id=1, name=BLU, age=22)
    User(id=2, name=zhangsan, age=21)

分页查询并将结果封装为Map

//参数1为页数，参数2为每页记录数
    Page<Map<String, Object>> page = new Page<>(1,2);
    Page<Map<String, Object>> result = mapper.selectMapsPage(page, null);
    System.out.println(result.getSize());
    System.out.println(result.getTotal());
    List<Map<String, Object>> list = result.getRecords();
    for (Map<String, Object> map : list) { 
    	System.out.println(map);
    }

查询结果：

2
    8
    {name=BLU, id=1, age=22}
    {name=zhangsan, id=2, age=21}

selectOne 查询

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.eq("id", 1);
    User user = mapper.selectOne(wrapper);
    System.out.println(user);

多表关联查询

user表：  
![在这里插入图片描述][20200719144121266.png]  
product表：  
![在这里插入图片描述][20200719144224183.png]  
ProductVO：

package com.blu.entity;
    
    import lombok.Data;
    
    @Data
    public class ProductVO { 
    	private Integer category;
    	private Integer count;
    	private String description;
    	private Integer userId;
    	private String username;
    }

UserMapper

package com.blu.mapper;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.Select;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.blu.entity.ProductVO;
    import com.blu.entity.User;
    
    public interface UserMapper extends BaseMapper<User> { 
    	
    	@Select("select p.*,u.name username from product p,user u where p.user_id = u.id and u.id= #{id}")
    	List<ProductVO> productList(Integer id);
    }

测试：

@Test
    public void test() { 
    	List<ProductVO> list = mapper.productList(1);
    	System.out.println(list);	
    }

结果：

[ProductVO(category=1, count=10, description=手机, userId=1, username=BLU), ProductVO(category=2, count=200, description=冰箱, userId=1, username=BLU)]

#### 删除 ####

根据 id 删除

mapper.deleteById(8);

mapper.deleteBatchIds(Arrays.asList(9,10));

带条件删除

QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.eq("age", 99);
    mapper.delete(wrapper);

Map<String, Object> map = new HashMap<String, Object>();
    map.put("name", "sss");
    map.put("age",66);
    mapper.deleteByMap(map);

#### 添加 ####

User user = new User();
    user.setAge(100);
    user.setName("zhangsan");
    mapper.insert(user);

#### 修改 ####

User user = mapper.selectById(1);
    user.setAge(24);
    QueryWrapper<User> wrapper = new QueryWrapper<User>();
    wrapper.eq("id", 1);
    mapper.update(user, wrapper);

[20200719144121266.png]: /images/20230209/60946a4e35cf4c359b0ce083d6d4dd76.png
[20200719144224183.png]: /images/20230209/0f41d29ae1894aad9dbb0bf637ed0f15.png