<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.5.0</version><relativePath/><!-- lookup parent from repository --></parent><dependencies><!--注意事项1:由于mybatisPlus并未被收录到idea的系统内置配置,无法直接选择加入--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.1</version></dependency><!--数据源 --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.16</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies>
4).制作实体类与表结构
(类名与表名对应,属性名与字段名对应)
createdatabaseifnotexists mybatisplus_db characterset utf8;use mybatisplus_db;CREATETABLEuser(
id bigint(20)primarykeyauto_increment,
name varchar(32)notnull,
password varchar(32)notnull,
age int(3)notnull,
tel varchar(32)notnull);insertintouservalues(null,'tom','123456',12,'12345678910');insertintouservalues(null,'jack','123456',8,'12345678910');insertintouservalues(null,'jerry','123456',15,'12345678910');insertintouservalues(null,'tom','123456',9,'12345678910');insertintouservalues(null,'snake','123456',28,'12345678910');insertintouservalues(null,'张益达','123456',22,'12345678910');insertintouservalues(null,'张大炮','123456',16,'12345678910');
# 取消SpringBoot启动banner图标#spring:main:banner-mode: off # 关闭SpringBoot启动图标(banner)## 取消MybatisPlus启动banner图标# mybatis-plus日志控制台输出mybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:banner: off # 关闭mybatisplus启动图标
2、条件查询方式
1、条件查询方式
MyBatisPlus将书写复杂的SQL查询条件进行了封装,使用编程的形式完成查询条件的组合。
//方式一:按条件查询QueryWrapper<User> qw =newQueryWrapper<>();//lt小于:less than; gt大于:greater than//le小于等于:less than or equal//ge大于等于:greater than or equal
qw.lt("age",18);//where age < 18List<User> userList = userDao.selectList(qw);//select * from userSystem.out.println(userList);
2、或者关系(or)
//或者关系LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//或者关系:小于18岁或者大于10岁//where age < 18 or age > 10
lqw.lt(User::getAge,18).or().gt(User::getAge,10);List<User> userList = userDao.selectList(lqw);System.out.println(userList);
- 购物设定价格区间、户籍设定年龄区间(le ge匹配 或 between匹配)
LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//范围查询 lt le gt ge eq between
lqw.between(User::getAge,10,30);List<User> userList = userDao.selectList(lqw);System.out.println(userList);
- 查信息,搜索新闻(非全文检索版:like匹配)
//模糊匹配LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//%,_必须是一个//likeLeft() == where name like '%J'//likeRight() == where name like 'J%'
lqw.like(User::getName,"J");//where name like '%J%'List<User> userList = userDao.selectList(lqw);System.out.println(userList);/*****************************************************************/- 排序
//排序LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//lqw.orderByAsc(User::getAge); //升序
lqw.orderByDesc(User::getAge);//降序List<User> userList = userDao.selectList(lqw);System.out.println(userList);
# 创建新的表tbl_user做测试:USE mybatisplus_db;CREATETABLE tbl_user (
id BIGINT(20)PRIMARYKEYauto_increment,`name`VARCHAR(32)NOTNULL,
pwd VARCHAR(32)NOTNULL,
age INT(3)NOTNULL,
tel VARCHAR(32)NOTNULL,
deleted INT(1)DEFAULT'0',
version INT(11)DEFAULT'1');insertinto tbl_user(name,pwd,age,tel)values('snake','123456',28,'12345678910');insertinto tbl_user(name,pwd,age,tel)values('张益达','123456',22,'12345678910');insertinto tbl_user(name,pwd,age,tel)VALUES('张大炮','123456',16,'12345678910');
1、按照主键删除多条记录
//删除指定多条数据List<Long> list =newArrayList<>();
list.add(1402551342481838081L);
list.add(1402553134049501186L);
list.add(1402553619611430913L);//where id in (1,2,3)
userDao.deleteBatchIds(list);2、 根据主键查询多条记录
//查询指定多条数据List<Long> list =newArrayList<>();
list.add(1L);
list.add(3L);
list.add(4L);List<User> users = userDao.selectBatchIds(list);
3.逻辑删除
- 删除操作业务问题:业务数据从数据库中丢弃
- 物理删除:将数据从硬盘(数据库)当中删除(delete from user where id = 1)
- 逻辑删除:为数据设置是否可用状态字段,删除时设置状态字段为不可用状态,
- 数据保留在数据库中(update user set deleted=1 where id = 1)
1、数据库表中添加逻辑删除标记字段,deleted默认值为0
- 注意:不要使用SQL的关键字做为表名或者字段名,容易出错
所以:订单order - orders
是否删除字段delete -> deleted
//mybatis plus的配置类@ConfigurationpublicclassMpConfig{@BeanpublicMybatisPlusInterceptormpInterceptor(){//1.定义Mp拦截器MybatisPlusInterceptor mpInterceptor =newMybatisPlusInterceptor();//2.添加分页拦截器
mpInterceptor.addInnerInterceptor(newPaginationInnerInterceptor());//3.添加乐观锁拦截器: set version = version+1 where version =?
mpInterceptor.addInnerInterceptor(newOptimisticLockerInnerInterceptor());return mpInterceptor;}}
4、使用乐观锁机制在修改前必须先获取到对应数据的verion方可正常进行
@TestvoidtestUpdate()throwsInterruptedException{//1.先通过要修改的数据id将当前数据查询出来//目的:在更新之前必须先知道当前的versionUser user = userDao.selectById(222L);System.out.println(user);//2.将要修改的属性逐一设置进去
user.setName("Jock888");Thread.sleep(15*1000);//在休眠期间使用MySQL客户端去修改version值//UPDATE tbl_user SET name=?, age=?, tel=?, version=?// WHERE id=? AND version=? AND deleted=0int num = userDao.updateById(user);if(num ==0){//在我查询 和 修改 之间,有其他人(线程)修改了此数据,导致版本号发生了变化//数据库受影响行数==0,说明修改失败,有两种处理方案://1. 提示用户修改失败,让用户自行决定是否再次修改System.err.println("修改失败,需要再次修改");//2. TODO 重新调用查询+修改,自动重试(危险)}else{System.out.println("修改成功");}}
- 1、乐观锁实现,必须先查询,再更新
- 2、乐观锁拦截器负责添加2个sql片段:
update user set version = 原来的版本号+1 where version=原来查询出来的版本号
//UPDATE tbl_user SET name=?, age=?, tel=?, version=? WHERE id=? AND version=?