ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: Java Enterprise in a Nutshell, 2nd Edition

An Introduction to JDBC, Part 3

Related Reading

Java Enterprise in a Nutshell
By William Crawford, Jim Farley, David Flanagan

by William Crawford, Jim Farley and David Flanagan

Editor's note: In part three of this four-part excerpt on JDBC from Java Enterprise in a Nutshell, learn about error handling, prepared statements, BLOBs and CLOBs.

Handling Errors

Any JDBC object that encounters an error serious enough to halt execution throws a SQLException. For example, database connection errors, malformed SQL statements, and insufficient database privileges all throw SQLException objects.

The SQLException class extends the normal java.lang.Exception class and defines an additional method called getNextException( ). This allows JDBC classes to chain a series of SQLException objects together. SQLException also defines the getSQLState( ) and getErrorCode( ) methods to provide additional information about an error. The value returned by getSQLState( ) is one of the ANSI-92 SQL state codes; these codes are listed in Chapter 12. getErrorCode( ) returns a vendor-specific error code.

An extremely conscientious application might have a catch block that looks something like this:

try {
 // Actual database code
} 
catch (SQLException e) {
 while(e != null) { 
 System.out.println("\nSQL Exception:");
 System.out.println(e.getMessage(  ));
 System.out.println("ANSI-92 SQL State: " + e.getSQLState(  ));
 System.out.println("Vendor Error Code: " + e.getErrorCode(  ));
 e = e.getNextException(  );
 } 
}

SQL Warnings

JDBC classes also have the option of generating (but not throwing) a SQLWarning exception when something is not quite right, but at the same time, not sufficiently serious to warrant halting the entire program. For example, attempting to set a transaction isolation mode that is not supported by the underlying database might generate a warning rather than an exception. Remember, exactly what qualifies as a warning condition varies by database.

Related Articles:

An Introduction to JDBC, Part 2
Part Two of this excerpt from Java Enterprise in a Nutshell focuses on database connection, statements and results.

An Introduction to JDBC, Part 1
In this excerpt from Chapter 2 of Java Enterprise in a Nutshell, the authors introduce the JDBC architecture.

SQLWarning encapsulates the same information as SQLException and is used in a similar fashion. However, unlike SQLException objects, which are caught in try/catch blocks, warnings are retrieved using the getWarnings( ) methods of the Connection, Statement, ResultSet, CallableStatement, and PreparedStatement interfaces. SQLWarning implements the getMessage( ), getSQLState( ), and getErrorCode( ) methods in the same manner as SQLException.

If you are debugging an application, and you want to be aware of every little thing that goes wrong within the database, you might use a printWarnings( ) method like this one:

void printWarnings(SQLWarning warn) {
 while (warn != null) {
 System.out.println("\nSQL Warning:");
 System.out.println(warn.getMessage(  ));
 System.out.println("ANSI-92 SQL State: " + warn.getSQLState(  ));
 System.out.println("Vendor Error Code: " + warn.getErrorCode(  ));
 warn = warn.getNextWarning(  );
 }
}

You can then use the printWarnings( ) method as follows:

// Database initialization code here
ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");
printWarnings(stmt.getWarnings(  ));
printWarnings(rs.getWarnings(  ));
// Rest of database code

Prepared Statements

The PreparedStatement object is a close relative of the Statement object. Both accomplish roughly the same thing: running SQL statements. PreparedStatement, however, allows you to precompile your SQL and run it repeatedly, adjusting specific parameters as necessary. Since processing SQL strings is a large part of a database's overhead, getting compilation out of the way at the start can significantly improve performance. With proper use, it can also simplify otherwise tedious database tasks.

As with Statement, you create a PreparedStatement object from a Connection object. In this case, though, the SQL is specified at creation instead of execution, using the prepareStatement( ) method of Connection:

PreparedStatement pstmt = con.prepareStatement(
 "INSERT INTO EMPLOYEES (NAME, PHONE) VALUES (?, ?)");

This SQL statement inserts a new row into the EMPLOYEES table, setting the NAME and PHONE columns to certain values. Since the whole point of a PreparedStatement is to be able to execute the statement repeatedly, we don't specify values in the call to prepareStatement( ), but instead use question marks (?) to indicate parameters for the statement. To actually run the statement, we specify values for the parameters and then execute the statement:

pstmt.clearParameters(  );
pstmt.setString(1, "Jimmy Adelphi");
pstmt.setString(2, "201 555-7823");
pstmt.executeUpdate(  );

Before setting parameters, we clear out any previously specified parameters with the clearParameters( ) method. Then we can set the value for each parameter (indexed from 1 to the number of question marks) using the setString( ) method. PreparedStatement defines numerous setXXX( ) methods for specifying different types of parameters; see the java.sql reference material later in this book for a complete list. Finally, we use the executeUpdate( ) method to run the SQL.

The setObject( ) method can insert Java object types into the database, provided that those objects can be converted to standard SQL types. setObject( ) comes in three flavors:

setObject(int parameterIndex, Object x, int targetSqlType, int scale)
setObject(int parameterIndex, Object x, int targetSqlType)
setObject(int parameterIndex, Object x)

Calling setObject( ) with only a parameter index and an Object causes the method to try and automatically map the Object to a standard SQL type (see Table 2-1). Calling setObject( ) with a type specified allows you to control the mapping. The setXXX( ) methods work a little differently, in that they attempt to map Java primitive types to JDBC types.

You can use PreparedStatement to insert null values into a database, either by calling the setNull( ) method or by passing a null value to one of the setXXX( ) methods that take an Object. In either case, you must specify the target SQL type.

Let's clarify with an example. We want to set the first parameter of a prepared statement to the value of an Integer object, while the second parameter, which is a VARCHAR, should be null. Here's some code that does that:

Integer i = new Integer(32);
pstmt.setObject(1, i, Types.INTEGER);
pstmt.setObject(2, null, Types.VARCHAR);
// or pstmt.setNull(2, Types.VARCHAR);

Batch Updates

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.

The new 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 executeBatch( ).

Note that you can use only SQL statements that return an update count (e.g., CREATE, DROP, INSERT, UPDATE, 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.

The addBatch( ) method works slightly differently for PreparedStatement and 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 OUT or INOUT parameters.

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 BLOB and 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 Reader.)

Java & XML Data Binding

Related Reading

Java & XML Data Binding
By Brett McLaughlin

In JDBC 2.0, the ResultSet interface includes getBlob( ) and getClob( ) methods, which return Blob and Clob objects, respectively. The Blob and 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 (the getBytes( ) method of Blob and the getSubString( ) method of Clob).

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 Blob and 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 Blob or 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.

David Flanagan is the author of a number of O'Reilly books, including Java in a Nutshell, Java Examples in a Nutshell, Java Foundation Classes in a Nutshell, JavaScript: The Definitive Guide, and JavaScript Pocket Reference.


View catalog information for Java Enterprise in a Nutshell, Second Edition

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.