Database Programming with JDBC

Interacting Directly with Your Database

By Erin Carroll and Andrew Wilson

The Java language, touted for being highly portable, was lacking a major component until recently. Database connectivity in today's Information Age must certainly be considered a major component of any programming model. To meet this requirement, JavaSoft has recently introduced the JDBC API (Application Programming Interface) for database access as flexible and portable as Java code.

The JDBC API was developed to have a standard, ODBC-like (Open Database Connectivity) interface that will allow Java-application developers access to databases residing on different servers—with different database management systems—in much the same way as ODBC. The plan was to use drivers that interpret standard JDBC syntax into native Database Management System (DBMS) syntax. From a development point of view, the underlying platform and DBMS are not very relevant, as Java is portable and uses drivers that deal with the underlying DBMS. This removes concerns about DBMS specifics from the application developer's task list.

The drivers that translate from JDBC to native DBMS must be ANSI SQL-2 entry-level compliant. JDBC also supports pass-through, where the Structured Query Language (SQL) syntax is passed directly to the database engine without translation at the driver level. As far as JDBC is concerned, this basically means anything goes; the underlying DBMS will handle the syntax.This is important, because even with a standard API, each DBMS may provide extensions that will allow for optimization of the particular engine. For example, Database B provides the minimal functionality to be ODBC compliant, but Database A has functionality not covered or required in the ODBC specifications. In this case, the pass-through option may be chosen to use the special features of Database A, even though it limits the portability of the code being developed.

The JDBC API Opens Up Your Java Applications

The JDBC API is a low-level, generic SQL API that will provide the flexibility needed for developing Java applications connecting to different DBMS engines, enterprise—wide or across the Internet. This interface provides application developers with a SQL-compliant interface much like the Microsoft API, so no additional learning curve is necessary for database development in Java.

According to JavaSoft, the JDBC API was modeled after the Microsoft ODBC API. Both are based on the Microsoft, X\Open SQL Call Level Interface (CLI). Open database connectivity means that applications are not limited to "talking" to one driver that is connected to one database. Rather, an application may be connected to multiple databases residing on multiple servers using different DBMS engines. This means you can create an applet to connect to your favorite-vacation-spots database, while querying your busy calendar-of-important-events database. Point, click and viol! You can schedule a week in Tahiti right after you finish your next big project.

OK, maybe this small example doesn't adequately illustrate the endless possibilities. Just imagine you are an information provider with huge databases distributed across many different platforms. Your task is to provide your customers with simple, usable, limited access to the data they require. Your job just got easier.

Although ODBC is a good solution for connectivity in other languages, the addition of the JDBC is necessary for database connectivity through Java because the ODBC is a C interface, making it inappropriate for Java access. JavaSoft states that:

calls from Java to native C code have a number of drawbacks in the security, implementation, robustness, and automatic portability of applications.

The Big Beans of the JDBC

The JDBC API has been implemented as a group of interfaces that allow connection, execution of SQL statements, and result-set processing by communicating with a database via the JDBC Driver Manager and DBMS-specific driver. JavaSoft lists the following as the most important JDBC API interfaces:

• java.sql.DriverManager: loads drivers and supports new database connections.
• java.sql.Connection: represents a connection to a database.
• java.sql.Statement: holds a SQL statement to be executed.
• java.sql.ResultSet: provides access to the result set of a java.sql.Statement.

Open Database Connectivity

Much like the ODBC architecture, the JDBC is comprised of a single, common interface, DBMS-specific drivers, and a Driver Manager. Placed between the Java application and the JDBC drivers, one of the Driver Manager's jobs is to establish the connection between the appropriate driver and the Java application. When the Driver Manager receives a request to connect to a specific URL, it will attempt to locate a driver able to connect to the database at that URL. A driver list is consulted when there are multiple drivers that may connect to a given database. The list of drivers is configured by the user and a driver is selected by the JDBC Driver Manager based on the order and security of the driver in the list.

