1.MyBatis-Plus简介:
MyBatis-Plus是一个MyBatis的增强工具,在MyBatis的基础上只做增强不做改变,为简化开发、提高效率而生。MyBatis-Plus提供了通用的mapper和service,可以在不编写任何SQL语句的情况下,快速的实现对单表的CRUD批量、逻辑删除、分页等操作。
2.BaseMapper提供的CRUD功能
2.1 添加功能:
@Test
public void insert(){
User user = new User();
user.setName("users");
user.setAge(43);
user.setEmail("users@xja.com");
int result = this.userMapper.insert(user);
}
2.2 删除功能:
2.2.1 deleteById:
@Test
public void deleteById(){
int result = this.userMapper.deleteById(1696801209841856514L);
}
2.2.2 deleteByMap:
@Test
public void deleteByMap(){
Map<String,Object> map = new HashMap<>();
map.put("name","admin");
map.put("age",40);
int result = this.userMapper.deleteByMap(map);
}
2.2.3 deleteBatchIds(批量删除):
@Test
public void deleteBatchIds(){
List<Long> ids = Arrays.asList(1L, 2L, 3L);
int result = this.userMapper.deleteBatchIds(ids);
}
2.2.4 delete(根据条件删除):
@Test
public void delete() {
User user = new User();
user.setName("zhangsan");
user.setAge(40);
QueryWrapper<User> wrapper = new QueryWrapper<>(user);
this.userMapper.delete(wrapper);
}
2.3 修改功能:
2.3.1 updateById:
@Test
public void updateById(){
User user = new User();
user.setId(1L);
user.setName("Tom");
user.setAge(40);
int result = this.userMapper.updateById(user);
}
2.3.2 update(根据条件修改):
@Test
public void update() {
User user = new User();
user.setAge(80); // 需要更新的字段
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("name", "Jack"); // 设置更新条件
// 执行更新操作
int result = this.userMapper.update(user, wrapper);
}
2.4 查询功能:
2.4.1 selectById:
@Test
public void selectById() {
User user = this.userMapper.selectById(1L);
System.out.println(user);
}
2.4.2 selectBatchIds:
@Test
public void selectBatchIds() {
List<Long> ids = Arrays.asList(1L, 2L, 3L, 4L, 5L);
List<User> users = this.userMapper.selectBatchIds(ids);
users.forEach(System.out::println);
}
2.4.3 selectOne:
@Test
public void selectOne() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("name", "Tom");
// 只能查询一条数据,如果超过一条则报错
User user = this.userMapper.selectOne(wrapper);
System.out.println(user);
}
2.4.4 selectAll:
@Test
public void selectAll(){
//通过条件构造器查询一个list集合,若没有条件,则可以设置null 为参数
List<User> userList = this.userMapper.selectList(null);
userList.forEach(System.out::println);
}
2.4.5 selectCount(根据wrapper查询中记录数):
@Test
public void selectCount() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.gt("age", 23); //查询年龄大于23岁的成员信息
Integer count = this.userMapper.selectCount(wrapper);
System.out.printf("总记录数:%d", count);
}
2.4.6 selectList:
@Test
public void selectList() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.lt("age", 50); // 年龄小于50岁
List<User> users = this.userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
3. IService(通用Service)提供的CRUD功能:
3.1 添加功能:
3.1.1 insertMore(批量添加):
@Test
public void insertMore(){
List<User> users = new ArrayList<>();
for (int i = 0 ; i <= 5 ; i++ ){
User user = new User();
user.setAge(20);
user.setName("刘备");
users.add(user);
}
boolean b = this.iUserService.saveBatch(users);
System.out.println("成功与否:" + b);
}
3.2 删除功能:
3.3 修改功能:
3.4 查询功能:
3.4.1 getById:
@Test
public void getById(){
User user = this.iUserService.getById(1L);
System.out.println("user:" + user);
}
3.4.2 getCount:
@Test
public void getCount(){
int count = this.iUserService.count();
System.out.println("查询的总记录数为:" + count);
}
4. 常用注解:
4.1 @TableName:
作用:设置实体类所对应的表名,如@TableName("t_user")
4.2 @TableId:
作用:将属性所对应的字段指定为主键,value属性用于指定主键的字段,type属性设置主键生成策略
值 | 描述 |
---|---|
IdType.ASSIGN_ID(默认) | 基于雪花算法的策略生成数据id,与数据库id是否设置自增无关 |
IdType.AUTO | 使用数据库的自增策略,注意,该类型请确保数据库设置了id自增,否则无效 |
4.3 @TableField:
作用:指定属性所对应的字段名,如@TableField("user_name")
4.4 @TableLogic:
作用:在实体类中表示删除状态的字段上加该注解,会将MyBatis-Plus提供的删除方法自动转为更新该条记录为删除状态,查询方法会自动转为查询不为删除状态的记录;
0为未删除状态,1为删除状态。
5. Wapper(条件构造器)学习:
5.1 Wapper介绍:
Wrapper:条件构造抽象类,最顶端父类
AbstractWrapper: 用于查询条件封装,生成 sql的 where 条件
QueryWrapper : 查询条件封装
UpdateWrapper: Update 条件封装
AbstractLambdaWrapper : 使用Lambda 语法
LambdaQueryWrapper : 用于Lambda语法便用的查询Wrapper
LambdaUpdateWrapper : Lambda 更新封装Wrapper
5.2 QueryWrapper 学习:
常用方法:
eq:equals,等于
gt:greater than ,大于 >
ge:greater than or equals,大于等于≥
lt:less than,小于<
le:less than or equals,小于等于≤
between:相当于SQL中的BETWEEN
like:模糊匹配。like(“name”,“黄”),相当于SQL的name like ‘%黄%’
likeRight:模糊匹配右半边。likeRight(“name”,“黄”),相当于SQL的name like ‘黄%’
likeLeft:模糊匹配左半边。likeLeft(“name”,“黄”),相当于SQL的name like ‘%黄’
notLike:notLike(“name”,“黄”),相当于SQL的name not like ‘%黄%’
isNull
isNotNull
and:SQL连接符AND
or:SQL连接符OR
in: in(“age",{1,2,3})相当于 age in(1,2,3)
groupBy: groupBy(“id”,“name”)相当于 group by id,name
orderByAsc :orderByAsc(“id”,“name”)相当于 order by id ASC,name ASC
orderByDesc :orderByDesc (“id”,“name”)相当于 order by id DESC,name DESC
5.2.1 查询用户名包含a,年龄在20到30之间,邮箱信息不为nulL的用户信息:
@Test
public void queryWrapper01(){
//查询用户名包含a,年龄在20到30之间,邮箱信息不为nulL的用户信息
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.like("name","a")
.between("age",21,80)
.isNotNull("email");
List<User> userList = this.userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
5.2.2 查询用户信息,按照年龄的降序排序,若年龄相同,则按照id升序排序:
@Test
public void queryWrapper02(){
//查询用户信息,按照年龄的降序排序,若年龄相同,则按照id升序排序
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.orderByDesc("age")
.orderByAsc("id");
List<User> userList = this.userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
5.2.3 删除邮箱地址为nulL的用户信息:
@Test
public void queryWrapper03(){
//删除邮箱地址为nulL的用户信息
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.isNull("email");
int result = this.userMapper.delete(queryWrapper);
System.out.println("==========================");
System.out.println(result);
System.out.println("==========================");
}
5.2.4 将(年龄大于20并且用户名中包含有a) 或邮箱为null的用户信息修改:
@Test
public void queryWrapper04(){
//将(年龄大于20并且用户名中包含有a) 或邮箱为null的用户信息修改
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.gt("age",20)
.like("name","a")
.or()
.isNull("email");
User user = new User();
user.setAge(30);
user.setName("xiaomi");
user.setEmail("admin@xja.com");
int result = this.userMapper.update(user,queryWrapper);
System.out.println("==========================");
System.out.println(result);
System.out.println("==========================");
}
5.2.5 将用户名中包含有a并且 (年龄大于20或邮箱为null) 的用户信息修改:
@Test
public void queryWrapper05(){
//将用户名中包含有a并且 (年龄大于20或邮箱为null) 的用户信息修改
//Lambda中的条件优先执行
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.like("name","a")
.and(i->i.isNull("email").or().gt("age",20));
User user = new User();
user.setAge(25);
user.setName("huawei");
user.setEmail("huawei@xja.com");
int result = this.userMapper.update(user,queryWrapper);
System.out.println("==========================");
System.out.println(result);
System.out.println("==========================");
}
5.2.6 组装select语句的学习(选择只查询特定字段):
@Test
public void queryWapper06(){
//查询用户的用户名、年龄、邮箱信息
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("name","age","email");
List<Map<String,Object>> maps = this.userMapper.selectMaps(queryWrapper);
maps.forEach(System.out::println);
}
5.2.6 组装子查询的学习:
@Test
public void queryWrapper(){
//查询id小于等于100的用户信息
//select * from user where id in(select id from user where id <= 4)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("id","select id from user where id <= 4");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
5.2.7 condition组装条件的学习:
@Test
public void queryWrapper08(){
String name = "a";
Integer ageBegin = null;
Integer ageEnd = 30;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like(StringUtils.isNotBlank(name),"name",name)
.ge(ageBegin!=null,"age",ageBegin)
.le(ageEnd!=null,"age",ageEnd);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
5.3 UpdateWrapper 学习(参照QueryWrapper):
@Test
public void updateWrapper(){
//将用户名中包含有a并且 (年龄大于20或邮箱为null) 的用户信息修改
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.like("name","a")
.and(i->i.isNull("email").or().gt("age",20));
updateWrapper.set("name","honor").set("email","honor@qq.com");
int result = this.userMapper.update(null, updateWrapper);
System.out.println(result);
}
5.4 LambdaQueryWrapper学习(对5.2.7的改进):
@Test
public void lambdaQueryWrapper08(){
String name = "a";
Integer ageBegin = null;
Integer ageEnd = 30;
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.like(StringUtils.isNotBlank(name),User::getName,name)
.ge(ageBegin!=null,User::getAge,ageBegin)
.le(ageEnd!=null,User::getAge,ageEnd);
List<User> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);
}
5.5 LambdaUpdateWrapper学习(对5.3的改进):
@Test
public void lambdaUpdateWrapper(){
//将用户名中包含有a并且 (年龄大于20或邮箱为null) 的用户信息修改
LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.like(User::getName,"a")
.and(i->i.isNull(User::getEmail).or().gt(User::getAge,20));
lambdaUpdateWrapper.set(User::getName,"honor").set(User::getEmail,"honor@qq.com");
int result = this.userMapper.update(null, lambdaUpdateWrapper);
System.out.println(result);
}