百万excel数据 JAVA JDBC+EasyExcel导入
1、添加依赖
- 2+ 版本支持 Java7和Java6
- 3+ 版本至少 Java8
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
2、添加Excel映射的实体
public class PersonInfoExcel implements Serializable {
@ExcelIgnore
private String pid;
@ExcelProperty("个人编号")
private String pno;
@ExcelProperty("姓名")
private String pname;
@ExcelProperty("身份证")
private String idNo;
@ExcelProperty("乡镇编码")
private String townCode;
@ExcelProperty("乡镇")
private String townName;
@ExcelProperty("村")
private String village;
@ExcelProperty("户主")
private String householder;
@ExcelProperty("参保状态")
private String insStatus;
@ExcelIgnore
private String hosConsortiumCode;
@ExcelProperty("YLTMC")
private String hosConsortiumName;
@ExcelIgnore
private String insuredYear;
@ExcelIgnore
private String impBatchNo;
@ExcelIgnore
private Date crtTime;
@ExcelIgnore
private String crtUsr;
@ExcelIgnore
private Date updTime;
@ExcelIgnore
private String updUsr;
@ExcelProperty("备注")
private String remark;
//get set 方法
}
3、添加导入监听类
这里一开始用的是MyBatis的批量新增和在foreach里面一条条的新增发现速度比较慢,一百万的数据导入用了一个小时多的时间。为了提升导入的性能,使用了JDBC进行保存。
public class PersonInfoListener extends AnalysisEventListener<PersonInfoExcel> {
private Logger logger = LoggerFactory.getLogger(PersonInfoListener.class);
/**
* 每隔5000条存储数据库,然后清理list ,方便内存回收
*/
static final int BATCH_COUNT = 5000;
List<PersonInfoExcel> list = new ArrayList<>()