Java gets ready for client/server primetime with the JDBC database access API.
Java has been called the vehicle by which developers will be able to bring dynamic application behavior to the Web. Users can download applets from either local or remote Web servers for execution inside popular Web browsers such as Netscape Navigator. Applets run disconnected from the server, just like native applications. Java, however, has fallen short of its widely touted ability to connect to outside resources via a built-in application programming interface (API). For instance, there is no native capability to link to local or remote database servers directly from Java applets. Fortunately for client/server Java developers, the advent of the Java Database Connectivity (JDBC) API means an end to those shortcomings. Following in the footsteps of Microsoft and open database connectivity (ODBC), JDBC promises to database-enable any Java applet or application.
Like Microsoft's ODBC, JDBC gives Java developers a common API to most relational databases. This includes client/server databases such as Oracle, Sybase, Informix, and DB2, as well as legacy databases such as IMS. JDBC sends a request to a database server using the appropriate SQL dialect and then processes the results (answer set). JavaSoft is also looking to layer higher-level APIs on top of JDBC, such as mapping of database tables to native Java classes and embedded SQL. JDBC will support advanced database features as well, such as using scalar functions and invoking stored procedures and triggers.
JDBC is riding the wave of popularity and hype that surrounds Java. Most major Java-enabled tools and relational database vendors such as Borland International Inc., Centura Corp., IBM, Rogue Wave Software Inc., and XDB Systems Inc. endorse JDBC. Moreover, JavaSoft and Intersolv (a major ODBC driver vendor) worked together on a JDBC compliance suite that is now available from JavaSoft. JavaSoft and Intersolv are also preparing for the release of a reference implementation of JDBC that uses the ODBC interface. This is the technology that lets Java applets use existing ODBC drivers. The JDBC API standard defines a set of Java classes required to perform standard relational database operations such as managing database connections, SQL statements, result sets, and metadata. Like ODBC, the JDBC API uses a driver manager that simultaneously supports multiple database drivers for various databases. The driver(s) can be downloaded with the applet or exist as native drivers on the client, depending on how you want to implement the security features of JDBC and Java.
The mechanisms to communicate with database servers exist as standard Java classes. Therefore, you don't need to revert to procedural C to interact with native database APIs or ODBC. This ensures that the security features of the Java language remain and that the JDBC code integrates cleanly in Java code. The JDBC API is expressed through several abstract Java interfaces that let you link to any number of databases. The key interfaces are the java.sql.DriverManager, java.sql.Connection, java.sql.Statement, and java.sql.ResultSet. The java.sql.DriverManager handles the loading and unloading of the appropriate database drivers required to make the connection. The java.sql.Connection interface exposes the database to the developer, representing the connection as an accessible Java component. The java.sql.Statement interface provides a container for executing SQL statements using a connection. Finally, the java.sql.ResultSet interface exposes the data that returns from the database server to the Java application. (See Figure 2.)
When you want to access a database, you'll obtain a java.sql.Connection object directly from the JDBC management layer and the java.sql.DriverManager. The Driver Manager uses the URL string as an argument, and the JDBC management layer locates and loads the appropriate driver for the target database to which the applet is attempting to link. The driver manager does this by querying each driver, locating the one that can connect to the URL. The drivers look at the URL to determine if it requires a sub-protocol that the driver supports. Having done that, the driver connects to the remote database, returning the correct java.sql connection object that is the applet's method for accessing services on the database.
JDBC can't do its job without a driver, and the JDBC management layer must know the location of each and every database driver available to it. There are two ways that JDBC does this. First, upon initialization, the java.sql.DriverManager class searches for the sql.drivers property in the system's properties. If it exists, the DriverManager class will load it. Second, you can call the specific driver explicitly, thus avoiding the search. Drivers may be downloaded over the network (Internet or Intranet) as an option.
Each driver must register with the DriverManager using the DriverManager.RegisterDriver method so that the DriverManager knows that the driver exists and where to find it. There are security issues here as well. For instance, JDBC only uses drivers coming from the local file system or from the same class loader.
The code in Listing 1 shows how a Java applet submits and processes a database query. JDBC returns a result set that's really a set of rows from the database. You can access the result set using the java.sql.ResultSet object, which provides several "get" methods that allow access to the data. To move through the result set, you invoke the ResultSet.next method.
While ODBC provides support for asynchronous processing, JDBC provides threading. This variation is not a limitation, but rather another way of doing the same thing. All you have to do is create a new thread when you need to provide asynchronous capabilities for a Java applet or application. JDBC supports transactions through the ability to execute several statements using a single transaction. By default, JDBC is in "auto-commit" mode, which means that each request exists within a single transaction. To disable this feature, you must call the Connection.setAutoCommit(false) method.
Once auto-commit is off, JDBC handles database requests as transactions. This means that you must batch the requests and then commit them as transactions through Connection.commit. If a problem occurs during the transaction, the applet may invoke Connection.roll-back to return the system to its pre-transaction state
JDBC provides cursor support using the ResultSet.getCursorName( ) method to return the cursor name linked to the result set. As with other implementations of cursors, you can use the JDBC cursor feature to invoke positioned updates and deletes and record scrolling and navigation options such as "next" and "previous." The database must support this feature in order for JDBC to take advantage of it.
Other database features are available as well, including the use of SQL escape syntax, stored procedures, and scalar functions. The escape syntax features let you map escape syntax into DBMS-specific syntax to support applications that require this feature. Stored procedures are callable from JDBC by calling the stored procedure with its arguments. You can use scalar functions such as ABS(number), DEGREED(number), WEEK(date), LENGTH(string), and DAYNAME(date).
There is another use for Java, however. Many developers are looking to take Java beyond the realm of the Web-bound, security-constrained Internet world to a full-blown application development language. Java applications function the same as any other client applications, such as those written using C++. The advantage of using Java as a true application development language is that you don't have to overcome the limitations I mentioned earlier. These applications can read and write local files and open any network connection.
The attraction of Java application development lies in JDBC's ability to create Java database applications for use inside the protection of the corporate firewall. Because the applications are trusted, you may give them privileges not granted to untrusted applets downloaded from the Web. You will also have the option of moving the Java application to the Web for use as a common applet on an untrusted system, with all of the security protections required.
In addition to using the traditional two-tier development model, Java and JDBC also provide three-tier access to database servers. With this three-tier architecture, the Java applet or application makes a call to a service layer at the middle tier. This layer can be a transaction-processing monitor, object request broker (ORB), or Web server API (CGI, ISAPI, or NSAPI). The middle-tier service, in turn, makes a call to a database server.
No one can deny that JDBC will be a force in the Java market. JDBC is moving in the right direction by creating a specification using a database-neutral communications API. ODBC was successful with its specification; JDBC will succeed in much the same way - even more so when you consider the hype surrounding Java and Java-enabled tools. By bridging to existing ODBC-enabled databases, JDBC can maintain compatibility with existing systems that don't have to know anything about JDBC.
Tools such as Symantec's Café and Borland's Latte will drive JDBC into the mainstream. These tools strive to provide seamless database access from Java applets and applications. JDBC will fill those needs. Moreover, JDBC can databaseenable existing Java systems. JDBC is still an evolutionary product, and developers won't really begin to understand its benefits until early 1997. In addition, Microsoft will not sit by and let JavaSoft drive deeper into its market. ActiveX provides similar services, going directly to the ODBC interface. Still, the momentum behind Java and JDBC seems unstoppable. If you want to create Java-enabled client/server applications, JDBC should be at the top of your list for database connectivity solutions.
class Select {
public static void main(String argv[]) {
try {
// Create a URL specifying an ODBC data source name.
String url = "jdbc:odbc:wombat";
// Connect to the database at that URL.
Connection con = DriverManager.getConnection(url, "kgh", "");
// Execute a SELECT statement
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c, d, key FROM
Table1");
// Step through the result rows.
System.out.println("Got results:");
while (rs.next()) {
// get the values from the current row: int a = rs.getInt(1);
Numeric b = rs.getNumeric(2);
char c[] = rs.getString(3).tocharArray();
boolean d = rs.getBoolean(4);
String key = rs.getString(5);
// Now print out the results:
System.out.print(" key=" + key);
System.out.print(" a-" + a);
System.out.print(" b=" + b);
System.out.print(" c=");
for (int I = 0; I < c.lngth; I++) {
System.out.print(c[i]);
}
System.out.print(" d=" + d);
System.out.print("\n");
}
stmt.close();
con.close();
} catch (java.lang.Exception ex) {
ex.printStackTrace();
}
}
}