JDBC Refresher

Both Oracle and DB2XML use Java Database Connectivity. For those with limited database background, the Java Database Connectivity API, or JDBC, is an API that performs database access from Java applications. JDBC accomplishes most of what it does through a native API that translates Java methods to native calls. One key feature of JDBC is that it is platform independent. A JDBC-based application can run virtually unchanged from one JVM to another, from one database to another, making it a perfect vehicle for accessing XML information. In the remainder of this section, we will introduce the basics of JDBC so that new developers can get up to speed with JDBC quickly.

Over time, JDBC has evolved into an API for accessing any form of tabular data—that is, spreadsheets and flat files, as well as databases. JDBC is also an alternative to using CGI programs to retrieve data from databases and display them in Web-based applications. It's only natural that Java developers would want to use JDBC to manage XML data.

Historically, JDBC was modeled after the Microsoft ODBC de facto standard. Interestingly enough, ODBC itself was based on the X/Open CLI specification. Although Open Database Connectivity (ODBC) is simply a C-level API, JDBC represents a fully object-oriented database access layer. Since JDBC closely follows the functionality of ODBC, a JDBC driver can be implemented on top of an ODBC driver (a JDBC-ODBC "bridge"). In fact, numerous JDBC-ODBC bridge drivers exist.

Driver Types and the JDBC Architecture

Figure 10.1 shows the JDBC architecture. The top of the architecture is the Java application that accesses a database via the JDBC API, defined in the java.sql package. The JDBC API then calls one of four different types of JDBC drivers, depending on the data source.

The four driver types are

• Type 1 drivers, often called bridge drivers, show a JDBC face but call into an ODBC driver, which itself is normally implemented on top of a native driver written in C, C++, or another language.

• Type 2 drivers are implemented directly against the underlying native driver, eliminating the ODBC layer. Native drivers are still required.

• Type 3 drivers interface over the network to a remote database. Type 3 drivers are typically written in Java.

• Type 4 drivers are completely written in Java and access a database directly without the need for any intervening layers. From a pure performance perspective, type 4 drivers are best because they have the least overhead. In addition, since they are written completely in Java, type 4 drivers can run virtually unchanged on any platform.

A Simple JDBC Application

Listing 10.3 shows a simple JDBC application, but one that shows many of the useful features of JDBC.

Code Listing 10.3. dumpDB.Java—Simple Application to Dump Table Contents

 1:  package sams.chp10;
 2:  import java.sql.*;
 3:  public class dumpDB
 4:  {
 5:      public static void main(String args[])
 6:      {
 7:          try
 8:          {
 9:              if (args.length<2)
10:              {
11:                  System.out.println("Usage:");
12:                  System.out.println("sams.chp10.dumpDB dbName tableName");
13:                  System.exit(1);
14:              }
15:              Class.forName("COM.cloudscape.core.JDBCDriver");
16:              Connection  connection =
17:                  DriverManager.getConnection("jdbc:cloudscape:"+ args[0]);
18:              Statement   statement = connection.createStatement();
19:              ResultSet   resultSet;
20:              String      sql;
21:              // printout entire database
22:              sql = "SELECT * FROM "+args[1];
23:              resultSet = statement.executeQuery(sql);
24:              ResultSetMetaData md = resultSet.getMetaData();
25:              int colCt = md.getColumnCount();
26:              while (resultSet.next())
27:              {
28:                  for ( int i = 1; i<= colCt; i++)
29:                  {
30:                      String value  = resultSet.getString(i);
31:                      if ( i > 1)
32:                          System.out.print(" ");
33:                      System.out.print(value.trim());
34:                  }
35:                  System.out.println(" ");
36:              }
37:              statement.close();
38:              resultSet.close();
39:              connection.close();
40:          }
41:          catch (Exception e)
42:          {
43:              System.out.println("Error:" + e);
44:          }
45:      }
46:  }

Examining a few key lines of the listing, we see

• Line 1—All JDBC-based applications must import java.sql.*.

• Line 15—We force the class loader to load an appropriate driver. For this example we are using the Cloudscape database available from http://www.cloudscape.com and also on the CD-ROM. The actual driver name is database dependent; the name COM.cloudscape.core.JDBCDriver was defined by Cloudscape.

• Lines 16–17—After we have loaded the driver, we can use the JDBC driver manager to create a connection to the database on our behalf using the driver. The driver URL is also driver dependent. The URL jdbc:cloudscape:dbName was also specified by Cloudscape. The format of the URL is always jdbc:subprotocol:subname where subprotocol is typically the database provider and subname is data necessary to make the database connection. What the driver manager does is work as a class factory object, returning objects that implement the connection interface.

• Line 18—We obtain a Statement object from the connection. Later we use this statement object to execute an SQL statement such as select * from... or something similar. The Statement class is the most basic of the three classes representing SQL statements. We could have also used a prepared statement or a callable statement. Examination of these two classes is left to the reader.

