目录
4.3 更新时,version + 1,如果 where 语句中的 version 版本不对,则更新失败
一 更新操作
注意:update时生成的sql自动是动态sql:UPDATE user SET age=? WHERE id=?
@Test
public void testUpdateById(){
User user = new User();
user.setId(1244892079889334273L);
user.setAge(28);
user.setName("Annie");
int result = userMapper.updateById(user);
System.out.println("影响的行数:" + result);
}
测试结果
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@c6e0f32] was not registered for synchronization because synchronization is not active
updateFill 。。。。。。
2020-11-14 16:37:59.671 INFO 21100 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-11-14 16:37:59.997 INFO 21100 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@1873189623 wrapping com.mysql.cj.jdbc.ConnectionImpl@73545b80] will not be managed by Spring
==> Preparing: UPDATE user SET name=?, age=? WHERE id=?
==> Parameters: Annie(String), 28(Integer), 2020-11-14 16:37:59.665(Timestamp), 1244892079889334273(Long)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@c6e0f32]
影响的行数:1
二 自动填充
需求描述:
项目中经常会遇到一些数据,每次都使用相同的方式填充,例如记录的创建时间,更新时间等。
我们可以使用MyBatis Plus的自动填充功能,完成这些字段的赋值工作
1 数据库修改
在 User 表中添加 datetime 类型的新的字段 create_time、update_time
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2 实体类修改
实体上增加字段并添加自动填充注解
@Data
public class User {
private Long id;
private String name;
private Integer age;
private String email;
// 自动填充注解
@TableField(fill = FieldFill.INSERT)
private Date createTime;
// 自动填充注解
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;
}
3 实现元对象处理器接口
注意:不要忘记添加 @Component 注解
/**
* @className: MyMetaObjectHandler
* @description: 实现元对象处理器接口
* @date: 2020/11/14
* @author: cakin
*/
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
/**
* 功能描述:添加数据时处理
*
* @param metaObject 元对象
* @author cakin
* @date 2020/11/14
* @description: 当添加一个对象时,如果一个对象中有 createTime 和 updateTime,会自动填充这两个字段,值为 new Date()
*/
@Override
public void insertFill(MetaObject metaObject) {
System.out.println("insertFill 。。。。。。");
this.setFieldValByName("createTime", new Date(), metaObject);
this.setFieldValByName("updateTime", new Date(), metaObject);
}
/**
* 功能描述:更新数据时处理
*
* @param metaObject 元对象
* @author cakin
* @date 2020/11/14
* @description: 当更新一个对象时,如果一个对象中有 updateTime,会自动填充这个字段,值为 new Date()
*/
@Override
public void updateFill(MetaObject metaObject) {
System.out.println("updateFill 。。。。。。");
this.setFieldValByName("updateTime", new Date(), metaObject);
}
}
4 测试代码
@Test
public void testInsert(){
User user = new User();
user.setName("cakin");
user.setEmail("798103175@qq.com");
user.setAge(18);
// 并没设置创建和更新时间
// user.setCreateTime(new Date());
// user.setUpdateTime(new Date());
//返回值:影响的行数
int result = userMapper.insert(user);
System.out.println("影响的行数:" + result); // 影响的行数
System.out.println("user id:" + user.getId()); // id自动回填
}
5 测试结果
三 乐观锁
1 场景
一件商品,成本价是80元,售价是100元。老板先是通知小李,说你去把商品价格增加50元。小李正在玩游戏,耽搁了一个小时。正好一个小时后,老板觉得商品价格增加到150元,价格太高,可能会影响销量。又通知小王,你把商品价格降低30元。
此时,小李和小王同时操作商品后台系统。小李操作的时候,系统先取出商品价格100元;小王也在操作,取出的商品价格也是100元。小李将价格加了50元,并将100+50=150元存入了数据库;小王将商品减了30元,并将100-30=70元存入了数据库。是的,如果没有锁,小李的操作就完全被小王的覆盖了。
现在商品价格是70元,比成本价低10元。几分钟后,这个商品很快出售了1千多件商品,老板亏1多万。
2 乐观锁与悲观锁
乐观锁:小王保存价格前,会检查下价格是否被人修改过了。如果被修改过了,则重新取出的被修改后的价格,150元,这样他会将120元存入数据库。一般我们通过在表中增加 version 字段来实现。
悲观锁:小李取出数据后,小王只能等小李操作完之后,才能对价格进行操作,也会保证最终的价格是120元。
3 模拟不使用乐观锁修改价格产生冲突
3.1 数据库中增加商品表
CREATE TABLE `product` (
`id` bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '商品名称',
`price` int(11) DEFAULT '0' COMMENT '价格',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.2 添加数据
INSERT INTO product (id, NAME, price) VALUES (1, '外星人笔记本', 100);
3.3 实体类
/**
* @className: Product
* @description: 产品
* @date: 2020/11/14
* @author: cakin
*/
@Data
public class Product {
private Long id;
private String name;
private Integer price;
}
3.4 Mapper
/**
* @className: ProductMapper
* @description: 产品mapper
* @date: 2020/11/14
* @author: cakin
*/
@Repository
public interface ProductMapper extends BaseMapper<Product> {
}
3.5 测试代码
@Test
public void testConcurrentUpdate() {
// 1 小李获取数据
Product p1 = productMapper.selectById(1L);
System.out.println("小李取出的价格:" + p1.getPrice());
// 2 小王获取数据
Product p2 = productMapper.selectById(1L);
System.out.println("小王取出的价格:" + p2.getPrice());
// 3 小李加了50,存入数据库
p1.setPrice(p1.getPrice() + 50);
productMapper.updateById(p1);
// 4 小王减了30员,存入数据库
p2.setPrice(p2.getPrice() - 30);
int result = productMapper.updateById(p2);
if (result == 0) {
System.out.println("小王更新失败");
//发起重试
p2 = productMapper.selectById(1L);
p2.setPrice(p2.getPrice() - 30);
productMapper.updateById(p2);
}
// 最后的结果
// 用户看到的商品价格
Product p3 = productMapper.selectById(1L);
System.out.println("最后的结果:" + p3.getPrice());
}
3.6 测试结果
4 乐观锁解决方案
4.1 数据库中添加version字段
CREATE TABLE `product` (
`id` bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '商品名称',
`price` int(11) DEFAULT '0' COMMENT '价格',
`version` int(11) DEFAULT '0' COMMENT '乐观锁版本号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
4.2 取出记录时,获取当前version
SELECT id,`name`,price,`version` FROM product WHERE id=1
4.3 更新时,version + 1,如果 where 语句中的 version 版本不对,则更新失败
UPDATE product SET price=price+50, `version`=`version` + 1 WHERE id=1 AND `version`=1
接下来介绍如何在Mybatis-Plus项目中,使用乐观锁。
5 乐观锁实现流程
5.1 修改实体类
添加 version 字段以及 @Version 注解
@Data
public class Product {
private Long id;
private String name;
private Integer price;
/**
* 乐观锁的版本号
*/
@Version
private Integer version;
}
5.2 创建配置文件
创建包 config,创建文件 MybatisPlusConfig.java
此时可以删除主类中的 @MapperScan 扫描注解,并将该注解放到配置类上。
@EnableTransactionManagement //事务处理
@Configuration
@MapperScan("com.atguigu.mybatis_plus.mapper")
public class MyBatisPlusConfig {
}
5.3 注册乐观锁插件
/**
* 乐观锁插件
*
* @return
*/
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
return new OptimisticLockerInterceptor();
}
5.4 测试结果
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3a2b2322] was not registered for synchronization because synchronization is not active
2020-11-14 17:24:45.728 INFO 20352 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-11-14 17:24:46.114 INFO 20352 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@1030384622 wrapping com.mysql.cj.jdbc.ConnectionImpl@36ddaebf] will not be managed by Spring
==> Preparing: SELECT id,name,price,version FROM product WHERE id=?
==> Parameters: 1(Long)
<== Columns: id, name, price, version
<== Row: 1, 外星人笔记本, 100, 0
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3a2b2322]
小李取出的价格:100
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ff55ff] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@891232836 wrapping com.mysql.cj.jdbc.ConnectionImpl@36ddaebf] will not be managed by Spring
==> Preparing: SELECT id,name,price,version FROM product WHERE id=?
==> Parameters: 1(Long)
<== Columns: id, name, price, version
<== Row: 1, 外星人笔记本, 100, 0
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ff55ff]
小王取出的价格:100
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@148c7c4b] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@593045830 wrapping com.mysql.cj.jdbc.ConnectionImpl@36ddaebf] will not be managed by Spring
==> Preparing: UPDATE product SET name=?, price=?, version=? WHERE id=? AND version=?
==> Parameters: 外星人笔记本(String), 150(Integer), 1(Integer), 1(Long), 0(Integer)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@148c7c4b]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7c2b58c0] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@296974277 wrapping com.mysql.cj.jdbc.ConnectionImpl@36ddaebf] will not be managed by Spring
==> Preparing: UPDATE product SET name=?, price=?, version=? WHERE id=? AND version=?
==> Parameters: 外星人笔记本(String), 70(Integer), 1(Integer), 1(Long), 0(Integer)
<== Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7c2b58c0]
小王更新失败
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5c60b0a0] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2049646260 wrapping com.mysql.cj.jdbc.ConnectionImpl@36ddaebf] will not be managed by Spring
==> Preparing: SELECT id,name,price,version FROM product WHERE id=?
==> Parameters: 1(Long)
<== Columns: id, name, price, version
<== Row: 1, 外星人笔记本, 150, 1
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5c60b0a0]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5833f5cd] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@17808347 wrapping com.mysql.cj.jdbc.ConnectionImpl@36ddaebf] will not be managed by Spring
==> Preparing: UPDATE product SET name=?, price=?, version=? WHERE id=? AND version=?
==> Parameters: 外星人笔记本(String), 120(Integer), 2(Integer), 1(Long), 1(Integer)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5833f5cd]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@31d6f3fe] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1980560788 wrapping com.mysql.cj.jdbc.ConnectionImpl@36ddaebf] will not be managed by Spring
==> Preparing: SELECT id,name,price,version FROM product WHERE id=?
==> Parameters: 1(Long)
<== Columns: id, name, price, version
<== Row: 1, 外星人笔记本, 120, 2
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@31d6f3fe]
最后的结果:120
2020-11-14 17:24:46.531 INFO 20352 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2020-11-14 17:24:46.554 INFO 20352 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
Process finished with exit code 0