NOTE: The material in this chapter is based on JDBCtm API Tutorial and Reference, Second Edition: Universal Data Access for the Javatm 2 Platform, published by Addison Wesley as part of the Java series, ISBN 0-201-43328-1.
The JDBCtm API is the application programming interface that provides universal data access for the Javatm programming language. It includes the JDBC 1.0 API, which provides the basic functionality for data access. The JDBC 2.0 API supplements the basic API with more advanced features and provides a standard way to access the latest object-relational features being supported by today's relational database management systems. In addition, the new API includes features such as scrollable and updatable result sets and improved performance. It also extends JDBC technology beyond the client to the server with connection pooling and distributed transactions.
The JDBC 2.0 API includes all of the API that is part of J2SE, and also the API in the JDBC Standard Extension API. The following list defines various terms used in talking about the JDBC API.
java.sql package (the JDBC 2.0 core API) and the javax.sql package (the JDBC Standard Extension API). The J2EE, includes the complete JDBC 2.0 API.
java.sql; the JDBC API that is part of the J2SE, which includes the JDBC 1.0 API plus the new JDBC API added in the java.sql package. Some of the new features in this package are scrollable result sets, batch updates, programmatic updates, and support for the new SQL3 data types.
javax.sql, which may be downloaded from http://java.sun.com/products/jdbc. This package makes it easier to build server-side applications using the Java platform by providing an open architecture that supports connection pooling and distributed transactions that span multiple database servers. The DataSource API plays an integral part in these capabilities and also works with the Javatm Naming and Directory Interfacetm (JNDI) to improve portability and make code maintenance easier. The javax.sql package also provides the RowSet API, which makes it easy to handle data sets from virtually any data source as JavaBeanstm components.
java.sql package-the JDBC 2.0 core API, which includes the JDBC 1.0 API plus the new API added in the J2SE
javax.sql package-the JDBC Standard Extension API
In keeping with the policy of maintaining backward compatibility, applications written using the JDBC 1.0 API will continue to run with both the J2SE, and the J2EE, just as they have always run. Having been well designed from the beginning, the JDBC 1.0 API is essentially unchanged. Applications using features added in the JDBC 2.0 API will, of course, need to be run using a driver that supports those features.
The package java.sql, being part of the core API, is automatically included when the J2SE, is downloaded. A driver vendor may bundle the package javax.sql with its driver product, but if a driver does not include the package, it can be downloaded from the JDBC website at the following URL:
http://java.sun.com/products/jdbc
It is also possible to get the javax.sql package by downloading the J2EE, which includes both the java.sql and javax.sql packages. Programmers who are not writing Enterprise JavaBeanstm (EJB) components or other server code will probably want to download the J2SE, rather than the J2EE, to limit the amount of disc space used.
The JDBC API is a Java API for accessing virtually any kind of tabular data. (As a point of interest, JDBC is the trademarked name and is not an acronym; nevertheless, JDBC is often thought of as standing for "Java Database Connectivity.") The JDBC API consists of a set of classes and interfaces written in the Java programming language that provide a standard API for tool/database developers and makes it possible to write industrial strength database applications using an all-Java API.
The JDBC API makes it easy to send SQL statements to relational database systems and supports all dialects of SQL. But the JDBC 2.0 API goes beyond SQL, also making it possible to interact with other kinds of data sources, such as files containing tabular data.
The value of the JDBC API is that an application can access virtually any data source and run on any platform with a Java Virtual Machine. In other words, with the JDBC API, it isn't necessary to write one program to access a Sybase database, another program to access an Oracle database, another program to access an IBM DB2 database, and so on. One can write a single program using the JDBC API, and the program will be able to send SQL or other statements to the appropriate data source. And, with an application written in the Java programming language, one doesn't have to worry about writing different applications to run on different platforms. The combination of the Java platform and the JDBC API lets a programmer write once and run anywhere.
The Java programming language, being robust, secure, easy to use, easy to understand, and automatically downloadable on a network, is an excellent language basis for database applications. What is needed is a way for Java applications to talk to a variety of different data sources. JDBC is the mechanism for doing this.
The JDBC 2.0 API extends what can be done with the Java platform. For example, the JDBC API makes it possible to publish a web page containing an applet that uses information obtained from a remote data source. Or an enterprise can use the JDBC API to connect all its employees (even if they are using a conglomeration of Windows, Macintosh, and UNIX machines) to one or more internal databases via an intranet. With more and more programmers using the Java programming language, the need for easy and universal data access from Java is continuing to grow.
MIS managers like the combination of the Java platform and JDBC technology because it makes disseminating information easy and economical. Businesses can continue to use their installed databases and access information easily even if it is stored on different database management systems or other data sources. Development time for new applications is short. Installation and version control are greatly simplified. A programmer can write an application or an update once, put it on the server, and everybody has access to the latest version. And for businesses selling information services, the combination of the Java and JDBC technologies offers a better way of getting out information updates to external customers.
In simplest terms, a JDBC technology-based driver ("JDBC driver") makes it possible to do three things:
The following code fragment gives a simple example of these three steps:
Connection con = DriverManager.getConnection(}"jdbc:myDriver:wombat", "myLogin", "myPassword"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (rs.next()) { int x = rs.getInt("a"); String s = rs.getString("b"); float f = rs.getFloat("c");
The JDBC API is used to invoke (or "call") SQL commands directly. It works very well in this capacity and is easier to use than other database connectivity APIs, but it was also designed to be a base upon which to build alternate interfaces and tools. An alternate interface tries to be "user-friendly" by using a more understandable or more convenient API that is translated behind the scenes into the JDBC API. Because the JDBC API is complete and powerful enough to be used as a base, it has had various kinds of alternate APIs developed on top of it, including the following:
Prior to the development of the JDBC API, Microsoft's ODBC (Open DataBase Connectivity) API was the most widely used programming interface for accessing relational databases. It offers the ability to connect to almost all databases on almost all platforms. So why not just use ODBC from Java?
The answer is that you can use ODBC from Java, but this is best done with the help of the JDBC API in the form of the JDBC-ODBC Bridge. The question now becomes, "Why do you need the JDBC API?"
There are several answers to this question.
Microsoft has introduced new APIs beyond ODBC such as OLE (Object Linking and Embedding) DB, ADO (ActiveX Data Objects), and RDS (Remote Data Service). In many ways these APIs move in the same direction as the JDBC API. For example, OLE DB and ADO are also object-oriented interfaces to databases that can be used to execute SQL statements. However, OLE DB is a low-level interface designed for tools rather than developers. ADO is newer and more like the JDBC API, but it is not pure Java. RDS provides functionality similar to the JDBC API's RowSet facility, but RDS is also not written in the Java programming language, and it is not portable.
Most recently, Microsoft has introduced UDA (Universal Data Access) as an umbrella term that covers OLE DB, ADO, RDS, and ODBC. The JDBC 2.0 API contains all of the important functionality of UDA plus features not found in UDA, such as SQL3 support.
The JDBC API supports both two-tier and three-tier models for database access.
Figure 1.1: illustrates a two-tier architecture for data access.
In the two-tier model, a Java applet or application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user's machine as the client, and the machine housing the data source as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet.
In the three-tier model, commands are sent to a "middle tier" of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications. Finally, in many cases, the three-tier architecture can provide performance advantages.
Figure 1.2: illustrates a three-tier architecture for database access.
Until recently, the middle tier has typically been written in languages such as C or C++, which offer fast performance. However, with the introduction of optimizing compilers that translate Java bytecode into efficient machine-specific code and technologies such as Enterprise JavaBeanstm, the Java platform is fast becoming the standard platform for middle-tier development. This is a big plus, making it possible to take advantage of Java's robustness, multithreading, and security features.
With enterprises increasingly using the Java programming language for writing server code, the JDBC API is being used more and more in the middle tier of a three-tier architecture. Some of the features that make JDBC a server technology are its support for connection pooling, distributed transactions, and disconnected rowsets. And, of course, the JDBC API is what allows access to a data source from a Java middle tier.
SQL is the standard language for accessing relational databases. Unfortunately, SQL is not yet as standard as one would like.
One area of difficulty is that data types used by different DBMSs (DataBase Management Systems) sometimes vary, and the variations can be significant. JDBC deals with this by defining a set of generic SQL type identifiers in the class java.sql.Types. Note that, as used in this material, the terms "JDBC SQL type," "JDBC type," and "SQL type" are interchangeable and refer to the generic SQL type identifiers defined in java.sql.Types. There is a more complete discussion of data type conformance in the chapter "Mapping SQL and Java Types."
Another area of difficulty with SQL conformance is that although most DBMSs use a standard form of SQL for basic functionality, they do not conform to the more recently defined standard SQL syntax or semantics for more advanced functionality. For example, not all databases support stored procedures or outer joins, and those that do are not always consistent with each other. Also, support for SQL3 features and data types varies greatly. It is hoped that the portion of SQL that is truly standard will expand to include more and more functionality. In the meantime, however, the JDBC API must support SQL as it is.
One way the JDBC API deals with this problem is to allow any query string to be passed through to an underlying DBMS driver. This means that an application is free to use as much SQL functionality as desired, but it runs the risk of receiving an error on some DBMSs. In fact, an application query may be something other than SQL, or it may be a specialized derivative of SQL designed for specific DBMSs (for document or image queries, for example).
A second way JDBC deals with problems of SQL conformance is to provide ODBC-style escape clauses. The escape syntax provides a standard JDBC syntax for several of the more common areas of SQL divergence. For example, there are escapes for date literals and for stored procedure calls.
For complex applications, JDBC deals with SQL conformance in a third way. It provides descriptive information about the DBMS by means of the interface DatabaseMetaData so that applications can adapt to the requirements and capabilities of each DBMS. Typical end users, however, need not worry about metadata.
Because the JDBC API is used as a base API for developing database access tools and other APIs, it also has to address the problem of conformance for anything built on it. A JDBC driver must support at least ANSI SQL-92 Entry Level. (ANSI SQL-92 refers to the standards adopted by the American National Standards Institute in 1992. Entry Level refers to a specific list of SQL capabilities.) Note, however, that although the JDBC 2.0 API includes support for SQL3 and SQLJ, JDBC drivers are not required to support them.
Given the wide acceptance of the JDBC API by database vendors, connectivity vendors, Internet service vendors, and application writers, it has become the standard for data access from the Java programming language.
The JDBC API is a natural choice for developers using the Java platform because it offers easy database access for Java applications and applets.
At the time of this writing, a number of JDBC-based products have already been deployed or are in development. The status of these products changes frequently, so the reader should consult the JDBC web page for the latest information. It can be found at the following URL:
http://java.sun.com/products/jdbc
provides three JDBC product components:
The JDBC DriverManager class has traditionally been the backbone of the JDBC architecture. It is quite small and simple; its primary function is to connect Java applications to the correct JDBC driver and then get out of the way. With the availability of the Standard Extension packages javax.naming and javax.sql, it is now also possible to use a DataSource object registered with a Java Naming and Directory Interfacetm (JNDI) naming service to establish a connection with a data source. Both means of getting a connection can be still used, but using a DataSource object is recommended whenever possible.
The JDBC driver test suite provides some confidence that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
The JDBC-ODBC bridge allows ODBC drivers to be used as JDBC drivers. It was implemented as a way to get JDBC off the ground quickly, and long term will provide a way to access some of the data sources for which there are no JDBC drivers.
Figure 1.3: shows various driver implementation possibilities.
[D]JDBC technology-based drivers generally fit into one of four categories:
Driver categories 3 and 4 are the preferred way to access databases using the JDBC API. Driver categories 1 and 2 are interim solutions where direct pure Java drivers are not yet available. There are possible variations on categories 1 and 2 (not shown in the following table) that require middleware, but these are generally less desirable solutions. Categories 3 and 4 offer all the advantages of Java technology, including automatic installation (for example, downloading the JDBC driver with an applet that uses it).
Table 1.1 shows the four categories and their properties. The table uses the following definitions for types of network connections:
At the time of this writing, there are dozens of drivers in Category 1, ODBC drivers that can be used with 's bridge. There are about a dozen Category 2 drivers built on top of native APIs for DBMSs. Many Category 3 drivers are available, and there are Category 4 drivers for all of the major DBMSs.
To get the latest information on drivers, check the JDBC web site at
http://java.sun.com/products/jdbc
and Intersolv, a leading database connectivity vendor, worked together to produce the JDBC-ODBC Bridge and the JDBC Driver Test Suite.
A new generation of DBMSs that are Java-aware is emerging. These new DBMSs, called Java-relational DBMSs, include new data types that allow an object in the Java programming language to be used as a column value in a database table. The new JDBC 2.0 features support this new generation of DBMSs, and several database vendors are creating products with Java-relational capabilities. It should be noted, however, that the 2.0 mechanisms are optional. If a DBMS does not support this feature, a JDBC 2.0 driver is not required to implement it.
Various JDBC application development tools are under way. Watch the pages for updates.