Developers may elect to eliminate the Driver Manager from the driver-selection process, because applications are allowed to connect directly to a particular driver without going through the database-management layer. However, in either scenario, it is the responsibility of the driver, once it has been loaded, to register with the JDBC Driver Manager.

Currently, the only driver available is the beta version of Intersolv's JDBC-ODBC Bridge Driver, built "on top of ODBC." The Bridge Driver has been implemented as a DLL that maps JDBC functions to ODBC calls. According to Intersolv, this design will allow any ODBC driver—compliant with version 2.0 and above—to be used with the JDBC-ODBC Bridge Driver.

True open connectivity will be realized when vendors eventually create JDBC drivers that contain implementations of the abstract classes that are provided by the JDBC API and will allow connections through the Driver Manager to many different DBMS engines.

The initial implementation of the JDBC does not include support for the following:

• Committing transactions across different connections.
• Scrollable cursors.
• ODBC-style bookmarks.

Writing an JDBC-Enabled Applet

The demonstration applet is pretty straightforward. It attaches to a data source and allows us to do a series of queries, updates, inserts, and deletes. It's very short and fairly simple to read.

Though the applet is simple, setting up the JDBC so that an applet can be written is by far the hardest part. There are several small items to keep in mind. If the system is not set up correctly, many odd problems that make little or no sense may result. Almost all of these report back a message stating something similar to "No suitable driver found."

The first consideration is our CLASSPATH. JDK 1.0.2 requires that CLASSPATH point to \JAVA\LIB\CLASSES.ZIP, but we need to expand that so it also points to the directory of our JDBC install and the location of our JDBC-ODBC bridge. Ours, for example, is "set CLASSPATH=C:\JAVA\LIB \CLASSES.ZIP;C:\ JAVA\JDBC\CLASSES;C:\JAVA\JDBC-ODBC\CLASSES." This allows us to just compile our applet and load various classes needed at run time.

In order for the JDBC-ODBC bridge to work, we need to load a JDBCODBC.DLL. This DLL is located under the JDBC-ODBC\CLASSES\JDBC\ODBC directory. Either copy the DLL into a directory your PATH environment currently points to or add the new path to your current PATH settings. The latter is a better choice, since the JDBC-ODBC bridge might be updated several more times and this will help avoid mismatched DLLs and classes. Your applet will not work until you do this.

Finally, we need to create a data source. Windows NT and 95 have an ODBC-setup option in the control panels, which can create a new system data source. You must do this so that the JDBC can actually communicate to the database using the bridge. The reason will become apparent later on.

Listing One shows our applet's class-member variable declarations and our include statements. There are three new types we have not seen before: Connection, Statement, and ResultSet. ResultSet is pretty obvious: It establishes a connection to a data source. Statement is used to pass an SQL statement to the ODBC driver. ResultSet stores the returned information from the ODBC driver. These new types are added when we import java.sql.*.

After initializing the user interface, we need to connect to the database. But first, we need to load the JDBC-ODBC driver and test it; see Listing Two. We load the driver using the Class.forName method, which loads the class and keeps it resident in memory until the applet terminates. We then test the driver with the method Driver.jdbcCompliant, which will return TRUE if the driver does follow the JDBC specification. Finally, we check the driver to confirm that it can attach to the data source's URL. Once we know that it will work, we can actually attach to the database.

The URL for a data source is broken down into three parts, each separated by colons and the entire string in quotes. The first part, JDBC, represents the interface. The subprotocol name is the second part; in our case this will be ODBC. The final part is the subname, which is the data source's name. In our example, this entire URL will read jdbc:odbc:TestDB.

Once we have a valid URL, we can attempt to connect to the database using the DriverManager.getConnection method. This function will attempt to attach to the URL using each driver loaded. In our case it will use the JdbcOdbcDriver and return a new Connection class. With that connection we can then generate a new Statement class, which will allow us to actually query the database; see Listing Three.

