Chapter 3: Integrating Databases with JDBC

This chapter discusses how to integrate databases with Java using Java Database Connectivity (JDBC) and other techniques.

The Role of JDBC

Java Database Connectivity classes and interfaces allow an application to send SQL statements to a database management system (DBMS) and retrieve the results. JDBC functions in the same way as Open Database Connectivity (ODBC). One of JDBC’s strengths is interoperability: A developer can create JDBC applications without targeting a specific DBMS. Users can use specific JDBC drivers to target a specific database. Interoperability on the client side is also provided when using all Java JDBC drivers. Figure 3-1 shows Java clients running on different platforms.

Figure 3-1: Java clients running on different platforms.

The JDBC interface provides the application with a set of methods that enable database connections, queries, and result retrievals. It is the interface between specific database drivers and the Java user application, applet, or servlet.

The functions a user application can call are methods of connection, statements, or results object classes. Java is an object-oriented programming language, and the problems of impedance mismatch between Structured Query Language (SQL) and object-oriented programming (OOP) languages have been minimized but are still noticeable.

JDBC characteristics

The JDBC characteristics are as follows:

• JDBC is a call level SQL interface for Java. This interface is totally independent of the available database management systems. It is a low-level Application Programming Interface (API) that allows a Java program to issue SQL statements and retrieve their results. JDBC also provides methods for error and warning messages management. As shown in Figure 3-2, simple client/server scenarios require JDBC at the client side.

Figure 3-2: JDBC is located on the client side.

• JDBC doesn’t restrict the type of SQL queries passed to an underlying DBMS driver. An application may use as much SQL functionality as needed. The underlying drivers are authorized to claim JDBC compliance on the condition that they fully support ANSI SQL-92 Entry Level. SQL-2

• Entry Level conformance is widely supported today and guarantees a wide level of portability.

• JDBC may be implemented on top of common SQL level APIs, in particular on top of ODBC.

• JDBC provides a Java interface that stays consistent with the rest of the Java system. There are no conflicts because of opposed philosophies expressed by the impedance mismatch between the object-oriented world (Java) and the tabular world (SQL).

• The JDBC mechanisms are simple to understand and use. This simplicity doesn’t mean that functionality suffers.

• JDBC uses strong, static typing whenever possible. This approach enables more error checking at compile time.

• The concept of “one functionality, one method” has been adopted by JDBC, as opposed to many other DBMS SQL level APIs, to keep it simple yet powerful for the beginner as well as the experienced developer.

• JDBC can serve as a base for higher-level APIs such as embedded SQL for Java. DBMSs implement SQL, which is designed specifically for use with databases. JDBC requires that the SQL statements be passed as strings to Java methods. An embedded SQL preprocessor enables a programmer to instead mix SQL statements directly with Java: For example, a Java variable can be used in a SQL statement to receive or provide SQL values. The embedded SQL preprocessor then translates this Java/SQL mix into Java with JDBC calls.

• JavaSoft and others have announced plans to implement a direct mapping of relational database tables to Java classes. In this object/relational mapping, each row of the table becomes an instance of that class, and each column value corresponds to an attribute of that instance. Programmers can then operate directly on Java objects; the required SQL calls to fetch and store data are automatically generated “beneath the covers.” More sophisticated mappings are also provided — for example, where rows of multiple tables are combined in a Java class.

JDBC components

The following are the JDBC components:

• The application

• The driver manager

• The driver

• The data source

The application

The user application invokes JDBC methods to send SQL statements to the database and retrieve results. It performs these tasks:

• Requests a connection with a data source

• Sends SQL statements to the data source

• Defines storage areas and data types for the result sets

• Requests results

• Processes errors

• Controls transactions by requesting commit or rollback operations

• Closes the connection

Listing 3-1 provides a short example illustrating the use of JDBC to access a database and perform a simple update of a table. This example renumbers the records of a table. Note that there may be other techniques to renumber the records of a table in a more efficient way; the goal here is only to provide an example. The remaining parts of this book go into further details of every feature set of JDBC.

Listing 3-1: An Example of a JDBC Program

