TOC
BACK
FORWARD
HOME

Java 1.1 Unleashed

- 44 -
Building Database Applets with JDBC

by Shelley Powers

IN THIS CHAPTER

  • Database Applets and Security Restrictions
  • Converting Java Database Applications to Java Applets
  • A Simple Update Applet
  • A Simple Query Applet


The Java Database Connectivity (JDBC) API was demonstrated in Chapter 43, "Getting Started with JDBC," but none of those examples showed how to use the JDBC to build a database-accessing applet. This chapter explains how to use the JDBC to build applets; this chapter also covers the restrictions on applets and provides examples of some typical uses.

This chapter also demonstrates probably the number one reason for using Java development tools such as Symantec Café or Microsoft Visual J++: it's easy to send information to and from the database using the JDBC but it's not so easy using the AWT (Abstract Window Toolkit) classes. The development tools automate much of the AWT class use and make it much easier to create database forms as shown in the examples.

Database Applets and Security Restrictions

Before the release of the JDK version 1.1, accessing databases from an applet that was downloaded from an HTTP server was restricted in two ways: The applet could not access native methods (that is, it could not access nonJava drivers on the client), and the applet could not access a database that was not located on the same network server as the Web server.

Not being able to access native methods--including database drivers written in C or C++--effectively eliminated the use of the JDBC-ODBC bridge for an applet. The restriction on the database location also seriously hampered most professional uses of this technology. Many companies do not have their database and Web server on the same computer--or even in the same physical location.

USING THE JDBC-ODBC BRIDGE WITH VISUAL J++
So, you created a Java applet or application using Visual J++ and the JDBC-ODBC bridge. When you ran it, however, you got a java.lang.UnsatisfiedLinkError. Before you start a long involved process of debugging, note that the first release of the JDBC-ODBC bridge does not work with the first release of Visual J++. Visual J++ implements native methods in a different manner than the JDBC-ODBC bridge and the two techniques clash.

How to work around the problem? Compile the application with Visual J++, but run it with the java.exe application that comes with JDK 1.1. Alternatively, compile and run the applet or application with the JDK, just make sure that javac cannot "see" the Microsoft classes, or you will get a compile time error.

Another limitation for applets (rather than a restriction) is that the original JDBC classes were not included as part of the core Java classes. Therefore, they were not included by the major browsers that allow Java applets. Most browsers prohibit downloading classes from a directory that begins with java (to prevent hackers from creating a bad extension of one of the standard Java classes and then overwriting the class in the client when the class is downloaded). Unfortunately, this "security feature" also prevents the downloading of the JDBC classes, turning the original limitation into yet another restriction: the client machine must already have the JDBC classes if any JDBC applet is to work. A workaround that some of the JDBC driver developers created was to copy the JDBC classes to another subdirectory and code their own classes accordingly.

Of course, with the release of the JDK 1.1, several of these restrictions have been either eased or eliminated.

The JDBC classes are now a part of the core Java classes, which means that anyone using a JDK 1.1-compliant browser can have access to the JDBC classes with no additional downloading.

With the new security features in JDK 1.1, an applet can be digitally signed as described in Chapter 16, "The Security Package," and Chapter 34, "Java Security." When an applet is digitally signed, it becomes known as a trusted applet, which means that it is no longer bound (or as tightly bound) by the traditional applet securities. A trusted applet can, for example, access native methods or access a database located somewhere other than on the Web server.

Loading the Database Applet

If restrictions are not an issue with an applet, what happens when the applet class is downloaded? When an applet that uses the JDBC is downloaded, nothing unusual happens until it tries to connect to a database. The first bit of code to accomplish that feat is the code to load the JDBC driver:

Class.forName("imaginary.sql.iMsqlDriver");

At this point, the browser or application tries to load the driver classes from the local CLASSPATH. If the driver classes are not found in the designated CLASSPATH, the browser or application attempts to access the driver classes using the ClassLoader(). When ClassLoader() is defined for the implemented object, it accesses the classes it requires from the network as an array of bytes, which are then translated to the necessary classes on the client side.

For example, if an applet is downloaded from a site that uses the mSQL-JDBC driver classes, and the driver is not located on the client, the HTTP log file for the server might look something like this:

