Top Ten Oracle JDBC Tips

5. Use the PreparedStatement object for overall database efficiency.

When you use a PreparedStatement object to execute a SQL statement, the statement is parsed and compiled by the database, and then placed in a statement cache. From then on, each time you execute the same PreparedStatement, it is once again parsed, but no recompile occurs. Instead, the precompiled statement is found in the cache and is reused. For an enterprise application with a large number of users executing the same SQL statements repeatedly, the reduction in compiling from the use of PreparedStatements can improve the performance of the database. If it were not for the fact that the overhead on the client side of creating, preparing, and executing a PreparedStatement takes longer than creating and executing a Statement, I would recommend you use PreparedStatements for all, except dynamic, SQL statements (See Tip #3).

6. Use PreparedStatements for batching repetitive inserts or updates.

You can significantly reduce the amount of time it takes to perform repetitive inserts and updates if you batch them. Oracle's implementation of Statements and CallableStatements appears to, but doesn't actually, support batching. Batching is only supported by PreparedStatements. With Oracle, you can choose standard JDBC batching using the addBatch() and executeBatch() methods, or you can choose Oracle's proprietary method, which is faster, by utilizing the OraclePreparedStatement's setExecuteBatch() method along with the standard executeUpdate() method. To use Oracle's proprietary batching mechanism, call setExecuteBatch() as follows:

PreparedStatement pstmt3D null;

try {

  ((OraclePreparedStatement)

    pstmt).setExecuteBatch(30);

  ...

  pstmt.executeUpdate();

}

The value specified when calling setExecuteBatch() is the threshold that, when reached, automatically causes SQL statements executed with the standard executeUpdate() method to be sent to the database as a batch. You can force the transmission of a batch at any time by calling the OraclePreparedStatement's sendBatch() method.

7. Use the Oracle locator methods to insert and update large objects (LOBs).

Oracle's implementation of PreparedStatement does not fully support the manipulation of large objects like BLOBs and CLOBs. Specifically, the Thin driver does not support the use of the PreparedStatement object's setObject() and setBinaryStream() methods to set a BLOB's value, nor does it support the use of setCharacterStream() to set a CLOB's value. In addition, only methods in the locator itself, represented by a java.sql.Blob or a java.sql.Clob, can retrieve a LOB's value from the database. The fact that you can use a PreparedStatement to insert or update a LOB, but need to use a locator to retrieve a LOB's value, is inconsistent. Because of these two issues, I recommend you consistently use the locator's methods to insert, update, and retrieve LOB data.

8. Use SQL92 syntax for calling your stored procedures.

You can use either SQL92 or Oracle PL/SQL block syntax when calling stored procedures. Since little can be gained by using the proprietary Oracle PL/SQL block syntax, and since it may confuse the next programmer who maintains your application, I recommend you always use the SQL92 syntax to invoke stored procedures.

9. Use Object SQL to move your object model into the database.

Now that you can utilize Oracle as an object-relational database, consider moving your application's object model into the database. The current paradigm is to create Java beans as pseudo database objects that map their attributes to relational tables, and then to add methods to those beans. While this works well enough in Java, any other software application accessing the database can't leverage your object model because its behaviors only exist outside of the database, in your Java beans. If you utilize Oracle's object-relational technology, you can model both data and behavior in the database by creating new database object types. Then you can generate your custom Java bean classes using a tool such as JPublisher. If you use this approach, not only can your Java application see your application's object model, but so can any other software application that needs to share your application's data and behaviors.

10. Leverage SQL to perform work in the database.

My most important tip is for you to utilize SQL's set-oriented approach to solve your database processing needs, rather than degrading to the use of a procedural language such as Java. Often I see code where a programmer queries one table for a set of rows, and for each row in that set, queries several other tables for information. Finally the programmer creates separate UPDATE statements to "batch" update the first table's data. The very same task can be accomplished with a single UPDATE statement that uses a multicolumn sub-query in the set clause. Why write 100 lines of code to perform a batch update, pulling all the data across the network and then pushing it back to the database again, which is very inefficient, when the same task can be completed with one SQL statement, accessing all required data directly in the database? I recommend you learn how to use the SQL language to its fullest.

Summary

I've only lightly covered each of these topics in order to give you a high-level understanding of what is possible, and how you can use these techniques to your advantage. In my book, Java Programming with Oracle JDBC, I cover each of these topics and many more in a very comprehensive fashion. You can contact me at don@donaldbales.com. Instead of good luck I wish you good skill!

Donald Bales is a Computer Applications Consultant specializing in the analysis, design, and programming of distributed systems; systems integration; and data warehousing.

O'Reilly & Associates recently released (December 2001) Java Programming with Oracle JDBC.

• Sample Chapter 19, Performance, is available free online.

• You can also look at the Table of Contents, the Index, and the Full Description of the book.

• For more information, or to order the book, click here.

Return to ONJava.com.