The Statement class does all the work, sending the SQL string to the actual data source's driver through the JDBC-ODBC bridge. We do this by calling Statement.executeQuery, which returns a ResultSet. There is also another method, Statement.executeUpdate, which we use to insert, delete, or update a row within our datasource. It's important to understand that the Statement class is what allows us to make queries of the database. Everything prior to this simply establishes a communication session.

The ResultSet class is used to interpret the row(s) we just received. We may have several rows in a single ResultSet, so before we can process anything, we need to move to the right row by calling ResultSet.next. Initially we are on a blank row, and any manipulation of this blank row will raise a SQLException. Each time we query the data source, we must move to the next row before we try to read the data. Moving beyond the last row also raises a SQLException.

Assuming we don't raise an exception, we should be able to read the data from the row and display it in our applet, shown in Listing Four.

The ability to read is significant; however, we need to add some functionality so that we can do more than just look at our database. Statement.executeUpdate inserts, deletes, and updates rows in our database. We need specify only the correct SQL string to pass to the ODBC driver to carry out our task. Listing Five shows how we delete a record from the database.

Another useful function is the ability to scroll through the database. Listing Six shows the Next and Previous buttons in action. Next works fairly simply; Previous, however, is a little trickier since it involves going backward through the row list. A minor inconvenience of the JDBC is that the database is not scrollable, which means that you can only move forward in the database. To move backward, you need to issue a new SQL statement and trap the ResultSet.

Finally, we should clean up our connections during the termination of the applet. Connection, Statement, and ResultSet all have close functions that explicitly close each layer's interaction with the database; see Listing Seven. This makes your code more readable to other users. Further, by explicitly closing your JDBC services, you guarantee that another Java interpreter will not inadvertently leave your database open after the applet has terminated. This sample application, although basic, provides the framework needed to access databases using Java and the JDBC API. Using this as a starting point, you can now go on and create your own platform-independent database applications.

Java Continues to Mature

Java can be looked at in two different ways. The first is as a programming language, with characteristics such as organization, syntax, mechanics, and construction. The second is as its application, with the ability to apply the language in a variety of different applications and products. Java as a language is very powerful and fairly easy for the average C++ developer to learn, cutting the learning curve dramatically, while it has tremendous support for graphics, GUI interfaces, multiple threads, sound, file I/O, and exception handling.

If all this functionality sounds familiar, it's because the Microsoft Foundation Classes (MFC) had much of it back in the days of Visual C++ 1.0. With time, Microsoft expanded the MFC to include things that made it easier to write more complex code, such as OLE, WINSOCK, and ODBC connectivity. Now MFC can significantly reduce the time it takes to write an application because so many of the critical classes are included.

Java had many classes included from the beginning, such as networking and a multitude of good exception classes. However, it needed to take another step to make it a viable developer solution. New classes have been added, one of the most important being the JDBC, giving us connectivity to the databases that drive our various businesses and reducing the time it would have taken to code the interface ourselves.

The JDBC has a significant impact upon daily business. The JDBC can give us access to any one of our databases, bringing it online with minimal effort. We can add more robust user-friendly interfaces, and we can trust the exception handling in Java not to crash our server when a fault occurs. Finally, we can reduce the time that it takes to implement a Web and/or database solution.

The JDBC is another step in making Java one of the most powerful languages available. It currently gives us more connectivity than anyone would ever want to code themselves. It provides a common code that can be run on any platform, eliminating the old concept of porting from Windows to UNIX, which never seemed to look or work remotely like the original-platform version. It now gives us access to a variety of databases and expands the range of supported applications.

As developers, we need to make a decision about Java. Do we continue to look at Java as a language that we keep locked away in the Internet, or do we start to put together full applications using Java, knowing that it has many, if not all, of the features of MFC? Many of us use MFC in the applications we write or run. Knowing that we can write the same applications in Java—and that it will work in the same way on all platforms—how long will it be before we switch? Will we switch before our competitors?

(Get the source code for this article here.)

---

Erin and Andy are engineers at NuMega Technologies, Inc.