usr1-dialup22.seattle.mci.net - - [02/Feb/1997:16:56:23 -0500] "GET /book/sample
5.html HTTP/1.0" 200 212
usr1-dialup22.seattle.mci.net - - [02/Feb/1997:16:56:27 -0500] "GET /book/sample
5.class HTTP/1.0" 200 5725
usr1-dialup22.seattle.mci.net - - [02/Feb/1997:16:56:35 -0500] "GET /book/imagin
ary/sql/iMsqlDriver.class HTTP/1.0" 200 2350
ip3.pdx1.pacifier.com - - [02/Feb/1997:16:56:37 -0500] "GET /pbug/member/company
/../index.htm HTTP/1.0" 200 2699
usr1-dialup22.seattle.mci.net - - [02/Feb/1997:16:56:38 -0500] "GET /book/imagin
ary/sql/iMsqlConnection.class HTTP/1.0" 200 3661
usr1-dialup22.seattle.mci.net - - [02/Feb/1997:16:56:47 -0500] "GET /book/imagin
ary/sql/msql/MsqlException.class HTTP/1.0" 200 350
usr1-dialup22.seattle.mci.net - - [02/Feb/1997:16:56:47 -0500] "GET /book/imagin
ary/sql/msql/Msql.class HTTP/1.0" 200 350
usr1-dialup22.seattle.mci.net - - [02/Feb/1997:16:56:47 -0500] "GET /book/imagin
ary/sql/iMsqlResultSet.class HTTP/1.0" 200 350

As you can seen, the first access is to the page containing the applet; the second access is to the applet itself; all the accesses that follow are the results of the ClassLoader() pulling in the driver classes.

The advantage of this approach is that the person who accesses the applet does not have to take any heroic efforts to run the applet. The disadvantage, of course, is the additional time it takes to download the classes before the applet can run properly. However, once the classes are downloaded, most browsers cache the classes locally, eliminating the need to download the classes again on the next access.

Converting Java Database Applications to Java Applets

As you have learned, using the JDBC classes from an applet is no different than using them from a Java application--as long as restrictions are not an issue. For example, to convert some of the examples from Chapter 43, "Getting Started with JDBC," all you have to do is add the applet code to the top of the file (replacing the main() method) and provide an area in which you can access and display information.

The first example in Chapter 43 displayed information from a purchase order table stored in a Sybase SQL Anywhere table. The first step in converting this program is to replace the main() method and to redefine the class to extend the Applet class. Here is the original code:

import java.sql.*;
import sun.jdbc.odbc.*;

