Top Ten Oracle JDBC Tips by Donald Bales, author of Java Programming with Oracle JDBC 12/19/2001

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.