Integrating Databases with Java via JDBC

With help from JDBC, Java's ability to integrate with popular commercial DBMS and its network-oriented nature make it an ideal client/server computing environment

The network-oriented nature of Java makes it an ideal candidate for client/server computing, especially now that the ability to integrate it with popular commercial Database Management Systems (DBMS) is in the making. I have heard people question the validity of Java for formal application development. Many users are confused as to the nature of Java and adhere to the notion that it is useful only for making simple animation and small applets for the Web. This is like saying that your heavy-duty Dodge Ram pickup is useful only for taking you to the neighborhood grocery store.

Most of the vendors see the opportunities of Java's portability and security as the prime reason to replace the current programming languages in their main development environment. However, it seems that the mass media concentrates only on the toys that you can make. The truth is that everything that you can do in C++, you can do in Java. And more. And even though currently there is a dearth of market-ready Java applications, this isn't surprising. Consider the large span of time that C++ has already had in the market.

Nevertheless, Java provides new means for communicating with databases through a system similar to that of C and C++ applications. In addition, the platform neutrality of Java cannot be stressed enough from the programmer's point of view.

Top commercial database product vendors like Oracle, IBM, Sybase, SAS, and Borland have been taking a careful look at the Java-DBMS integration methodology. A large number of third-party developers such as Gupta, Rogue Wave, Symantec, and Intersolv are also very interested in this new market. A small part of this interest stems from the need to keep a high-profile image. But in large part, these companies see a new opportunity that programmers everywhere will be taking advantage of in the near future. Starting now gives them a head start in this revolutionary market. Database and connectivity vendors can get a jump on what will be the next, truly distributed processing system.

JDBC

The first standardized work on Java-DBMS connectivity appears in a draft specification known as the Java Database Connectivity (JDBC) Application Programming Interface (API) specification. Created with the help of the aforementioned database and database-tool vendors, it is intended to fill the current vacancy in this level of connectivity that has prompted companies like Weblogic to develop proprietary interfaces.

To avert a potential Tower of Babel in the future due to proprietary mechanisms, work on JDBC is progressing rapidly. The initial specification was released on March 8, 1996 as a draft for open analysis. Towards the end of summer when the beta stages will near completion, vendors will also be close to releasing their candidate products for JDBC compliance.

JDBC creates a programming-level interface for communicating with databases in a uniform manner similar in concept to Microsoft's Open Database Connectivity (ODBC) component which has become the standard for personal computers and LANs. The JDBC standard itself is based on the X/Open SQL Call Level Interface, the same basis as that of ODBC. This is one of the reasons why the initial development of JDBC is progressing so fast.

Object classes for opening transactions with these databases are written completely in Java to allow much closer interaction than you would get by embedding C language function calls in Java programs, as you would have to do with ODBC. This way we can still maintain the security, the robustness, and the portability that make Java so exciting. However, to promote its use and maintain some level of backward compatibility, JDBC can be implemented on top of ODBC and other common SQL APIs from vendors.

JDBC consists of two main layers: the JDBC API supports application-to-JDBC Manager communications; the JDBC Driver API supports JDBC Manager-to-Driver implementation communications. The Manager handles communications with multiple drivers of different types from direct-interface implementations in Java to network drivers and ODBC-based drivers.

In terms of Java classes, the JDBC API consists of:

  • java.sql.Environment - allows the creation of new database connections;
  • java.sql.Connection - connection-specific data structures;
  • java.sql.Statement - container class for embedded SQL statements;
  • java.sql.ResultSet - access control to results of a statement.

The JDBC Driver API is contained in java.sql.Driver. Each driver must provide class implementations of the following virtual classes: java.sql.Connection, java.sql.Statement, java.sql.PreparedStatement, java.sql.CallableStatement, and java.sql.ResultSet. These virtual classes describe the API but are specific to how each database functions. The JDBC-ODBC bridge performs translations of JDBC calls to that which can be understood by ODBC clients at a C language level. This bridge is quite small due to the similarities of the specifications. The bridge is needed to isolate native C language calls to a controlled area while maintaining compatibility with non-JDBC databases.

JDBC over the Internet

One prime concern for JDBC is how it will function over the Internet. Sun is certainly pushing for active commercial use of large networks like the Internet, and the database vendors also recognize this importance.

