| ## 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(int`column`)` The display size of the specified column. `public abstract String getColumnLabel(int`column`)` The label to be used with the column if it is stored in the database. `public abstract String getColumnName(int`column`)` Returns the name of the column, given the column index. `public abstract int getColumnType(int`column`)` The data type of the column, given the column index. `public abstract String getColumnTypeName(int`column`)` The specific data type name of the data type for the column, given a column index. `public abstract int getPrecision(int`column`)` The precision defined for the column, if any, given a column index. `public abstract int getScale(int`column`)` The scale defined for the column, if any, given a column index. `public abstract String getSchemaName(int`column`)` 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(int`column`)` The table name of the column, given a column index. `public abstract boolean isAutoIncrement(int`column`)` 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(int`column`)` Returns `true` if the column is case sensitive, whether case is important or not; otherwise, returns `false`. `public abstract boolean isCurrency(int`column`)` 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(int`column`)` Returns `true` if the column can be updated; otherwise, returns `false` `public abstract boolean isNullable(int`column`)` Returns `true` if the column value can be set to `null`; otherwise, returns `false`. `public abstract boolean isReadOnly (int`column`)` Returns `true` if the column is read only; otherwise, returns `false`. `public abstract boolean isSearchable (int`column`)` Returns `true` if the column can be used in a search clause; otherwise, returns `false`. `public abstract boolean isSigned (int`column`)` Returns `true` if the value of the column is a signed value (such as a signed integer); otherwise, returns `false`. `public abstract boolean isWritable (int`column`)` 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.
| ©Copyright, Macmillan Computer Publishing. All rights reserved.