Spring事务如何实现部分回滚

一、需求：使用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即可

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所管理（更深层次的原因，需要好好总结一下，等总结完会完善文章）