LOB Data Processing
This chapter describes how to process LOB data in tbJDBC.
The JDBC standard provides two types of data for processing LOB data: BLOB for binary data and CLOB for string data. To efficiently use these two types of data, tbJDBC uses locators.
In general, after LOB data is stored in a database, a locator is generated to provide access to where the data is stored. Users can read and write LOB data using the locator to improve system performance.
The following LOB classes are provided by tbJDBC.
com.tmax.tibero.jdbc.TbBlob
com.tmax.tibero.jdbc.TbClob
LOB Locators
Getting a LOB Locator
A LOB locator can be obtained by calling the method of the ResultSet object or the CallableStatement object provided by the JDBC standards.
ResultSet Object
ResultSet.getBlob()
ResultSet.getClob()
ResultSet.getObject()The following example shows how to obtain a LOB locator using the ResultSet object.
ResultSet rs = stmt.executeQuery("SELECT document, image FROM library");
while (rs.next())
{
Clob document = rs.getClob(1);
// or Clob document = (Clob)rs.getObject(1);
Blob image = rs.getBlob(2);
// or Blob image = (Blob)rs.getObject(1);
}CallableStatement Object
CallableStatement.getBlob()
CallableStatement.getClob()
CallableStatement.getObject()The following example shows how to obtain a LOB locator using the CallableStatement object.
CallableStatement cstmt = conn.prepareCall("{call proc(?)}");
cstmt.registerOutParameter(1, Types.CLOB);
cstmt.execute();
Clob document = cstmt.getClob(1);
// or Clob document = (Clob)cstmt.getObject(1);Passing a LOB Locator
A LOB locator can be passed by calling the method of the PreparedStatement object or the CallableStatement object provided by the JDBC standards.
PreparedStatement Object
PreparedStatement.setBlob()
PreparedStatement.setClob()
PreparedStatement.setObject()The following example shows how to pass a LOB locator using the PreparedStatement object.
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO library
VALUES (?, ?)");
pstmt.setClob(1, document);
// or pstmt.setObject(1, document, Types.CLOB);
pstmt.setBlob(2, image);
// or pstmt.setObject(1, image, Types.BLOB);
pstmt.executeUpdate();CallableStatement Object
CallableStatement.setBlob()
CallableStatement.setClob()
CallableStatement.setObject()The following example shows how to pass a LOB locator using the CallableStatement object.
CallableStatement cstmt = conn.prepareCall("{call proc(?, ?)}");
cstmt.setClob(1, document);
// or cstmt.setObject(1, document, Types.CLOB);
cstmt.setBlob(2, image);
// or cstmt.setObject(1, image, Types.BLOB);
cstmt.execute();Reading and Writing LOB Data
After a LOB locator has been obtained, it can read or write data using the APIs provided by the JDBC standards. LOB data can be read typically as a byte array or stream. The data can be accessed at any time while the connection is active because the LOB data, unlike other typical streams, exists in the database server.
JDBC standards provide the following APIs to read or write LOB data.
InputStream Clob.getAsciiStream()
Reader Clob.getCharacterStream()
String Clob.getSubString()
OutputStream Clob.setAsciiStream()
Writer Clob.setCharacterStream()
int Clob.setString()
InputStream Blob.getBinaryStream()
byte[] Blob.getBytes()
OutputStream Blob.setBinaryStream()
int Blob.setBytes()
The following example reads LOB data.
// BLOB data
InputStream is = image.getBinaryStream();
byte[] imageData = new byte[1000]
int readLength = is.read(imageData);
// CLOB data
Reader reader = document.getCharacterStream();
char[] docData = new char[1000];
int readLength = reader.read(docData, 0, docData.length);The following example writes LOB data.
// BLOB data
byte[] imageData = {32, 53, 78, 19, 2, 93};
OutputStream os = image.setBinaryStream();
os.write(imageData);
// CLOB data
char[] docData = {'J', 'D', 'B', 'C', '안', '내', '서'};
Writer writer = document.setCharacterStream();
writer.write(docData);
writer.flush();
writer.close();In the previous example, the OutputStream or Writer object is automatically transmitted to the database whenever data is written, thus execution of an UPDATE statement is not needed. However, to apply all changes made to LOB data, a commit operation is required since LOB data itself is included in the scope of a transaction.
Temporary LOB
tbJDBC provides temporary LOB to save large data in a LOB format. However, this large data is not saved in a table.
For this purpose, the following methods are provided.
Connection.createBlob() return Blob
Connection.createClob() return Clob
Connection.createNClob() return NClob
Blob.free()
Clob.free()
NClob.free()
When temporary LOBs are used to store data, the data is saved in temporary tablespace. If the data in the tablespace is no longer needed, users must free up the temporary LOBs. Otherwise, the data remains in the tablespace.
Temporary LOBs are normally used to bind LOB column data. The following is an example.
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO
library VALUES (?, ?)");
pstmt.setInt(1, 20090811);
Clob doc = conn.createClob();
doc.setString(1, "JDBC Guide<br>writer:Rachael<br>proofreader:Patrick");
pstmt.setClob(2, doc);
pstmt.executeUpdate();
doc.free();
pstmt.close();The previously described methods are the APIs of the JDBC standards. Some of them may not be available depending on the Java version. To address this problem, tbJDBC provides the following methods for implementation. These methods may lower compatibility with other JDBCs, but in tbJDBC, they can be used independently of any Java versions.
TbConnection.createTbBlob() return TbBlob
TbConnection.createTbClob() return TbClob
TbConnection.createTbNClob() return TbNClob
API Lists
Standard API
For information about standard APIs related to LOBs, refer to the JDBC standard documents.
Extended API
tbJDBC additionally provides BLOB, CLOB, and NCLOB methods as LOB related APIs.
CONNECTION API
The TbConnection class additionally provides the following APIs.
TbBlob createTbBlob()
Creates a temporary BLOB in the database, and returns the TbBlob
object.
TbClob createTbClob()
Creates a temporary CLOB in the database, and returns the TbClob object.
TbNClob createTbNClob()
Creates a temporary NCLOB in the database, and returns the
TbNClob object.
BLOB API
The TbBlob class additionally provides the following APIs.
TbBlob createEmptyBlob()
Creates and returns EMPTY_BLOB.
void close()
Closes a BLOB.
OutputStream getBinaryOutput
Stream()
Equivalent to setBinaryStream(1L).
OutputStream getBinaryOutput
Stream(long)
Equivalent to setBinaryStream(long).
void open(int)
Opens a BLOB in int mode.
CLOB API
The TbClob class additionally provides the following APIs.
TbClob createEmptyClob()
Creates and returns EMPTY_CLOB.
void close()
Closes a CLOB.
int getBufferSize()
Returns 32 kilobytes of buffer size for smooth communication with the server.
long getChars (long offset, char[] buffer)
Reads CLOB data from the offset location, and stores it in the buffer.
long getChars(long offset, char[]
buffer, long numChars)
Reads a number of characters of CLOB data equal to numChars starting at the location in offset, and stores it in the buffer.
long getChars(long offset, char[] buffer, long bufOffset, long numChars)
Reads a number of characters of CLOB data equal to numChars starting at the location in offset, and stores it in the bufOffset location in the buffer.
void open(int)
Opens a CLOB in int mode.
long putChars (long offset, char[] buffer)
Reads buffer data and stores it in the offset location.
long putChars(long offset, char[]
buffer, long numChars)
Reads a number of characters of buffer data equal to numChars, and stores it in the offset location.
long putChars(long offset, char[] buffer, long bufOffset, long numChars)
Reads a number of characters of buffer data equal to numChars from the bufOffset location, and stores it in the offset location.
NCLOB API
The TbNClob class additionally provides the following APIs.
TbNClob createEmptyNClob()
Creates and returns EMPTY_NCLOB.
void close()
Closes an NCLOB.
int getBufferSize()
Returns 32 kilobytes of buffer size for smooth communication with the server.
long getChars(long offset, char[]
buffer)
Reads NCLOB data from the offset location, and stores it in the buffer.
long getChars(long offset, char[]
buffer, long numChars)
Reads a number of characters of NCLOB data equal to numChars starting at the location in offset, and stores it in the buffer.
long getChars(long offset, char[] buffer, long bufOffset, long numChars)
Reads a number of characters of NCLOB data equal to numChars starting at the location in offset, and stores it in the bufOffset location in the buffer.
void open(int)
Opens an NCLOB in int mode.
long putChars(long offset, char[] buffer)
Reads buffer data and stores it in the offset location.
long putChars(long offset, char[] buffer, long numChars)
Reads a number of characters of buffer data equal to numChars, and stores it in the offset location.
long putChars(long offset, char[] buffer, long bufOffset, long numChars)
Reads a number of characters of buffer data equal to numChars from the bufOffset location, and stores it in the offset location.
Last updated