This is a very nice solution sent by Julio
This solution is very good for huge files and does not put a lot of load on the JVM heap
Hi!
this is a scriptlet of upload/download of BLOBs on Oracle DB. This solution have some years (was original developed for a Ora8i and tested later on 9 and 10), and was tunned for Very Large Blobs (>500Mb) using jdbc only. You see in this solution that Blob content never is stored on memory, which gives a very good scalability.
I erase all error management on the examples, please you MUST have a LOT of error management!!
UPLOAD
Code:
public int write(String id, InputStream is) {
Connection con = getConnection();
//
// create a new record with empty blob
String sql = "INSERT INTO "+getTableName()+" (ID,"+getBlobFieldName()+") VALUES (?,empty_blob())";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
int rowsAffected = pstmt.executeUpdate();
pstmt.close();
//
// Get empty blob for update
sql = "SELECT "+getFieldName()+" FROM "+getTableName()+" WHERE ID="+id+" FOR UPDATE";
pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
rs.next();
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(1);
OutputStream os = blob.getBinaryOutputStream();
//
// fill blob
byte[] buf = new byte[1024];
int len = -1;
int len_total = 0;
while ( (len=is.read(buf,0,1024)) != -1) {
os.write(buf,0,len);
len_total += len;
}
//
// close resources
os.close();
rs.close();
pstmt.close()
con.close()
//
// return writed bytes
return len_total;
}
- The InputStream of Document can be get from HTTPRequest.
- Be carefull with cast to oracle.sql.BLOB on Application Servers JEE 1.4 o later, this use was because Oracle have a Bug with the homonym function on JDBC Blob class.
DOWNLOAD
Code:
public int read(OutputStream os) {
String sql = "SELECT "+getBlobFieldName()+" FROM "+getTableName()+" WHERE "+getConstraint();
Connection con = getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
rs.next();
java.sql.Blob blob = rs.getBlob(1);
InputStream is = blob.getBinaryStream();
byte[] buf = new byte[1024];
int len = -1;
int len_total = 0;
while ( (len=is.read(buf,0,1024)) != -1) {
os.write(buf,0,len);
len_total += len;
}
is.close();
rs.close();
pstmt.close()
con.close()
return len_total;
}
//
// This is a Servlet doGet method
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType(getMimeType());
response.setHeader("Content-Disposition","in-line;filename=\""+getFilename()+"\"");
java.io.OutputStream out = response.getOutputStream();
read(out);
out.flush();
out.close();
}
