HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML

Programming with Databases Using Java, by Robert J. Brunner

A primary benefit of using the Java programming language is the wide range of packages available for simplifying a variety of programming tasks. One of these tasks is to provide a persistent storage for Java programs. Actually, this can be accomplished using several different techniques, including Serialization, SQLJ, JDBC and eventually JDO.

JDBC (Java Database Connectivity) is routinely covered by many different authors in varying detail; however, the fundamental basics of using it to connect a Java application to a database is often casually discussed or, worse, ignored completely. With the spread of CASE tools for simplifying Java-database interactions (using, perhaps, EJB), many users have become insulated from the actual details of what is going on "under the hood."

This is a critical point in really understanding what your application is doing, especially when you consider that you might want to connect to a database from a variety of Java applications, including applets, servlets, JavaServer Pages, Swing applications and Enterprise JavaBeans. This article provides a gentle introduction to the fundamentals so you can eventually tackle more challenging projects with greater confidence.

Before delving any further into the details of JDBC, let's make sure we all understand what JDBC does and doesn't do. Primarily, JDBC encapsulates the specifics of connecting to a database, sending SQL statements over the established connection, and processing the results from executed SQL statements. In addition, JDBC provides access to specific metadata (data that describes data) for both the database you're working with and the metadata for the result of the SQL query.

