1.采用开源项目EasyExcel进行数据文件读取和错误文件生成，读取速度效果明显，支持xls、xlsx文件自动识别。
2.优化原有数据校验逻辑，将需要判断的数据加载至内存，在内存中完成导入数据的校验工作。
3.采用并行批量导入数据库的方式，加快入库速度。
实现
1. 导入相关依赖
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>2.实际类读取
/**
* <p>1. 创建excel对应的实体对象 参照{@link ImportUser}
* <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link IhmportUserDataListener}
* <p>3. 直接读即可
*/
String fileName = "F://usersdata20w.xlsx";
// 需要指定读用ImportUser.class去读，然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, ImportUser.class, new ImportUserDataListener()).sheet().doRead();
3.实现监听器
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进
public class ImportUserDataListener extends AnalysisEventListener<ImportUser> {
private Logger logger = LoggerFactory.getLogger(getClass());
/**
* 每隔规定数量存一次储数据库，然后清理list
*/
private static final int BATCH_COUNT = 100000;
private List<ImportUser> impUserlist = new ArrayList<ImportUser>();
private List<ImportUser> impVaildUserlist = new ArrayList<ImportUser>();
private List<ImportUser> impErrorUserlist = new ArrayList<ImportUser>();
private List<UserAutzEntity> impUserAutzlist = new ArrayList<UserAutzEntity>();
private List<String> permGroupIdList = new ArrayList<>();
private Map<String, String> groupNameIdMap = new HashMap<String, String>();
private Map<String, Integer> usernameRepeat = new HashMap<String, Integer>();
private Map<String, Integer> ipAddrRepeat = new HashMap<String, Integer>();
public ImportUserDataListener() {
}
/**
* 每次创建Listener的时候需要把spring管理的类传进来
*/
public ImportUserDataListener(UserGroupsService userGroupsService, SysUserService sysUserService) {
//查詢所有的用戶組名称和用戶組ID，用于之后的数据校验
List<UserGroupsEntity> groups = userGroupsService.queryAllGroupName();
logger.info("查詢所有的用戶組名称和用戶組ID，用于之后的数据校验!");
for (UserGroupsEntity ug : groups) {
groupNameIdMap.put(ug.getGroupName(), ug.getGroupId());
}
//查询当前登录用户的用户组权限
logger.info("查询当前登录用户的用户组权限!");
SysUserEntity loginUser = ShiroUtils.getUserEntity();
if(!loginUser.getUserId().equals(Constant.SUPER_ADMIN)){
System.out.println("loginUser.getUserId():"+loginUser.getUserId());
permGroupIdList = sysUserService.selectGroupIdList( loginUser.getUserId());
}
}
/**
* 每解析一条数据，调用此方法
* 优化参考：解析数据的同时不要添加任何的判断逻辑，先将数据存入内存，之后进行数据筛选工作
*/
@Override
public void invoke(ImportUser user, AnalysisContext context) {
//将合法有效的用户存入内存
impUserlist.add(user);
}
/**
* 所有数据解析完成之后会调用次方法
* 优化建议：此处完成数据校验、数据入库、错误用户信息文件生成等工作
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
logger.info("数据读取完成！共读取数据：{}条。", impUserlist.size());
logger.info("开始校验数据合法性！");
/**
* 遍历内存中的用户数据对象，进行必要的数据校验
*/
for (ImportUser user : impUserlist) {
//TODO:完善并检查属性判断逻辑是否完整
//判断用户名
if (user.getUserName() == null) {
user.setErrorCause("用户名不能为空");
impErrorUserlist.add(user);
continue;
}
//判断导入文件中的用户名username是否重复
if (usernameRepeat.containsKey(user.getUserName())) {
user.setErrorCause("用户名重复，请重新填写");
impErrorUserlist.add(user);
continue;
} else {
usernameRepeat.put(user.getUserName(), 1);
}
//判断导入文件中的静态IP是否重复
if (user.getFramedIpAddress() != null) {
if (ipAddrRepeat.containsKey(user.getFramedIpAddress())) {
user.setErrorCause("静态IP重复，请重新填写");
impErrorUserlist.add(user);
continue;
} else {
ipAddrRepeat.put(user.getFramedIpAddress(), 1);
}
}
//判断导入的用户组是否存在
if (user.getUserGroup() == null || user.getUserGroup().length() == 0) {
user.setGroupId("default");//默认用户组
} else {
if (!groupNameIdMap.containsKey(user.getUserGroup())) {
user.setErrorCause("用户指定的组不存在");
impErrorUserlist.add(user);
continue;
}
user.setGroupId(groupNameIdMap.get(user.getUserGroup()));
}
//判断当前登录账户是否有权限插入填写的用户组
if (!permGroupIdList.isEmpty() && !permGroupIdList.contains(user.getGroupId())) {
user.setErrorCause("当前管理员没有对此用户组的操作权限");
impErrorUserlist.add(user);
continue;
}
//设置用户主键ID标识
user.setUserId(java.util.UUID.randomUUID().toString().replace("-", ""));
//存储通过数据校验的合法用户
impVaildUserlist.add(user);
}
logger.info("数据合法性校验完毕！有效数据{}条，非法数据{}条.", impVaildUserlist.size(), impErrorUserlist.size());
//并行批量插入数据库
logger.info("开始插入数据");
//注意此处使用了lambda语法，java8以上支持
Insert2DBConsumer.insertData(impVaildUserlist(), ImportUserDataListener::insertUserToDBByJdbc);
logger.info("数据成功入库");
//生成导入错误文件
if (!impErrorUserlist.isEmpty()) {
recordErrors();
}
//TODO:清理集合中元素，释放内存
impErrorUserlist.clear();
impVaildUserlist.clear();
impUserlist.clear();
usernameRepeat.clear();
ipAddrRepeat.clear();
logger.info("释放批量导入临时占用内存，导入结束！");
}
/**
* 利用原生JDBC批量存储至数据库
*/
private void insertUserToDBByJdbc(List<ImportUser> impVaildUserlist) {
// 利用JDBC批量导入数据库，注意主库与备库的事务控制
Connection connection = null;
PreparedStatement cmd = null;
private final static String columnStr = "USER_ID,USER_NAME,GROUP_ID,USER_STATUS,CALLING_ID,USER_PASSWORD,CREATE_DATE,EXPIRE_DATE,"
+ "FULL_NAME,CONTACT_ADDR,CONTACT_POSTCODE,CONTACT_EMAIL,CONTACT_TEL,CONTACT_PHONE";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@192.16.31.25:1521:orcl1", "root", "root");
connection.setAutoCommit(false);
cmd = connection.prepareStatement("insert into USERS(" + columnStr + ") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
for(UsersEntity user: importUsers){
try {
cmd.setString(1, user.getUserId());
cmd.setString(2, user.getUserName());
cmd.setString(3, user.getGroupId());
cmd.setInt(4, user.getUserStatus());
cmd.setString(5, user.getCallingId());
cmd.setString(6, user.getUserPassword());
cmd.setString(7, user.getCreateDate());
cmd.setString(8, user.getExpireDate());
cmd.setString(9, user.getFullName());
cmd.setString(10, user.getContactAddr());
cmd.setString(11, user.getContactPostcode());
cmd.setString(12, user.getContactEmail());
cmd.setString(13, user.getContactTel());
cmd.setString(14, user.getContactPhone());
cmd.addBatch();
} catch (SQLException e) {
e.printStackTrace();
}
connection.commit();
}
} catch (Exception e) {
e.printStackTrace();
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
// 关闭连接
try {
if (cmd != null) {
cmd.close();
}
if (connection != null) {
connection.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
logger.info("存储数据库成功！");
}
/**
* 生成错误文件
*/
private void recordErrors() {
logger.info("正在生成错误文件，无效数据共计{}条。"+impErrorUserlist.size());
String fileName = "G:\\工作目录\\NMCP\\ErrorUsers" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, ImportUser.class).sheet("模板").doWrite(impErrorUserlist);
logger.info("错误文件生成结束！");
}
}