Learning about Blob and Clob in Oracle

Blob refers to the writing of a Binary Large Object, that is, the English Binary Large Object, and Clob refers to the writing of a Large Character Object, that is, the English Character Large Object. This type is designed to store a large amount of data, BLOB is used to store a large amount of binary data, and CLOB is used to store a large amount of text data.

So someone must ask why the other two types have to be used since there are already VARCHAR and VARBINARY types? In fact, the problem is very simple. VARCHAR and VARBINARY have their own limitations. First, the length of the two types is limited and cannot exceed a certain limit. The length of the examples in VARCHAR and ORA cannot exceed 4000. Someone will ask again, the LONGVARCHAR type can be used as a type of storage characters in the database to meet requirements and store long characters. Why do we have to use the CLOB type? In fact, if you have used the LONGVARCHAR type, it is not difficult to find out. An important defect of this type is that you cannot use LIKE for conditional search. (I will introduce how to implement LIKE-LIKE fuzzy search in CLOB later) In addition to the above problems, in the database, VARCHAR and VARBINARY are used to read or write all the content. For K or larger data, this is the read/write method, it is far more practical than stream-based read/write.

There are two interfaces in JDBC that correspond to the BLOB and CLOB types in the database, java. SQL. Blob and java. SQL. Clob. You can use the ResultSet. getBlob () method to obtain the object of this interface. The only difference from the usual search is that after we get Blob or Clob objects, we don't get any data, but we can get data through the methods in these two interfaces.

For example:
Blob B = resultSet. getBlob (1 );
InputStream bin = B. getBinaryStryeam ();
Clob c = resultSet. getClob (2 );
Reader cReader = c. getCharacterStream ():
A more direct method can be used for Clob-type reading, that is, using ResultSet directly. getCharacterStream (); method to obtain the response stream, but this method is not safe, so we recommend that you use the method in the preceding example to obtain the Reader.
Another method is to obtain data blocks instead of data streams.
For example
Blob B = resultSet. getBlob (1 );
Byte data = B. getByte (0, B. length ());
Clob c = resultSet. getClob (2 );
String str = c. getSubString (0, c. length ()):
Here I want to explain that this method is actually not safe. If you are very careful, it is easy to find getByte () and getSubString () the second parameter in both methods is of the int type, while BLOB and CLOB are used to store a large amount of data. The return values of Bolb. length () and Clob. length () are of the long type, so it is not safe. It is not recommended here. But why do we mention this method here? I will tell you the answer later. here you need to remember that using data blocks is a method.

During storage, the following parameters are also used in PreparedStatement and callablestatemem to pass Blob and Clob objects to SQL using the setBlob () and setClob methods as parameters. This may sound simple, right, but it is not what we think. Unfortunately, JDBC does not provide Blob and Clob object independent of the database driver because of these two types. Therefore, you need to write your own driver-related code, but this is also far-fetched. What is the solution? This requires the previous thought to use data blocks for write operations. We also use the PreparedStatement and callablestatemem classes, but the parameter settings can be changed to setAsciiStream, setBinaryStream, setCharacterStream, and setObject (of course, the first three also have length issues)
The following is an example for your understanding.
Public void insertFile (File f) throws Exception {
FileInputStream FCM = new FileInputStream (f, Connection conn );
Byte [] buffer = new byte [1024];
Data = null;
Int sept = 0; int len = 0;

While (sept = FS. read (buffer ))! =-1 ){
If (data = null ){
Len = sept;
Data = buffer;
} Else {
Byte [] temp;
Int tempLength;

TempLength = len + sept;
Temp = new byte [tempLength];
System. arraycopy (data, 0, temp, 0, len );
System. arraycopy (buffer, 0, temp, len, sept );
Data = temp;
Len = tempLength;
}
If (len! = Data. length ()){
Byte temp = new byte [len];
System. arraycopy (data, 0, temp, 0, len );
Data = temp;
}
}
String SQL = "insert into fileData (filename, blobData) value (?,?) ";
PreparedStatement ps = conn. prepareStatement (SQL );
Ps. setString (1, f. getName ());
Ps. setObject (2, data );
Ps.exe cuteUpdate ();

}

Finally, as we have just mentioned the length of the characters read by the Clob type, I would like to give you another piece of code, hoping to help you.
Public static String getClobString (ResultSet rs, int col ){
Try {
Clob c = resultSet. getClob (2 );
Reader reader = c. getCharacterStream ():
If (reader = null ){
Return null;
}
StringBuffer sb = new StringBuffer ();
Char [] charbuf = new char [4096];
For (int I = reader. read (charbuf); I> 0; I = reader. read (charbuf )){
Sb. append (charbuf, 0, I );
}
Return sb. toString ();
} Catch (Exception e ){
Return "";
}
}

In addition, it seems that the LIKE search problem has been mentioned earlier. The LONGVARCHAR type cannot be searched by LIKE (at least not in ORA, but I have not tried other databases ), in ORA, we can use this function dbms_lob.instr to replace LIKE.

Select docid, dat0 from text where dbms_lob.instr (dat0, 'wei ', 1, 1)> 0

The docid field in the text table is used to store the document content in the clob type with the document No. dat0. It means to retrieve the data with the first word "wei" in the first dat0. It sounds like google's "good luck"