Most of issues surrounding security are already addressed in the Java language specification; however, other elements like addressing specific instances of databases across the Internet must be taken into account. For this reason, JDBC borrows the Uniform Resource Locator (URL) syntax for globally unique database addressing. Most readers will recognize common URL schema like http://www.javaworld.com, indicating a World Wide Web address. JDBC follows a similar syntax. First, let's review all the elements of the URL, in case anyone is not familiar with it.

A complete URL consists of several elements as outlined in the example below:

http://host.domain.com:80/directory/directory/filename

The word "http" indicates the URL schema; this example indicates that it is of the HyperText Transport Protocol. The next group "host.domain.com" is the unique Internet hostname as assigned to each machine. The number "80" indicates the Internet host port number at which the service is located; port number 80 is the default port for http services. The set of directories then outline where in the hierarchy on that service the "filename" is located and, of course, the filename indicates the actual data item to retrieve.

JDBC's URL structure is very similar:

jdbc:odbc://host.domain.com:400/databasefile

Note that in addition to the main schema, JDBC, this structure also includes a subprotocol for ODBC. The subprotocol indicates how the JDBC Manager will access the database; in this case, the database file is accessed through the JDBC-ODBC bridge. The rest is the same as for regular URLs, indicating the hostname, port, and database location and name.

For programmers

To help current database-management staff and programmers understand JDBC better, we provide the following details about this interface.

Asynchronous operation that is sometimes built into other database APIs as specific SQL statements and additional mechanisms is already covered by the multithreaded nature of Java. The programmer can create separate threads from the main one for each asynchronous activity. However, all operations using the java.sql classes must be written as multithread-safe code, even if the objects themselves mostly access the operations through single threads. For details on making your code multithread safe, please look in the documentation for Java. The API provides transaction-handling and rollback procedures as well. However, the details of how the implementation works is left to the driver. Each transaction is started with a specific beginTransaction method and can then be committed or aborted as necessary. Once committed or aborted, all open statements for that transaction will be closed.

SQL has often been extended to provide the missing functionality in the original standards specification. To accommodate for these, JDBC provides a mechanism for embedding extended SQL statements similar to that available in ODBC. The syntax here is:

{keyword ... parameters ...}

To further compensate for the features of specific databases, vendors are allowed to extend the basic statement classes for their own designs. However, the basic statement classes must be supported at minimum with all databases for JDBC compliance.

Data types

The following table shows the data types supported in JDBC

SQL Type    Java Type       Description
CHAR        String          Single Character
VARCHAR     String          Variable length string of characters
LONGVARCHAR java.io.InputStream Very long (multi-megabyte) strings 
NUMERIC     java.sql.Numeric    Absolute precision fixed-point values
DECIMAL     java.sql.Numeric    Absolute precision Decimal value
BIT     boolean         Single bit/binary value (on or off)
TINYINT     byte            8-bit integer
SMALLINT    short           16-bit integer
INTEGER     int         signed 32-bit integer
BIGINT      long            signed 64-bit integer
REAL        float           Floating-point value
FLOAT       float           Floating-point value
DOUBLE      double          Large floating-point value
BINARY      byte[]          Array of binary values
VARBINARY   byte[]          Variable length array of binary values
LONGVARBINARY   java.io.InputStream Very large (multi-megabyte) array of binary values
DATE        java.sql.Date           Date value
TIME        java.sql.Time       Time value (GMT)
TIMESTAMP   java.sql.Timestamp  Time value with additional nanosecond field 

To sum up

JDBC follows an established specification and tried implementation for database-neutral communications from applications. The bridging solution to current ODBC systems allow users to maintain compatibility with older systems that may not have JDBC counterparts. Leading vendors are working very actively on getting JDBC components for their databases out to market in a relatively short period of time.

JDBC is still in the germination stage. Any fruits of this labor may be seen later this year when the vendors are ready. There is a certain implicit haste in all Java development right now. It seems that competition is brewing from the Microsoft side. ActiveX (formerly OLE Control Extensions) and Visual Basic comprise Microsoft's bid for the online portable networked applet world. The problem of platform neutrality is clearly evident in that solution; however, there is already a large base of developers for the Visual Basic market that may hold the potential to overwhelm other efforts. Enterprise-level components like JDBC may be among the deciding factors when software development firms are faced with this new race.

Rawn Shah is vice president of RTD Systems & Networking Inc., a network-consulting and integration company based in Tucson, AZ.

Learn more about this topic

Recommended
Join the discussion
Be the first to comment on this article. Our Commenting Policies
See more