事务实践 手动创建提交事务 复现幻读 枚举类应用

我的项目结构

#我用到的表
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
`id` int(11) NOT NULL COMMENT 'id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
COMMENT '名称',
`price` decimal(12, 2) NOT NULL COMMENT '价格',
`price_unit` enum('CNY','USD') CHARACTER SET utf8 COLLATE utf8_general_ci
NOT NULL DEFAULT 'CNY' COMMENT '价格单位',
`description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `IDX_UNIQUE_NAME`(`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT =
'商品' ROW_FORMAT = Dynamic#数据库状态设置 对于load_file语句使用不了的去mysql配置文件加配置 具体参考论坛其它教程
show global VARIABLES like '%secure%';
set global require_secure_transport = off;show global variables like 'local_infile';
set global local_infile=1;# 查看mysql自动提交状态
show variables like 'autocommit';# 开启自动提交
set @@autocommit=1;# 关闭自动提交
set @@autocommit=0;

JavaBean 接口没有粘出来 可以照着实现出来

/**
* 货品类
*/
package com.juc.Pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.stereotype.Component;@Data
@Component
@NoArgsConstructor
@AllArgsConstructor
public class Goods {private Integer id; //idprivate String name; //名称private Double price; //价格private price_unit price_unit; //价格单位private String description; //描述}
/**
* 枚举类
*/
package com.juc.Pojo;import com.juc.config.BaseEnum;public enum price_unit implements BaseEnum<price_unit,String> {CNY("1"), USD("2");private String value;price_unit() {}private price_unit(String value) {this.value = value;}public static boolean isChina(String value) {return CNY.getValue().equals(value);}public static boolean isAmerica(String value) {return USD.getValue().equals(value);}@Overridepublic String getValue() {return value;}}

访问层

package com.juc.Controller;import com.juc.Pojo.Goods;
import com.juc.Pojo.price_unit;import com.juc.Service.JUCService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.lang.reflect.Field;import static org.springframework.transaction.annotation.Isolation.REPEATABLE_READ;
import static org.springframework.transaction.annotation.Propagation.REQUIRED;@RestController
@RequestMapping("/JUC")
@Transactional(propagation=REQUIRED ,isolation=REPEATABLE_READ)
public class SQLController {@Autowiredprivate DataSourceTransactionManager dataSourceTransactionManager;@Autowiredprivate TransactionDefinition transactionDefinition;@Autowiredprivate PlatformTransactionManager txManager;@Autowiredprivate JUCService jucService;@Value("${file.setPath}")private String setFilePath;@RequestMapping("/Test")public void SQLTest(){TransactionStatus transactionStatus = null;try{transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);Goods goods01 = new Goods();goods01.setPrice_unit(price_unit.valueOf("CNY"));goods01.setId(2);goods01.setPrice(120.25);goods01.setDescription("键盘连接线");goods01.setName("腹灵");jucService.saveGoods(goods01);Goods goodsBefore4 = jucService.selectById(1);System.out.println("提交前查询---:"+ goodsBefore4);Goods goods02 = new Goods();goods02.setPrice_unit(price_unit.valueOf("CNY"));goods02.setId(1);goods02.setPrice(120.25);goods02.setDescription("键盘,鼠标");jucService.updateGoods(goods02);Goods goodsBefore5 = jucService.selectById(1);System.out.println("提交前查询A1---:"+ goodsBefore5);SQLTest01();Goods goodsBefore1 = jucService.selectById(1);System.out.println("提交前查询A---:"+ goodsBefore1);dataSourceTransactionManager.commit(transactionStatus);//提交Goods goodsAfter = jucService.selectById(1);System.out.println("提交后查询---:"+ goodsAfter);}catch(Exception e){e.printStackTrace();if(transactionStatus!=null){dataSourceTransactionManager.rollback(transactionStatus);//回滚}}}public void SQLTest01(){TransactionStatus transactionStatus2 = dataSourceTransactionManager.getTransaction(transactionDefinition);Goods goods03 = new Goods();goods03.setPrice(599.6);goods03.setDescription("键盘");goods03.setId(1);jucService.updateGoods(goods03);Goods goodsBefore2 = jucService.selectById(1);System.out.println("提交前查询B---:"+ goodsBefore2);dataSourceTransactionManager.commit(transactionStatus2);//提交Goods goodsBefore3 = jucService.selectById(1);System.out.println("提交后查询B---:"+ goodsBefore3);}@RequestMapping("/read")public void readFile(){Goods goods = new Goods();Field[] fields = goods.getClass().getDeclaredFields();int i = fields.length+1;jucService.read(i);jucService.insert(setFilePath);}}

业务层

package com.juc.Service.impl;import com.juc.Dao.GoodsDao;
import com.juc.Pojo.Goods;
import com.juc.Service.JUCService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;import java.io.*;
import java.util.ArrayList;
import java.util.List;@Service
public class JUCServiceImpl implements JUCService {@Autowiredprivate GoodsDao dao;@Value("${file.readPath}")private String readFilePath;@Value("${file.setPath}")private String setFilePath;@Overridepublic Boolean saveGoods(Goods goods) {try{dao.saveGoods(goods);return true;}catch (Exception e){e.printStackTrace();return false;}}@Overridepublic Goods selectById(int id) {try{Goods goods = dao.selectById(id);return goods;}catch (Exception e){e.printStackTrace();return new Goods();}}@Overridepublic Boolean updateGoods(Goods goods) {try {dao.updateGoods(goods);return true;} catch (Exception e) {e.printStackTrace();return false;}}@Overridepublic void read(int ZiDuanNumber) {FileWriter fw = null;try {File fileTxt = new File(setFilePath);FileInputStream file = new FileInputStream(readFilePath);InputStreamReader read = new InputStreamReader(file,"GBK");BufferedReader bufferedReader = new BufferedReader(read);fw = new FileWriter(fileTxt, true);PrintWriter pw = new PrintWriter(fw);String str = "";StringBuilder ZiDuanNumberString = new StringBuilder();for (int i = 0; i < ZiDuanNumber - 1; i++) {ZiDuanNumberString.append(",");}String NumberString = ZiDuanNumberString.toString();while ((str = bufferedReader.readLine()) != null){if (fileTxt == null || fileTxt.length()==0) {while(str.equals(NumberString)){str = bufferedReader.readLine();}List<String> stringList = new ArrayList<>();for (int i = 0; i < str.split(",").length; i++) {if (str.split(",")[i] == null || str.split(",")[i].equals("")) {str.split(",")[i] = "空";stringList.add(str.split(",")[i]);}else{stringList.add(str.split(",")[i]);}}//stringList.remove(stringList.size()-1);pw.println(stringList.toString().substring(1,stringList.toString().length()-1));}else{while(str.equals(NumberString)){str = bufferedReader.readLine();}List<String> stringList = new ArrayList<>();for (int i = 0; i < str.split(",").length; i++) {if (str.split(",")[i] == null || str.split(",")[i].equals("")) {str.split(",")[i] = "空";stringList.add(str.split(",")[i]);}else{stringList.add(str.split(",")[i]);}}//stringList.remove(stringList.size()-1);System.lineSeparator();pw.println(stringList.toString().substring(1,stringList.toString().length()-1));}}pw.flush();fw.flush();pw.close();fw.close();}catch (Exception e){e.printStackTrace();}}@Overridepublic void insert(String path) {dao.load_file(path);}}

<?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= "com.juc.Dao.GoodsDao" ><insert id="load_file">LOAD DATA LOCAL INFILE #{path,jdbcType=VARCHAR} INTO TABLE goods CHARACTER SET utf8FIELDS TERMINATED BY ','LINES TERMINATED BY '\n'</insert><insert id="saveGoods" parameterType="com.juc.Pojo.Goods">insert into goods(id,name,price,price_unit,description)values(#{id,},#{name},#{price},#{price_unit},#{description})</insert><update id="updateGoods" parameterType="com.juc.Pojo.Goods">update goods<set><if test="name != null and name != ''">name = #{name},</if><if test="price != null and price != ''">price = #{price},</if><if test="@com.juc.Pojo.price_unit@isChina(price_unit)">price_unit = #{price_unit},</if><if test="@com.juc.Pojo.price_unit@isAmerica(price_unit)">price_unit = #{price_unit},</if><if test="description != null and description != ''">description = #{description},</if></set>where id = #{id}</update><select id="selectById" parameterType="int" resultType="com.juc.Pojo.Goods">select * from goods where id = #{id}</select>
</mapper>

#项目访问
server:port: 8080
#数据库
spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/rjd?serverTimezone=UTC&allowLoadLocalInfile=trueusername: rootpassword: 123456
#mybatis配置
mybatis:type-aliases-package: com.juc.Pojomapper-locations: classpath:mapper/*.xmltypeHandlersPackage: com.juc.config
#属性名
file:readPath: D:/day01/lianxi01/src/Test.csv #此处换成自己的文件路径setPath: D:/day01/lianxi01/src/Data.txt  #中间暂存文件 方便校验

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.5.4</version><relativePath/></parent><groupId>com.JUC</groupId><artifactId>JUC</artifactId><version>0.0.1-SNAPSHOT</version><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.0</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.26</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

//配置枚举类的转换 以便存入数据库
//接口 只有拿值的方法也可以写入拿属性名的方法
package com.juc.config;public interface BaseEnum<E extends Enum<?>, T> {/*** 获取枚举的值* @return 枚举的值*/T getValue();}
//抽象类
package com.juc.config;import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Objects;/*** 参考 http://blog.csdn.net/fighterandknight/article/details/51520595* 进行对本地项目的优化* <p>* 解决 Mybatis 中枚举的问题，* 获取 ResultSet 的值都是获取字符串的，然后比较字符串，以便通用。** @author Zhang Kai* @version 1.0* @since <pre>2018/2/9 17:26</pre>*/
public abstract class BaseEnumTypeHandler<E extends Enum<E> & BaseEnum> extends BaseTypeHandler<E> {/*** 枚举的class*/private Class<E> type;/*** 枚举的每个子类枚*/private E[] enums;/*** 一定要有默认的构造函数，不然抛出 not found method 异常*/public BaseEnumTypeHandler() {}/*** 设置配置文件设置的转换类以及枚举类内容，供其他方法更便捷高效的实现** @param type 配置文件中设置的转换类*/public BaseEnumTypeHandler(Class<E> type) {if (type == null) {throw new IllegalArgumentException("Type argument cannot be null");}this.type = type;this.enums = type.getEnumConstants();if (this.enums == null) {throw new IllegalArgumentException(type.getSimpleName()+ " does not represent an enum type.");}}@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, E parameter,JdbcType jdbcType) throws SQLException {/** BaseTypeHandler已经帮我们做了parameter的null判断* 数据库存储的是枚举的值，所以我们这里使用 value ， 如果需要存储 name，可以自定义修改*/if (jdbcType == null) {ps.setString(i, Objects.toString(parameter.getValue()));} else {ps.setObject(i, parameter.getValue(), jdbcType.TYPE_CODE);}}@Overridepublic E getNullableResult(ResultSet rs, String columnName)throws SQLException {String i = rs.getString(columnName);if (rs.wasNull()) {return null;} else {return locateEnumStatus(i);}}@Overridepublic E getNullableResult(ResultSet rs, int columnIndex)throws SQLException {String i = rs.getString(columnIndex);if (rs.wasNull()) {return null;} else {return locateEnumStatus(i);}}@Overridepublic E getNullableResult(CallableStatement cs, int columnIndex)throws SQLException {String i = cs.getString(columnIndex);if (cs.wasNull()) {return null;} else {return locateEnumStatus(i);}}/*** 枚举类型转换，由于构造函数获取了枚举的子类 enums，让遍历更加高效快捷，* <p>* 我将取出来的值 全部转换成字符串 进行比较，** @param value 数据库中存储的自定义value属性* @return value 对应的枚举类*/private E locateEnumStatus(String value) {for (E e : enums) {String simpleName = e.getClass().getSimpleName();//单个判断if("price_unit".equals(simpleName)){if ("CNY".equals(value)){value = "1";} else if ("USD".equals(value)) {value = "2";}}if (Objects.toString(e.getValue()).equals(value)) {return e;}}throw new IllegalArgumentException("未知的枚举类型：" + value + ",请核对"+ type.getSimpleName());}
}//实现类
package com.juc.config;
import com.juc.Pojo.price_unit;
import org.apache.ibatis.type.MappedTypes;/*** 枚举转换的公共模块** @author Zhang Kai* @version 1.0* @since <pre>2018/2/9 18:12</pre>*/
@MappedTypes(value = {price_unit.class})
public class SysEnumTypeHandler<E extends Enum<E> & BaseEnum> extends BaseEnumTypeHandler<E> {/*** 设置配置文件设置的转换类以及枚举类内容，供其他方法更便捷高效的实现** @param type 配置文件中设置的转换类*/public SysEnumTypeHandler(Class<E> type) {super(type);}
}