MySQL使用版本号实现乐观锁

原创转载请注明出处：https://www.cnblogs.com/agilestyle/p/11608581.html

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

乐观锁Version图示

MySQL使用版本号实现乐观锁-LMLPHP

Project Directory

MySQL使用版本号实现乐观锁-LMLPHP

Maven Dependency

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

 <project xmlns="http://maven.apache.org/POM/4.0.0"

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

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

     <modelVersion>4.0.0</modelVersion>

     <groupId>HelloSpring</groupId>

     <artifactId>org.fool.spring</artifactId>

     <version>1.0-SNAPSHOT</version>

     <parent>

         <groupId>org.springframework.boot</groupId>

         <artifactId>spring-boot-starter-parent</artifactId>

         <version>1.5.22.RELEASE</version>

     </parent>

     <dependencies>

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-web</artifactId>

             <exclusions>

                 <exclusion>

                     <groupId>org.springframework.boot</groupId>

                     <artifactId>spring-boot-starter-tomcat</artifactId>

                 </exclusion>

             </exclusions>

         </dependency>

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-jetty</artifactId>

             <exclusions>

                 <exclusion>

                     <groupId>org.eclipse.jetty.websocket</groupId>

                     <artifactId>websocket-server</artifactId>

                 </exclusion>

                 <exclusion>

                     <groupId>org.eclipse.jetty.websocket</groupId>

                     <artifactId>javax-websocket-server-impl</artifactId>

                 </exclusion>

             </exclusions>

         </dependency>

         <dependency>

             <groupId>org.mybatis.spring.boot</groupId>

             <artifactId>mybatis-spring-boot-starter</artifactId>

             <version>1.3.5</version>

         </dependency>

         <dependency>

             <groupId>com.alibaba</groupId>

             <artifactId>druid-spring-boot-starter</artifactId>

             <version>1.1.20</version>

         </dependency>

         <dependency>

             <groupId>mysql</groupId>

             <artifactId>mysql-connector-java</artifactId>

         </dependency>

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-test</artifactId>

             <scope>test</scope>

         </dependency>

         <dependency>

             <groupId>org.mybatis.generator</groupId>

             <artifactId>mybatis-generator-core</artifactId>

             <version>1.3.7</version>

             <scope>test</scope>

         </dependency>

     </dependencies>

     <build>

         <plugins>

             <plugin>

                 <groupId>org.springframework.boot</groupId>

                 <artifactId>spring-boot-maven-plugin</artifactId>

             </plugin>

             <plugin>

                 <groupId>org.mybatis.generator</groupId>

                 <artifactId>mybatis-generator-maven-plugin</artifactId>

                 <version>1.3.7</version>

                 <configuration>

                     <configurationFile>sql/generatorConfig.xml</configurationFile>

                     <verbose>true</verbose>

                     <overwrite>true</overwrite>

                 </configuration>

             </plugin>

         </plugins>

     </build>

 </project>

application.properties

 server.port=

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

 spring.datasource.username=root

 spring.datasource.password=

 spring.datasource.driver-class-name=com.mysql.jdbc.Driver

 mybatis.type-aliases-package=org.fool.spring.model

 mybatis.mapper-locations=classpath:mapper/**/*.xml

