Spring事务如何实现部分回滚

灰太狼 2022-12-26 07:28 706阅读 0赞

# 一、需求：使用Excel批量导入数据，其中关联多张数据库表，需求是实现出现异常时关联的数据回滚，最后告诉用户成功导入的数量以及失败的原因 #

# 二、思路：一开始想，这不是很简单的问题，一个事务就可以解决，开启spring的注解事务即可 #

# 三、实现过程中遇到的问题：spring事务每次在出现异常的时候会将全部数据都回滚，包括正常的数据，与需求相违背（在同一个service开启注解事务失败） #

# 四、最终解决方法（这里主要讲Spring事务如何实现部分回滚，其他Excel相关等不赘述） #

## 1.事务在同一个service则使用手动开启事务解决 ##

@Service
    public class ReadWordServiceImpl implements ReadWordService { 
    	@Resource
        private CompanyMapper companyMapper;
        @Resource
        private UserCompanyRsMapper userCompanyRsMapper;
        @Resource
        private UserMapper userMapper;  
        @Resource
        UserRoleRsMapper userRoleRsMapper;
    	@Resource
        private ContactMapper contactMapper;
        //事务管理器，手动开启事务需要用到
        @Autowired
        private PlatformTransactionManager platformTransactionManager;
    
        /** * 管理员批量导入用户信息 * @param file * @return */
        @Override
        public CommonRespon importUsersByExcel(MultipartFile file) { 
            CommonRespon respon = new CommonRespon();
            StringBuffer msg = new StringBuffer();
            if (file.isEmpty()) { 
                msg.append("数据导入失败，原因：Excel文件为空！\n");
                respon.setCode(Const.CODE_SUCCESS);
                respon.setMsg(msg.toString());
                return respon;
            }
            int numberOfSuccess = 0;
            int numberOfFailure = 0;
            int numberOfRows = 0;
            XSSFSheet sheet = null;
            XSSFRow row = null;
            try { 
                //根据路径获取这个操作excel的实例
                XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
                //根据页面index 获取sheet页
                sheet = wb.getSheetAt(0);
                row = null;
                numberOfRows = sheet.getLastRowNum();
            } catch (IOException e) { 
                e.printStackTrace();
                msg.append("数据导入失败，原因：读取Excel发生异常！\n");
                respon.setCode(Const.CODE_SUCCESS);
                respon.setMsg(msg.toString());
                return respon;
            }
            for (int i = 2; i <= numberOfRows; i++) { 
            	//手动开启事务
                DefaultTransactionDefinition definition = new DefaultTransactionDefinition();
                definition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
                TransactionStatus status = platformTransactionManager.getTransaction(definition);
                try { 
                    row = sheet.getRow(i);
                    boolean result = importService.saveUserByTransactional( row, msg, i );
                    if (!result) { 
                        numberOfFailure++;
                    }
                    //手动提交事务
                    platformTransactionManager.commit(status);
                } catch (Exception e) { 
                    e.printStackTrace();
                    msg.append("第" + (i-1) + "条数据导入失败，原因：发生异常"+ e.getClass() +"！\n");
                    //手工回滚异常
                    platformTransactionManager.rollback(status);
                    numberOfFailure++;
                }
            }
            numberOfSuccess = numberOfRows - numberOfFailure - 1;
            respon.setCode(Const.CODE_SUCCESS);
            msg.append("数据导入完成，数据总数量为" + (numberOfRows-1) + "条，成功导入" + numberOfSuccess + "条数据，导入失败数据数量为" + numberOfFailure + "条！");
            respon.setMsg(msg.toString());
            return respon;
        }
        
    	/** 数据库表相关联的操作方法 **/
    	public boolean saveUserByTransactional(XSSFRow row,StringBuffer msg,int i) { 
            if (i == 3) { 
                row.getCell(6).setCellType(CellType.STRING);
            }
            //设置单元格为字符串类型
            row.getCell(0).setCellType(CellType.STRING);
            row.getCell(1).setCellType(CellType.STRING);
            String email = row.getCell( 0 ).getStringCellValue();
            String name = row.getCell( 1 ).getStringCellValue();
            if(email == null || !Pattern.matches(MyConstants.EMAIL_REGEX,email)) { 
                msg.append("第" + (i-1) + "条数据导入失败，原因：邮箱格式不正确！\n");
                return false;
            }
            //登录名不允许为邮箱格式
            if (name == null || Pattern.matches(MyConstants.EMAIL_REGEX, name)) { 
                msg.append("第" + (i-1) + "条数据导入失败，原因：登录名格式不正确（不能为空或者邮箱格式）！\n");
                return false;
            }
            User user = userMapper.selectByNameOrEmail(name, email);
            if(user != null) { 
                msg.append("第" + (i-1) + "条数据导入失败，原因：邮箱或登录名已存在！\n");
                return false;
            }
            row.getCell(4).setCellType(CellType.STRING);
            String password = row.getCell(4).getStringCellValue();
            User importUser = new User();
            importUser.setCreateTime(new Date());
            importUser.setUuid(UUIDUtils.genUUid());
            importUser.setName(name);
            importUser.setEmail(email);
            //加密
            importUser.setPassword(new BCryptPasswordEncoder().encode(password));
            importUser.setRemark(password);
            importUser.setDeleted(0);
            importUser.setOpen(0);
            importUser.setAvatar(ConstantUtil.USER_AVATAR);
            //插入用户
            userMapper.insert(importUser);
            row.getCell(2).setCellType(CellType.STRING);
            String type = row.getCell( 2 ).getStringCellValue();
            if ("机构".equals(type)) { 
                //机构添加用户角色关系
                addRole(importUser.getId(), UserUtil.ROLE_COMPANY);
                //添加机构以及用户机构关系
                row.getCell(3).setCellType(CellType.STRING);
                String companyName = row.getCell( 3 ).getStringCellValue();
                Company company = new Company();
                company.setUuid(UUIDUtils.genUUid());
                company.setCreateTime(new Date());
                company.setName(companyName); //企业名称
                companyMapper.insert(company);
    
                Contact contact = new Contact(); //企业联系人
                contact.setCreateTime(new Date());
                contact.setUuid(UUIDUtils.genUUid());
                contact.setCompanyUuid(company.getUuid());
                contactMapper.insert(contact);
    
                UserCompanyRs userCompanyRs = new UserCompanyRs();
                userCompanyRs.setCompanyUuid(company.getUuid());
                userCompanyRs.setUserUuid(importUser.getUuid());
                userCompanyRs.setCreateTime(new Date());
                userCompanyRsMapper.insert(userCompanyRs);
            }else { 
                //人才添加用户角色关系
                addRole(importUser.getId(), UserUtil.ROLE_TALENT);
            }
            return true;
        }
    }

