TOC
BACK
FORWARD
HOME

Java 1.1 Unleashed

- 42 -
Databases and Java

by Shelley Powers

IN THIS CHAPTER

  • A History of Java and Data Access
  • Java and Object Databases
  • Java and Persistent Data Storage on the Host
  • Java and the JDBC


Java is an ideal language for persistent data storage. By using class inheritance and data encapsulation, a Java application developed to work with one type of data storage can be ported or extended to work with another. An example is an application currently working with an RDBMS (Relational Database Management System) that is extended to use RMI (Remote Method Invocation) to store data in a file.

A common misconception with Java is that database access can occur only with the JDBC (Java Database Connection) API. Although the JDBC provides a handy set of lower level classes to manage database connections and transactions, it is not a mandatory component. Several chapters in this book reference the mSQL JDBC driver (included on the CD-ROM that accompanies this book) written by George Reese. George wrote his driver to run with the MsqlJava classes created by Darryl Collins. MsqlJava uses the Socket classes included in the java.net package and the data input and output stream classes included in the java.io package. If you wanted to, you could as easily connect to an mSQL database using MsqlJava instead of the mSQL JDBC driver.

Additional efforts are underway to establish a standard Java class API to interface with object DBMSes (Database Management Systems). Companies are also creating their own optimized data access classes, such as Microsoft's DAO (Data Access Object) classes.

As an alternative to persistent storage of data in database management systems, Java can also be used to store data to files using object serialization.

A History of Java and Data Access

Version 1.0 of the Java Developer's Kit was released in January 1996. This kit contained all the classes necessary to implement database access--although developers had to code their own connections.

The JDK contained core classes grouped together into packages; two of these (java.net and java.io) provide the classes necessary to connect to a database and send and receive data. However, most of us do not want to create a socket and stream data to and from it to implement a simple transaction.

In February 1996, Sun released the JDBC as a separate package under the name java.sql. With the release of JDK 1.1, the JDBC package is not included as part of the core Java classes. Chapter 19, "The SQL Package," contains an overview of this package as well as a description of how the JDBC works and a list of the major classes and interfaces.

The JDBC API is based on the SQL CLI (Call Level Interface). This standard explains how to access databases with function calls embedded in applications. The SQL CLI also forms the basis for the ODBC standard from Microsoft. You can read more about this standard at http://www.jcc.com/sql_stnd.html.

The JDBC consists of a basic set of interfaces and a DriverManager class used as the basis for several drivers. Drivers provide the functionality specific to a database or database middleware layer. With the JDBC, Sun also released a basic ODBC driver known as the JDBC-ODBC bridge (created by Intersolv for Sun and meant to connect to ODBC-compliant databases).

Certain limitations became immediately apparent when using the JDBC. First, the JDBC-ODBC bridge used native method calls and a DLL (Dynamic Link Library) to connect to a database, prohibiting its use in applets accessible through the Internet. Because of applet security issues, the JDBC limits network connectivity to the same host from which the applet was downloaded. For use with an applet, databases must be installed on the same server as the HTTP server--a potential security risk. A workaround is to have the driver access a middleware layer, which in turn accesses a database located elsewhere; several companies have produced this type of driver.

Java and Object Databases

Information about accessing data using Java can be found at the JavaSoft site http://www.javasoft.com/jdbc/. In addition to listing drivers and information about the uses of JDBC, the site also references current efforts to bind the work of the ODMG (Object Database Management Group) with Java to create an API for accessing object-based databases.

The ODMG plans to release a specification that includes this Java-to-ODMG binding some time in 1997. The specification is expected to cover issues of persistence and to include a set of classes created in Java that will map directly to the ODMG objects.

For those who are impatient to try this technology, implementations of object-based persistent data stores using Java do currently exist. As this book goes to press, two companies (POET Software and Object Design, Inc.) have implementations of this technology; you can download them for evaluation from the Internet. Additionally, the JDK 1.1 has a set of object serialization classes included in the java.io package that facilitate storage of objects.

Object Serialization

Object serialization is covered in more detail in Chapter 31, "Persistence and Java Serialization," but a brief overview of how this technology works will clarify the next section covering specific products.

