Top Ten Oracle JDBC Tips (from: http://www.onjava.com/pub/a/onjava/2001/12/19/oraclejdbc.html)

The Java Database Connectivity (JDBC) API is a set of interfaces that allow a Java programmer to access a database. The interfaces are implemented by each vendor's set of implementation classes. After several years of working with Oracle's JDBC implementation, I've learned a number of things that you can do to squeeze out the best performance and the most functionality.

1. Use the Oracle Thin driver for client-side access.

Oracle provides four driver types to use when developing Java programs. Two are for client-side use with programs such as applications, applets, and servlets, while the other two are for server-side (or internal) use with Java stored procedures in the database. On the client side, you can choose between the OCI driver, which communicates to the database through the Oracle Client software, utilizing the Java Native Interface (JNI), or the Thin driver, a 100% pure Java driver that communicates directly with the database. Oracle recommends using the OCI driver on the client side in order to maximize performance, and intuitively that seems to make sense; however, I recommend using the Thin driver. I have found through testing that the Thin driver usually outperforms the OCI driver.

2. Turn off auto-commit for better performance.

When you first establish a connection to the database, the connection, by default, is in auto-commit mode. For better performance, turn auto-commit off by calling the Connection's setAutoCommit() method, passing it a boolean false, as follows:

conn.setAutoCommit(false);

Be aware, however, that once you turn auto-commit off, you'll have to manually manage your transactions by calling the Connection's commit() and rollback() methods.

3. Use the Statement object for time-critical or dynamic SQL statements.

When it comes to executing a SQL statement, you have two choices: you can use a PreparedStatement object or a Statement object. A PreparedStatement parses and compiles a SQL statement once, no matter how many times you reuse it. When you use a Statement, each time a SQL statement is executed, it is again parsed and compiled. This might lead you to think that using a PreparedStatement would be faster than using a Statement; however, my tests show this is not the case on the client side. So, for time-critical SQL operations, unless you are going to batch SQL statements, you should consider using Statements.

In addition, the use of a Statement also makes it easier to write dynamic SQL statements, because you can simply concatenate strings together to build a valid SQL statement. Consequently, I also recommend the Statement object for ease of use in creating and executing dynamic SQL statements.

4. Use helper functions to format your dynamic SQL statements.

When you create dynamic SQL statements to be executed using a Statement object, you need to deal with several formatting issues, such as escaping single tick (or single quote) characters (') with another tick character, and wrapping date values with the Oracle to_date() function. For example, if you want to create a SQL statement to insert the last name O'Reilly into a table, you'll have to replace the single tick character with two tick characters side-by-side as follows: O''Reilly. This can be best accomplished by creating a helper method that performs the replacement. Then use your helper method when concatenating your strings to formulate a SQL statement. Your helper method can also add in the ticks that you'll need at the beginning and end of each string. Similarly, you can create a helper method to take a Date value and output it as a string expression based on the Oracle to_date() function.

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 [e-mail]. Instead of good luck I wish you good skill!