The phrase "JDBC encapsulates..." implies two things. One, that the JDBC API is composed primarily of interfaces. This is important, because it indicates that someone else must supply the implementation, better known as the JDBC driver (which we'll explore later in this article). The second point is that the JDBC API hides the differences between different databases, which allows you to migrate your application quickly between different database vendors. If you're not careful, of course, there's enough flexibility within the JDBC API to quickly tie yourself to a database vendor through the use of specific functionality. However, by following good object-oriented programming practices (also known as best practices), any potential liabilities can be minimized.

JDBC Architecture
The basic JDBC architecture (which roughly translates to the JDBC 1.2 API) is remarkably simple, given all that it's designed to accomplish. Essentially, vendors who implement the JDBC API must shoulder the burden of providing the detailed implementation, but that's a good thing for you as a consumer, of course, as it spurs competition. JDBC was designed to provide a "call-level" SQL interface for Java applications, which means that a user must have a working knowledge of SQL to actually use JDBC.

While this might seem counterproductive, it actually works to your advantage as it simplifies the development of tools, either custom developed or commercially produced, that can automate the complexities of database programming in Java. As an example, tools for creating Java classes from database schema (and vice versa), known as schema mapping, can be purchased from several major vendors.

Figure 1: Outline of the JDBC architecture
Figure 1

The architecture that results from using JDBC to handle database interactions with a Java application is outlined in Figure 1. It may appear a bit confusing at first glance, but that's primarily a result of the flexibility inherent in the JDBC model as well as in the four types of JDBC drivers (discussed later). Any Java application (e.g., an applet, servlet or JSP) that uses the JDBC API to communicate with a database system will utilize an appropriate JDBC driver to handle all interactions with the database.

Interestingly enough, the developer doesn't directly control which driver is used; this is handled by the Driver Manager (note that JDBC 2.0 introduces an alternative, the Data Source Object, which uses a slightly different architecture). This design allows a single application to interact with different databases using different JDBC drivers. In fact, the Driver Manager will select the appropriate driver object to use from the pool of drivers that it knows about based on the database contact information (the JDBC URL) supplied by the developer.

The rest of the information flow through the JDBC architecture depends on the specific type of JDBC driver used. The drivers come in four different flavors, cleverly named by Sun Microsystems as Type 1, Type 2, Type 3 and Type 4.

As previously indicated, the Type 1 driver is the Sun-provided JDBC-ODBC bridge, which translates the JDBC API into the Microsoft-developed ODBC API. Using this bridge, a Java application can interact with any ODBC-aware application, including the entire Microsoft Office suite. However, this simple flexibility introduces an extra layer of indirection, which, among other things, can severely limit performance: not only does the JDBC have to be transformed into ODBC, but the ODBC must be transformed into the application's own API, typically involving the equivalent of a database client library API.

On the other hand, the Type 2 JDBC drivers (also known as partial Java drivers) translate the JDBC API directly into the database client library API, reducing the level of complexity. This requires that the client (the machine running the Java application) must be running a subset of the (possible binary) code from the database-specific client API. This probably requires an extra license per client, as well as potential code distribution nightmares (for example, does your database vendor support all of your potential client platforms? How will you keep all of your clients synchronized with the latest version of the database libraries?).

Type 3 drivers are written in pure Java, and as a result overcome many of the limitations of the previous two types of drivers. This class of JDBC drivers communicates in a middleware protocol that provides an extra layer of flexibility. The middleware component can interact with many different database systems as this type of driver provides a server (i.e., the middleware component) that handles the specific database communications, allowing different applications to use the same JDBC driver to communicate with different database systems.

The Type 4 driver, also a pure Java driver, communicates directly with the database server in a database-specific protocol. This architecture, while conceptually the cleanest, isn't optimized for any specific hardware and operating system platforms (which can be done for Type 3 drivers). Instead, it's generally optimized for a specific database server, and as a result can provide significant performance benefits if you don't mind being tied to a specific database system (e.g., Oracle).

Choosing the Storage
The first step in designing a JDBC application, of course, is to select the database to use, never an easy task, especially in a "team" environment. In fact, if you're working on a corporate LAN, your ability to pick and choose a database system may be significantly limited due to security restrictions. (This results from the fact that most database systems will need to run as a daemon that will likely provide a network accessible port - a major security concern if not done properly.) On the other hand, a corporate LAN may already have both a database system and a JDBC driver available, which can easily be used for evaluation on new projects.

Assuming free reign on picking a database, three different classes of database systems can be used to develop JDBC applications. The first class is simple databases, of which Microsoft Access is the best example. These are generally so prolific in their distribution that you can ususally find one readily available. In fact, using the JDBC-ODBC bridge driver provided by Sun with the JDBC API, you can treat any data source that has an ODBC interface as potential persistent storage. Although these systems can be useful either for learning how to program with JDBC or for very simple Java database applications, they're not recommended for Internet-based applications (e.g., e-commerce) as they're not designed for large traffic volume or distributed transactions.

The second class is lightweight database systems, which generally, at a minimum, provide a basic persistent storage implementation but without a significant amount of the bells and whistles found in the next class of systems. Many of the databases that fall into this class are either open source or very reasonably priced. For example, mSQL is a moderately powerful relational database system that is free for noncommercial applications. Another product that falls in this category is MySQL, which has rather liberal licensing policies, including a GPL for specific versions.

The final class of database systems includes the household names (well, maybe only those households that are occupied by programming enthusiasts or Internet investors) of the database industry, including Oracle, Sybase, SQL Server and Informix. These systems are powerful, full-featured, network-ready software systems that are "ready for prime time" straight out of the box. Of course, the old adage that you get what you pay for certainly applies here, as these systems can take a major bite out of your wallet. However, if you're developing an e-commerce site, these systems are generally preferred due to their high performance standards, as well as their ability to scale along with your application. Finally, these systems often provide value-added enhancements such as the ability to serve as EJB containers and SQLJ interfaces, and include a well-stocked toolbox. In general, full-featured versions of these database systems are available for evaluation.

Figure 2: JavaSoft driver query Web page
Figure 2

Finding the Right Driver
Once a database has been selected, the last step before jumping into writing code is to choose an appropriate JDBC driver. Fortunately, this last step has been simplified by the Sun JDBC team. A Web page at the JavaSoft Web site (see Figure 2; http://industry.java.sun.com/products/jdbc/drivers) provides a simple form that you can use to find all drivers that have been registered with Sun. On this form you enter specific information regarding your project's requirements - for instance, a specific database system or version of JDBC. As a demonstration, Figure 3 shows the form filled out to find all drivers that support the JDBC 2.x API and connect to an Oracle database. As of late spring 2000, the driver query returned eight separate drivers. As can be seen in Figure 4, the query returns not only the name of the driver, but also its type, the JDBC version it supports, the database systems it supports as well as any supported JDBC standard extensions, and the date of availability.

Figure 3: JavaSoft driver query Web page as completed to find a JDBC 2.x Oracle-complaint driver
Figure 3

Figure 4: Result of the driver query shown in Figure 3
Figure 4

Conclusion This article will hopefully prepare newcomers for the more specific JDBC example articles and discussions commonly available. Programming with databases using Java is a rewarding and challenging task, and for those who enjoy working on cutting-edge technology, a must-have tool for their programming toolbox.

Author Bio
Robert Brunner is a member of the research staff at the California Institute of Technology, where he focuses on very large (multiterabyte) databases, particularly on KDD (Knowledge Discovery in Databases) and advanced indexing techniques. He has used Java and databases for more than three years, and has been the Java database instructor for the Java Programming Certificate at California State University Pomona for the past two years. He can be contacted at: [email protected]


All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.