Object serialization simply means to store an object in such a way that the object can be retrieved--as an object--from the store. Objects can be manipulated in memory and can reference their members and any associations. With serialization, you can also store these same objects in a persistent format (such as a system file) for later retrieval.

At its simplest, object serialization is not very difficult. Listing 42.1 shows class ObjectServer with two methods: wrtobj() and rdobj(). The first method creates a Hashtable object and inserts three values into it. The newly created object is then serialized to a file named HASH. The second method, rdobj(), opens this new file and uses serialization to read the object back in. After the Hashtable object is read, one of its elements is accessed and the value of the element is written to standard output. The complete source code for this application can be found on the CD-ROM that comes with this book.

Listing 42.1. The ObjectServer class.

//
// Example using object serialization
//
import java.io.*;
import java.util.*;
// class
public class ObjectServer
{
public static void main(String arg[]) {
    try {
        wrtobj();
        rdobj();
    }
    catch(Exception e) {
        System.out.println(e.getMessage());
    }
}
// create object
// serialize to file
public static void wrtobj()
{
    // create the hashtable object
    Hashtable numbers = new Hashtable();
    numbers.put("one", new Integer(1));
    numbers.put("two", new Integer(2));
    numbers.put("three", new Integer(3));
    // serialize the object to a file
    try {
        FileOutputStream outFile = new FileOutputStream("hash");
        ObjectOutput  outStream  =  new  ObjectOutputStream(outFile);
        outStream.writeObject(numbers);
        outStream.flush();
    }
    catch(IOException e) {
        System.out.println(e.getMessage());
    }
 
}
// open serialization file
// read in object
// access object member
public static void rdobj()
{
    Hashtable numbers;
  
    // open the file and read in the serialized object
    try {
        FileInputStream inFile = new FileInputStream("hash");
        ObjectInputStream inStream = new ObjectInputStream(inFile);
        numbers = (Hashtable)inStream.readObject();
        }
        catch(IOException e) {
            System.out.println(e.getMessage());
        }
        catch(ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
    // access the hashtable value and print out
    Integer ivalue = (Integer)numbers.get("one");
    if (ivalue != null) {
        System.out.println("one = " + ivalue);
    }
}


}

Listing 42.2 shows the result of running this Java application using java.exe.

Listing 42.2. The output from the ObjectServer application.

E:\JDK\java\bin java objser
one = 1

One thing you probably noticed in Listing 42.1 is that object serialization involves reading and writing from a file. Because of this file access, applet security implemented with most browsers prohibits this code from being used in an untrusted applet. Later in this chapter, RMI (Remote Method Invocation) is used with the object serialization class to serialize the data to the Web server machine. You can read more about using digital authorization to create trusted applets and other security issues in Chapter 16, "The Security Package," Chapter 34, "Java Security," and Chapter 37, "Code Signing and JAR Security."

ObjectStore PSE for Java and the Java Software Development Kit

Object Design, Inc., manufactures the product ObjectStore PSE (Persistent Storage Engine) for Java; ObjectStore provides single-use persistent storage. The company provides a free downloadable version of this product at their Web site at http://www.odi.com. Their fully functional version of this product, ObjectStore PSE Pro for Java, can also be purchased from the Object Design, Inc., site. The company has created an API and a toolset to give you the ability to create persistent Java stores.

To use the product in your Java applet or application, you first initialize ObjectStore:

ObjectStore.initialize(null, null);

Type the following lines to create a database and a transaction:

Database db = Database.create (sname, Database.allRead | Database.allWrite);
Transaction tr = Transaction.begin(Transaction.update);

The objects are then stored using a createRoot() method call, which appends the object to the database. For example, the following example creates the Rose object in the Flower class with a constructor that takes two strings and an integer:

Flower rose = new Flower("rose", "perennial",1);
db.createRoot("Rose", rose);
tr.commit();

To read the object, a transaction is created, the object is retrieved from the database, and the object members (including any associated objects) are accessed directly:

Transaction tr = Transaction.begin(Transaction.readOnly);
Flower someflower = (Flower)db.getRoot("Rose");
String s = someflower.name;
tr.commit();

ObjectStore PSE does require that a postprocessor be used on the class file to make the class object capable of persistence.

NOTE: ObjectStore PSE for Java has been licensed by Netscape and will be included with the final released version of Netscape Communicator. Additionally, Microsoft has licensed the product and is bundling it with the Microsoft SDK for Java.

POET Software has created its own Java API to handle persistent storage; that product is called the POET Java Software Development Kit (SDK). The product can be downloaded for evaluation from the POET Web site at http://www.poet.com. Information about POET Software's Java SDK product is a bit sketchy at the time this book goes to press because the product is in beta release and most of the documentation is about POET Software's other products.

From the examples, it looks as if the POET product works in a manner similar to ObjectStore PSE except that you can work with remote as well as local persistent data stores. You would have to establish a connection, open a database, create an object, and store it.

Java and Persistent Data Storage on the Host

The previous examples of data storage using some form of object serialization all had one thing in common: The data was stored on the client machine rather than on the server.

In addition to the interim release of the JDBC, Sun has also released a Java API for RMI. This API provides a set of classes that allow methods to be invoked on the client from a Java server based on the Web server. With the concept of trusted applets, the methods are eventually accessible from a Java server located anywhere.

As are the JDBC classes, the RMI classes are part of the core Java class library Sun is releasing with JDK version 1.1. A more detailed description of RMI is given in Chapter 17, "The RMI Package."

To use this technique with object serialization, you must follow several steps. Returning to the earlier example, which serialized a Hashtable object, the sample class is extended to include RMI. First, the interface for the remote object is created as shown in Listing 42.3.

Listing 42.3. The objser object interface.

//
// interface for remote object
//
package my.objser;
public interface objser extends java.rmi.Remote {
    void wrtobj() throws java.rmi.RemoteException;
    int rdobj() throws java.rmi.RemoteException;


}

Note the same two methods created in the original class ObjectServer shown in Listing 42.1. After the interface is created and installed on the CLASSPATH, under its own package folder of my.objser, the object serialization server is created as shown in Listing 42.4.

Listing 42.4. The objimp object serialization server.

//
// Object Serialization Server
//
package my.objser;
import java.rmi.*;
import java.rmi.server.UnicastRemoteObject;
import java.io.*;
import java.util.*;
public class objimp
   extends UnicastRemoteObject
   implements objser
{   
    public objimp(String s) throws java.rmi.RemoteException {
    super();
    }

// create object
// serialize to file
public void wrtobj()
{
    System.out.println("creating hashtable");
    Hashtable numbers = new Hashtable();
    numbers.put("one", new Integer(1));
    numbers.put("two", new Integer(2));
    numbers.put("three", new Integer(3));
    try {
        FileOutputStream outFile = new FileOutputStream("hash");
        ObjectOutput  outStream  =  new  ObjectOutputStream(outFile);
        outStream.writeObject(numbers);
        outStream.flush();
    }
    catch(IOException e) {
        System.out.println(e.getMessage());
    }
}
// open serialization file
// read in object
// access object member
public int rdobj()
{
    Hashtable numbers;
    System.out.println("reading in from file");
    try {
        FileInputStream inFile = new FileInputStream("hash");
        ObjectInputStream inStream = new ObjectInputStream(inFile);
        numbers = (Hashtable)inStream.readObject();
        Integer ivalue = (Integer)numbers.get("one");
        if (ivalue != null) {
            int i = ivalue.intValue();
            return i;
        }
        else
            return 0;
        }
        catch(IOException e) {
            System.out.println(e.getMessage());
        }
        catch(ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
        return 0;
}
public static void main(String args[]) 
{
    // Create and install the security manager
    System.setSecurityManager(new RMISecurityManager());
    try {
        objimp obj = new objimp("HelloServer");
        Naming.rebind("HelloServer", obj);
        System.out.println("ObjectImpl created and bound
           in the registry to the name HelloServer");
    } 
    catch (Exception e) {
        System.out.println("ObjectImpl.main: an exception occurred:");
        e.printStackTrace();
        }
    }


}

The objimp class should be installed in the same package location created for the objser interface. The actual methods used with this class are very similar to those in the original class (shown in Listing 42.1) except that the methods are not defined as static. Interface methods cannot be created with the static modifier because they are overridden in the server. In the main() method for the class, the server is created and bound.

Finally, an applet is created to access the object from the server and to invoke the wrtobj() and rdobj() methods. The results are shown in Listing 42.5.

Listing 42.5. The ObjectApplet class that calls the server object.

//
// object serialization applet
//
package my.objser;
import java.awt.*;
import java.rmi.*;
public class ObjectApplet extends java.applet.Applet {
    String message;
    public void init() {
    try {
         objser obj = (objser)
         Naming.lookup("//" + getCodeBase().getHost() + "/HelloServer");
         message = "host is " + getCodeBase().getHost();
         obj.wrtobj();
         int i = obj.rdobj();
         message = message + " value is " + i;
     } catch (Exception e) {
         System.out.println("HelloApplet: an exception occurred:");
         e.printStackTrace();
         }
    }
   
    public void paint(Graphics g) {
       g.drawString(message, 25, 50);
    }


}

Next, an HTML file is created that contains a reference to the new applet, as shown in Listing 42.6.

Listing 42.6. Creating the Object.htm file with reference to the ObjectApplet class.

<HTML>
<title>Object Serialization</title>
<center> <h1>Object Serialization - remotely</h1> </center>
<p>
<applet codebase="./classes"
    code="my.objser.ObjectApplet"
    width=500 height=120>
</applet>
</HTML>

After the Java classes are created and compiled, stub methods must be created to provide access to the server methods. This is done using the rmic tool, provided in the JDK 1.1:

rmic -d c:\webshare\wwwroot\classes my.objser.objimp

The -d flag is used to direct the tool to place the generated classes in the same directory as the server class. This command generates two files for objimp.class: objimp_Skel.class and objimp_Stub.class.

The next step is to start the RMI registration server. Because this example is being created and run in Windows 95, the command to start the RMI server is as follows:

start rmiregistry

Finally, the server is started. The -D option followed by the java.rmi.server.codebase specifies the URL for the stub class for the RMI server:

java -Djava.rmi.server.codebase=http://localhost/classes/ my.objser.objimp &

The location of the RMI server classes must be on the CLASSPATH defined for the Web server. At the time this book goes to press, no browser had yet implemented JDK version 1.1, so the JDK-supplied applet viewer program is used to run the new applet:

appletviewer http://localhost/Object.htm &

This statement opens the HTML file and starts the ObjectApplet applet. Figure 42.1 shows the result of this operation, including the system prints from the server in the window directly under the applet.

Figure 42.1.
Running the ObjectApplet applet.

Object serialization and RMI are effective techniques for storing and accessing data on the server without requiring access to a DBMS.

Java and the JDBC

Probably the most common data access technique in use with Java is the JDBC. Within weeks after the release of JDBC 1.0 in 1996, several drivers were available--most downloadable from the Internet for evaluation or direct use.

As explained in Chapter 19, JDBC implements a two-layer architecture: one layer is implemented by the driver developer, and the other layer is implemented by the application or applet developer. As you also learned in Chapter 19, drivers can fall into four categories: JDBC-ODBC drivers, Native API drivers, Java-Net drivers, and native protocol. Because of limitations on access and the necessity of binaries on the client, the first two categories are better suited to intranet use only; the latter two categories can be used for intranet or Internet access.

With the final release of JDK 1.1 and its incorporation into the more popular browsers, database access with Java through applets will not only be simple to implement, but simple to maintain.

Chapter 43, "Getting Started with JDBC," provides several examples of how to use JDBC in Java-based applications. Chapter 44, "Building Database Applets with JDBC," provides examples of database access through an applet embedded in a browser. However, as an example of how to use the JDBC, the following sections create the beginnings of a database utility using the DatabaseMetaData interface. To orient you in this process, the next section reviews the ResultSetMetaData interface.

The ResultSetMetaData Interface

Chapter 19 covered most of the interfaces and classes in the JDBC but left the DatabaseMetaData and ResultSetMetaData interfaces for this chapter. The DatabaseMetaData interface provides meta data (information about the database, its procedures, tables, columns, indexes, transactions, and so on) and is covered in the following section. The ResultSetMetaData class provides information about the result set that is returned by a transaction. Its methods are listed next. Note that each method throws a SQLException exception.

  • public abstract string getCatalogName(int column)

    Gets the catalog name for the table of the specified column index.

  • public abstract int getColumnCount()

    The number of columns in the ResultSet.

  • public abstract int getColumnDisplaySize(intcolumn)

    The display size of the specified column.

  • public abstract String getColumnLabel(intcolumn)

    The label to be used with the column if it is stored in the database.

  • public abstract String getColumnName(intcolumn)

    Returns the name of the column, given the column index.

  • public abstract int getColumnType(intcolumn)

    The data type of the column, given the column index.

  • public abstract String getColumnTypeName(intcolumn)

    The specific data type name of the data type for the column, given a column index.

  • public abstract int getPrecision(intcolumn)

    The precision defined for the column, if any, given a column index.

  • public abstract int getScale(intcolumn)

    The scale defined for the column, if any, given a column index.

  • public abstract String getSchemaName(intcolumn)

    The schema defined for the column, if supported in the database and if any exists for the column, given a column index.

  • public abstract String getTableName(intcolumn)

    The table name of the column, given a column index.

  • public abstract boolean isAutoIncrement(intcolumn)

    Returns true if the column value is automatically incremented when a new row is created; otherwise, returns false. Used for numeric identifiers.

  • public abstract boolean isCaseSensitive(intcolumn)

    Returns true if the column is case sensitive, whether case is important or not; otherwise, returns false.

  • public abstract boolean isCurrency(intcolumn)

    Returns true if the column is formatted for currency values; returns false if the column is not formatted or if currency formatting is not applicable.

  • public abstract boolean isDefinatelyWritable(intcolumn)

    Returns true if the column can be updated; otherwise, returns false

  • public abstract boolean isNullable(intcolumn)

    Returns true if the column value can be set to null; otherwise, returns false.

  • public abstract boolean isReadOnly (intcolumn)

    Returns true if the column is read only; otherwise, returns false.

  • public abstract boolean isSearchable (intcolumn)

    Returns true if the column can be used in a search clause; otherwise, returns false.

  • public abstract boolean isSigned (intcolumn)

    Returns true if the value of the column is a signed value (such as a signed integer); otherwise, returns false.

  • public abstract boolean isWritable (intcolumn)

    Returns true if the value of the column is writeable; otherwise, returns false.

A ResultSetMetaData object is created as a result of a method call to a ResultSet object:

ResultSetMetaData rsmd = rs.getMetaData();

Once the object is created, you can use it to find information about the result set, such as a column's name:

String sname;
String sallnames = "columns are ";
int icount = rsmd.getColumnCount();
for (i = 0; i < icount; i++) {
    sname = rsmd.getColumnName(i);
    sallnames+=sallnames + sname + ",";


}
TIP: When would you use ResultSetMetaData? If you work with dynamic SQL, you will need this interface. In addition, if you issue generic SQL statements such as Select * from sometable, you will find that ResultSetMetaData provides useful information.

The DatabaseMetaData Interface

The DatabaseMetaData interface provides information about the database itself, rather than the data it contains. JDBC drivers may or may not implement this class; if the class is not implemented, any of the methods for the class throw a SQLException error.

As a handy way to explain the usefulness of the DatabaseMetaData interface and to provide a good first step for beginning to work with data access in Java, the Java dbutil application was created to work with the JDBC-ODBC bridge. This tool takes five parameters: the ODBC database name as defined with the ODBC Administrator, the user name, the password (sorry, the password is not encrypted), the information category, and the information category option. Among the options the existing tool can provide is information about the database, its procedures, tables, columns, and transactions.

dbutil is a Java application; the first method of the application to examine is the main() procedure:

public static void main(String arg[]) {
   try {
        //connect to ODBC database
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:" + arg[0];
        // connect
        Connection con = DriverManager.getConnection(url,arg[1],arg[2]);
        // get databasemetadata object
        DatabaseMetaData dmd = con.getMetaData();
        // get option 1 and 2
        String soption = arg[3];
        String soption2 = arg[4];
        //check argument to see which load to run
        if (soption.compareTo("procedures") == 0) {
               // output procedure information
               output_procedure_info(dmd,soption2);
               }
        else if (soption.compareTo("database") == 0) {
               // output database information
               output_database_info(dmd,soption2);
               }
        else if (soption.compareTo("tables") == 0) {
               // output table information
               output_table_info(dmd,soption2);
               }
        else if (soption.compareTo("columns") == 0) {
               // output column information
               output_column_info(dmd,soption2);
               }
        else {
               // output transaction information
               output_transaction_info(dmd,soption2);
        } 
        //close connection
        con.close();
        }
     catch(Exception e) {
        System.out.println(e.getMessage());
        }


}

The main() method processes the arguments passed on the command line, using the first to connect to the specific database, the second and third arguments as the user name and password, and the last two arguments to see which options to run. After the connection is made, a DatabaseMetaData object is created.

The first information method is output_procedure_info():

// output_procedure_info
//
// options are:
//
//      are procedures callable
//      do stored procedures support escape sequences
//
public static void output_procedure_info(DatabaseMetaData dmd,
                                         String soption)
{
    boolean b;
 
    try {
        // are all procedures callable
        if (soption.compareTo("callable") == 0) {
           b = dmd.allProceduresAreCallable();
           if (b) 
              System.out.println("You can call procedures");
           else
              System.out.println("You cannot call procedures");
           }
        // do stored procedures support escape syntax
        else {
           b = dmd.supportsStoredProcedures();
           if (b)
              System.out.println("Stored Procedures support escape syntax");
           else
              System.out.println("Stored Procedures do not support escape syntax");
           }
        }
    catch(SQLException e) {
        System.out.println(e.getMessage());
    }


}

This method calls the specific method associated with the DatabaseMetaData object to find the information requested by the user. With this method, all the results from the object calls are of type boolean. The next method, output_table_info(), uses a ResultSet object to process the return from a method call:

// output_table_info
//
// options are:
//
//   are tables selectable
//
//   for each schema get the tables
//
public static void output_table_info(DatabaseMetaData dmd,
                                     String soption)
{
    boolean b;
    ResultSet rs;
    ResultSet rs2;
    String tempString = "";
    String resultString = "";
    String sschema;
    try {
        // are tables selectable
        if (soption.compareTo("selectable") == 0) {
           b = dmd.allTablesAreSelectable();
           if (b) 
              System.out.println("You can select from all tables");
           else
              System.out.println("You cannot select from all tables");
           }
        // for each schema get table names and types
        else if (soption.compareTo("tables") == 0) {
           rs = dmd.getSchemas();
           while(rs.next()) {
             sschema = rs.getString(1);
             System.out.println("Searching Schema " + sschema + "\n");
             rs2 = dmd.getTables(null,sschema, "%",null);
             while(rs2.next()) {
                tempString = rs2.getString(3) + " ";
                tempString+= rs2.getString(4);
                resultString+= tempString + "\n";
                }
             }
           System.out.println("Tables are \n" + resultString);
           }
    }
    catch(SQLException e) {
        System.out.println(e.getMessage());
    }


}

The entire class is presented in Listing 42.7 and can be found on the CD-ROM that accompanies this book.

Listing 42.7. The dbutil database utility class.

/*
 * Database Utility
 *
 * Author:  Shelley Powers
 *
 */
import java.sql.*;
import sun.jdbc.odbc.*;
import java.io.*;
public class dbutil
{
public static void main(String arg[]) {
     try {
        //connect to ODBC database
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:" + arg[0];
        // connect
        Connection con = DriverManager.getConnection(url,arg[1],arg[2]);
        // get databasemetadata object
        DatabaseMetaData dmd = con.getMetaData();
        // get option 1 and 2
        String soption = arg[3];
        String soption2 = arg[4];
        //check argument to see which load to run
        if (soption.compareTo("procedures") == 0) {
               // output procedure information
               output_procedure_info(dmd,soption2);
               }
        else if (soption.compareTo("database") == 0) {
               // output database information
               output_database_info(dmd,soption2);
               }
        else if (soption.compareTo("tables") == 0) {
               // output table information
               output_table_info(dmd,soption2);
               }
        else if (soption.compareTo("columns") == 0) {
               // output column information
               output_column_info(dmd,soption2);
               }
        else {
               // output transaction information
               output_transaction_info(dmd,soption2);
        } 
        //close connection
        con.close();
        }
     catch(Exception e) {
           System.out.println(e.getMessage());
        }
}
// output_procedure_info
//
// options are:
//
//      are procedures callable
//      do stored procedures support escape sequences
//
public static void output_procedure_info(DatabaseMetaData dmd,
                                         String soption)
{
    boolean b;
    try {
        // are all procedures callable
        if (soption.compareTo("callable") == 0) {
           b = dmd.allProceduresAreCallable();
           if (b) 
              System.out.println("You can call procedures");
           else
              System.out.println("You cannot call procedures");
           }
        // do stored procedures support escape syntax
        else {
           b = dmd.supportsStoredProcedures();
           if (b)
              System.out.println("Stored Procedures support escape syntax");
           else
              System.out.println("Stored Procedures do not support escape syntax");
           }
        }
    catch(SQLException e) {
        System.out.println(e.getMessage());
    }
}
// output_database_info
//
// options are:
//
//      get database URL
//      get user name
//      get database schemsa
//      get database catalogs
//      is database readonly
//
public static void output_database_info(DatabaseMetaData dmd,
                                        String soption)
{
    boolean b;
    ResultSet rs;
    String s;
    String tempString;
    String stringResult = " ";
    try {
        // get url
        if (soption.compareTo("url") == 0) {
           s = dmd.getURL();
           System.out.println("URL of database is " + s);
           }
        // get user name
        else if (soption.compareTo("user") == 0) {
           s = dmd.getUserName();
           System.out.println("User name is " + s);
           }
        // get schemas
        else if (soption.compareTo("schema") == 0) {
           rs = dmd.getSchemas();
           while(rs.next()) {
                tempString = rs.getString(1) + "\n";
                stringResult+=tempString;
                }
           System.out.println("Database Schemas are: \n" + stringResult);
           }
        // is database readonly
        else if (soption.compareTo("readonly") == 0) {
           b = dmd.isReadOnly();
           if (b)
                System.out.println("Database is readonly");
           else
                System.out.println("Database is not readonly");
           }
        // get database catalogs
        else {
           rs = dmd.getCatalogs();
           while (rs.next()) {
        
              // get catalog
              tempString = rs.getString(1) + "\n";
              stringResult+=tempString;
              }
           System.out.println("Catalogs are: \n" + stringResult);
           }
    }
    catch(SQLException e) {
        System.out.println(e.getMessage());
    }
}
// output_transaction_info
//
// options are:
//
//      does database support dm transactions
//      does a data definition transaction force a commit
//
public static void output_transaction_info(DatabaseMetaData dmd,
                                           String soption)
{
    boolean b;
    try {
        // does database support dm transactions
        if (soption.compareTo("dm") == 0) {
           b = dmd.supportsDataManipulationTransactionsOnly();
           if (b) 
              System.out.println("You can use data manipulation statements");
           else
              System.out.println("You cannot use data manipulation statements");
           }
        // do ddl statements force a commit
        else {
           b = dmd.dataDefinitionCausesTransactionCommit();
           if (b)
              System.out.println("A data definition statement will force a commit");
           else
              System.out.println("A data definition statement will not force a commit");
           }
    }
    catch(SQLException e) {
        System.out.println(e.getMessage());
    }
}
// output_table_info
//
// options are:
//
//   are tables selectable
//
//   for each schema get the tables
//
public static void output_table_info(DatabaseMetaData dmd,
                                     String soption)
{
    boolean b;
    ResultSet rs;
    ResultSet rs2;
    String tempString = "";
    String resultString = "";
    String sschema;
    try {
        // are tables selectable
        if (soption.compareTo("selectable") == 0) {
           b = dmd.allTablesAreSelectable();
           if (b) 
              System.out.println("You can select from all tables");
           else
              System.out.println("You cannot select from all tables");
           }
        // for each schema get table names and types
        else if (soption.compareTo("tables") == 0) {
           rs = dmd.getSchemas();
           while(rs.next()) {
             sschema = rs.getString(1);
             System.out.println("Searching Schema " + sschema + "\n");
             rs2 = dmd.getTables(null,sschema, "%",null);
             while(rs2.next()) {
                tempString = rs2.getString(3) + " ";
                tempString+= rs2.getString(4);
                resultString+= tempString + "\n";
                }
             }
           System.out.println("Tables are \n" + resultString);
           }
    }
    catch(SQLException e) {
        System.out.println(e.getMessage());
    }
}
//
// output_column_info
//
// The only option at this time for
// columns is a list of columns for each table and
// each schema
//
public static void output_column_info(DatabaseMetaData dmd,
                                      String soption)
{
    ResultSet rs;
    ResultSet rs2;
    ResultSet rs3;
    String tempString;
    String resultString = "";
    String sschema;
    String stable;
   try {
     if (soption.compareTo("columns") == 0) {
       // for each schema
       rs = dmd.getSchemas();
       while (rs.next()) {
          sschema = rs.getString(1);
          System.out.println("Searching through schema " + sschema + "\n");
          // for each table
          rs2 = dmd.getTables(null,sschema,"%",null);
          while (rs2.next()) {
             stable = rs2.getString(3);
             System.out.println("Searching through table " + stable + "\n");
             // get and print out columns
             rs3 = dmd.getColumns(null, sschema,stable,"%");
             while (rs3.next()) {
                tempString = rs3.getString(3) + " ";
                tempString+= rs3.getString(4) + " ";
                tempString+= rs3.getString(6) + "\n";
                resultString+= tempString;
                }
             System.out.println("Columns are : \n" + resultString + "\n");
             }
          }
       }
    }
    catch(SQLException e) {
        System.out.println(e.getMessage());
    }
}


}

Running the dbutil tool is quite simple. I ran the tool against the Northwind database that comes with Access 97:

java dbutil nwind admin dba procedures callable

Provided that you have the data source (nwind) established and suitably named, this example returns whether the stored procedures in the database are callable. The results are shown here:

You can call procedures

Then I ran the same query against a Sybase SQL Anywhere database I had:

java dbutil nwind admin dba procedures callable

In this case, the result from the tool was as follows:

You can call procedures

I ran the tables category and the tables option to list the tables in the SQL Anywhere database; the first part of the result (the result is fairly long) is shown in Listing 42.8. Note that the output was directed to a file:

java dbutil Zoo dba sql procedures callable

Listing 42.8. The tables.out file: The first several lines of the results of running dbutil against a Sybase SQL Anywhere database.

Searching Schema DBA

Searching Schema DBO

Searching Schema SYS

Tables are 
app_user TABLE
bogus TABLE
company TABLE
counter TABLE
datawindow_table TABLE
department TABLE
dept_position TABLE
employee TABLE
guest_type TABLE
guests TABLE
obj_dw_table TABLE
pbcatcol TABLE
pbcatedt TABLE
pbcatftm TABLE
pbcattbl TABLE
. . .

Running this same command against Northwind results in the following output:

[Microsoft][ODBC Microsoft Access 97 Driver]Driver not capable

Microsoft Access 97 does not allow for this type of call; an exception occurred.

The JDK 1.1 documentation contains a list of the methods and variables for use with the DatabaseMetaData interface, which are too numerous to list here. Use the JDK documentation to modify the tool to suit your needs--or to have a little fun with some very simple coding that provides very useful results.

Summary

This chapter provides an overview and some examples of the different techniques you can use with Java for data storage. Among the techniques examined were these:

  • Accessing databases using java.io and java.net

  • Using object serialization

  • Using object serialization with ODMS

  • Using object serialization and RMI

  • Using the JDBC

No single technique can solve every problem. If the business is creating a tracking system and wants to store data locally and upload once a day, using object serialization--with or without RMI--is the best solution. If the company wants to provide direct access to a commercial database, such as Oracle or Sybase, the JDBC is the best solution. If the company wants to store information about an object, object serialization--with or without access to an ODMS--is probably the best solution.

The point is, you should examine your requirements, security issues, the length of time you have to maintain the store, DBMS access, and other information before making a decision about which technique to use. You may find that a combination of more than one provides the best overall solution.

TOCBACKFORWARDHOME


©Copyright, Macmillan Computer Publishing. All rights reserved.