• Line 19—JDBC returns results into a ResultSet object, which can be used like Enumeration objects. Resultsets represent rows of data returned. We then use the ResultSet.type ResultSet.type get(int columnNumber) or type get(String columnName) to get the actual data.

• Line 23—Executing a statement results in our resultset, which we walk on line 30, getting each value back as a string. We could have used statement.executeUpdate("sql...") to update the table. Insert and delete functionality is also provided.

• Lines 37–39—These lines clean up any mess we have made, freeing objects in the reverse order of their creation. We should .close() any statements and connections we made to free up resources.

Meta-data

One of the cool things about JDBC is that we can use it to gather meta-data, or data about the data. From an XML standpoint, this is very useful because we can create, on-the-fly, XML data and DTDs with nothing more then a table name. The class that makes this possible is java.sql.ResultSetMetaData. The meta-data class contains information such as

• How many columns are in the resultset?

• What are the column names and are they case sensitive?

• Is NULL a valid value for a field?

• Do the columns have display labels?

• Which table did the results come from?

• What is the data type of a given field?

Listing 10.4 makes use of the resultset meta-data and outputs an XML document, including its DTD, from the contents of the table.

Code Listing 10.4. dumpAsXML.Java—Export a Table as XML

 1: package sams.chp10;
 2: import java.sql.*;
 3:
 4: public class dumpAsXML
 5: {
 6:     public static void OutputData(ResultSetMetaData md, ResultSet rs)
 7:     {
 8:         try
 9:         {
10:             System.out.println("<" + md.getTableName(1)+"s>");
11:             while (rs.next())
12:             {
13:                 System.out.println("	<"+ md.getTableName(1)+">");
14:                 for ( int i = 1; i<= md.getColumnCount(); i++)
15:                 {
16:                     System.out.print("		<" + md.getColumnName(i)+">");
17:                     String value  = rs.getString(i);
18:                     System.out.print(value.trim());
19:                     System.out.println("</" + md.getColumnName(i) +">");
20:                 }
21:                 System.out.println("	</" +md.getTableName(1)+">");
22:                 System.out.println(" ");
23:             }
24:             System.out.println("</" + md.getTableName(1)+"s>");
25:         }
26:         catch (SQLException sqle) {}
27:     }
28:
29:     public static void OutputDTD(ResultSetMetaData md)
30:     {
31:         try
32:         {
33:             System.out.println("<?xml version="1.0"encoding="UTF-8" ?>");
34:             // make the rather simplistic choice that the table name of column 1
35:             // is the name of the results
36:             System.out.println("<!DOCTYPE " + md.getTableName(1) + "s [");
37:             System.out.println("<!ELEMENT " +
38:                                 md.getTableName(1) + "s (" +
39:                                 md.getTableName(1) + "*)> ");
40:             System.out.println("<!ELEMENT " + md.getTableName(1) + " (");
41:             for ( int i = 1; i<= md.getColumnCount(); i++)
42:             {
43:                 System.out.print("	"+ md.getColumnName(i));
44:                 if ( 1 == md.isNullable(i) )
45:                     System.out.print("?");
46:                 if ( i < md.getColumnCount())
47:                     System.out.println(",");
48:             }
49:             System.out.println("	)>");
50:             for ( int i = 1; i<= md.getColumnCount(); i++)
51:             {
52:                 System.out.println("	<!ELEMENT " +
53:                                     md.getColumnName(i) +
54:                                     " (#PCDATA)>");
55:             }
56:             System.out.println("]>");
57:         }
58:         catch (SQLException sqle) {};
59:     }
60:     public static void main(String args[])
61:     {
62:         try
63:         {
64:             if (args.length<2)
65:             {
66:                 System.out.println("Usage:");
67:                 System.out.println("sams.chp10.dumpAsXML dbName tableName");
68:                 System.exit(1);
69:             }
70:             Class.forName("COM.cloudscape.core.JDBCDriver");
71:             Connection  connection =
72:                 DriverManager.getConnection("jdbc:cloudscape:"+ args[0]);
73:             Statement   statement = connection.createStatement();
74:             ResultSet   resultSet;
75:             String      sql;
76:             // printout entire database
77:             statement = connection.createStatement();
78:             sql = "SELECT * FROM "+args[1];
79:             resultSet = statement.executeQuery(sql);
80:             ResultSetMetaData md = resultSet.getMetaData();
81:             OutputDTD(md);
82:             OutputData(md,resultSet);
83:             statement.close();
84:             resultSet.close();
85:             connection.close();
86:         } catch (Exception e) {};
87:     }
88: }

A close examination of this listing shows how outputting the data and DTD is achieved. Line 80 exercises the ResultSet.getMetaData() method and returns a ResultSetMetaData object, which is then used as input to outputting a DTD. Lines 36 and 41 use the meta-data to get the table name associated with the data as well as the column names and generate a DTD from the table.

Even without special database extensions, we can take advantage of JDBC to generate SQL.