Mybatis-Plus常用的增删改查坑

添加依赖

   <!--实体类上加上@Data注解就不用写get，set，toString，equals等方法了--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!--mybatis-plus依赖--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.1.0</version></dependency>

Mapper接口要继承自BaseMapper并将实体类作为泛型

public interface UserMapper extends BaseMapper<User> {
}

增 insert

User user = new User();
user.setUserName("向南");
user.setAge(32);
user.setEmail("[e-mail]");
user.setManagerId(1088248166370832385L);
int rows = userMapper.insert(user);

删 delete

deleteById

@Test
public void deleteById(){int rows = userMapper.deleteById(1152147420519170050L);System.out.println("删除的记录数："+rows);
}

deleteByMap

/*** DELETE FROM user WHERE name = ? AND age = ? */
@Test
public void deleteByMap(){HashMap<String, Object> map = new HashMap<>();map.put("age",32);map.put("name","朱上林");int rows = userMapper.deleteByMap(map);System.out.println("删除了："+rows+"条记录");
}

deleteBatchIds

@Test
public void deleteBatchIds(){int rows = userMapper.deleteBatchIds(
Arrays.asList(1152134491950309377L,1152138510835929090L, 1152139353765216258L));System.out.println("受影响的行数："+rows);
}

Lambda删除

@Test
public void deleteByLambdaWrapper(){LambdaQueryWrapper<User> lambdaWrapper = new LambdaQueryWrapper<>();// DELETE FROM user WHERE age = ? OR age > ? lambdaWrapper.eq(User::getAge,32).or().gt(User::getAge,40);int rows = userMapper.delete(lambdaWrapper);System.out.println("受影响的行数："+rows);
}

改 update

updateById

@Test
public void updateUser(){User user = new User();user.setId(1094590409767661571L);user.setAge(28);user.setEmail("[e-mail]");int rows = userMapper.updateById(user);//UPDATE user SET age=?, email=? WHERE id=? System.out.println("受影响的行数： "+rows);
}

update

@Test
public void updateByWrapper(){UpdateWrapper<User> wrapper = new UpdateWrapper<>();//where条件构造器wrapper.eq("name","朱上林");//set改变的数据设置User user = new User();user.setAge(29);user.setEmail("[e-mail]");int rows = userMapper.update(user, wrapper);//UPDATE user SET age=?, email=? WHERE name = ?System.out.println("受影响的行数： "+rows);
}

查 select

selectById

@Test
public void selectById(){User user = userMapper.selectById(1088248166370832385L);//SELECT id,name,age,email,manager_id,create_time FROM user WHERE id=?System.out.println(user);
}

selectBatchIds

@Test
public void selectBatchIds(){List<Long> idList = Arrays.asList(1087982257332887553L, 1094590409767661571L, 1152134491950309377L);List<User> userList = userMapper.selectBatchIds(idList);//SELECT id,name,age,email,manager_id,create_time FROM user WHERE id IN ( ? , ? , ? )userList.forEach(System.out::println);
}

selectByMap

@Test
public void selectByMap(){HashMap<String, Object> map = new HashMap<>();map.put("name", "朱上林");map.put("age",26);
//注意map中的key是表中的列，不是实体类中的属性List<User> userList = userMapper.selectByMap(map);//SELECT id,name,age,email,manager_id,create_time FROM user WHERE name = ? AND age = ?userList.forEach(System.out::println);
}

条件构造器查询