ddl.sql

 CREATE TABLE `goods` (

   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

   `name` varchar(32) NOT NULL COMMENT '商品名称',

   `state` tinyint(4) unsigned NOT NULL COMMENT '1.正常；2.缺货',

   `version` bigint(20) unsigned NOT NULL,

   `create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

   `update_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

   PRIMARY KEY (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 insert into goods(name, state, version) values('iPhone', 1, 1);

 insert into goods(name, state, version) values('iMac', 1, 1);

 insert into goods(name, state, version) values('iPad', 1, 1);

 insert into goods(name, state, version) values('iWatch', 1, 1);

generatorConfig.xml

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

 <!DOCTYPE generatorConfiguration

         PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"

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

 <generatorConfiguration>

     <classPathEntry

             location="/Users/${user.name}/.m2/repository/mysql/mysql-connector-java/5.1.48/mysql-connector-java-5.1.48.jar"/>

     <context id="test" targetRuntime="MyBatis3">

         <plugin type="org.mybatis.generator.plugins.ToStringPlugin"/>

         <plugin type="org.mybatis.generator.plugins.UnmergeableXmlMappersPlugin"/>

         <commentGenerator>

             <property name="suppressAllComments" value="true"/>

         </commentGenerator>

         <jdbcConnection driverClass="com.mysql.jdbc.Driver"

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

                         userId="root"

                         password="123456">

         </jdbcConnection>

         <javaModelGenerator targetPackage="org.fool.spring.model" targetProject="src/main/java">

             <property name="enableSubPackages" value="true"/>

             <property name="trimStrings" value="true"/>

         </javaModelGenerator>

         <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources">

             <property name="enableSubPackages" value="true"/>

         </sqlMapGenerator>

         <javaClientGenerator targetPackage="org.fool.spring.dao.mapper" targetProject="src/main/java"

                              type="XMLMAPPER">

             <property name="enableSubPackages" value="true"/>

         </javaClientGenerator>

         <table tableName="goods" domainObjectName="Goods"

                enableCountByExample="false" enableUpdateByExample="false"

                enableDeleteByExample="false" enableSelectByExample="false"

                selectByExampleQueryId="false">

         </table>

     </context>

 </generatorConfiguration>

自动生成model和mapper

 mybatis-generator:generate -e

Goods.java

 package org.fool.spring.model;

 import java.util.Date;

 public class Goods {

     private Long id;

     private String name;

     private Byte state;

     private Long version;

     private Date createTime;

     private Date updateTime;

     public Long getId() {

         return id;

     }

     public void setId(Long id) {

         this.id = id;

     }

     public String getName() {

         return name;

     }

     public void setName(String name) {

         this.name = name == null ? null : name.trim();

     }

     public Byte getState() {

         return state;

     }

     public void setState(Byte state) {

         this.state = state;

     }

     public Long getVersion() {

         return version;

     }

     public void setVersion(Long version) {

         this.version = version;

     }

     public Date getCreateTime() {

         return createTime;

     }

     public void setCreateTime(Date createTime) {

         this.createTime = createTime;

     }

     public Date getUpdateTime() {

         return updateTime;

     }

     public void setUpdateTime(Date updateTime) {

         this.updateTime = updateTime;

     }

     @Override

     public String toString() {

         StringBuilder sb = new StringBuilder();

         sb.append(getClass().getSimpleName());

         sb.append(" [");

         sb.append("Hash = ").append(hashCode());

         sb.append(", id=").append(id);

         sb.append(", name=").append(name);

         sb.append(", state=").append(state);

         sb.append(", version=").append(version);

         sb.append(", createTime=").append(createTime);

         sb.append(", updateTime=").append(updateTime);

         sb.append("]");

         return sb.toString();

     }

 }

GoodsMapper.java

 package org.fool.spring.dao.mapper;

 import org.fool.spring.model.Goods;

 public interface GoodsMapper {

     int deleteByPrimaryKey(Long id);

     int insert(Goods record);

     int insertSelective(Goods record);

     Goods selectByPrimaryKey(Long id);

     int updateByPrimaryKeySelective(Goods record);

     int updateByPrimaryKey(Goods record);

 }

GoodsMapper.xml

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

 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 <mapper namespace="org.fool.spring.dao.mapper.GoodsMapper">

   <resultMap id="BaseResultMap" type="org.fool.spring.model.Goods">

     <id column="id" jdbcType="BIGINT" property="id" />

     <result column="name" jdbcType="VARCHAR" property="name" />

     <result column="state" jdbcType="TINYINT" property="state" />

     <result column="version" jdbcType="BIGINT" property="version" />

     <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />

     <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />

   </resultMap>

   <sql id="Base_Column_List">

     id, name, state, version, create_time, update_time

   </sql>

   <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">

     select

     <include refid="Base_Column_List" />

     from goods

     where id = #{id,jdbcType=BIGINT}

   </select>

   <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">

     delete from goods

     where id = #{id,jdbcType=BIGINT}

   </delete>

   <insert id="insert" parameterType="org.fool.spring.model.Goods">

     insert into goods (id, name, state,

       version, create_time, update_time

       )

     values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{state,jdbcType=TINYINT},

       #{version,jdbcType=BIGINT}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}

       )

   </insert>

   <insert id="insertSelective" parameterType="org.fool.spring.model.Goods">

     insert into goods

     <trim prefix="(" suffix=")" suffixOverrides=",">

       <if test="id != null">

         id,

       </if>

       <if test="name != null">

         name,

       </if>

       <if test="state != null">

         state,

       </if>

       <if test="version != null">

         version,

       </if>

       <if test="createTime != null">

         create_time,

       </if>

       <if test="updateTime != null">

         update_time,

       </if>

     </trim>

     <trim prefix="values (" suffix=")" suffixOverrides=",">

       <if test="id != null">

         #{id,jdbcType=BIGINT},

       </if>

       <if test="name != null">

         #{name,jdbcType=VARCHAR},

       </if>

       <if test="state != null">

         #{state,jdbcType=TINYINT},

       </if>

       <if test="version != null">

         #{version,jdbcType=BIGINT},

       </if>

       <if test="createTime != null">

         #{createTime,jdbcType=TIMESTAMP},

       </if>

       <if test="updateTime != null">

         #{updateTime,jdbcType=TIMESTAMP},

       </if>

     </trim>

   </insert>

   <update id="updateByPrimaryKeySelective" parameterType="org.fool.spring.model.Goods">

     update goods

     <set>

       <if test="name != null">

         name = #{name,jdbcType=VARCHAR},

       </if>

       <if test="state != null">

         state = #{state,jdbcType=TINYINT},

       </if>

       <if test="version != null">

         version = #{version,jdbcType=BIGINT},

       </if>

       <if test="createTime != null">

         create_time = #{createTime,jdbcType=TIMESTAMP},

       </if>

       <if test="updateTime != null">

         update_time = #{updateTime,jdbcType=TIMESTAMP},

       </if>

     </set>

     where id = #{id,jdbcType=BIGINT}

   </update>

   <update id="updateByPrimaryKey" parameterType="org.fool.spring.model.Goods">

     update goods

     set name = #{name,jdbcType=VARCHAR},

       state = #{state,jdbcType=TINYINT},

       version = #{version,jdbcType=BIGINT},

       create_time = #{createTime,jdbcType=TIMESTAMP},

       update_time = #{updateTime,jdbcType=TIMESTAMP}

     where id = #{id,jdbcType=BIGINT}

   </update>

 </mapper>

GoodsMapperExt.java

 package org.fool.spring.dao.mapper.extension;

 import org.fool.spring.model.Goods;

 public interface GoodsMapperExt {

     int casUpdateByPrimaryKey(Goods goods);

 }

GoodsMapperExt.xml

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

 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 <mapper namespace="org.fool.spring.dao.mapper.extension.GoodsMapperExt">

     <resultMap id="BaseResultMap" type="org.fool.spring.model.Goods">

         <id column="id" jdbcType="BIGINT" property="id"/>

         <result column="name" jdbcType="VARCHAR" property="name"/>

         <result column="state" jdbcType="TINYINT" property="state"/>

         <result column="version" jdbcType="BIGINT" property="version"/>

         <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>

         <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>

     </resultMap>

     <sql id="Base_Column_List">

         id, name, state, version, create_time, update_time

     </sql>

     <update id="casUpdateByPrimaryKey" parameterType="org.fool.spring.model.Goods">

         update goods

         set name = #{name,jdbcType=VARCHAR},

         state = #{state,jdbcType=TINYINT},

         version = version + 1

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

     </update>

 </mapper>

Application.java

 package org.fool.spring;

 import org.mybatis.spring.annotation.MapperScan;

 import org.springframework.boot.SpringApplication;

 import org.springframework.boot.autoconfigure.SpringBootApplication;

 @SpringBootApplication

 @MapperScan("org.fool.spring.dao.mapper")

 public class Application {

     public static void main(String[] args) {

         SpringApplication.run(Application.class, args);

     }

 }

ApplicationTest.java

 package org.fool.spring.test;

 import org.fool.spring.Application;

 import org.fool.spring.dao.mapper.GoodsMapper;

 import org.fool.spring.dao.mapper.extension.GoodsMapperExt;

 import org.fool.spring.model.Goods;

 import org.junit.Test;

 import org.junit.runner.RunWith;

 import org.springframework.beans.factory.annotation.Autowired;

 import org.springframework.boot.autoconfigure.EnableAutoConfiguration;

 import org.springframework.boot.test.context.SpringBootTest;

 import org.springframework.test.context.junit4.SpringRunner;

 @RunWith(SpringRunner.class)

 @SpringBootTest(classes = Application.class)

 @EnableAutoConfiguration

 public class ApplicationTest {

     @Autowired

     private GoodsMapper goodsMapper;

     @Autowired

     private GoodsMapperExt goodsMapperExt;

     @Test

     public void testSelect() {

         Goods result = goodsMapper.selectByPrimaryKey(1L);

         assert 1 == result.getId();

     }

     @Test

     public void testUpdate() {

         Goods goods1 = goodsMapper.selectByPrimaryKey(1L);

         Goods goods2 = goodsMapper.selectByPrimaryKey(1L);

         goods1.setName("iPhone");

         goods2.setName("iPhoneXR");

         int result1 = goodsMapperExt.casUpdateByPrimaryKey(goods1);

         assert 1 == result1;

         int result2 = goodsMapperExt.casUpdateByPrimaryKey(goods2);

         assert 0 == result2;

     }

 }