A good memory is better than a bad pen. 20-java Summary of CLOB and BLOB fields for Oracle

Overall situation

CLOB field in the actual work, there are still a lot of application scenarios, but the Blob field in today's large system, the role of more and more unsatisfactory.

Because of database resources, upgrade costs are highest in large systems, and large amounts of data in BLOB fields can cause serious IO problems for the database. If we buy millions of of small machines, just to store a few ordinary pictures, it is estimated that the teeth will be laughed off, although the database has a good authentication system, but does not mean that the file system can not be implemented.

Therefore, if the old system, if we do not plan to fully reconstruct and upgrade, then this blob is used to use, if it is a new system, in principle, the BLOB field is not recommended, the implementation of high cost, and cumbersome operation.

using JDBC to manipulate the LOB fields of an Oracle database, writing, modifying, and reading three ways is not difficult to master. There are several notable differences compared to other types of fields:

1 , auto-commit must be canceled

the automatic commit must be canceled with Setautocommit (false) before the access operation begins . Other types of fields do not have this special requirement. This is because when you access the LOB Type field, you typically have to do more than one operation. If not, Oracle will throw a "read violation order" error.

2 and inserted in different ways.

LOB Data cannot be inserted directly (insert) Like other types of data. An empty LOB object must first be inserted before insertion, the empty object of type CLOB is Empty_clob (), and the empty object of the BLOB type is Empty_blob (). The previously inserted record is then queried with the Select command and locked, and then the empty object is modified to the LOB object to be inserted.

3 and different ways of modification.

when modifying other types of fields, use Update ... SET ... command. While the LOB Type field, you can only use SELECT ... The FOR UPDATE command queries the record and locks it before it can be modified. And the modification also has two kinds of modification: first, the original data based on the modification (that is, overlay modification), the implementation of SELECT ... For update and then change the data, the second is to replace (clear the original data, and then modify), first execute the update command to set the value of the LOB field to an empty LOB object, and then make the first modification. It is recommended that you use the replacement method to achieve the same effect as the other field update operations.

4 , access should use the LOB action classes provided by the database JDBC driver.

for Oracle databases, you should use Oracle.sql.CLOB and Oracle.sql.BLOB. When you do not use LOB classes provided by the database JDBC driver, the program is run with an "abstract method call" error, because the Java.sql.Clob defined by JDBC and Java.sql.Blob interface, some of which are not actually implemented in the driver provided by the database manufacturer.

5 , access means and file operations are similar.

for BLOB types, the Inputstream/outputstream class is applied, and this class does not encode conversions and accesses bytes-by-byte. The Oracle.sql.BLOB class provides the Getbinarystream () and Getbinaryoutputstream () two methods, and the previous method is used to read the Oracle's BLOB fields. The latter method is used to write data to the Oracle BLOB field.

for the Clob type, the Reader/writer class is applied, and this class is encoded for conversion. The Oracle.sql.CLOB class provides the Getcharacterstream () and Getcharacteroutputstream () two methods, and the previous method is used to read the CLOB field of Oracle. The latter method is used to write data to the Oracle CLOB field.

It should be explained that in order to significantly improve the efficiency of the program execution , read and write operations on the Blob/clob field should use the buffer operation class (with the buffered prefix), namely: Bufferedinputstream, Bufferedoutputstream,bufferedreader,bufferedwriter.

A good memory is better than a bad pen. 20-java Summary of CLOB and BLOB fields for Oracle