// updating data in a database table import java.sql.*; class SimpleExample {     public static void main(String args[])     {         String url = "jdbc:odbc:mysource";         Connection myConnection = null;         try         {             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");             myConnection = DriverManager.getConnection(url, "javauser",             "hotjava");             myConnection.setAutoCommit(false);             Statement firstStmt = myConnection.createStatement();             Statement secondStmt = myConnection.createStatement();             ResultSet rs = firstStmt.executeQuery("SELECT * FROM friends             ORDER BY name, firstname FOR UPDATE");             String cursor = rs.getCursorName();             // scan the result set             int i = 0;             while (rs.next())             {                 secondStmt.executeUpdate(                  "UPDATE friends SET id = " + i + " WHERE CURRENT OF " + cursor);             }             rs.close();             firstStmt.close();             secondStmt.close();             myConnection.commit();         }         catch(java.lang.Exception ex)         {             myConnection.rollback();             ex.printStackTrace();         }         finally         {             myConnection.close();         }     } }

The driver manager

The driver manager’s primary purpose is to load specific drivers for the user application. It may also perform the following tasks:

• Locates a driver for a particular database

• Processes JDBC initialization calls

• Provides entry points to JDBC functions for each specific driver

• Performs parameter and sequence validation for JDBC calls

The driver

The driver processes JDBC methods invocations, sends SQL statements to a specific data source, and returns results back to the application. When necessary, the driver translates and/or optimizes requests so that requests conform to the syntax supported by the specific DBMS. The driver performs the following:

• Establishes a connection to a data source

• Sends requests to the data source

• Performs translations when requested by the user application

• Returns results to the user application

• Formats errors in standard JDBC error codes

• Manipulates cursors if necessary

• Initiates transactions, if explicitly required

The data source

The data source consists of the data the user application wants to access and its associated parameters — that is, the type of DBMS and network layer (if any) used to access the DBMS.

New features of JDBC 3.0

Version 3.0 of JDBC introduced several new features and changes. Previous versions already enabled developers to write complete and rich database-driven applications. The latest revision provides even more functionality: Truly professional database applications can be written in Java. Here is a summary of the changes and additions to JDBC 3.0:

• Transactions can be rolled back to savepoints.

• Prepared statements are reusable by connection pools.

• It has enriched connection pool properties.

• It has a new metadata interface for parameters of prepared statements and calls to stored procedures.

• The retrieval of auto-generated keys is now possible.

• Parameters can be passed by name to a prepared statement or called to a stored procedure.

• It features holdable cursor support.

• It has new java.sql.Types.BOOLEAN and java.sql.Types.DATALINK data types.

• Blob and Clob objects can be altered in a simpler manner.

• It enables the retrieval of objects referenced by Ref objects.

• SQL BLOB, CLOB, ARRAY, and REF columns can now be updated in a simpler way.

• Metadata provides information about transform groups and type mapping.

• SQL type hierarchies are now retrievable.

• A relationship between JDBC and the J2EE Connector Architecture has been established.

JDBC driver types

There are several types of JDBC drivers; the different types are shown in Table 3-1. Some are written in Java and are entirely portable; others are partly Java and make use of proprietary libraries to communicate with a specific database. A special type is the JDBC-ODBC Bridge, which enables developers to use existing ODBC drivers to make connections to databases on either Microsoft Windows platforms or UNIX.

Table 3-1: JDBC DRIVER TYPES

Type	Description
Type 1	Bridge drivers (for example, the JDBC-ODBC Bridge)
Type 2	Partly Java and partly native code drivers
Type 3	Pure Java client drivers that use a native middleware peer on the server side
Type 4	Pure Java client drivers that connect directly to the database

Type 1 drivers

A Type 1 JDBC driver doesn’t implement a communication protocol for a particular database; it only provides a mapping to another database connectivity API. The JDBC-ODBC Bridge is such a driver: It enables the use of existing ODBC drivers to provide the connection to a given database without requiring developers to use ODBC calls from within their programs. Figure 3-3 shows the layering in place with Type 1 drivers. In most cases, the ODBC driver relies on a native driver, which in turn relies on a Net driver. Whereas the role of a native driver is to provide functional access to a specific database engine, the role of the Net driver is to provide a communication protocol for TCP/IP or Inter-Process Communication (IPC) mechanisms, for example.

Figure 3-3: The JDBC-ODBC Bridge driver.

The advantage of this type of driver is that you can quickly set up a database connection, provided that an ODBC driver is available on the platform. The drawback is that using ODBC isn’t always the fastest way to communicate with the database because many layers of code are involved: the JDBC-ODBC Bridge, the ODBC driver manager, the ODBC driver, and the Net library used by the ODBC driver.

Type 2 drivers

