BLOB Practice: Take the BLOB object to implement file upload and download in Oracle (original)

BLOB is completely practiced:

Take the BLOB object to upload and download (original) in Oracle

Recently, a J2EE project is required, you need to upload and download the file on the JSP page. I have known that JDBC supports the access to large objects (LOB), thinking is very easy, doing a lot of problems, reading a lot of articles, but there is no shortprint. As a netizen article says: "... 99% of the tutorial on the Internet is not enough, even Sun own documents have been wrong ...", the actual situation is roughly like this.

So many problems with BLOB, I think most of them are caused by database developers and application server companies in JDBC driver. In practical applications, each person's development is different, so that a netizen's Solution has no way to reproduce in other people's applications, so that the sound of the sound. As for why it is not compatible, what problems, I don't have time to get it, just talk about how we solve problems.

Based on the above reasons, let's list our development environment, so that someone can not match it, recruit people.

Database Oracle 9i

Application Server Bea Weblogic 8.11

Development Tools JBuilder X

The JSP implementation file UPLOAD / DOWNLOAD can be divided into several pieces: files are submitted to forming inputsteam; inputsteam is in the BLOB format; the data is read from the library to INPUTSTEAM; InputStream outputs to the page to form a download file. Let's talk about blob first.

1. BLOB Introduction

(1) Situation of directly obtaining database connections

This is the standard way provided by Oracle, first insert an empty blob object, then update this empty object. code show as below:

// Get the database connection (the driver package is WebLogic, no new version of any new version)

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

Connection Con = DriverManager.getConnection

"JDBC: Oracle: Thin: @localhost: 1521: TestDB", "TEST", "TEST");

// Handling a transaction

Con.SetAutocommit (False);

Statement ST = con.createstatement ();

// Insert an empty object

St.executeUpdate ("INSERT INTO BLOBIMG VALUES (103, EMPTY_BLOB ()") ")

// Lock the data line with for Update mode

ResultSet RS = St.executeQuery

"SELECT Contents from blobimg where id = 103 for update");

IF (rs.next ()) {

// Get java.sql.blob object, then Cast is Oracle.Sql.blob

Oracle.Sql.blob Blob = (Oracle.Sql.blob) rs.getblob (1) .;

/ / To the output stream of the database

OutputStream outstream = blob.getbinaryoutputstream ();

// Here you use a file to simulate the input stream

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

InputStream Fin = New FileInputStream (file);

// Write the 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);

}

// Off (attention order)

Fin.close ();

Outstream.flush ();

Outstream.close ();

C.Commit ();

C. close ();

(2) Get database connections through JNDI

Configure the Oracle's JDBC Connection Pool and DataSource in WebLogic, bind to Context, assume that the bind is called "ORADS".

In order to get a database connection, be 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 written in 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 above code is constant

/ / Can't use Oracle.Sql.blob, will report Classcast unusual

WebLogic.jdbc.vendor.racle.OracleThinblobblob = (WebLogic.jdbc.vendor.Oracle.OracleThinblob) rgetblob (1);

// After the code is not changed

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 ();

C.Commit ();

C. close ();

2. Blob out

Read the BLOB data from the database does not have the difference between the difference between the connection pool, only need J2SE's standard java.sql.blob can get the output stream (Note Difference Java.Sql.blob and Oracle.Sql.blob) . code show as below:

Connection Con = ConnectionFactory.getConnection ();

Con.SetAutocommit (false); statement st = con.createstatement ();

// The SQL statement here no longer needs "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 ();

// use file analog output flow

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

OutputStream Fout = New FileOutputStream (file);

// Write the BLOB data below

Byte [] b = new byte [1024];

INT LEN = 0;

While ((len = INS.READ (B))! = -1) {

Fout.write (B, 0, LEN);

}

// close

Fout.close ();

INS.CLOSE ();

C.Commit ();

C. close ();

3. Submit files from JSP Pages to Database

(1) The code submitted to the page is as follows:

(2) Since the JSP does not provide the processing capability uploaded by the file, only the third party development package is used. There are a lot of open source packages on the Internet, we choose Apache Jakarta's fileupload, at http://jakarta.apache.org/commons/fileupload/index.html you can download package and complete API documents. Fario is adajspexception

The code for handling the page (Handle.jsp) is as follows

<%

Boolean ismultipart = fileUpload.ismultipartContent (required);

