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:

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 Systems Architect, Computer Applications Consultant, and Business Analyst specializing in the analysis, design, and programming of web-based, client-server, and distributed applications, the internationalization of existing applications, systems integration, and data warehousing.