## 2.使用spring的注解事务，需要把事务相关的操作抽出来到独立的service ##

### 2.1.启动类开始事务支持，加上注解@EnableTransactionManagement即可 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoT0xn_size_16_color_FFFFFF_t_70]

### 2.2.导入接口ImportService ###

package com.doctor.cloud.overseasTalent.web.service;
    
    import org.apache.poi.xssf.usermodel.XSSFRow;
    
    /** * @Author: zxb * @Date: 2020/12/3 15:47 * @Version: 1.0 * @Description: Description **/
    public interface ImportService { 
        /** * 插入用户 * @param row * @param msg * @param i * @return */
        boolean saveUserByTransactional(XSSFRow row, StringBuffer msg, int i);
    }

### 2.3导入接口实现类ImportServiceImpl（需要事务处理的方法加上@Transactional(rollbackFor = Exception.class)，spring事务默认只有在RunTimeException的时候才会回滚，我这里需求是有异常就回滚，所以加上了(rollbackFor = Exception.class)的配置） ###

package com.doctor.cloud.overseasTalent.web.service.impl;
    
    import com.doctor.cloud.common.utils.util.UUIDUtils;
    import com.doctor.cloud.overseasTalent.api.entity.*;
    import com.doctor.cloud.overseasTalent.web.config.security.MyConstants;
    import com.doctor.cloud.overseasTalent.web.mapper.*;
    import com.doctor.cloud.overseasTalent.web.remote.CompanyRemote;
    import com.doctor.cloud.overseasTalent.web.remote.UploadFileRemote;
    import com.doctor.cloud.overseasTalent.web.service.ImportCompanyService;
    import com.doctor.cloud.overseasTalent.web.service.ImportService;
    import com.doctor.cloud.overseasTalent.web.support.util.ConstantUtil;
    import com.doctor.cloud.overseasTalent.web.support.util.UserUtil;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import javax.annotation.Resource;
    import java.util.Date;
    import java.util.regex.Pattern;
    
    /** * @Author: zxb * @Date: 2020/12/3 15:48 * @Version: 1.0 * @Description: Description **/
    @Service
    public class ImportServiceImpl implements ImportService { 
        @Resource
        private CompanyMapper companyMapper;
    
        @Resource
        private JobMapper jobMapper;
    
        @Resource
        private CompanyRemote companyRemote;
    
        @Resource
        private UploadFileRemote uploadFileRemote;
    
        @Resource
        private ImportCompanyService importCompanyService;
    
        @Resource
        private UserCompanyRsMapper userCompanyRsMapper;
    
        @Resource
        private UserMapper userMapper;
    
        @Resource
        RoleMapper roleMapper;
    
        @Resource
        UserRoleRsMapper userRoleRsMapper;
    
        @Resource
        AuditMapper auditMapper;
    
        @Resource
        private ContactMapper contactMapper;
    
    	/** 使用事务对插入用户操作进行管理 **/
        @Override
        @Transactional(rollbackFor = Exception.class)
        public boolean saveUserByTransactional(XSSFRow row, StringBuffer msg, int i) {       
            //设置单元格为字符串类型
            row.getCell(0).setCellType(CellType.STRING);
            row.getCell(1).setCellType(CellType.STRING);
            String email = row.getCell( 0 ).getStringCellValue();
            String name = row.getCell( 1 ).getStringCellValue();
            if(email == null || !Pattern.matches( MyConstants.EMAIL_REGEX,email)) { 
                msg.append("第" + (i-1) + "条数据导入失败，原因：邮箱格式不正确！\n");
    // numberOfFailure++;
                return false;
            }
            //登录名不允许为邮箱格式
            if (name == null || Pattern.matches(MyConstants.EMAIL_REGEX, name)) { 
                msg.append("第" + (i-1) + "条数据导入失败，原因：登录名格式不正确（不能为空或者邮箱格式）！\n");
                return false;
            }
            User user = userMapper.selectByNameOrEmail(name, email);
            if(user != null) { 
                msg.append("第" + (i-1) + "条数据导入失败，原因：邮箱或登录名已存在！\n");
                return false;
            }
            row.getCell(4).setCellType(CellType.STRING);
            String password = row.getCell( 4 ).getStringCellValue();
            User importUser = new User();
            importUser.setCreateTime(new Date());
            importUser.setUuid(UUIDUtils.genUUid());
            importUser.setName(name);
            importUser.setEmail(email);
            //加密
            importUser.setPassword(new BCryptPasswordEncoder().encode(password));
            importUser.setRemark(password);
            importUser.setDeleted(0);
            importUser.setOpen(0);
            importUser.setAvatar( ConstantUtil.USER_AVATAR);     
            //插入用户
            userMapper.insert(importUser);
            row.getCell(2).setCellType(CellType.STRING);
            String type = row.getCell( 2 ).getStringCellValue();
            if ("机构".equals(type)) { 
                //机构添加用户角色关系
                addRole(importUser.getId(), UserUtil.ROLE_COMPANY);
                //添加机构以及用户机构关系
                row.getCell(3).setCellType( CellType.STRING);
                String companyName = row.getCell( 3 ).getStringCellValue();
                Company company = new Company();
                company.setUuid( UUIDUtils.genUUid());
                company.setCreateTime(new Date());
                company.setName(companyName); //企业名称
                companyMapper.insert(company);
    
                Contact contact = new Contact(); //企业联系人
                contact.setCreateTime(new Date());
                contact.setUuid(UUIDUtils.genUUid());
                contact.setCompanyUuid(company.getUuid());
                contactMapper.insert(contact);
    
                UserCompanyRs userCompanyRs = new UserCompanyRs();
                userCompanyRs.setCompanyUuid(company.getUuid());
                userCompanyRs.setUserUuid(importUser.getUuid());
                userCompanyRs.setCreateTime(new Date());
                userCompanyRsMapper.insert(userCompanyRs);
            }else { 
                //人才添加用户角色关系
                addRole(importUser.getId(), UserUtil.ROLE_TALENT);
            }
            return true;
        }
    
        /** * 给用户添加角色 * @param userId * @param code */
        public void addRole(Long userId,String code){ 
            Role role=roleMapper.selectByCode(code);
            if(role!=null){ 
                UserRoleRs userRoleRs=new UserRoleRs();
                userRoleRs.setRoleId(role.getId());
                userRoleRs.setUserId(userId);
                userRoleRsMapper.insert(userRoleRs);
            }
        }
    }