IF (ismultipart) {

// Create a new UPLOAD object

DiskfileUpload Upload ();

/ / Set the parameters of upload files

//upload.setsizethreshold(YourMaxMemorySize);

//upload.setsizemax (YOURMAXREQUESTSIZE);

String rootpath = getServletConfig (). GetServletContext (). GetRealPath ("/");

Upload.setRepositoryPath (rootpath "// uploads");

// Analyze the comes from the Request, return to the Item collection, // polling items, if not a form field, is a file object.

List items = Upload.Parsequest (Request);

Iterator it = items.iterator ();

While (ore.hasnext ()) {

FILEITEM ITEM = (FileItem) iter.next ();

// If it is a file object

IF (! item.isformfield ()) {

// If you are a text file, you can display directly

//out.println (item.getstring ());

// Write uploaded files to the server / Web-INF / WebStart / under the file named Test.txt

// file upgradedfile = new file (rootpath "// uploads // Test.txt");

//Item.write (UPLOADEDFILE);

// The following code is to enter the library (omitted):

// Note the acquisition of the input stream

...

InputStream UploadedStream = item.getinputStream ();

...

}

/ / Otherwise it is a normal form

Else {

Out.println ("FieldName:" item.getfieldname () "
");

Out.println ("Value:" item.getstring () "
");}

}

}

%>

4. Read BLOB from the database and save it to the client disk

This code is a bit strange and will pop up the file save dialog window, and the BLOB data is read out to the local file. The full text is listed as follows:

<% @ Page ContentType = "Text / HTML; Charset = GBK" Import = "java.io. *" IMPORT = "java.sql. *" Import = "Test.global.connectionFactory"%> <%

Connection Con = ConnectionFactory.getConnection ();

Con.SetAutocommit (False);

Statement ST = con.createstatement ();

ResultSet RS = St.executeQuery

"SELECT Contents from blobimg where id = 103");

IF (rs.next ()) {

BLOB BLOB = rs.getblob (1);

InputStream INS = blob.getbinarystream ();

Response.setContentType ("Application / Unknown");

Response.addheader ("Content-Disposition", "Attachment; FileName =" "Output.txt");

OutputStream outstream = response.getOutputStream ();

Byte [] bytes = new byte [1024];

INT LEN = 0;

While ((len = INS.READ (Bytes))! = - 1) {OutStream.write (bytes, 0, len);

}

INS.CLOSE ();

Outstream.close ();

Outstream = NULL;

C.Commit ();

C. close ();

}

%>

Attention

5. The last question is to display the image file stored in the form of BLOB to the JSP page.

The solution is to first read the image file from the database from the database, then write it on the page output stream with servlet. Finally, this servlet contains the appropriate image location in the JSP page.

The servlet code is as follows:

Public void doget (httpservletRequest Request, HttpservletResponse Response) THROWS

ServletException, IOException {

INT ID = integer.parseint (Request.getParameter ("ID"));

Try {

Connection Con = ConnectionFactory.getConnection ();

Con.SetAutocommit (False);

String SQL = "SELECT Contents from blobimg where id =?";

PreparedStatement PSMT = Con.PrepareStatement (SQL);

Psmt.setint (1, ID);

ResultSet RS = psmt.executeQuery ();

IF (rs.next ()) {

BLOB BB = rs.getblob (1);

InputStream INS = bb.getbinaryStream ();

Response.setContentType ("image / gif");

OutputStream outstream = response.getOutputStream ();

Byte [] bytes = new byte [1024];

INT LEN = 0;

While ((len = INS.READ (Bytes))! = -1) {

Outstream.write (Bytes, 0, Len);

}

INS.CLOSE ();

Outstream.close ();

Outstream = NULL;

C.Commit ();

C. close ();

}

}

Catch (Exception EX) {}

}

The JSP code is as follows:

Note Configure servlet mapping in Web.xml.

In this way, BLOB is complete in JSP.

This article refers to a large number of netizens' articles, this is here to thank.

1. Several cases of manipulating Oracle Database LOB fields through JDBC http://dev.9cbs.net/develop/Article/26/26786.shtm

2. About writing files with java.sql.blob type BLOB Http://community.9cbs.net/expert/faq/faq_index.asp?id=1971163. WebLogic7 can operate the big field of Oracle9i, why is WebLogic8? Http://dev2dev.bea.com.cn/bbs/thread.jspa?forumid=123&threadid=5390&MessageId=23598

4. How to show file Download Dialog Box in IE 6.0 http://www.experts-exchange.com/web/web_languages/jsp/Q_20842012.html

5. JPEG picture in SQL Server 2000 shows garbled code on JSP http://search.9cbs.net/expert/topic/2462/2462925.xml?temp=3.071231e-02