Advanced Features
This section covers some of the advanced features of JDBC:
Binary Large OBjects (BLOBs) and Character Large OBjects (CLOBs), which make moving large objects to and from the database faster and easier
Dates and times, which are used to handle timestamps and so forth
Batch updates, which are used to improve the efficiency of making many updates to the database at a single time
Let's look at each of these in detail.
BLOBs and CLOBs
BLOBs and CLOBs are SQL data types available for efficient storage and retrieval of large objects. BLOBs contain binary data while CLOBs contain only characters.
BLOB and CLOB data types are created to store and retrieve very large objects such as a user's pictures or very large text files. BLOBs and CLOBs offer two benefits: First, they provide a convenient way to represent a large amount of data as a single database object. In other data types, such as integers, the amount of data that can be stored is limited. Second, DBMSs have been optimized to work with these data types, which means that reading and writing large objects is efficient, and certainly much faster than if you had to break them up into multiple smaller objects.
Using BLOBs and CLOBs
Both BLOBs and CLOBs are accessed via the ResultSet object returned as part of an SQL query. First, create instances of BLOBs and CLOBs:
java.sql.Blob myBlob = null; java.sql.Clob myClob = null;
Next, get a ResultSet that contains a column named "blobcolumn" of BLOBs and a column named "clobcolumn" of CLOBs by doing a query:
… query that returns ResultSet with BLOB and CLOB here …
myBlob = rs.getBlob("blobcolumn");
myClob = rs.getClob("clobcolumn");
Finally, display the BLOB using an InputStream:
java.io.InputStream readis = myReadBlob.getBinaryStream();
for (int i=0 ; i < STREAM_SIZE ; i++) {
r[i] = (byte) readis.read();
System.out.println("output [" + i + "] = " + r[i]);
}
And also display the CLOB using another InputStream:
java.io.InputStream readClobis = myReadClob.getAsciiStream();
char[] c = new char[26];
for (int i=0 ; i < 26 ; i++) {
c[i] = (char) readClobis.read();
System.out.println("output [" + i + "] = " + c[i]);
}
Batch Updates
Batch updates enable you to combine a group of updates to the database into a single batch operation. All the updates can be sent inside of a single database call. So, if you have n updates, instead of doing n database calls, you only do one. This streamlines the process of doing multiple updates to the database. In single updates, the JDBC driver contains a lock on a given table and then releases it for every update. Batch updates enable updates to be optimized around a single lock and can only be used on a single table in a database.
If you remember from the earlier Update examples, multiple updates to the database are handled as follows:
… locate driver, create Connection Object, etc. …
/* Execute an Update to insert two new entries into the
table named employee.
stmt.executeUpdate ("INSERT INTO employee VALUES ('JOE', 'Louisiana'
,5)");
stmt.executeUpdate ("INSERT INTO employee VALUES ('SHANDELLE',
'Ohio' ,6)");
These statements could be combined into a single batch of updates:
… locate driver, create Connection Object, Etc. …
// create a Statement object
Statement stmt = myConn.createStatement();
// add SQL statements to the batch
stmt.addBatch("INSERT INTO employee VALUES ('JOE', 'Louisiana'
,5)");
stmt.addBatch("INSERT INTO employee VALUES ('SHANDELLE', 'Ohio'
,6)");
/* Send the statements using the executeBatch method
to the DBMS in a try/catch block to catch exceptions. */
try {
stmt.executeBatch();
} catch (Exception e) {
System.out.println("Exception in batch:
" + e);
}
Batch statements are executed in the order that they are received. Using batch updates with prepared statements is similar:
// Load Driver Instance, Get Connection, etc. here…
/* Create instance of the PreparedStatement
class with SQL wildcards specified by ?'s */
PreparedStatement pstmt =
myConn.prepareStatement("INSERT INTO employee VALUES (?,?,?)");
/* Execute an Update to insert two new entries
into the table named employee using PreparedStatement. */
/* Replace wildcards with values for each.
pstmt.setString(1, 'JOE'),
pstmt.setString(2, 'Louisiana'),
pstmt.setInt(3, 5));
/* Instead of calling executeQuery() here, call a addBatch
method to add the current batch parameter specified above. */
pstmt.addBatch();
/* Replace wildcards with values for each for the second
values in the batch update. */
pstmt.setString(1, 'SHANDELLE'),
pstmt.setString(2, 'Ohio'
pstmt.setInt(3, 6));
/* Instead of calling executeQuery() here, call a addBatch
method to add the current batch parameter specified above. */
pstmt.addBatch();
/* Call executeBatch to finish it off. This method returns an array
of integers that specify the number of updated rows for each state-
ment in the batch. */
int[] updateCounts = pstmt.executeBatch();
.
.