如何在不使用FileInputStream或filepath作为字符串java的情况下从java.sql.Blob类型的zip文件读取和提取zip条目 [英] How to read and extract zip entries from java.sql.Blob type zip file without having FileInputStream or filepath as a string java
问题描述
public static void unzipFiles(java.sql.Blob zip) throws Exception{
String paths = "";
byte[] blobAsBytes = zip.getBytes(1, (int) zip.length());
ZipInputStream zis = new ZipInputStream(zip.getBinaryStream(), StandardCharsets.UTF_8);
ZipEntry zipEntry = null;
while ((zipEntry = zis.getNextEntry()) != null) {
paths=zipEntry.getName()+" ";
}
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
String sql = "INSERT INTO E (FILENAME) VALUES (:paths)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, paths);
pstmt.executeUpdate();}
我试图将一个java.sql.Blob的zip文件(zip)从pl/sql传递到java方法，并读取zip文件中的文件名和内容.我想将它们作为Zip条目检索，然后将它们插入到oracle数据库中的临时表中.但是问题是当我将Blob转换为字节数组时，它不会将其作为zip条目.返回null.任何解决方案将不胜感激.
I'm trying to pass a zip file (zip) as java.sql.Blob to the java method from pl/sql and read file names and contents inside the files in the zip. I want to retrieve them as Zip entries and then insert them to a temporary table in the oracle database. But the issue is when I convert the blob into a byte array it doesn't take it as zip entries. returns null. Any solution would be really appreciated.
推荐答案
这是比 zip 文件更通用的解决方案(Java的本地ZIP支持也不能处理所有ZIP格式[1] ).
This is a more generic solution than just zip files (also Java's native ZIP support does not handle all ZIP formats [1]).
使用 xz :
Use the loadjava utility to load commons-compress and it's dependency xz:
loadjava -user USERNAME/PASSWORD@SID xz.jar commons-compress-1.10.jar
(根据需要更新，以更新版本)
然后，您可以在数据库内部创建Java源代码:
Then you can create a Java source inside the database:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED UNZIP AS
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.compress.archivers.ArchiveException;
import org.apache.commons.compress.archivers.ArchiveInputStream;
import org.apache.commons.compress.archivers.ArchiveStreamFactory;
import org.apache.commons.compress.archivers.ArchiveEntry;
import oracle.jdbc.driver.OracleDriver;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.BLOB;
import org.apache.commons.compress.compressors.CompressorException;
import org.apache.commons.compress.compressors.CompressorStreamFactory;
public class Unzip {
public static ARRAY listFiles(
final BLOB file
)
throws java.sql.SQLException
{
final ArrayList<String> files = new ArrayList<String>();
if ( file != null && file.length() > 0 )
{
ArchiveInputStream ais = null;
try
{
InputStream stream = file.binaryStreamValue();
try {
stream = new CompressorStreamFactory().createCompressorInputStream( stream );
}
catch ( CompressorException e ){}
ais = new ArchiveStreamFactory().createArchiveInputStream( stream );
ArchiveEntry entry;
while( ( entry = ais.getNextEntry() ) != null )
{
if ( entry.isDirectory() || entry.getSize() == 0 )
continue;
files.add( entry.getName() );
}
}
catch ( ArchiveException e ){
files.add( "ERROR: " + e.getMessage() );
}
catch ( IOException e ){
files.add( "ERROR: " + e.getMessage() );
}
finally
{
try { if ( ais != null ) ais.close(); } catch( IOException e ){}
}
}
final String[] fs = new String[ files.size() ];
files.toArray( fs );
final Connection conn = new OracleDriver().defaultConnection();
return new ARRAY(
ArrayDescriptor.createDescriptor( "SYS.ODCIVARCHAR2LIST", conn ),
conn,
fs
);
}
public static BLOB unzip(
final BLOB file,
final String path
)
throws java.sql.SQLException
{
ArchiveInputStream ais = null;
BLOB extractedFile = null;
Connection conn = null;
if ( file != null && file.length() > 0 && path != null )
{
try {
InputStream stream = file.binaryStreamValue();
try {
stream = new CompressorStreamFactory().createCompressorInputStream( stream );
}
catch ( CompressorException e ){}
ais = new ArchiveStreamFactory().createArchiveInputStream( stream );
ArchiveEntry entry;
while( ( entry = ais.getNextEntry() ) != null ){
if ( !entry.getName().equals( path ) )
continue;
final byte[] bytes = new byte[8096];
long pos = 1;
int len;
conn = new OracleDriver().defaultConnection();
extractedFile = BLOB.createTemporary( conn, false, BLOB.DURATION_CALL );
while ( ( len = ais.read( bytes ) ) > 0 ) {
extractedFile.setBytes( pos, bytes, 0, len );
pos += len;
}
break;
}
}
catch ( final ArchiveException e ){}
catch ( final IOException e ){}
finally
{
try { if ( ais != null ) ais.close(); } catch( final IOException e ){}
try { if ( conn != null ) conn.close(); } catch( final SQLException e ){}
}
}
return extractedFile;
}
}
/
SHOW ERRORS;
/
(根据需要添加适当的异常处理代码.)
然后，您可以创建包装器函数，以便可以从数据库中调用代码:
Then you can create wrapper functions so you can call the code from the database:
CREATE OR REPLACE FUNCTION UNZIP_LIST_FILES(
zipfile IN BLOB
)
RETURN SYS.ODCIVARCHAR2LIST
AS LANGUAGE JAVA
NAME 'Unzip.listFiles( oracle.sql.BLOB ) return oracle.sql.ARRAY';
/
SHOW ERRORS;
/
CREATE OR REPLACE FUNCTION UNZIP(
zipfile IN BLOB,
filePath IN VARCHAR2
)
RETURN BLOB
AS LANGUAGE JAVA
NAME 'Unzip.unzip( oracle.sql.BLOB, java.lang.String ) return oracle.sql.BLOB';
/
SHOW ERRORS;
/
然后，您可以使用它们将其插入新表:
Then you can use them to insert into the new table:
INSERT INTO your_table_of_unzipped_files ( filepath, file )
SELECT n.COLUMN_VALUE,
UNZIP( t.your_blob, n.COLUMN_VALUE )
FROM your_table_of_zipped_files t
CROSS JOIN TABLE( UNZIP_LIST_FILES( t.your_blob ) ) n
这篇关于如何在不使用FileInputStream或filepath作为字符串java的情况下从java.sql.Blob类型的zip文件读取和提取zip条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