public class sample1
{

public static void main(String arg[]) {

Here is the code after it has been converted to an applet:

import java.applet.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class sample1 extends Applet
       implements ActionListener


{

Note that the import sections in these examples are different: The code for the applet has to include the Applet class and the AWT class in addition to the JDBC and driver classes.

The init() method is essential for the applet. This method is where the components necessary for accessing or displaying data are created and laid out (see Listing 44.1; the code is also on the CD-ROM that accompanies this book).

Listing 44.1. The init() method for a database-accessing applet.

// set background
    // create layout
    // and place applet components
public void init()
{
        // set background color and font
        setBackground(new Color(255,255,255));
        setFont(new Font("Helvetica", Font.PLAIN, 12));

        // create layout using GridBagLayout
        GridBagLayout gridbag = new GridBagLayout();
        setLayout(gridbag);

        GridBagConstraints Constraints = new GridBagConstraints();

        // define constraints for objects to be added
        Constraints.weightx=1.0;
        Constraints.weighty=0.0;
        Constraints.insets = new Insets(4, 2, 4, 2);

        Constraints.anchor=GridBagConstraints.CENTER;
        Constraints.fill = GridBagConstraints.NONE;
        Constraints.gridwidth = GridBagConstraints.REMAINDER;

        // create search button and add
        searchButton = new Button("Get all POs");

        gridbag.setConstraints(searchButton, Constraints);
        add(searchButton);
        searchButton.addActionListener(this);

        // create results text area
        textResult = new TextArea(7,80);

        // add with constraints to layout
        // change Y constraint to take up remainder of
        // height allocated to applet
        Constraints.weighty=1.0;

        gridbag.setConstraints(textResult,Constraints);
        add(textResult);


}

Now you can see why Java tools are becoming so popular. Connecting to the database and accessing the data are fairly trivial compared to creating the display. (And this example is a fairly simple one!)

After the display is created, you code an event handler that captures the click of the searchButton button created in the init() method. The handler calls a method that finally connects to the database:

// handleEvent
    //
    // Overriding handleEvent allows us to
    // capture the search button push
    //
    // call super.handleEvent to allow other
    // events to be processed correctly

public void actionPerformed(ActionEvent event) {

      if (event.getSource() == searchButton) {
          processRequest();
      }


  }

The handler must invoke the ancestor code for all events it does not process directly. The processRequest() method shown in the event-handler code connects to the database, accesses the data, and displays it as shown in Listing 44.2 (the code is also located on the CD-ROM that accompanies this book). The output is literally a direct copy of the original code, except that the query output is displayed in a field rather than to system output.

Listing 44.2. The sample1.class code performing a simple query.

// processRequest
    //
    // This method will load the database driver,
    // connect to the database, create the retrieval statement
    // and output the results.
    private synchronized void processRequest()
    {
     String stringResult = "";
     String result;

     try {

        //connect to ODBC database
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:Zoo";

        // connect
        Connection con = DriverManager.getConnection(url,"dba", "sql");

        // create Statement object
        Statement stmt = con.createStatement();

        String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
        + " from dba.purchase_order";

        // run query
        ResultSet rs = stmt.executeQuery(sqlselect);

        // process results - could have processed in a loop
        while(rs.next()) {
                result = "";
                result = rs.getString(1) + "\t";
                result+= rs.getString(3) + "\t";
                result+= rs.getDate(2) + "\t";
                result+= rs.getString(4) + "\n";
                stringResult+=result;
                }

        textResult.setText(stringResult);

        // close result set and statement
        rs.close();
        stmt.close();
        }
        // catch all exceptions and print out exception message
        catch (Exception e) {
             textResult.setText(e.getMessage());
        }


    }

What's the main difference between this method and the same processing approach in the original code in Listing 44.1? Aside from using different result set access methods, the difference is in one line:

textResult.setText(stringResult);

compared to:

System.out.println("Values are: " + result);

If you run the applet using the applet viewer utility that comes with the JDK, your results will look similar to those shown in Figure 44.1. Note that to use this utility, you must embed the applet into an HTML file and run that file. The samples in this chapter all come with associated HTML files.

Figure 44.1.
A simple database query applet.

To convert a Java database application to a Java applet, follow these steps:

1. Add in the import statements for the Applet and AWT classes. Applets require the Applet class; most applets use some form of interface component, which the AWT classes provide.

2. Replace the main() method with an implementation of the Applet class. Note, however, that you can keep the main() function. The applet should work correctly.

3. (Optional) Add a constructor, a destructor, an optional getAppletInfo() method, start(), stop(), and paint() applet methods, and any other methods you think apply. The default behavior for these Applet methods is handled by the Applet class if you do not override the methods.

4. Add an init() method and create your data access and display components in this method.

5. Make sure that the code accesses data from components rather than from the command line or system input, and that it displays data to components rather than to the system output.

You can find more information about creating applets in Chapter 8, "Introduction to Applet Programming"; you can find more information about using the interface components of the AWT in Chapter 22, "Creating User Interface Components."

A Simple Update Applet

If you want your applet to make a modification to the data in the database, you must provide components for the user to use to input data.

In this section, you create a sample applet that accesses data from the user and updates the retail_item table from the sample Sybase SQL Anywhere database, Zoo, included on the CD-ROM that accompanies this book.

The retail_item table contains the following fields:

Field Description
item_nbr Integer field containing item identifier
item_desc Text field containing item description
qty_per_pkg Integer field containing the number of items contained in each package sold
wholesale_cost Numeric field containing the wholesale cost of the item
retail_cost Numeric field containing the retail cost of the item
company_id Integer identifier of the company that makes the item
color Optional color of the item
size Optional size of the item
company_item_identifier Not used in this example


The fields used to reference the retail_item columns just mentioned must be available to all methods for the class. These fields are defined as private members of the class:

private Button updateButton;
private TextField itemNbr;
private TextField itemDesc;
private TextField itemQty;
private TextField itemWholesale;
private TextField itemRetail;
private TextField itemCompany;
private TextField itemColor;
private TextField itemSize;
private TextField textResult;

The fields are defined as private only because they are not available to any other class; however, they could just as easily have been defined as public.

Next, the components must be created and laid out. The init() method of the sample class looks similar to Listing 44.3 if we limit our AWT layout classes to the BorderLayout and FlowLayout classes. This code can also be found on the accompanying CD-ROM.

Listing 44.3. The init() method of the sample applet.

// set background
    // create layout
    // and place applet components
public void init()
{
        // set background color and font
        setBackground(new Color(255,255,255));
        setFont(new Font("Helvetica", Font.PLAIN, 12));

        // create borderlayout
        setLayout(new BorderLayout(5,5));

        // create upperpanel
        Panel upperpanel = new Panel();
        upperpanel.setLayout(new BorderLayout());

        // create top panel of upper panel
        Panel uppertoppanel = new Panel();
        uppertoppanel.setLayout(new FlowLayout(1,5,5));

        uppertoppanel.add(new Label("Item Nbr:"));
        itemNbr = new TextField("",5);
        uppertoppanel.add(itemNbr);

        uppertoppanel.add(new Label("Item Desc:"));
        itemDesc = new TextField("", 25);
        uppertoppanel.add(itemDesc);

        upperpanel.add("North", uppertoppanel);

        // create bottom panel of upper panel
        Panel upperbotpanel = new Panel();
        upperbotpanel.setLayout(new FlowLayout(1,5,5));

        upperbotpanel.add(new Label("Item Qty:"));
        itemQty = new TextField("",3);
        upperbotpanel.add(itemQty);

        upperbotpanel.add(new Label("Item Wholesale Cost:"));
        itemWholesale = new TextField("", 7);
        upperbotpanel.add(itemWholesale);

        upperpanel.add("South", upperbotpanel);

        // add to main borderlayout
        add("North", upperpanel);

        // create middlepanel
        Panel middlepanel = new Panel();
        middlepanel.setLayout(new BorderLayout());

        // create top panel of middle panel
        Panel middletoppanel = new Panel();
        middletoppanel.setLayout(new FlowLayout(1,5,5));

        middletoppanel.add(new Label("Item Retail:"));
        itemRetail = new TextField("",7);
        middletoppanel.add(itemRetail);

        middletoppanel.add(new Label("Item Company:"));
        itemCompany = new TextField("", 3);
        middletoppanel.add(itemCompany);

        middlepanel.add("North", middletoppanel);

        // create bottom panel of middle panel
        Panel middlebotpanel = new Panel();
        middlebotpanel.setLayout(new FlowLayout(1,5,5));

        middlebotpanel.add(new Label("Item Color:"));
        itemColor = new TextField("",8);
        middlebotpanel.add(itemColor);

        middlebotpanel.add(new Label("Item Size:"));
        itemSize = new TextField("", 8);
        middlebotpanel.add(itemSize);

        middlepanel.add("South", middlebotpanel);

        add("Center", middlepanel);

        // add bottom layer
        Panel botpanel = new Panel();
        botpanel.setLayout(new FlowLayout(1,5,5));
        updateButton = new Button("   Update   ");
        updateButton.addActionListener(this);
        
        botpanel.add(updateButton);

        textResult = new TextField("", 25);
        botpanel.add(textResult);

        add("South", botpanel);


}

The BorderLayout container class allows you to insert components using the geographical place names "North", "South", "East", "West", and "Center". Alternatively, the FlowLayout container class allows you to add components that are placed one after another in a linear manner. Both layout managers are relatively simple to use. In Listing 44.3, notice the use of the panel object. The panel object allows you to implement a different layout container, which is then inserted into another layout container, and so on. This capability allows developers to use a variety of layouts to display components.

Once the layout is created, you create the event handler for the update button:

// handleEvent
    //
    // Overriding handleEvent allows us to
    // capture the search button push
    //
    // call super.handleEvent to allow other
    // events to be processed correctly

public void actionPerformed(ActionEvent event) {
      if (event.getSource() == updateButton) {
           processRequest();
      }
  }

Finally, the code to process the database update is created, as shown in Listing 44.4. This file can also be found on the accompanying CD-ROM.

Listing 44.4. The sample2.class code to update a database table.

// processRequest
    //
    // This method will load the database driver,
    // connect to the database, create the retrieval statement
    // and output the results.
    private synchronized void processRequest()
    {
        String stringResult = "";
        String result;

        String stringNbr,
               stringDesc,
               stringQty,
               stringWholesale,
               stringRetail,
               stringCompany,
               stringColor,
               stringSize;

        try {

        //connect to ODBC database
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:Zoo";

        // connect
        Connection con = DriverManager.getConnection(url,"dba", "sql");

        textResult.setText("Connected...");

        // create Statement object
        Statement stmt = con.createStatement();

        // get insert values
        stringNbr = itemNbr.getText();
        stringDesc = itemDesc.getText();
        stringQty = itemQty.getText();
        stringWholesale = itemWholesale.getText();
        stringRetail = itemRetail.getText();
        stringCompany = itemCompany.getText();
        stringColor = itemColor.getText();
        stringSize = itemSize.getText();

        String updatestring = "INSERT INTO RETAIL_ITEM(item_nbr,
        item_desc, qty_per_pkg, "
                + "wholesale_cost, retail_cost, company_id, color, size) "
                + "VALUES(" + stringNbr + ",'" + stringDesc +
                "'," + stringQty + "," + stringWholesale + "," +
                stringRetail + "," + stringCompany + ",'" +
                stringColor + "','" + stringSize + "')";

        // run query
        textResult.setText("Working...");
        int ct = stmt.executeUpdate(updatestring);

        textResult.setText("completed");

        // close statement
        stmt.close();
        }
        // catch all exceptions and print out exception message
        catch (Exception e) {
             textResult.setText(e.getMessage());
        }


}

As you can see from this example, the values are accessed from the AWT components and then used to create an update string. Once the update string is created and executed, the results of the update are output to the results field. Notice that the code also outputs text strings that inform the user of the progress of the update. This notification is essential, especially for longer transactions. If you don't have such a notification scheme, your user will wonder whether an error has occurred.

The example just shown is simpler than you would find in a real-world business application. The example does no checking to make sure that mandatory fields are filled in and does no checking to see whether the user entered appropriate values. Also, a production-quality applet would probably use a drop-down list for the company identifier field and would pull in values from the database and load them to the list when the applet is initialized.

If you run this sample applet using the applet viewer, you should see something similar to Figure 44.2 when the applet loads. In the figure, behind the applet is the DOS window containing the class activity of the applet. If you type some values, the applet looks similar to Figure 44.3.

Figure 44.2.
The sample2 applet after being loaded.

After you enter the values and click the Update button, the applet runs. In Figure 44.4, note that the result field shows the value completed. This notification means that the record was successfully entered into the database.

Now that you have added data to the database, you may find it handy to review the data for accuracy. The next section creates a simple query-based applet to do just that.

Figure 44.3.
The sample2 applet after data has been typed into AWT components.

Figure 44.4.
The sample2 applet after the record insert is completed.

A Simple Query Applet

Once you have successfully created an applet that enters data into a database, you will want to create an applet that enables one or more people to view the data.

Creating an applet to display data is no different than creating one to update a database. AWT components that must be accessed by both the init() method and the database-processing method are usually defined as members of the Applet class. In the next example, the retail_item table from the preceding example is used as the basis for a query. The user can view all the records of the table or can choose to view retail items of a certain color, size, or both. Based on these criteria, two components must be created to access the two query values of color and size. Additional components are a button that runs the query and a text field that displays the results. Following is the definition of the members for the new applet:

Choice      colorChoice;
Choice      sizeChoice;
Button      searchButton;
TextArea    resultsTextArea;

For this applet, I decided to use drop-down list boxes (also called choice components) for the two query values. This arrangement assists users by providing a list of valid query values. The init() method uses a combination of BorderLayout, FlowLayout, and GridBagLayout container layout classes (see Listing 44.5 and the accompanying CD-ROM). I used the GridBagLayout class because it allows the developer to define a component that takes up the remainder of the space allocated to the applet (both vertically and horizontally).

Listing 44.5. The init() method for the query applet.

// init()
    //
    // method will instantiate the applet
    //
    // the applet objects will be created and placed
    // in this method
    //

public void init()
{
        Panel       layout_area;
        Panel       search_area;
        Panel       result_area;

        // set background to white
        setBackground(new Color(255,255,255));

        // create border layout as default
        setLayout(new BorderLayout());

        layout_area = new Panel();
        layout_area.setLayout(new BorderLayout());

        // add layout_area to top of background panel
        add("North",layout_area);

        // add top label to layout
        setFont(new Font("Helvetica",Font.BOLD,12));
        layout_area.add("North",new Label("Enter value(s) to search on:"));
        setFont(new Font("Helvetica",Font.PLAIN,12));

        // create gridlayout search panel
        // add to background layout
        {
            search_area = new Panel();
            search_area.setLayout(new FlowLayout(1,5,5));
            layout_area.add("Center",search_area);

            // create color Choice object
            // add to layout with label

            // create Choice object colorChoice
            // add colors to Choice object
            colorChoice = new Choice();
            search_area.add(colorChoice);

            colorChoice.addItem("");
            colorChoice.addItem("Red");
            colorChoice.addItem("Orange");
            colorChoice.addItem("Navy");
            colorChoice.addItem("Green");
            colorChoice.addItem("Yellow");
            colorChoice.addItem("White");
            colorChoice.addItem("Black");
            colorChoice.addItem("Blue");
            colorChoice.addItem("Purple");

            search_area.add(new Label("Color:"));

            // create Choice object sizeChoice
            // add sizes to Choice object
            sizeChoice = new Choice();
            search_area.add(sizeChoice);

            sizeChoice.addItem("");
            sizeChoice.addItem("T");
            sizeChoice.addItem("J");
            sizeChoice.addItem("S");
            sizeChoice.addItem("M");
            sizeChoice.addItem("L");
            search_area.add(new Label("Size:"));

            search_area.add(new Label("     "));
            searchButton = new Button("  Search  ");
            searchButton.addActionListener(this);
            search_area.add(searchButton);
        }

        // create another panel for the result set
        // make the type of layout GridBagLayout
        {
            result_area = new Panel();

            // add results label
            setFont(new Font("Helvetica",Font.BOLD,12));
            add("Center", new Label("Results:"));
            setFont(new Font("Courier",Font.PLAIN,12));

            add("South",result_area);

            GridBagLayout gridbag = new GridBagLayout();
            result_area.setLayout(gridbag);

            GridBagConstraints Constraints = new GridBagConstraints();
            Constraints.weightx=1.0;
            Constraints.weighty=1.0;
            Constraints.anchor=GridBagConstraints.CENTER;
            Constraints.fill = GridBagConstraints.BOTH;
            Constraints.gridwidth = GridBagConstraints.REMAINDER;

            // create results text area
            resultsTextArea = new TextArea(15,60);

            // add with constraints to layout
            gridbag.setConstraints(resultsTextArea,Constraints);
            result_area.add(resultsTextArea);

            resultsTextArea.setEditable(false);
        }



}

After each Choice component is created, data values are added to it using the addItem() method. When the user clicks the down arrow for the component, a list box opens, displaying the values that have been added to it. The user can then click any one of these options to make it the selected item for that component.

USING DIFFERENT FONTS
As you can see in the preceding code samples, you can use different fonts and sizes in the same applet. You may find it effective to use a larger font for buttons and labels and a smaller font for the displayed result. Listing 44.6 shows the rest of the query applet, which includes the event handler, the database connection method, and a method that gets the query values and builds a where clause for the query. You can also find this code on the CD-ROM that accompanies this book.

Listing 44.6. sample3: A simple database query applet.

// check to see if event was search button being
    // pressed;
    // if so
    //      connect to database
    //      create query
    //      send statement
    //      process results
    //  else
    //      disregard
    //
     public void actionPerformed(ActionEvent event) {
      if (event.getSource() == searchButton) {
           processRequest();
           }      
     }

  // process_request
  //
  // connect to database
  // build where clause
  // retrieve data
  // output results
  public void processRequest()
    {
        String whereString = "";
        String stringSelect = "";
        String tempString = "";
        String stringResult = "";

        try {
             resultsTextArea.setText("working...");

            //connect to database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";

            // connect
            con = DriverManager.getConnection(url, "dba", "sql");

            // create Statement
            stmt = con.createStatement();

            whereString = buildWhere();

            // execute statement
            stringSelect = "Select retail_item.item_nbr," +
                 "retail_item.item_desc,retail_item.qty_per_pkg," +
                 "retail_item.color," +
                 "retail_item.size,retail_item.retail_cost " +
                 "from retail_item" + whereString;

            rs =  stmt.executeQuery(stringSelect);

            resultsTextArea.setText("Processing Results...");

            while (rs.next()) {

                // get item number
                tempString = rs.getString(1) + "\t";
                stringResult+=tempString;

                // get description
                tempString=rs.getString(2) + "\t";
                stringResult+=tempString;

                // get qty
                tempString = rs.getString(3) + "\t";
                stringResult+=tempString;

                // get color
                tempString = rs.getString(4) + "\t";
                if (rs.wasNull()) {
                        tempString = "\t\t";
                        }
                stringResult+=tempString;

                // get size
                tempString= rs.getString(5) + "\t";
                if (rs.wasNull()) {
                        tempString = "\t\t";
                        }
                stringResult+=tempString;

                // get retail cost
                tempString="$" + rs.getString(6) + "\n";
                stringResult+=tempString;
            }
            if (stringResult == "") {
                stringResult = "No Data Found for Query \n" + stringSelect;
            }
            resultsTextArea.setText(stringResult);

            stmt.close();
            rs.close();
            }

            catch (Exception e) {
                resultsTextArea.setText(e.getMessage());
            }
    }

    // buildWhere
    //
    // check Choice components
    // if an item is selected and is not
    //   the one at position 0, access the selected item
    //
    // if choice is category, access the category code
    // return generated where clause
    public String buildWhere()
    {
        String colorString = null;
        String sizeString = null;
        String whereString = null;

         int colorInt = colorChoice.getSelectedIndex();
         if (colorInt > 0) {
             colorString = colorChoice.getSelectedItem();
         }

         int sizeInt = sizeChoice.getSelectedIndex();
         if (sizeInt > 0) {
             sizeString = sizeChoice.getSelectedItem();
         }

         if (colorString != null) {
             whereString = " Where ";
             whereString = whereString + " retail_item.color = '" + colorString + "'";
         }

         if (sizeString != null) {
             if (whereString == null) {
                whereString = " Where ";
             }
             else {
                whereString = whereString + " and ";
             }
             whereString = whereString + " retail_item.size = '" + sizeString + "'";
         }
        if (whereString == null) {
             whereString = "";
         }
    return whereString;



    }

The applet checks to see whether the user selected a color and size value and uses these to modify the query string. The results are parsed with a tab character (/t) appended to add some white space between the fields. The results are then displayed in the TextArea result field.

Also consider the processRequest() method; it uses the JDBC wasNull() method to check the code to see whether a value is returned from some fields. This function is called after the get method to see whether the returned result is null. You should make this check on any field defined as optional (that is, any field for which null values are allowed) in the database. Fig-ure 44.5 shows the newly loaded applet in the applet viewer.

Figure 44.5.
The sample3 applet after loading.

TIP: Use the result field to display the query string if no data is found. In this way, you can determine whether no data was found because there was none to match the query, or because the query did not contain all the components you assumed it would contain.

If you select all the Black retail items and run the applet, you see results similar to those shown in Figure 44.6. If you select all the Black retail items of size L (large), you see a smaller subset of records, as shown in Figure 44.7.

Figure 44.6.
The sample3 applet with all Black retail items selected.

Figure 44.7.
The sample3 applet with all Black Large retail items selected.

Summary

This chapter introduced the techniques of using the JDBC with applets. Not all JDBC technology is accessible by an applet, although these restrictions and limitations have improved with the new security features of the JDK 1.1.

You use the JDBC from an applet no differently than you use the JDBC from an application--except that you must use the AWT component classes to provide a way to access and display data. Although this chapter did not explain the AWT, it did demonstrate how to display query results and access data for a database update.

TOCBACKFORWARDHOME


©Copyright, Macmillan Computer Publishing. All rights reserved.