/** * 管理员批量导入用户信息 * @param file * @return */
        @Override
        public CommonRespon importUsersByExcel(MultipartFile file) { 
            CommonRespon respon = new CommonRespon();
            StringBuffer msg = new StringBuffer();
            if (file.isEmpty()) { 
                msg.append("数据导入失败，原因：Excel文件为空！\n");
                respon.setCode(Const.CODE_SUCCESS);
                respon.setMsg(msg.toString());
                return respon;
            }
            int numberOfSuccess = 0;
            int numberOfFailure = 0;
            int numberOfRows = 0;
            XSSFSheet sheet = null;
            XSSFRow row = null;
            try { 
                //根据路径获取这个操作excel的实例
                XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
                //根据页面index 获取sheet页
                sheet = wb.getSheetAt(0);
                row = null;
                numberOfRows = sheet.getLastRowNum();
            } catch (IOException e) { 
                e.printStackTrace();
                msg.append("数据导入失败，原因：读取Excel发生异常！\n");
                respon.setCode(Const.CODE_SUCCESS);
                respon.setMsg(msg.toString());
                return respon;
            }
            for (int i = 2; i <= numberOfRows; i++) {            
                try { 
                    row = sheet.getRow(i);
                    boolean result = importService.saveUserByTransactional( row, msg, i );
                    if (!result) { 
                        numberOfFailure++;
                    }
                } catch (Exception e) { 
                    e.printStackTrace();
                    msg.append("第" + (i-1) + "条数据导入失败，原因：发生异常"+ e.getClass() +"！\n");            
                    numberOfFailure++;
                }
            }
            numberOfSuccess = numberOfRows - numberOfFailure - 1;
            respon.setCode(Const.CODE_SUCCESS);
            msg.append("数据导入完成，数据总数量为" + (numberOfRows-1) + "条，成功导入" + numberOfSuccess + "条数据，导入失败数据数量为" + numberOfFailure + "条！");
            respon.setMsg(msg.toString());
            return respon;
        }

# 五、总结： #

## 1.开启事务的方法必须是public方法 ##

## 2.同一个service内进行事务处理将导致事务失效，或者说达不到程序要求的结果（采用手动开启事务可以解决） ##

## 3.serviceA调用serviceB的方法，这时只需要serviceB开启Spring注解事务，serviceA捕获serviceB方法的异常即可达到部分回滚的效果 ##

## 4.针对第三点，至于为什么要独立出来Spring的注解事务才生效，因为Spring注解事务是基于AOP实现的，没有独立出来的话那么开启事务的方法并没有被IOC所管理（更深层次的原因，需要好好总结一下，等总结完会完善文章） ##

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoT0xn_size_16_color_FFFFFF_t_70]: /images/20221120/2ceb3bb66961477aa30c1e05a6781499.png