/**
*查询名字包含“雨”子，并且年龄小于40
*/
@Test
public void selectByWrapper(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件: WHERE name LIKE ? AND age < ?wrapper.like("name", "%雨%").lt("age",40);//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}/*
查询名字包含“雨”子，并且年龄大于等于20，小于等于40，并且email不为null*/
@Test
public void selectByWrapper2(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件:WHERE name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULLwrapper.like("name", "%雨%")
.between("age",20,40)
.isNotNull("email");//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}/*
查询名字为"王"姓，或者年龄大于等于26，先按照年龄降序排列，年龄相同的按照id升序排列*/
@Test
public void selectByWrapper3(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件:WHERE name LIKE 王% OR age >= 26 ORDER BY age DESC , id ASC wrapper.likeRight("name","王").or().ge("age",26).orderByDesc("age").orderByAsc("id");//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}/*
查询创建日期为2019-2-14，并且直属上级领导 姓"王"*/
@Test
public void selectByWrapper4(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件:create_time字段格式化为"年-月-日"后值为2019-02-14，并且，直属上级姓"王"（子查询）//WHERE date_format(create_time,'%Y-%m-%d') = ? AND manager_id IN (select id from user where name like '王%') wrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}","2019-02-14").inSql("manager_id","select id from user where name like '王%'");//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}/*
查询名字为"王"姓 且（年龄小于40或邮箱不为空）*/
@Test
public void selectByWrapper5(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件:WHERE name LIKE ? AND ( age < ? OR email IS NOT NULL )
//wrap->wrap表示将wrapper传一个到后面，前面是一个整体，后面是一个整体，两个整体再andwrapper.likeRight("name","王").and(wrap->wrap.lt("age",40).or().isNotNull("email"));//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}/*
查询年龄大于40 或者（年龄大于20并且邮箱不为空）*/
@Test
public void selectByWrapper6(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件:WHERE age < ? OR ( age >= ? AND email IS NOT NULL ) wrapper.lt("age",40).or(wrap->wrap.ge("age",20).isNotNull("email"));//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}/*
查询(年龄小于40或者邮箱不为空) 并且 名字为"王"姓*/
@Test
public void selectByWrapper7(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件:WHERE ( age < ? OR email IS NOT NULL ) AND name LIKE ? wrapper.nested(wq->wq.lt("age",40).or().isNotNull("email")).likeRight("name","王");//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}/*
查询年龄是30，31，32，33
age in (30,31,32,33)*/
@Test
public void selectByWrapper8(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件:WHERE age IN (?,?,?,?) wrapper.in("age",Arrays.asList(30,31,32,33));//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}

常用构造器

常用条件构造器：
等于 =
例: eq("name", "老王")--->name = '老王'
不等于 <>
例: ne("name", "老王")--->name <> '老王'
大于 >
例: gt("age", 18)--->age > 18
大于等于 >=
例: ge("age", 18)--->age >= 18
小于 <
例: lt("age", 18)--->age < 18
小于等于 <=
例: le("age", 18)--->age <= 18
BETWEEN 值1 AND 值2
例: between("age", 18, 30)
--->age between 18 and 30
NOT BETWEEN 值1 AND 值2
例: notBetween("age", 18, 30)
--->age not between 18 and 30
LIKE '%值%'
例: like("name", "王")
--->name like '%王%'
NOT LIKE '%值%'
例: notLike("name", "王")
--->name not like '%王%'
LIKE '%值'
例: likeLeft("name", "王")
--->name like '%王'
LIKE '值%'
例: likeRight("name", "王")
--->name like '王%'
字段 IS NULL
例: isNull("name")
--->name is null
字段 IS NOT NULL
例: isNotNull("name")
--->name is not null
字段 IN 
例: in("age",{1,2,3})
--->age in (1,2,3)
字段NOTIN
例: notIn("age",{1,2,3})
--->age not in (1,2,3)
字段 IN ( sql语句 )
例: inSql("id", "select id from table where id < 3")
--->id in (select id from table where id < 3)
字段 NOT IN ( sql语句 )
例: notInSql("id", "select id from table where id < 3")
--->age not in (select id from table where id < 3)
分组：GROUP BY 字段, ...
例: groupBy("id", "name")
--->group by id,name
排序：ORDER BY 字段, ... ASC
例: orderByAsc("id", "name")
--->order by id ASC,name ASC
排序：ORDER BY 字段, ... DESC
例: orderByDesc("id", "name")
--->order by id DESC,name DESC
HAVING ( sql语句 )
例: having("sum(age) > 10")
--->having sum(age) > 10
例: having("sum(age) > {0}", 11)
--->having sum(age) > 11
OR 拼接
例: eq("id",1).or().eq("name","老王")
--->id = 1 or name = '老王'
OR 嵌套
例: or(i -> i.eq("name", "李白").ne("status", "活着"))
--->or (name = '李白' and status <> '活着')
AND 嵌套
例: and(i -> i.eq("name", "李白").ne("status", "活着"))
--->and (name = '李白' and status <> '活着')
apply拼接sql
例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")
--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")

查询部分字段

/**
*查询指定的字段
*/
@Test
public void selectByWrapperSuper(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件: SELECT id,name FROM user WHERE name LIKE ? AND age < ? wrapper.select("id","name").like("name", "%雨%").lt("age",40);//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}

/*** 查询除了name和age的所有有字段，名字中包含"雨"，且年龄小于40*/
@Test
public void selectByWrapperSuper2(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//构造器赋予条件: SELECT id,name FROM user WHERE name LIKE ? AND age < ?wrapper.like("name", "%雨%").lt("age",40)
.select(User.class,info->!info.getColumn().equals("name")&&!info.getColumn().equals("age"));//执行条件构造器查询List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);
}

条件查询

@Test
public void testContdition(){String name = "王";String email = "";//WHERE name LIKE ? ，因为email为空，所以不加入到条件构造器中condition(name, email);
}private void condition(String name, String email){QueryWrapper<User> wrapper = new QueryWrapper<>();//当传过来的参数name，email不为空字符串时，才加入到查询构造器中wrapper.like(StringUtils.isNotEmpty(name),"name",name).like(StringUtils.isNotEmpty(email),"email",email);List<User> userList = userMapper.selectList(wrapper);userList.forEach(System.out::println);
}

创建条件构造器时，传入实体对象

 /*
创建条件构造器时，传入实体对象，就可以代替构造条件wrapper...*/@Testpublic void selectByWrapperEntity(){//创建实体对象User user = new User();user.setAge(26);//相当于WHERE age=26//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>(user);//使用实体对象，就不用构造条件了List<User> userList = userMapper.selectList(wrapper);//遍历输出结果userList.forEach(System.out::println);}

allEq

@Test
public void selectByWrapperAllEq(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();//创建一个参数map对象HashMap<String, Object> params = new HashMap<>();params.put("name", "王天风");params.put("age", null);wrapper.allEq(params);//WHERE name = ? AND age IS NULLList<User> userList = userMapper.selectList(wrapper);userList.forEach(System.out::println);
}

selectMaps

@Test
public void selectByWrapperMaps(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.select("id","name").like("name", "雨").lt("age", 40);List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);/*** 这样查询结果只有我们想要的字段和对应的值，没有很多没用的字段和对应的值为null* {name=张雨琪, id=1094590409767661570}* {name=刘红雨, id=1094592041087729666}*/maps.forEach(System.out::println);
}
/*** 按照直属上级分组，查询每组最大年龄，平均年龄，最小年龄，并且只取年龄总和小于500的组* select avg(age) avg_age,max(age) max_age,min(age) min_age* from user* group by manager_id* having sum(age)<500*/
@Test
public void selectByWrapperMaps02(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.select("avg(age) avg_age", "max(age) max_age", "min(age) min_age").groupBy("manager_id").having("sum(age)<{0}", 500);List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);/*** {max_age=40, avg_age=40.0000, min_age=40}* {max_age=25, avg_age=25.0000, min_age=25}* {max_age=33, avg_age=29.6250, min_age=26}*/maps.forEach(System.out::println);
}

selectCount

/*** 查询名字中有"雨"子的记录数量
* 注意查询记录数量，不能查询具体的列名*/
@Test
public void selectCount(){QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.like("name","雨");Integer count = userMapper.selectCount(wrapper);System.out.println(count);
}

Lambda条件构造器

好处：可以放置列名写错，写错编译就不通过

VehicleBO one = vehicleService.getOne(Wrappers.<VehicleBO>lambdaQuery().eq(VehicleBO::getVin, reqVo.getVin()));

/*** Lambda条件构造器* 查询王姓，年龄小于40*/
@Test
public void selectLambdaWrapper(){// 创建Lambda条件构造器LambdaQueryWrapper<User> lambdaWrapper = new LambdaQueryWrapper<>();//设置Lambda条件：WHERE name LIKE ? AND age < ? lambdaWrapper.like(User::getName,"雨").lt(User::getAge, 40);List<User> userList = userMapper.selectList(lambdaWrapper);userList.forEach(System.out::println);
}

/*** Lambda条件构造器* name like '王%' and (age<40or email is not null)*/
@Test
public void selectLambdaWrapper2(){// 创建Lambda条件构造器LambdaQueryWrapper<User> lambdaWrapper = new LambdaQueryWrapper<>();//设置Lambda条件：WHERE name LIKE ? AND ( age < ? OR email IS NOT NULL ) lambdaWrapper.likeRight(User::getName,"王").and(lwq->lwq.lt(User::getAge,40)//and(lwq->lwq)嵌套
.or().isNotNull(User::getEmail));//or()拼接List<User> userList = userMapper.selectList(lambdaWrapper);userList.forEach(System.out::println);
}

分页查询

配置分页插件类

分页查询selectPage

 /*分页查询：年龄大于26岁
*/@Testpublic void selectPage(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.gt("age",26);//创建一个Page对象：从第一页开始查询，每页5条数据Page<User> page = new Page<>(1, 5);
/***  Page<User> page = new Page<>(1, 5,false);
*  这样表示不查询总记录数*///selectPage返回一个Ipage对象IPage<User> iPage = userMapper.selectPage(page, wrapper);//Ipage对象中有总页数，总记录条数，所有记录的get方法System.out.println("总页数"+iPage.getPages());System.out.println("总记录条数"+iPage.getTotal());List<User> userList = iPage.getRecords();userList.forEach(System.out::println);}

 /*分页查询：年龄大于26岁的姓名和年龄
*/@Testpublic void selectMapPage(){//创建一个查询条件构造器对象QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.select("name", "age").gt("age",26);//创建一个Page对象：从第一页开始查询，每页5条数据Page<User> page = new Page<>(1, 5);//selectPage返回一个Ipage对象IPage<Map<String, Object>> iPage = userMapper.selectMapsPage(page, wrapper);//Ipage对象中有总页数，总记录条数，所有记录的get方法System.out.println("总页数"+iPage.getPages());System.out.println("总记录条数"+iPage.getTotal());List<Map<String, Object>> userList = iPage.getRecords();userList.forEach(System.out::println);}

坑

Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: 无法将 NULL 插入 (“ZHXY_RSW_LOCAL”.“T_EXAM_SUBJECT”.“ID”)
使用mybatis-plus插入时，明明设置了id的值，还报这个错
在id字段上，添加 @TableId(type = IdType.INPUT)