一、需求：使用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所管理（更深层次的原因，需要好好总结一下，等总结完会完善文章）
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
