通过存储过程调用java 代码,进行解压ZIP和读取文件从而更新数据表信息
解压zip文件的java代码
package com.abc;
import java.io.*;
import java.nio.charset.Charset;
import java.util.Enumeration;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;
public class ZipUtils {
public void decompressionFile(String zipFilePath, String deCompressionPath, String readEncoding) {
FileOutputStream fos = null;
InputStream is = null;
ZipFile zipFileItems = null;
try {
zipFileItems = new ZipFile(zipFilePath , Charset.forName(readEncoding));
Enumeration<? extends ZipEntry> items = zipFileItems.entries();
while(items.hasMoreElements()) {
ZipEntry zipCurrentItem = items.nextElement();
if(zipCurrentItem.isDirectory()) {
File newFile = new File(deCompressionPath+zipCurrentItem.getName());
if(!newFile.exists()) {
newFile.mkdirs();
}
}else {
is = zipFileItems.getInputStream(zipCurrentItem);
byte[] b = new byte[1024];
File newFile = new File(deCompressionPath+zipCurrentItem.getName());
if(!newFile.exists()) {
new File(deCompressionPath).mkdirs();
newFile.createNewFile();
}
fos = new FileOutputStream(newFile);
int len;
while((len = is.read(b)) != -1) {
fos.write(b, 0, len);
}
System.out.println("解压文件 : "+zipCurrentItem.getName()+" 完成!");
}
}
zipFileItems.close();
}catch (FileNotFoundException e) {
System.err.println("你输入的文件路径有误,请检查文件路径及其后缀是否正确!");
} catch (IOException e) {
System.out.println("文件流异常断开!");
} finally {
closeAll(fos,is,zipFileItems,null,null);
}
}
private void closeAll(FileOutputStream fos, InputStream is, ZipFile zf
, ZipOutputStream zos, BufferedInputStream bis) {
if(null != fos) {
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(null != bis) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(null != zos) {
try {
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(null != is) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(null != zf) {
try {
zf.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
解压tar.gz的代码
package com.abc;
import org.apache.commons.compress.archivers.tar.TarArchiveEntry;
import org.apache.commons.compress.archivers.tar.TarArchiveInputStream;
import org.apache.commons.compress.archivers.tar.TarArchiveOutputStream;
import org.apache.commons.compress.compressors.gzip.GzipCompressorInputStream;
import org.apache.commons.compress.compressors.gzip.GzipCompressorOutputStream;
import org.apache.commons.compress.utils.IOUtils;
import java.io.*;
public class TarFileUtil {
public void decomPression(String TarFilePath, String deCompressionPath) throws IOException {
File sourceFile = new File(TarFilePath);
TarArchiveInputStream fin = null;
try {
fin = new TarArchiveInputStream(new GzipCompressorInputStream(new FileInputStream(sourceFile)));
File extraceFolder = new File(deCompressionPath);
TarArchiveEntry entry;
while ((entry = fin.getNextTarEntry()) != null) {
if (entry.isDirectory()) {
continue;
}
File curfile = new File(extraceFolder, entry.getName());
File parent = curfile.getParentFile();
if (!parent.exists()) {
parent.mkdirs();
}
IOUtils.copy(fin, new FileOutputStream(curfile));
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
closeAll(fin);
}
}
public void closeAll(TarArchiveInputStream fin){
if(null != fin){
try{
fin.close();
}catch (IOException e){
e.printStackTrace();
}
}
}
}
创建Java Source
create or replace and compile java source named ziputils as
import java.io.*;
import java.nio.charset.Charset;
import java.util.Enumeration;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;
public class ZipUtils {
public static void decompressionFile(String zipFilePath, String deCompressionPath, String readEncoding) {
FileOutputStream fos = null;
InputStream is = null;
ZipFile zipFileItems = null;
try {
zipFileItems = new ZipFile(zipFilePath , Charset.forName(readEncoding));
Enumeration<? extends ZipEntry> items = zipFileItems.entries();
while(items.hasMoreElements()) {
ZipEntry zipCurrentItem = items.nextElement();
if(zipCurrentItem.isDirectory()) {
File newFile = new File(deCompressionPath+zipCurrentItem.getName());
if(!newFile.exists()) {
newFile.mkdirs();
}
}else {
is = zipFileItems.getInputStream(zipCurrentItem);
byte[] b = new byte[1024];
File newFile = new File(deCompressionPath+zipCurrentItem.getName());
if(!newFile.exists()) {
new File(deCompressionPath).mkdirs();
newFile.createNewFile();
}
fos = new FileOutputStream(newFile);
int len;
while((len = is.read(b)) != -1) {
fos.write(b, 0, len);
}
}
}
zipFileItems.close();
}catch (FileNotFoundException e) {
e.fillInStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
closeAll(fos,is,zipFileItems,null,null);
}
}
private static void closeAll(FileOutputStream fos, InputStream is, ZipFile zf
, ZipOutputStream zos, BufferedInputStream bis) {
if(null != fos) {
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(null != bis) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(null != zos) {
try {
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(null != is) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(null != zf) {
try {
zf.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
创建调用java source 的存储过程
create or replace procedure zipFilePsnInfo(srcPath varchar,
destPath varchar,
zippwd varchar) as
language java name 'ZipUtils.decompressionFile(java.lang.String, java.lang.String,java.lang.String)';
创建遍历文件更新数据库信息的存储过程
create or replace procedure prc_psndoc_updateinfo is
lineData varchar(500);
cur_file UTL_FILE.file_type;
sperloc1 smallint;
sperloc2 smallint;
sperloc3 smallint;
sperloc4 smallint;
sperloc5 smallint;
psncode varchar(10);
mobileno varchar(20);
phoneno varchar(100);
emailno varchar(100);
inSqlBase varchar(500) := 'update bd_psndoc set mobile=''@mobileno@'', homephone = ''@phoneno@'',email = ''@emailno@'' where code=''@psncode@'' ';
inSql varchar(500);
dealInfo varchar(4000) := '成功';
isSucess char(1) := 'Y';
logSql varchar(500);
begTime char(19) := to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS');
endTime char(19);
fileName varchar(200);
begin
zipFileWithPwd('F:\fileexpdir\oa_org_staff_20210914.zip', 'F:\test\', 'UTF-8');
fileName := 'oa_org_staff_' || to_char(sysdate, 'YYYYMMDD') || '.dat';
-- 打开文件
cur_file := UTL_FILE.fopen('P_UPDATE', fileName, 'R', 32000);
-- 循环文件游标处理每行数据
loop
UTL_FILE.get_line(cur_file, lineData);
-- 获取分隔符位置
sperloc1 := instr(lineData, '|+|', 1, 1);
-- 获取分隔符位置
sperloc2 := instr(lineData, '|+|', 1, 3);
-- 获取分隔符位置
sperloc3 := instr(lineData, '|+|', 1, 4);
-- 获取分隔符位置
sperloc4 := instr(lineData, '|+|', 1, 5);
-- 获取分隔符位置
sperloc5 := instr(lineData, '|+|', 1, 6);
--截取员工号
psncode := substr(lineData, 1, sperloc1 - 1);
-- 截取手机号
mobileno := substr(lineData, sperloc2 + 3, sperloc3 - (sperloc2 + 3));
-- 截取电话号
phoneno := substr(lineData, sperloc3 + 3,sperloc4 - (sperloc3 + 3));
-- 截取邮箱
emailno := substr(lineData, sperloc4 + 3,sperloc5 - (sperloc4 + 3));
-- 转换插入语句
inSql := replace(replace(replace(replace(inSqlBase, '@mobileno@', mobileno), '@phoneno@', phoneno),'@emailno@', emailno),'@psncode@',psncode);
--执行更新人员信息sql
--update bd_psndoc set mobile = mobileno, officephone = phoneno,email = emailno where code=psncode;
--commit;
-- 执行
execute immediate inSql;
commit;
end loop;
-- 关闭文件
UTL_FILE.fclose(cur_file);
exception
-- 捕获文件读取不到数据的异常
when NO_DATA_FOUND then
UTL_FILE.fclose(cur_file);
endTime := to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS');
logSql := 'insert into hrs_ift_logs(pk_logs, workcode, workname, begtime, endtime, dealrst, dealinfo) values(''' || sys_guid() || ''', ''BD_PSNDOC'', ''更新人员信息'', ''' || begTime || ''',''' || endTime || ''', ''' || isSucess || ''', ''' || dealInfo || ''')';
execute immediate logSql;
-- 提交
commit;
-- 捕获其他异常,拿到错误信息
when others then
dealInfo := SQLCODE || ':' || SUBSTR(SQLERRM, 1, 200);
isSucess := 'N';
rollback;
-- 将处理结果存储到日志表中
logSql := 'insert into hrs_ift_logs(pk_logs, workcode, workname, begtime, endtime, dealrst, dealinfo) values(''' || sys_guid() || ''', ''BD_PSNDOC'', ''更新人员信息'', ''' || begTime || ''',''' || endTime || ''', ''' || isSucess || ''', ''' || dealInfo || ''')';
execute immediate logSql;
commit;
end prc_psndoc_updateinfo;
存储过程中打开文件的参数设置
cur_file := UTL_FILE.fopen('P_UPDATE', fileName, 'R', 32000);
Location 是路径参数,
FILENAME 是文件名,
OPEN_MODE是打开模式,'R'是读文本,'W'是写文本,'A'是附加文本,参数不分大小写,如果指定'A'但是文件不存在,它会用'W'先创建出来,'W'有覆盖的功能;
其中的location并不能简单的指定为'D:/temp'等路径,要建立一个DIRECTORY变量并付给权限(必须以DBA身份登录):
create or replace directory D_OUTPUT as 'F:/test' ;
grant read ,write on directory D_OUTPUT to username;
GRANT EXECUTE ON utl_file TO username;