How to process Oracle BLOB fields in JDBC Why should I write this article? I encountered this problem in my project some time ago. It took me two days to solve the Blob problem. I have also tried various methods introduced on the Internet. They all use the same principles, but they are not completely written. I have also followed the methods described on the Internet, but all of them fail because of some small details that are not mentioned. I hope that all the people who see this article will not take a detour. What detours will most people take?
1. Use the method in JDK for transmission. The getblob () method exists in the resultset and the setblob () method exists in the preparedstatement. Therefore, most people will try to read and write the setblob () and getblob () methods, or transmit the Blob between two databases. This method is actually not feasible. According to some information on the Internet, some methods in Sun official documents are incorrect. 2. Use resultset. getbinarystream and preparedstatement. setbinarystream to read and write blob or transmit data between two databases. I tried this method myself and found that if the Blob stores text files, there will be no problem. If it is a binary file, there will be a problem in transmission. Based on your experience and reading the official Oracle documents, you can use the following methods:
1. Create a record and insert BLOB Data
1.1 when creating a record, use the Oracle function to insert an empty blob. Assume that field a is of the Blob type:
Insert xxxtable (a, B, c) values (empty_blob (), 'xxx', 'yyyy ')
1.2 query the inserted record and update blob. Before the query, set an attribute of connection:
Conn. setautocommit (false); if this step is missing, exceptions such as fetch out of sequence may occur.
1.3 query the inserted record, followed by "for update", as shown below:
Select a from xxxtable Where xxx = 999 for update. If for update is missing, the row containing the lob value is not locked exception may occur.
1.4 obtain and update the blob from the queried blob field. The Code is as follows:
Blob blob = (BLOB) Rs. getblob ("");
Outputstream OS = blob. getbinaryoutputstream ();
Bufferedoutputstream output = new bufferedoutputstream (OS ); You can use the output. Write method to write the content to the output file. For example, we write a file into this field:
Bufferedinputstream input = new bufferedinputstream (new file ("C: // hpwave. log"). tourl (). openstream ());
Byte [] buff = new byte [2048]; // Buffer Used for file writing
Int bytesread;
While (-1! = (Bytesread = input. Read (buff, 0, Buff. Length ))){
Output. Write (buff, 0, bytesread );
System. Out. println (bytesread );
}
The above Code reads data from input 2 K and then writes it to output.
1.5 after the preceding execution is complete, close output, input, and the queried resultset.
1.6 finally run conn. Commit (); Submit the updated content, and execute conn. setautocommit (true); change back to the connction attribute
2. Modify the record. The method is similar to the above method,
2.1 First Update fields other than blob
2.2 use a similar method in 1.3 to obtain records
2.3 During modification, note the following: blob may be null in the record to be updated by a, so that blob is executed. if the value obtained by getbinaryoutputstream () is null, close the select record and execute update xxxtable set a = empty_blob () Where xxx, in this way, an empty blob (not null) is written, and the update record is executed using the method in 1.3 and 1.4. b. Do not forget to execute setautocommit (false), "for update", and the subsequent Conn. commit.
3. read data from BLOB fields.
3.1 setautocommit () is not required to read records, and select... for update.
3.2 query the output records using the normal select Method
3.3 obtain and read blob from resultset, as shown below:
Blob B _to = (BLOB) Rs. getblob ("");
Inputstream is = B _from.getbinarystream ();
Bufferedinputstream input = new bufferedinputstream (is );
Byte [] buff = new byte [2048];
While (-1! = (Bytesread = input. Read (buff, 0, Buff. Length ))){
// Write data here, for example, in the bufferedoutputstream of the file
System. Out. println (bytesread );
}
The data in blob is retrieved cyclically, written to buff, and then the buff content is written to the desired location.
4. Transmission of BLOB fields between two databases
Similar to the methods 1 and 3 above, while obtaining bufferedoutputstream, while obtaining bufferedinputstream, and then reading and writing, note that the connection used for writing should be executed Conn. setautocommit (false); Add "for update" and the final commit () when getting the record (); To sum up the above methods, it is simply to first create an empty blob, then obtain its bufferedoutputstream for writing, or obtain bufferedinputstream for reading. Hao congping Email: cqhcp@126.com |
