Accessing BLOB objects in Oracle to upload and download files ____oracle

Recently do a Java EE project, the need to implement the JSP page upload and download files. A long time ago that JDBC support for large objects (LOB) access, think it is easy to do to find a lot of problems, read a lot of articles, but there is no clue. As a Netizen article said: "... Tutorial 99% on the web is not working, and even Sun's own documentation has been wrong ... "and the situation is generally the case."

There are so many problems with access blobs, and I think most of them are caused by the incompatibility of the database developer and application server with the JDBC driver. and the actual application, each person's development running environment is different, make a netizen's solution no way to reappear in others ' application, so that scold sound piece. As for why it is incompatible, there are problems, I do not have time to find out, here only to say how we solve the problem.

For the above reasons, we will first list our development environment, so that some people do not deserve to be reviled.

Database Oracle 9i

Application Server BEA Weblogic 8.11

development tool JBuilder X

In the JSP implementation file Upload/download can be divided into such a few pieces: file submission to the formation of Inputsteam;inputsteam in a BLOB format storage; Data is read from the library as Inputsteam InputStream output to the page to form the download file. Let's talk about BLOBs first.

1. BLOB storage

(1) Direct access to the database connection

This is the standard method provided by Oracle to insert an empty BLOB object and then update the empty object. The code is as follows:

Get the database connection (the driver package is WebLogic and no new version is downloaded)

Class.forName ("Oracle.jdbc.driver.OracleDriver");

Connection con = drivermanager.getconnection (

"Jdbc:oracle:thin: @localhost: 1521:testdb", "Test", "test");

Processing transactions

Con.setautocommit (FALSE);

Statement st = Con.createstatement ();

Insert an empty object

St.executeupdate ("INSERT into blobimg values (103,empty_blob ())");

Lock data rows with a for Update method

ResultSet rs = st.executequery (

"Select contents from blobimg where id=103 for Update");

if (Rs.next ()) {

Get Java.sql.Blob object, then cast as Oracle.sql.BLOB

Oracle.sql.BLOB BLOB = (Oracle.sql.BLOB) rs.getblob (1).;

The output stream to the database

OutputStream OutStream = Blob.getbinaryoutputstream ();

This simulates the input stream with a file

File File = new file ("D://proxy.txt");

InputStream fin = new FileInputStream (file);

Writes an input stream to the output stream

Byte[] B = new byte[blob.getbuffersize ()];

int len = 0;

while (len = Fin.read (b))!=-1) {

Outstream.write (b, 0, Len);

Blob.putbytes (1,B);

}

Turn off (note order)

Fin.close ();

Outstream.flush ();

Outstream.close ();

Con.commit ();

Con.close ();

(2) Obtaining a database connection through Jndi

The JDBC Connection pool and DataSource that are configured in WebLogic to Oracle are bound to the context, assuming the binding is named "Orads".

In order to get a database connection, do a connection factory, the main code is as follows:

Context context = new InitialContext ();

ds = (DataSource) context.lookup ("Orads");

return Ds.getconnection ();

The following is the code that the BLOB writes to the database:

Connection con = connectionfactory.getconnection ();

Con.setautocommit (FALSE);

Statement st = Con.createstatement ();

St.executeupdate ("INSERT into blobimg values (103,empty_blob ())");

ResultSet rs = st.executequery (

"Select contents from blobimg where id=103 for Update");

if (Rs.next ()) {

The code doesn't change.

You can't use Oracle.sql.BLOB here, you'll report classcast anomalies.

Weblogic.jdbc.vendor.oracle.OracleThinBlobblob = (Weblogic.jdbc.vendor.oracle.OracleThinBlob) rs.getblob (1);

And then the code doesn't change.

OutputStream OutStream = Blob.getbinaryoutputstream ();

File File = new file ("D://proxy.txt");

InputStream fin = new FileInputStream (file);

Byte[] B = new byte[blob.getbuffersize ()];

int len = 0;

while (len = Fin.read (b))!=-1) {

Outstream.write (b, 0, Len);

}

Fin.close ();

Outstream.flush ();

Outstream.close ();

Con.commit ();

Con.close ();

2. Blob out of library

Read BLOB data from the database without the above differences due to the different connection pool, only need J2SE standard class Java.sql.Blob can get output stream (pay attention to distinguish Java.sql.Blob and Oracle.sql.BLOB). The code is as follows:

Connection con = connectionfactory.getconnection ();

Con.setautocommit (FALSE);

Statement st = Con.createstatement ();

The SQL statement here no longer requires "for update"

ResultSet rs = st.executequery (

"Select contents from blobimg where id=103");

if (Rs.next ()) {

Java.sql.Blob Blob = Rs.getblob (1);

InputStream ins = Blob.getbinarystream ();

Simulating an output stream with a file

File File = new file ("D://output.txt");

OutputStream fout = new FileOutputStream (file);

The following writes BLOB data to a file

Byte[] B = new byte[1024];

int len = 0;

while (len = Ins.read (b))!=-1) {

Fout.write (b, 0, Len);

}

Turn off

Fout.close ();

Ins.close ();

Con.commit ();

Con.close ();

3. Submitting files to a database from a JSP page

(1) The Code for submitting the page is as follows:

<form action= "handle.jsp" enctype= "Multipart/form-data" method= "POST" >

<input type= "hidden" name= "id" value= "the"/>

<input type= "File" name= "Filetoupload" >

<input type= "Submit" value= "Upload" >