- mybtisplus搭建
- mapper继承BaseMapper的方法
- 分页
- 封装查询条件->LambdaQueryWrapper
- 查询条件为空时,不让执行
- 指定查询列名
- 相等判断
- 范围查询
- like查询
- 数据库与字段名称不一致,缺少字段
- id生成策略
- 批量删除、查询
- 逻辑删除
- 乐观锁->并发
搭建
jar包
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
mapper继承BaseMapper的方法
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
- 根据对象新增
@Test // 根据对象进行新增 userMapper.insert(实体类)
// INSERT INTO user ( id, name, password, age, tel ) VALUES ( ?, ?, ?, ?, ? )
void insert(){
User user = new User(null,"aaa","aaa",12,"12");
int insert = userMapper.insert(user);
System.out.println(insert);
}
- 根据id进行删除
@Test // 根据id进行删除 DELETE FROM user WHERE id=?
void delete(){
int i = userMapper.deleteById("1588760312876683265");
}
- 根据id进行修改
@Test // 根据id进行修改 UPDATE user SET name=? WHERE id=?
void Update(){
User user = new User();
user.setId(1L);
user.setName("笑笑看");
int i = userMapper.updateById(user);
}
- 根据id查询,查询全部
@Test // 根据id进行查询
void SelectId(){
userMapper.selectById(1L);
}
@Test // 查询全部数据
void SelectUser(){
userMapper.selectList(null);
}
分页
分页代码
@Test // 分页
void PageUser(){ // 2当前页码 , 3当前显示的数据
Page page = new Page(2,3);
Page page1 = userMapper.selectPage(page, null);
System.out.println("当前页码值:"+page.getCurrent());
System.out.println("每页显示数:"+page.getSize());
System.out.println("一共多少页:"+page.getPages());
System.out.println("一共多少条数据:"+page.getTotal());
System.out.println("数据:"+page.getRecords());
}
分页插件
@Configuration
public class MPconfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
//1 创建MybatisPlusInterceptor拦截器对象
MybatisPlusInterceptor mpInterceptor=new MybatisPlusInterceptor();
//2 添加分页拦截器
mpInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return mpInterceptor;
}
}
LambdaQueryWrapper
// QueryWrapper 添加条件
@Test
public void QueryUsersList(){
QueryWrapper<User> qw = new QueryWrapper<User>();
// 1. qw.lt("age",18);
// 2. qw.lambda().lt(User::getAge,18);
// List list = userMapper.selectList(qw);
// System.out.println("查询结果为:"+list);
LambdaQueryWrapper<User> uw = new LambdaQueryWrapper<>();
// 设置多条件
uw.lt(User::getAge, 30);
uw.ge(User::getAge, 10);
// uw.lt(User::getAge, 30).or().ge(User::getAge, 10);
List<User> users = userMapper.selectList(uw);
System.out.println(users);
}
参数的值不为空
@Test
public void QueryUsersList2(){
User user = new User();
LambdaQueryWrapper<User> uw = new LambdaQueryWrapper<>();
// 当前参数为空时,不执行这个条件判断
uw.lt(user.getAge()!=null,User::getAge, user.getAge());
uw.ge(User::getAge,10);
List<User> users = userMapper.selectList(uw);
System.out.println(users);
}
查询指定列名
@Test
public void QueryUsersList3(){
// 1.SELECT id,name,name FROM user
LambdaQueryWrapper<User> uw = new LambdaQueryWrapper<>();
uw.select(User::getId,User::getName,User::getName);
// 2.SELECT id,name,age FROM user
QueryWrapper<User> queryWrapper = new QueryWrapper();
queryWrapper.select("id","name","age");
List<User> users = userMapper.selectList(uw);
System.out.println(users);
}
count(*)函数 分组函数
@Test
public void QueryUsersList4(){
QueryWrapper<User> queryWrapper = new QueryWrapper();
// 查询根据年龄分组的总记录数
queryWrapper.select("count(*) as num , age ,name");
queryWrapper.groupBy("tel");
List<User> users = userMapper.selectList(queryWrapper);
System.out.println(users);
}
相等判断
@Test
public void QueryUsersList5(){
LambdaQueryWrapper<User> lw = new LambdaQueryWrapper<>();
// (name = ? AND age = ?)
lw.eq(User::getName,"xiaowang")
.eq(User::getAge,21);
// 查询多条数据的时候使用
List<User> users = userMapper.selectList(lw);
// 查询一条数据的时候使用
User user = userMapper.selectOne(lw);
System.out.println(user);
System.out.println(users);
}
范围查询
@Test
public void Test(){
LambdaQueryWrapper<User> lw = new LambdaQueryWrapper<>();
// betwen age BETWEEN ? AND ?
lw.between(User::getAge,10,30);
List<User> users = userMapper.selectList(lw);
System.out.println(users);
}
like查询
@Test
public void Test1(){
LambdaQueryWrapper<User> lw = new LambdaQueryWrapper<>();
// lw.like(User::getName,'w'); // LIKE %w%
// lw.likeLeft(User::getName,"w"); // like %w
lw.likeRight(User::getName,"w"); // LIKE w%
List<User> users = userMapper.selectList(lw);
System.out.println(users);
}
不一致
@TableName("User") // 设置数据库表名
public class User {
@TableId(type = IdType.ASSIGN_ID) //1588880194645585922
private Long id;
private String name;
@TableField(value = "password",select = false) //设置数据库的名称,密码不在数据库查询
private String pwd;
private Integer age;
private String tel;
@TableField(exist = false) // 字段不在数据库中存在
private Integer online;
}
id生成策略
mybatis-plus:
# configuration:
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# global-config:
# banner: false
db-config:
id-type: assign_id #设置全局id的生成策略
# table-prefix: tab_ #设置全局表名前缀
@TableId(type = IdType.ASSIGN_ID) //1588880194645585922
private Long id;
批量删除、查询
@Test //批量删除 DELETE FROM User WHERE id IN ( ? , ? , ? )
public void DeleteUser(){
ArrayList<Long> list = new ArrayList<>();
list.add(1588880194645585922L);
list.add(1588758450714722306L);
list.add(123123123123132L);
int i = userMapper.deleteBatchIds(list);
System.out.println(i);
}
@Test //批量查询 DELETE FROM User WHERE id IN ( ? , ? , ? )
public void QueryUser(){
ArrayList<Long> list = new ArrayList<>();
list.add(1L);
list.add(2L);
list.add(3L);
int i = userMapper.deleteBatchIds(list);
System.out.println(i);
}
逻辑删除
逻辑删除,字段新增逻辑删除字段,用0代表存在,1代表删除。
不会删除这条数据,执行删除操作时,会将逻辑删除字段修改,查询时,不会查询已逻辑删除的数据
字段设置 // value 代表没用被删除 delval代表被删除
@TableLogic(value = "0" , delval = "1")
private Integer deleted;
查询代码
@Test
// 查询全部数据 SELECT id,name,age,tel,deleted FROM User WHERE deleted=0
void SelectUser(){
userMapper.selectList(null);
}
删除代码
@Test //逻辑批量删除 UPDATE User SET deleted=1 WHERE id IN ( ? ) AND deleted=0
public void DeleteUser(){
ArrayList<Long> list = new ArrayList<>();
list.add(00000000000000000002L);
int i = userMapper.deleteBatchIds(list);
System.out.println(i);
}
全局配置:
乐观锁
再数据库中,新增一个字段,作为锁字段,字段上面添加@Version,再添加乐观锁拦截器
再执行更新时: select xx , Version(1) + 1 where Version = 1
定义乐观锁字段
@Version
private Integer version;
添加乐观锁拦截器
@Configuration
public class MPconfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
//1 创建MybatisPlusInterceptor拦截器对象
MybatisPlusInterceptor mpInterceptor=new MybatisPlusInterceptor();
//2 添加分页拦截器
mpInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
//3.添加乐观锁
mpInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return mpInterceptor;
}
}
1.获取到乐观锁字段的数据,才可以执行乐观锁
@Test // 乐观锁 UPDATE User SET name=?, version=? WHERE id=? AND version=? AND deleted=0
void Update(){
User user = new User();
user.setId(00000000000000000001L);
user.setVersion(3);
user.setName("name-aaa");
int i = userMapper.updateById(user);
}
2.先查询数据的id,可以执行乐观锁
@Test // 根据id进行修改 UPDATE User SET name=?, age=?, tel=?, version=? WHERE id=? AND version=? AND deleted=0
void Update(){
User user = userMapper.selectById(00000000000000000001L);
user.setName("name-aaa");
int i = userMapper.updateById(user);
}
3.当同时操作Version 为1的数据时,只有一个可以执行成功
@Test
public void test00(){
User aaaa = userMapper.selectById(00000000000000000001L);
User bbbb = userMapper.selectById(00000000000000000001L);
bbbb.setName("bbbb要购买一个");
userMapper.updateById(bbbb);
aaaa.setName("aaaa要购买一个");
userMapper.updateById(aaaa);
// aaaa 与 bbbb 同时获取到乐观锁字段的值为1,bbbb先执行操作后,乐观锁字段变为2,
// 但是aaaa获取的乐观锁字段为1,所以bbbb执行成功,aaaa执行失败
}