mysql版本号锁_MySQL使用版本号实现乐观锁

乐观锁适用于读多写少的应用场景

乐观锁Version图示

Project Directory

Maven Dependency

1 <?xml version="1.0" encoding="UTF-8"?>

2

3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

5 4.0.0

6

7 HelloSpring

8 org.fool.spring

9 1.0-SNAPSHOT

10

11

12 org.springframework.boot

13 spring-boot-starter-parent

14 1.5.22.RELEASE

15

16

17

18

19 org.springframework.boot

20 spring-boot-starter-web

21

22

23 org.springframework.boot

24 spring-boot-starter-tomcat

25

26

27

28

29 org.springframework.boot

30 spring-boot-starter-jetty

31

32

33 org.eclipse.jetty.websocket

34 websocket-server

35

36

37 org.eclipse.jetty.websocket

38 javax-websocket-server-impl

39

40

41

42

43

44 org.mybatis.spring.boot

45 mybatis-spring-boot-starter

46 1.3.5

47

48

49

50 com.alibaba

51 druid-spring-boot-starter

52 1.1.20

53

54

55

56 mysql

57 mysql-connector-java

58

59

60

61 org.springframework.boot

62 spring-boot-starter-test

63 test

64

65

66

67 org.mybatis.generator

68 mybatis-generator-core

69 1.3.7

70 test

71

72

73

74

75

76

77 org.springframework.boot

78 spring-boot-maven-plugin

79

80

81 org.mybatis.generator

82 mybatis-generator-maven-plugin

83 1.3.7

84

85 sql/generatorConfig.xml

86 true

87 true

88

89

90

91

92

View Code

application.properties

1 server.port=9999

2

3 spring.datasource.url=jdbc:mysql://localhost:3306/test

4 spring.datasource.username=root5 spring.datasource.password=123456

6 spring.datasource.driver-class-name=com.mysql.jdbc.Driver7

8 mybatis.type-aliases-package=org.fool.spring.model9 mybatis.mapper-locations=classpath:mapper/**/*.xml

ddl.sql

1 CREATE TABLE`goods` (2 `id` bigint(20) unsigned NOT NULLAUTO_INCREMENT,3 `name` varchar(32) NOT NULL COMMENT '商品名称',4 `state` tinyint(4) unsigned NOT NULL COMMENT '1.正常；2.缺货',5 `version` bigint(20) unsigned NOT NULL,6 `create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),7 `update_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),8 PRIMARY KEY(`id`)9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;10

11 insert into goods(name, state, version) values('iPhone', 1, 1);12 insert into goods(name, state, version) values('iMac', 1, 1);13 insert into goods(name, state, version) values('iPad', 1, 1);14 insert into goods(name, state, version) values('iWatch', 1, 1);

generatorConfig.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2 "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

5

6

8

9

10

11

12

13

14

15

16

17

18

19 connectionURL="jdbc:mysql://localhost:3306/test"

20 userId="root"

21 password="123456">

22

23

24

25

26

27

28

29

30

31

32

33

34 type="XMLMAPPER">

35

36

37

38

39 enableCountByExample="false"enableUpdateByExample="false"

40 enableDeleteByExample="false"enableSelectByExample="false"

41 selectByExampleQueryId="false">

42

43

44

45

46

View Code

自动生成model和mapper

1 mybatis-generator:generate -e

Goods.java

1 packageorg.fool.spring.model;2

3 importjava.util.Date;4

5 public classGoods {6 privateLong id;7

8 privateString name;9

10 privateByte state;11

12 privateLong version;13

14 privateDate createTime;15

16 privateDate updateTime;17

18 publicLong getId() {19 returnid;20 }21

22 public voidsetId(Long id) {23 this.id =id;24 }25

26 publicString getName() {27 returnname;28 }29

30 public voidsetName(String name) {31 this.name = name == null ? null: name.trim();32 }33

34 publicByte getState() {35 returnstate;36 }37

38 public voidsetState(Byte state) {39 this.state =state;40 }41

42 publicLong getVersion() {43 returnversion;44 }45

46 public voidsetVersion(Long version) {47 this.version =version;48 }49

50 publicDate getCreateTime() {51 returncreateTime;52 }53

54 public voidsetCreateTime(Date createTime) {55 this.createTime =createTime;56 }57

58 publicDate getUpdateTime() {59 returnupdateTime;60 }61

62 public voidsetUpdateTime(Date updateTime) {63 this.updateTime =updateTime;64 }65

66 @Override67 publicString toString() {68 StringBuilder sb = newStringBuilder();69 sb.append(getClass().getSimpleName());70 sb.append(" [");71 sb.append("Hash = ").append(hashCode());72 sb.append(", id=").append(id);73 sb.append(", name=").append(name);74 sb.append(", state=").append(state);75 sb.append(", version=").append(version);76 sb.append(", createTime=").append(createTime);77 sb.append(", updateTime=").append(updateTime);78 sb.append("]");79 returnsb.toString();80 }81 }

View Code

GoodsMapper.java

