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
 

With the 1.1 Java release, JavaSoft has presented Java developers with vastly expanded Enterprise development capabilities through the Java Enterprise APIs. Java Enterprise includes database and distributed computing support. In this article, I want to take a look at this new API and provide an introduction to its use.

What is JDBC?
The Java Database Connectivity API, commonly referred to as JDBC, is part of the JavaSoft Enterprise computing strategy to provide developers of client/server applications with a single API for accessing any database engine. In the current world of database development, developers often design applications to conform to the quirks of the proprietary database access API's which exist for each engine. Although the Microsoft ODBC specification exists to remedy this situation, non-Windows solutions do not commonly make use of ODBC. In addition, developers have found ODBC to be rather difficult to program due to its gratuitous use of void pointers and overwhelming complexity. ODBC also maps poorly to Java.

In order to address the issue of Java database connectivity, JavaSoft put together a group of database industry partners to formulate JDBC. They set out to fulfill the following goals:

  • Provide a single SQL-level API that harnesses the power of today's database engines.
  • Provide a small class and method set for the simple and most common needs while providing the flexibility to handle more rare and complex needs in extra classes.
  • Remain consistent to the greatest degree possible with the rest of the core Java libraries
  • Re-use key abstractions from existing database APIs such as XOPEN CLI and ODBC so that developers are familiar with it and the industry finds the API easier to embrace.
The Structure of JDBC
JavaSoft provides this API through the java.sql package which will be part of the JDK 1.1 distribution. This package contains eight interfaces, seven classes and three exceptions.

The power of the JDBC API lies in its ability to provide the developer with a single API which can decide at runtime the database it will run against. Listing 1 provides the source code to the Update.java which ships with the Imaginary JDBC Implementation for mSQL.

As you can see, this code never makes reference to a non-Java class. Instead, it gets an actual JDBC implementation class based on arguments passed to the application at runtime.

A JDBC implementation is simply a set of database specific classes which implement the eight java.sql interfaces. An implementation supplies the sometimes proprietary communication with the database and wraps it up into methods specified by the JDBC interfaces. A developer thus writes application code referencing only JDBC interface names and methods. Porting the application to another DBMS requires nothing more that specifying different command line arguments when executing the application.

Of course, the application needs some way of finding a particular implementation. The JDBC DriverManager class performs this task by keeping a list of implementations of the JDBC Driver interface. The DriverManager class learns about a Driver implementation either from its name appearing in the "jdbc.drivers" property or by the application specifically loading an implementation. In the Update application in Listing 1, we performed a bit of hocus pocus to allow the proper Driver implementation to be specified at the command line and then instantiated using the Class.forName() construct. This forces the static constructor in the Driver implementation class to be called. JDBC requires a Driver's static constructor to register the driver implementation with the DriverManager.

Table 1
Table  1:

A DriverManager can naturally be aware of many JDBC implementations at once. In order to specify which implementation is desired for a connection as well as where and how that implementation should connect, JDBC uses database URLs to make database connections. Like other URLs, a database URL provides enough information to uniquely identify a resource. Database URLs in particular identify a Driver implementation and data source. The mSQL URL looks like:

jdbc:msql://athens.imaginary.com:4333/db_test

This URL tells JDBC that the application requires the mSQL implementation in order to connect to the db_test database located at athens.imaginary.com 4333. The official generic form of the JDBC URL is:

jdbc:<subprotocol>:<subname>

where <subprotocol> can be anything that a particular implementation accepts as identifying it as a desired implementation and <subname> can be anything that points the implementation to the proper data source.

Using JDBC to Access Data
JDBC handles data access through three basic interfaces, Connection, Statement and ResultSet. A Connection represents a database transaction. Though JDBC implementations are auto-commit by default, an application may turn off auto-commit and execute multiple SQL statements as part of a single transaction. Either way, a JDBC obtains a Connection object from the DriverManager by passing it the appropriate URL. It then uses this Connection object to obtain one or more Statement objects for executing SQL statements.

The Statement class is exactly what its name implies, a SQL statement. It provides three different methods for executing statements, executeQuery(), executeUpdate() and execute(). Again, as their names imply, executeQuery() is for executing queries and executeUpdate() is for executing updates through DELETE, INSERT, and UPDATE statements. This article ignores the execute() method which is used for executing statements you know nothing about at compile time. For example, if you have a class that simply gets random strings you want to execute against the database but you do not know if it is a query or update, then you use the execute() method to accomplish that task. Listing 2 shows a query and Listing 3 shows an update.

The execution of an update simply returns the number of rows affected by that update. A query, on the other hand, returns a ResultSet object which maps to the results of the query. Listing 4 shows the processing of query results.

ResultSet's next() method steps the result set through the rows it returns one at a time. When it is executed, it tries to move to the next row, returning true if another row exists or false if the last row has been processed. Finally, an application can get at column values through the ResultSet getXXX() methods. Two getXXX() methods exist for each native Java type plus a few Java object types. The first version of a getXXX() method allows an application to get a column value using the column number and the other allows an application to retrieve the value based on the column name.

Advanced JDBC
JDBC provides extra interfaces for performing less common or more complex tasks. For example, it has Statement subclasses which handle stored procedures and meta-data classes which provide special information about result sets and database connections. A detailed discussion of these classes is beyond the scope of this article, but you can find more information in the JDBC specification posted at http://splash.javasoft.com/jdbc.

About the Author
George Reese is a lead systems analyst with Carlson Marketing Group and author of Database Programming with JDBC and Java from O'Reilly and Associates. He can be reached at [email protected]

	

Listing 1: Update.java

import java.sql.*;

class Update {
  public static void main(String argv[]) {
    try {
      Class.forName(argv[0]);
      String url = argv[1];
      Connection con = DriverManager.getConnection(url, 
                                                   "borg",
                                                   "");
      Statement s = con.createStatement();   
      s.executeUpdate("INSERT INTO t_test " +
                      "(test_id, test_val) " +
                      "VALUES(5, 'Yet another " +
                      "silly test')");
      System.out.println("Insert succeeded.");
      s.close();
      con.close();
    }
    catch( Exception e ) {
      e.printStackTrace();
    }
  }
}

Listing 2: Simple Query

ResultSet r;
Statement s = connection.createStatement();

r = s.executeQuery("SELECT test_id, test_val FROM t_test");

Listing 3: Simple Update

Statement s = connection.createStatement();
int row_count;

row_count = s.executeUpdate("UPDATE t_test SET test_val = "+
                            "'womble' WHERE test_id = 5");

Listing 4: Result set processing

ResultSet r;
Statement s = connection.createStatement();

r = s.executeQuery("SELECT test_id, test_val FROM t_test");
while( r.next() ) {
  System.out.println("Got: test_id=" + r.getInt(1) + 
                     ", test_val=" + 
                     r.getString("test_val"));
}


 

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.