| ## 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.
| ©Copyright, Macmillan Computer Publishing. All rights reserved.