An Introduction to JDBC, Part 3
Pages: 1, 2
The original JDBC standard was not very efficient for loading large amounts of information into a database. Even if you use a
PreparedStatement, your program still executes a separate query for each piece of data inserted. If your software inserts 10,000 rows into the database, it can introduce a substantial performance bottleneck.
addBatch( ) method of
Statement allows you to lump multiple update statements as a unit and execute them at once. Call
addBatch( ) after you create the statement, and before execution:
con.setAutoCommit(false); // If some fail, we want to rollback the rest Statement stmt = con.createStatement( ); stmt.addBatch( "INSERT INTO CUSTOMERS VALUES (1, "J Smith", "617 555-1323"); stmt.addBatch( "INSERT INTO CUSTOMERS VALUES (2, "A Smith", "617 555-1132"); stmt.addBatch( "INSERT INTO CUSTOMERS VALUES (3, "C Smith", "617 555-1238"); stmt.addBatch( "INSERT INTO CUSTOMERS VALUES (4, "K Smith", "617 555-7823"); int upCounts = stmt.executeBatch( ); con.commit( );
Notice that we turn transaction auto-commit off before creating the batch. This is because we want to roll back all the SQL statements if one or more of them fail to execute properly (a more detailed discussion of transaction handling may be found later in this chapter, in the section "Transactions"). After calling
addBatch( ) multiple times to create our batch, we call
executeBatch( ) to send the SQL statements off to the database to be executed as a batch. Batch statements are executed in the order they are added to the batch.
executeBatch( ) returns an array of update counts, in which each value in the array represents the number of rows affected by the corresponding batch statement. If you need to remove the statements from a pending batch job, you can call
clearBatch( ), as long as you call it before calling
Note that you can use only SQL statements that return an update count (e.g.,
DELETE) as part of a batch. If you include a statement that returns a result set, such as
SELECT, you get a
SQLException when you execute the batch. If one of the statements in a batch can't be executed for some reason,
executeBatch( ) throws a
BatchUpdateException. This exception, derived from
SQLException, contains an array of update counts for the batch statements that executed successfully before the exception was thrown. If we then call
rollback( ), the components of the batch transaction that did execute successfully will be rolled back.
addBatch( ) method works slightly differently for
CallableStatement objects. To use batch updating with a
PreparedStatement, create the statement normally, set the input parameters, and then call the
addBatch( ) method with no arguments. Repeat as necessary and then call
executeBatch( ) when you're finished:
con.setAutoCommit(false); // If some fail, we want to rollback the rest PreparedStatement stmt = con.prepareStatement( "INSERT INTO CUSTOMERS VALUES (?,?,?)"); stmt.setInt(1,1); stmt.setString(2, "J Smith"); stmt.setString(3, "617 555-1323"); stmt.addBatch( ); stmt.setInt(1,2); stmt.setString(2, "A Smith"); stmt.setString(3, "617 555-1132"); stmt.addBatch( ); int upCounts = stmt.executeBatch( ); con.commit( );
This batch functionality also works with
CallableStatement objects for stored procedures. The catch is that each stored procedure must return an update count and may not take any
BLOBs and CLOBs
As users began to increase the volume of data stored in databases, vendors introduced support for Large Objects (LOBs). The two varieties of LOBs, binary large objects (BLOBs) and character large objects (CLOBs), store large amounts of binary or character data, respectively.
Support for LOB types across databases varies. Some don't support them at all, and most have unique type names (BINARY, LONG RAW, and so forth). JDBC 1.0 makes programs retrieve
CLOB data using the
getBinaryStream( ) or
getAsciiStream( ) methods. (A third method,
getUnicodeStream( ), has been deprecated in favor of the new
getCharacterStream( ) method, which returns a
In JDBC 2.0, the
ResultSet interface includes
getBlob( ) and
getClob( ) methods, which return
Clob objects, respectively. The
Clob objects themselves allow access to their data via streams (the
getBinaryStream( ) method of
Blob and the
getCharacterStream( ) method of
Clob) or via direct-read methods
getBytes( ) method of
Blob and the
getSubString( ) method of
To retrieve the data from a CLOB, simply retrieve the
Clob object and call the
getCharacterStream( ) method:
String s; Clob clob = blobResultSet.getBlob("CLOBFIELD"); BufferedReader clobData = new BufferedReader(clob.getCharacterStream( )); while((s = clobData.readLine( )) != null) System.out.println(s);
In addition, you can set
Clob objects when you are working with a
PreparedStatement, using the
setBlob( ) and setClob( ) methods. While the API provides update methods for streams, there are no
updateBlob( ) or
updateClob( ) methods, and the
Blob interface provides no mechanism for altering the contents of a
Blob already stored in the database (although some drivers support updating of BLOB and CLOB types via the
setBinaryStream( ) and
setCharacterStream( ) methods of
PreparedStatement). Note that the lifespan of a
Clob object is limited to the transaction that created it.
JDBC driver support for BLOB and CLOB types varies wildly. Some vendors don't support any LOB functionality at all, and others (including Oracle) have added extensions to allow manipulation of LOB data. Check your driver documentation for more details.
In the next installments, learn about metadata and then transactions using JDBC within the J2EE framework.
William Crawford, Jim Farley is a coauthor of Java Enterprise in a Nutshell, 2nd Edition, and has been developing web-based enterprise applications since 1995. He is currently the Director of the Informatics Solutions Group at Children's Hospital, Boston, where he and his team are building open source Personally Controlled Health Record systems and tools for managing agile development projects in healthcare and regulated industries.
View catalog information for Java Enterprise in a Nutshell, Second Edition
Return to ONJava.com.