最近公司有这个需求,将.csv文件导入数据库中,之前是导入mysql,后面由于客户需要用oracle,又将数据导入oracle中(苦笑),所以讲.csv文件导入mysql和oracle都用了。这里主要是说说如何将.csv文件导入mysql中,后面在说说导入oracle中。
这里是结合的springmvc + mybatis的方式进行操作的,下面说说我的步骤:
第一步:建立数据库和对应的表,这里建立三张表,第一张,导入信息表用于存放导入大小、名称等字段;第二张,导入目标表;第三章导入模板表,导入模板表主要是用户目标表需要导入的字段,不导入的字段就不需要加入模板表中;
第二步:上传需要导入的文件,将文件存储在项目路径下的临时目录,注意这里是临时目录,后面是要删除的不然占用服务器资源;
第三步:利用模板表建立导入临时表,在建立之前必须判断临时表是否存在;
第四步:将上传到临时文件中的.csv文件导入导入到临时表中;
第五步:将临时表中数据重新导入到正式表中;
第六步:删除临时表、删除临时资源文件;
下面是代码展示:
第一步:建立数据库和对应的表,这里建立三张表,第一张,导入信息表用于存放导入大小、名称等字段;第二张,导入目标表;第三章导入模板表,导入模板表主要是用户目标表需要导入的字段,不导入的字段就不需要加入模板表中;
第二步:上传需要导入的文件,将文件存储在项目路径下的临时目录,注意这里是临时目录,后面是要删除的不然占用服务器资源;
这里利用springMvc上传资源,下面是伪代码:
public Map<String, Object> importCsv(MultipartFile file){
Map<String, Object> resultMap = new HashMap<String, Object>();
String originalFilename = file.getOriginalFilename();
String suffixName = originalFilename.substring(originalFilename.lastIndexOf("."));
if (!".csv".equals(suffixName)) {
resultMap.put("STATE", -1);
resultMap.put("STATE_INFO","请上传csv文件!");
return resultJsonMap(resultMap);
}
tempFile = new File(path);
file.transferTo(tempFile);
}
第三步:利用模板表建立导入临时表,在建立之前必须判断临时表是否存在;
java 代码:首先查看临时表是否存在,存在则重新名称, 不存在就创建
// 1.创建临时表
tableName = "model_user_message_temp_" + UUID.randomUUID().toString().substring(0,6);
// 2.判断临时表是否存在
while (mapper.existUserInfoTemp(tableName) >= 1) {
tableName = "model_user_message_temp_" + UUID.randomUUID().toString().substring(0,6);
}
mapper.createInfoTempTable(tableName);
1.mybatis创建临时表
<update id="createInfoTempTable" parameterType="java.lang.String">
create table ${value}
as select * from model_user_message <!--这是专门用于导入数据模板表-->
where 1=2
</update>
2.查询临时表是否存在
<select id="existUserInfoTemp" parameterType="java.lang.String" resultType="java.lang.Integer">
SELECT COUNT(1) FROM User_Tables WHERE table_name=#{value}
</select>
第四步:将上传到临时文件中的.csv文件导入导入到临时表中;
这一步最主要的还是sql语句
<update id="importMsgToTemp" parameterType="java.util.Map">
LOAD DATA INFILE #{filePath} <!--Csv文件存放路径,存放在临时目录-->
INTO TABLE ${value} character set gb2312 <!--导入文件表名,临时表 character set utf-8 -->
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES <!--忽略第一行,第一行不导入-->
</update>
第五步:将临时表中数据重新导入到正式表中;
<!--sql语句就是这种形式, INSERT INTO 表(字段1,字段2)
select 字段1,字段2
from 表 where 条件
我这里面用到了正则表达式判断电话号码是否符合
-->
<update id="insertTableFormTempTable" parameterType="java.util.Map">
INSERT INTO import_user_message(
USER_ID,
USER_PHONE,
USER_FIELD1,
USER_FIELD2,
USER_FIELD3,
USER_FIELD4,
USER_FIELD5,
USER_NAME,
IMPORT_ID,
IS_LEGAL
) SELECT
sys_guid(),
USER_PHONE,
USER_FIELD1,
USER_FIELD2,
USER_FIELD3,
USER_FIELD4,
USER_FIELD5,
USER_NAME,
#{importId} AS IMPORT_ID,
'1' as IS_LEGAL
FROM ${tempTableName}
where regexp_like(USER_PHONE, '[1][345678][0-9]{9}$')
</update>
java代码就直接传入所需要的参数就可以了importId(导入Id)、tempTableName(临时表)。
第六步:删除临时表、删除临时资源文件;
注意:这里删除资源文件时,一点要先关闭流,不然文件时删除不掉的。如果流关了也没有删除掉,就试试下面这种方式
https://blog.csdn.net/u010371710/article/details/53913272
这里一点要删除临时表和临时资源文件、我是在 finally 代码块中删除的;