2017.9.3 更新v2.1.0: 进度监控调整为整个导入过程的进度监控,并且更加精确
前些时日开发了一款用Java将Excel导入数据库的工具:EXCEL-UTIL4J(源码和文档在这里),觉得还是写一个使用示例的好,主要想用来记录使用方法。
因为时间有限,工具只实现了一对一关系数据导入,以及只能使用hibernate作为ORM框架进行导入,所以本示例将只做一对一关系数据导入,并且最大可能的利用到工具的所有功能,使用hibernate进行导入。
示例中只会简单的提示代码作用,建议先大概浏览一下工具文档,这样有利于理解示例的代码,本示例代码源码见 excel-util4j-sample
需求
1. Excel表如下:
Excel表头
2. 数据表结构如下:
表结构
3. 分析
字段对应
Excel中的账号、密码在数据表excel_util4j_user(下简称user)中,分别对应username、password;
Excel中的姓名、性别、生日、电话、地址在excel_util4j_user_info(下简称info)中,分别对应name、gender、birthday、phone、address;
字段约束(下面有建表语句,展示更清晰)
唯一性
user表中username以及info表中phone字段为唯一的
非空
username、password、name
其他
username最大20字、password6~20字、name最大20字、phone最大20,并且需要验证是否为电话号码、address最大50字
常量值字段
user表中除了excel中的字段外,还有enable字段,该字段表示是否启用账号,值为0或1,分别表示禁用或启用,本例中所有账户设置为1
其他字段
每个用户还有角色,对应的表为excel_util4j_user_role(下简称user_role)表,由于excel-util4j只能导入一对一关系的数据,所以这里仅仅设置角色为一个,假设role_id为3
创建时间字段create_time,该字段不由excel导入,也不是常量字段,而是系统当前时间
密码处理
密码解析后,需要验证是否为6~20个字符,通过验证后需要转换为MD5+Base64加密的格式再存表
下面是建表语句,表结构和约束更清晰的展现:
user表:
CREATE TABLE excel_util4j_user (
id bigint(20) NOT NULL AUTO_INCREMENT,
username varchar(20) NOT NULL,
password varchar(20) NOT NULL,
create_time datetime DEFAULT NULL,
enable int(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY username_unique (username) USING BTREE
);
info表:
CREATE TABLE excel_util4j_user_info (
id bigint(20) NOT NULL AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
name varchar(20) NOT NULL,
gender int(1) DEFAULT NULL,
birthday date DEFAULT NULL,
phone varchar(20) DEFAULT NULL,
address varchar(50) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY phone_unique (phone) USING BTREE
);
user_role表:
CREATE TABLE excel_util4j_user_role (
id bigint(20) NOT NULL AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
role_id int(11) NOT NULL,
PRIMARY KEY (id)
);
编码
源码下载下来,然后mvn install -Dmaven.test.skip=true安装相应jar包,共4个
配置POM文件,将包添加到依赖:
online.dinghuiye
poi-kit
1.0.1
online.dinghuiye
excelutil-api
2.1.0
online.dinghuiye
excelutil
2.1.0
online.dinghuiye
persistence-hibernate-impl
2.1.0
mysql
mysql-connector-java
5.1.42
com.alibaba
druid
1.0.31
org.hibernate
hibernate-core
4.3.11.Final
org.apache.commons
commons-lang3
3.6
javax.validation
validation-api
1.1.0.Final
org.hibernate
hibernate-validator
5.4.1.Final
org.glassfish
javax.el
3.0.1-b08
com.alibaba
fastjson
1.2.33
创建实体类,用Intellij Idea自动生成,下面列出3个pojo代码,关键部分都有注释。属性注解参看文档。
值得注意的是:
UniqueValidator.class(v2.0.0版本的示例)或UsernameUniqueValidator.class和PhoneUniqueValidator.class(v2.1.0版本示例)是自定义的判重验证器,并不是使用的hibernate validator的自定义验证,是excel-util4j中实现的验证器,使用前需要编写类并实现online.dinghuiye.api.validation.Validator,代码见下文UniqueValidator验证器,另外两个验证器相似
password字段从excel导入后,需要先验证长度在6 ~ 20个字符,然后通过PasswordRepairer进行修正,即将POJO对象的password属性值设置为MD5+Base64加密的字符串。加密后的字符串长度不一定再满足6 ~ 20个字符了,hibernate存表时还会再次按照POJO注解进行验证,此时就可能无法验证通过而报错,所以需要将因为属性修正而可能影响到的验证的注解加上groups = {Validator.class}参数,Validator.class是online.dinghuiye.api.validation.Validator.class
createTime字段是系统时间字段,使用自定义转换器设值,使用谦虚编写类并实现online.dinghuiye.api.resolution.Convertor接口,重写方法返回需要的特定值即可,代码见下文CurrentTimeConvertor转换器(v2.1.0版本示例)
user pojo
package online.dinghuiye.example.entity;
import online.dinghuiye.api.annotation.validate.Validate;
import online.dinghuiye.api.validation.Validator;
import online.dinghuiye.core.annotation.convert.ConstValue;
import online.dinghuiye.core.annotation.convert.ValueConvert;
import online.dinghuiye.core.annotation.excel.SheetTitleName;
import online.dinghuiye.example.convertor.CurrentTimeConvertor;
import online.dinghuiye.example.va