1 packageorg.fool.spring.dao.mapper;2

3 importorg.fool.spring.model.Goods;4

5 public interfaceGoodsMapper {6 intdeleteByPrimaryKey(Long id);7

8 intinsert(Goods record);9

10 intinsertSelective(Goods record);11

12 Goods selectByPrimaryKey(Long id);13

14 intupdateByPrimaryKeySelective(Goods record);15

16 intupdateByPrimaryKey(Goods record);17 }

View Code

GoodsMapper.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2

3

4

5

6

7

8

9

10

11

12

13 id, name, state, version, create_time, update_time14

15

16 select17

18 from goods19 where id = #{id,jdbcType=BIGINT}20

21

22 delete from goods23 where id = #{id,jdbcType=BIGINT}24

25

26 insert into goods (id, name, state,27 version, create_time, update_time28 )29 values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{state,jdbcType=TINYINT},30 #{version,jdbcType=BIGINT}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}31 )32

33

34 insert into goods35

36

37 id,38

39

40 name,41

42

43 state,44

45

46 version,47

48

49 create_time,50

51

52 update_time,53

54

55

56

57 #{id,jdbcType=BIGINT},58

59

60 #{name,jdbcType=VARCHAR},61

62

63 #{state,jdbcType=TINYINT},64

65

66 #{version,jdbcType=BIGINT},67

68

69 #{createTime,jdbcType=TIMESTAMP},70

71

72 #{updateTime,jdbcType=TIMESTAMP},73

74

75

76

77 update goods78

79

80 name = #{name,jdbcType=VARCHAR},81

82

83 state = #{state,jdbcType=TINYINT},84

85

86 version = #{version,jdbcType=BIGINT},87

88

89 create_time = #{createTime,jdbcType=TIMESTAMP},90

91

92 update_time = #{updateTime,jdbcType=TIMESTAMP},93

94

95 where id = #{id,jdbcType=BIGINT}96

97

98 update goods99 set name = #{name,jdbcType=VARCHAR},100 state = #{state,jdbcType=TINYINT},101 version = #{version,jdbcType=BIGINT},102 create_time = #{createTime,jdbcType=TIMESTAMP},103 update_time = #{updateTime,jdbcType=TIMESTAMP}104 where id = #{id,jdbcType=BIGINT}105

106

View Code

GoodsMapperExt.java

1 packageorg.fool.spring.dao.mapper.extension;2

3 importorg.fool.spring.model.Goods;4

5 public interfaceGoodsMapperExt {6 intcasUpdateByPrimaryKey(Goods goods);7 }

GoodsMapperExt.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2

3

4

5

6

7

8

9

10

11

12

13 id, name, state, version, create_time, update_time14

15

16 update goods17 set name = #{name,jdbcType=VARCHAR},18 state = #{state,jdbcType=TINYINT},19 version = version + 1

20 where id = #{id,jdbcType=BIGINT} and version = #{version,jdbcType=BIGINT}21

22

Application.java

1 packageorg.fool.spring;2

3 importorg.mybatis.spring.annotation.MapperScan;4 importorg.springframework.boot.SpringApplication;5 importorg.springframework.boot.autoconfigure.SpringBootApplication;6

7 @SpringBootApplication8 @MapperScan("org.fool.spring.dao.mapper")9 public classApplication {10 public static voidmain(String[] args) {11 SpringApplication.run(Application.class, args);12 }13 }

ApplicationTest.java

1 packageorg.fool.spring.test;2

3 importorg.fool.spring.Application;4 importorg.fool.spring.dao.mapper.GoodsMapper;5 importorg.fool.spring.dao.mapper.extension.GoodsMapperExt;6 importorg.fool.spring.model.Goods;7 importorg.junit.Test;8 importorg.junit.runner.RunWith;9 importorg.springframework.beans.factory.annotation.Autowired;10 importorg.springframework.boot.autoconfigure.EnableAutoConfiguration;11 importorg.springframework.boot.test.context.SpringBootTest;12 importorg.springframework.test.context.junit4.SpringRunner;13

14 @RunWith(SpringRunner.class)15 @SpringBootTest(classes = Application.class)16 @EnableAutoConfiguration17 public classApplicationTest {18 @Autowired19 privateGoodsMapper goodsMapper;20

21 @Autowired22 privateGoodsMapperExt goodsMapperExt;23

24 @Test25 public voidtestSelect() {26 Goods result = goodsMapper.selectByPrimaryKey(1L);27 assert 1 ==result.getId();28 }29

30 @Test31 public voidtestUpdate() {32 Goods goods1 = goodsMapper.selectByPrimaryKey(1L);33 Goods goods2 = goodsMapper.selectByPrimaryKey(1L);34

35 goods1.setName("iPhone");36 goods2.setName("iPhoneXR");37

38 int result1 =goodsMapperExt.casUpdateByPrimaryKey(goods1);39 assert 1 ==result1;40 int result2 =goodsMapperExt.casUpdateByPrimaryKey(goods2);41 assert 0 ==result2;42 }43 }