NOTE: The material in this chapter is an updated excerpt from JDBCTM API Tutorial and Reference, Second Edition: Universal Data Access for the JavaTM 2 Platform, published by Addison Wesley, ISBN 0-201-43328-1. This document is intended as an overview of the major interfaces and classes and therefore does not cover all of the new features in the JDBCtm 3.0 API. For a more hands-on approach, the JDBC home page at
http://java.sun.com/products/jdbc
has links to basic and advanced tutorials. For complete information on the JDBC 3.0 API, consult the JDBC 3.0 Specification and the JDBC 3.0 API Specification (JavadocTM comments), available from
http://java.sun.com/products/jdbc/download.html
The JDBCTM API is the application programming interface that provides universal data access for the JavaTM platform. In other words, you use the JDBC API to work with a relational database or other source of data in an application written in the Java programming language.
The latest version, the JDBC 3.0 API, incorporates all previous versions and adds both new features and enhancements for existing features. One of the bigger changes is that all of the JDBC 3.0 API is included in the JavaTM 2 platform, Standard Edition (J2SETM), version 1.4, meaning that the Optional Package API no longer needs to be downloaded separately.
The JDBC 3.0 API includes the JDBC 1.0 API, which provides the basic functionality for data access. It also includes the JDBC 2.0 API, which 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. The JDBC 2.0 API also contains features such as scrollable and updatable result sets and improved performance. Further, it extends JDBC technology beyond the client to the server with connection pooling and distributed transactions. The JDBC 3.0 API adds features such as savepoints, pooled statements, and the ability to retrieve column values that are auto-generated keys, to name but a few. Appendix A has a complete list of new features for the 2.0 and 3.0 APIs.
The following list defines various terms used in talking about the JDBC API.
java.sql
package and the javax.sql
package.
javax.sql
, which makes it easier to build server-side applications using the Java platform. This package provides 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 1.0 API, plus the JDBC 2.1 core API, plus some of the new API in the JDBC 3.0 API
javax.sql
package - see JDBC Optional Package 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 Java 2 SDK, Standard Edition, and the Java 2 SDK, Enterprise Edition, 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 later versions will, of course, need to be run using a driver that supports those features.
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 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 3.0 API goes beyond SQL, also making it possible to interact with other kinds of data sources, such as files that are outside of a database.
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. You 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, you do not 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, run anywhere"TM.
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 API extends what can be done with the Java platform. For example, it 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 the easy and universal data access offered by the JDBC API 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:
Context ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup("jdbc/AcmeDB"); Connection con = ds.getConnection("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:
As interest in JDBC technology has grown, other JDBC-based tools have been developed to make building programs easier. For example, an application might present a menu of database tasks from which to choose. After a task is selected, the application presents prompts and blanks for filling in information needed to carry out the selected task. With the requested input typed in, the application then automatically invokes the necessary JDBC commands. With the help of such an application, users can perform database tasks even when they have little or no knowledge of SQL syntax.
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.
In summary, the JDBC API is a natural Java interface for working with SQL. It builds on ODBC rather than starting from scratch, so programmers familiar with ODBC will find it very easy to learn. The JDBC API retains some of the basic design features of ODBC; in fact, both interfaces are based on the Open Group (formerly X/Open) SQL CLI (Call Level Interface). The big difference is that the JDBC API builds on and reinforces the style and virtues of Java, and it goes beyond just sending SQL statements to a relational database management system.
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.
Moret recently, Microsoft introduced UDA (Universal Data Access) as an umbrella term that covers OLE DB, ADO, RDS, and ODBC. The JDBC 3.0 API contains all of the important functionality of UDA plus features not found in UDA, such as SQL99 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 middle tier written in the Java programming language.
SQL is the standard language for accessing relational databases. Unfortunately, SQL is not 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 SQL99 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 3.0 API includes support for SQL99, JDBC drivers are not required to support it.
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:
DriverManager
facility (included as part of the Java 2 Platform)
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 javax.naming
and javax.sql
packages, it is now also possible to use a DataSource
object registered with a naming service based on the Java Naming and Directory InterfaceTM ("JNDI") to establish a connection with a data source. It is still possible to get a connection using the DriverManager
class, but using a DataSource
object is recommended whenever possible.
The JDBC driver test suite helps developers be sure that their JDBC drivers conform to the specification for the JavaTM 2 Platform, Enterprise Edition (J2EETM). The test suite uses the J2EE Reference Implementation to test a JDBC driver with an application server, thus testing whether it will work as part of a J2EE compatible environment.
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 provide a way to access a data source for which there are no JDBC drivers.
Figure 1.3: shows various driver implementation possibilities.
JDBC technology-based drivers generally fit into one of four categories:
Driver categories 3 and 4 are the preferred ways 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:
Many drivers are available in all four categories. To get the latest information on drivers, check the JDBC web site at
http://servlet.java.sun.com/products/jdbc/drivers
and Merant, 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 JDBC API supports this new generation of DBMSs, and several database vendors are creating products with Java-relational capabilities. It should be noted, however, that these mechanisms are optional; if a DBMS does not have Java-relational capabilities, a JDBC driver is not required to have them.
Various JDBC application development tools are under way. Watch the pages for updates.