Java Programming with Oracle JDBC

Given the increasingly widespread use of Java as the multi-platform programming language of choice, there has been a tremendous need for an application programming interface (API) that allows Java programs to access databases through the execution of dynamic Structured Query Language (SQL) statements within Java methods. Java DataBase Connectivity (JDBC) is a Java API designed to fulfill that need. It is one of the most commonly used mechanisms for interacting with relational databases from within Java code. It comprises one concrete class and fourteen interfaces, which can then be implemented by any database vendor with their own product-specific classes. Oracle, the dominant relational database management system (RDBMS) in the world today, naturally has its own implementation of the JDBC API. Their incarnation of the JDBC API implements all of the standard JDBC interfaces, in addition to some Oracle-specific extensions. However, like all robust database APIs, the Oracle JDBC API can be difficult to fully learn and utilize. Fortunately, there exist a number of books in print to help the Java/Oracle programmer. O'Reilly & Associates, respected by developers the world over for its outstanding technical works, offers several Oracle JDBC books, one of which is Java Programming with Oracle JDBC (published on 15 December 2001 under the ISBN 978-0596000882), by Donald Bales. The volume covers Oracle8i, Release 2, Version 8.1.6, which is the first version of Oracle's database server product to support JDBC Version 2.0.

To even the most casual reader of the book, it is clear that Donald Bales has made every attempt to be thorough in his discussion of Java programming utilizing Oracle JDBC. Within its 477 pages, the book covers all of the essential aspects of the topic: connecting to an Oracle database from within the four major types of Java clients, Oracle advanced security, executing SQL statements, processing the result sets thus obtained, handling the various large datatypes, and working with object-relational SQL, including weakly and strongly typed object SQL.

The book's material is divided into twenty chapters, which are grouped into five parts. The first part comprises only one chapter, which contains an overview of the JDBC API, and an explanation of client/server computing, as well as the four major categories of Java clients that use Oracle JDBC: applications, applets, servlets, and internal objects — the last category referring to Enterprise Java Beans (EJB), and Oracle stored procedures written in Java.

The second part, Connections, explains how to set up connections to an Oracle database, for each of the aforesaid types of Java clients. Illustrative examples are provided for each of the four client categories. Establishing database connections can be one of the more challenging aspects of Oracle JDBC; thus the full coverage found in this book is invaluable. In addition, Bales relates Oracle's advanced security features, including authentication, data encryption (on both the server and client), data integrity (again, both server- and client-side), and Secure Sockets Layer (SSL). The critical topic of Oracle JDBC connections is wrapped up with a discussion of data sources accessed through Sun's JNDI implementation and those used through Oracle's connection cache interface.

In the third part, Relational SQL, Bales delineates how to utilize JDBC with relational SQL. He begins by presenting the basic elements of relational database analysis and design, in this case for a sample human resources (HR) database. Then he shows how to execute SQL statements (both regular and prepared statements) using JDBC, and how to retrieve the various types of result sets. In view of the storage limitations of the VARCHAR2 datatype, he discusses how to work with Oracle's large streaming datatypes: BLOBs, CLOBs, BFILEs, LONG RAWs, and LONGs. The last chapter in the third part addresses the important subject of how to call stored procedures from within Java methods. These stored procedures can be written in either PL/SQL (Oracle's original built-in programming language) or Java (new with Oracle8i).

Having looked at traditional relational database SQL, Bales devotes the fourth part of his book, Object-Relational SQL, to examining the two possible approaches for doing object-relational SQL using JDBC: utilizing object views and INSTEAD OF triggers to provide object-oriented presentations of the data stored in the tables, and storing and presenting the data using relational objects. He begins by transforming the sample HR database used earlier into an object database through the use of object views. Then he looks at weakly typed object SQL, which is the use of structures, arrays, and references for performing Data Manipulation Language (DML) operations on SQL objects. As part of this, he briefly discusses the corresponding Java object types: Struct, Array, and Ref. In addition to covering the three JDBC interfaces for the Java object types, he describes the corresponding Oracle classes, as well as the two descriptor classes used for creating new instances of Struct and Array classes. The final chapter in this part presents strongly typed object SQL, which is the use of client-side custom Java classes to do similar DML operations. Unfortunately, Bales does not clearly explain the correlation, if any, between the two possible approaches for doing object relational SQL using JDBC, with weakly vs. strongly typed object SQL. Moreover, he provides no guidelines as to which flavor of object SQL is most appropriate under which circumstances. But it may be argued that such guidance is beyond the apparent scope of the book. The chapter also explains JPublisher, Oracle's command-line utility for querying the database for the database object types, and creating SQLData or CustomDatum implementations for each SQL object. JPublisher itself is of course written in Java (but then, you weren't expecting Oracle to have developed it using Microsoft C++...).

The fifth and last part, Essentials, details various critical topics related to database integrity in Oracle: transactions, their scope and visibility, isolation levels, distributed transactions (including their implementation via XA), locking (implicit, row level, and table level), detection, and available solutions to the ongoing problem of data integrity. These guidelines should be useful for writing solid JDBC code that maintains the integrity of an Oracle database, even when multiple users are updating it. Then he presents a brief overview of performance issues associated with OCI and Thin drivers. The book concludes with a useful chapter on troubleshooting numerous problems commonly encountered by the Oracle JDBC developer.

Overall, Bales employs a clear writing style, with no evident fluff, and makes judicious use of clear examples. This is most welcome at a time when bookstore shelves are weighed down by bulky tomes of 1000+ pages, padded with marginally useful material, seemingly to justify their ever-increasing prices. O'Reilly's offerings tend to be far more lean and to the point. Bales's volume is both a tutorial and a reference, which is probably the best choice for this subject. It is clear that his knowledge of the subject is the result of many years in the trenches developing real Oracle database applications. The only notable weakness in this otherwise impressive book, is that the high-level options presented in separate places of the book are not always clearly tied together, leaving the reader wondering how the high level categories promised earlier relate to the current topics being discussed.

No review of Donald Bales's book would be complete without mentioning another JDBC book, also published by O'Reilly. Unlike Database Programming with JDBC and Java (by George Reese), Bales's book covers only database-specific topics, at a more concrete and basic level. In addition, Reese's book also includes an overview of database concepts, which would be unneeded by the typical Oracle developer, but could be valuable to any Java programmer unfamiliar with database programming.

Further information on JDBC can be obtained from Sun's website, which offers the complete JDBC Java specification. More specifically for Oracle, the complete JDBC API Javadoc can be found on the Oracle Technology Network (OTN) website. In addition, all of the program examples for the book and the errata can be downloaded from the O'Reilly website. Clearly, Java Programming with Oracle JDBC should prove quite useful to any programmer interested in developing Java applications that access Oracle databases using JDBC.