With Type 2 drivers, a native API driver forwards the calls to a locally installed library, which may be developed in C, for example, and provided by the database vendor. The local library may be a Dynamic Link Library (DLL) on Microsoft operating systems or a .so shared library on UNIX. Figure 3-4 shows native API, partly Java drivers. Type 2 drivers rely on a native driver (the library), which in turn often relies on a Net driver. This Net driver usually provides support for various communication protocols.

Figure 3-4: Native API, partly Java drivers.

Type 2 drivers are the most efficient because they use libraries that are supposed to be optimized for the database that the libraries target, but because the libraries are native, the solution won’t be portable. The solution requires a full installation and configuration of the libraries.

Type 3 drivers

Type 3 drivers are DBMS-independent all-Java Net drivers, and they use a DBMS-independent network protocol. They are very portable because they are 100 percent Java and lightweight because they implement only a thin layer of communication with a peer middleware that resides on the server. Figure 3-5 shows the Net driver.

Figure 3-5: The Net driver.

Because the driver part is 100 percent Java, the solution is portable. In some cases, the driver supports HTTP tunneling, which means that clients may still be able to connect to the database by using Web proxies and firewalls allowing HTTP traffic only. The disadvantage is that this type of driver necessitates the peer middleware being installed on the server side. This server side piece of software may use the JDBC-ODBC Bridge, pure Java, or native drivers to connect to the database.

Type 4 drivers

Type 4 drivers are native-protocol, all-Java drives that implement in Java all the layers necessary to communicate with the database. They are fully portable because they don’t use local libraries or other native code. Figure 3-6 shows native-protocol, all–Java drivers.

Figure 3-6: Native-protocol, all-Java drivers.

Type 4 drivers are the most portable drivers because they contain only Java classes. They are able to connect to the database without additional pieces of software on the client side as well as on the server side because they implement the wire protocol of the database. Type 4 drivers are the easiest to set up and deploy.

Examples of drivers

Table 3-2 contains several driver vendor names of each type. Notice that, at the time of writing, none of these drivers are actually JDBC 3.0 compliant. However, due to the broad use of Java and JDBC in the programmer community, such drivers will soon become available on the market. A complete list is available at http://java.sun.com/products/jdbc/.

Table 3-2: EXAMPLES OF DRIVERS

DBMS	Type 1	Type 2	Type 3	Type 4
Adabas	-	-	-	SAS/ACCESS
Cloudscape	-	-	-	Cloudscape
DB2	-	IBM	IBM, Intersolv, Hit Software	-
DMSII	-	-	Asgard	-
Essentia	-	-	Intersoft	-
Hypersonic	-	-	-	Hypersonic
Informix	-	-	Informix, Agave, IDS, I-Kinetics, Intersolv, OpenLink, SCO	SAS/ACCESS
Ingres	-	-	Caribou, Intersolv, OpenLink, SCO	SAS/ACCESS
InterBase	-	-	SCO	Borland
mSQL	-	-	-	Imaginary
Oracle	-	Intersolv, BEA	Agave, IDS, BEA, I-Kinetics, Intersolv, OpenLink, SCO Symantec	Oracle, SAS/ACCESS
PointBase	-	-	-	PointBase
Postgress	-	-	OpenLink	-
SAS	-	-	-	SAS
Sybase	-	Intersolv, BEA I-Kinetics, Sybase, Intersolv, OpenLink, SCO, Symantec	Agave, IDS, BEA,	Connect SW, Sybase
SQL Server	-	BEA	IDS, Intersolv, OpenLink, Symantec	-
Unify	-	-	OpenLink	-
Watcom	-	-	Sybase, Symantec	Sybase
Yard SQL	-	-	-	Yard Software
Via ODBC	JavaSoft	-	Agave, DataRamp, IDS, I-Kinetics, StormCloud, Symantec, Borland, BEA	-

Type 4 drivers are by far the most popular drivers because they are fully Java and don’t need additional OS-specific libraries and middleware. In addition, the

enhanced performance of Java 2 (JDK 1.3 and 1.4) shows no visible difference with native code drivers: Java is as fast as native code and sometimes faster than compiled C++ code.

The big picture

The JDBC interface defines the possible interactions between the user application and the driver manager. Figure 3-7 shows the relationship between the four JDBC components (the application, the JDBC driver manager, the various types of JDBC drivers, and the data source).

Figure 3-7: The complete JDBC architecture.