Using JavaBeans to Access Databases

   

Java™ 2 Primer Plus
By Steven Haines, Steve Potts

Table of Contents
Chapter 23.  Component-Based JavaServer Pages


A very common use of JavaBeans is to provide the JSP page with access to database information. This includes both updates and queries. In this section, we will enhance the JavaBean to include methods that both store data in database tables and retrieve it from those tables for display in a browser.

The TicketRequestBean needs to be enhanced to add the Java DataBase Connection (JDBC) code. This code is needed to access a database, to insert and retrieve data from it. We will use MS Access for the DBMS.

The schema of this single table database is shown in Figure 23.8.

Figure 23.8. An MS Access database will store the data from the JavaBean.

graphics/23fig08.gif

To use the Access database from Java, we need to define an ODBC data source for it. In Windows XP, we do this by clicking the DataSources(ODBC) icon in the Administrative tools folder in the Control Panel.

This will open the dialog box shown in Figure 23.9.

Figure 23.9. An ODBC datasource can be defined for this database.

graphics/23fig09.gif

Name the datasource TicketRequest and click the Select button. This gives you a dialog that will enable you to locate the database file that you created earlier in this section. Choose OK, and then OK again on the other dialog. Your datasource is now ready to use.

Click the Add button to display the dialog shown in Figure 23.10.

Figure 23.10. Select a driver for this database.

graphics/23fig10.gif

Select the Microsoft Access Driver and click the Finish button. This opens the dialog shown in Figure 23.11.

Figure 23.11. . Select the database file associated with this data-source using a dialog.

graphics/23fig11.gif

Updating the Database

We need an HTML page that gathers more information than the old ones. Listing 23.20 shows the HTML code for this page.

Listing 23.20 The RequestTicketForm.html File
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  <HTML>    <HEAD>      <TITLE>Add a Customer 4</TITLE>    </HEAD>    <H1 align="CENTER">    Enter the new customer's information    </H1>    <FORM action='/examples/jsp/jpp/TicketRequestProcessor4.jsp'>       Customer ID:         <INPUT TYPE="TEXT" NAME="custID"><BR><BR>       Customer Last Name:  <INPUT TYPE="TEXT" NAME="lastName"><BR><BR>       Customer First Name: <INPUT TYPE="TEXT" NAME="firstName"><BR><BR>       Cruise ID:           <INPUT TYPE="TEXT" NAME="cruiseID"><BR><BR>       Destination:         <INPUT TYPE="TEXT" NAME="destination"><BR><BR>       Port:                <INPUT TYPE="TEXT" NAME="port"><BR><BR>       Sailing:             <INPUT TYPE="TEXT" NAME="sailing"><BR><BR>       Number Of Tickets:   <INPUT TYPE="TEXT" NAME="numberOfTickets">       <BR><BR>       <CENTER>          <INPUT TYPE="SUBMIT">       </CENTER>    </FORM>    </BODY>  </HTML> 

We have added the following fields to our form:

  • cruiseID The cruise's ID in our system. We have dozens of cruises available.

  • destination Where the cruise is going: the Caribbean, Alaska, Europe, and so on

  • port The port of embarkation: Miami or San Juan.

  • sailing The date that the ship sails.

  • numberOfTickets How many tickets are being purchased.

The JSP now has to update the JavaBean for more fields, and it must tell the Bean to store the information in the database. Listing 23.21 shows this file.

Listing 23.21 The TicketRequestProcessor4.jsp File
 <jsp:useBean             scope="session" />  <html>  <head>  <title>Ticket Request Processor 4</title>  <body>  <h2>Ticket Request Processor 4</h2>  <br>  <jsp:setProperty name="trb2" property="custID"       param="custID" />  <jsp:setProperty name="trb2" property="firstName"       param="firstName" />  <jsp:setProperty name="trb2" property="lastName"       param="lastName" />  <jsp:setProperty name="trb2" property="cruiseID"       param="cruiseID" />  <jsp:setProperty name="trb2" property="destination"       param="destination" />  <jsp:setProperty name="trb2" property="port"       param="port" />  <jsp:setProperty name="trb2" property="sailing"       param="sailing" />  <jsp:setProperty name="trb2" property="numberOfTickets"       param="numberOfTickets" />  Here is the data that you entered  <br>  <br>  <jsp:getProperty name="trb2" property="custID"/>  <br>  <jsp:getProperty name="trb2" property="firstName"/>  <br>  <jsp:getProperty name="trb2" property="lastName"/>  <br>  <jsp:getProperty name="trb2" property="cruiseID"/>  <br>  <jsp:getProperty name="trb2" property="destination"/>  <br>  <jsp:getProperty name="trb2" property="port"/>  <br>  <jsp:getProperty name="trb2" property="sailing"/>  <br>  <jsp:getProperty name="trb2" property="numberOfTickets"/>  <br>  <br>  Here is the result of the update attempt  <br>  <%=     trb2.updateDB()  %>  </body>  </html> 

Notice that we are now referencing a new JavaBean, called TicketRequestBean2.

 <jsp:useBean             scope="session" /> 

We add more fields to the Bean using the same syntax that you have already seen. The final step is new, however. We make a call to a method in the JavaBean called updateDB(). This method is shown here in Listing 23.22.

Listing 23.22 The updateDB() Method
 public String updateDB()  {     java.sql.Connection dbConn = null;     Statement statement1 = null;     String createStatement;     String insertStatement;     try     {        // ============== Make connection to database ==================        //load the driver class        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");        //Specify the ODBC data source        String sourceURL = "jdbc:odbc:TicketRequest";        //get a connection to the database        dbConn = DriverManager.getConnection(sourceURL);        //If we get to here, no exception was thrown        System.out.println("The database connection is " + dbConn);        System.out.println("Making connection...\n");        //Create the statement        statement1 = dbConn.createStatement();        /////////////////////////////////////////////////////////////////        //        Create the row in the database                    //        /////////////////////////////////////////////////////////////////        insertStatement = "INSERT INTO TicketRequest VALUES(" +        custID + "," +        "'" + lastName + "'," +        "'" + firstName + "'," +        cruiseID + "," +        "'" + destination + "'," +        "'" + port + "'," +        "'" + sailing + "',"  +        numberOfTickets + ")";        System.out.println(insertStatement);         statement1.executeUpdate(insertStatement);        return "Successful Update";     } catch (Exception e)     {        System.out.println("Exception was thrown: " + e.getMessage());        return "UnSuccessful Update";     } finally     {        try        {           if (statement1 != null)              statement1.close();           if (dbConn != null)              dbConn.close();        } catch (SQLException sqle)        {           System.out.println("SQLException during close(): "                                                 + sqle.getMessage());        }     }  } 

This method does some standard JDBC processing to access the database. It first loads the ODBC processing class.

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 

Next, it names the TicketRequest data source that we created earlier in the chapter.

 String sourceURL = "jdbc:odbc:TicketRequest"; 

Finally, it creates a connection to the datasource via the driver class.

 //get a connection to the database  dbConn = DriverManager.getConnection(sourceURL); 

This connection will be used to process the data. We use it to create a Statement object.

 statement1 = dbConn.createStatement();  /////////////////////////////////////////////////////////////////  //        Create the rowin the database                    //  ///////////////////////////////////////////////////////////////// 

Next, we create a string that represents the SQL statement that we want to execute.

 insertStatement = "INSERT INTO TicketRequest VALUES(" +  custID + "," +  "'" + lastName + "'," +  "'" + firstName + "'," +  cruiseID + "," +  "'" + destination + "'," +  "'" + port + "'," +  "'" + sailing + "',"  +  numberOfTickets + ")"; 

Finally, we do an executeUpdate() method to run the SQL.

 statement1.executeUpdate(insertStatement); 

To run the program, open a browser and type the address of the RequestTicketForm.html file:

 http://localhost:1776/examples/RequestTicketForm.html 

The result from running this HTML file is shown here in Figure 23.12.

Figure 23.12. You update the database by calling a method in the JavaBean.

graphics/23fig12.gif

Retrieving the Data from the Database

Now that we have the data in the database, we need to retrieve and look at it. To do this, we need to enhance our JavaBean again by adding a method called retrieveFromDB(). Listing 23.23 shows this method.

Listing 23.23 The retrieve FromDB() Method
 public String retrieveFromDB()  {     java.sql.Connection dbConn = null;     Statement statement1 = null;     String createStatement;     String insertStatement;     try     {        // ============== Make connection to database ==================        //load the driver class        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");        //Specify the ODBC data source        String sourceURL = "jdbc:odbc:TicketRequest";         //get a connection to the database        dbConn = DriverManager.getConnection(sourceURL);        //If we get to here, no exception was thrown        System.out.println("The database connection is " + dbConn);        System.out.println("Making connection...\n");        //Create the statement        statement1 = dbConn.createStatement();        //         Create the tables in the database       //Populate the bean       String getBeanString =       "SELECT * FROM TicketRequest " +       "WHERE CustID = " + custID;       ResultSet results = statement1.executeQuery(getBeanString);       while (results.next())       {            lastName = results.getString("lastName");            firstName = results.getString("firstName");            cruiseID = results.getInt("cruiseID");            destination = results.getString("destination");            port = results.getString("port");            sailing = results.getString("sailing");            numberOfTickets = results.getInt("numberOfTickets");       }       return "Successful Retrieval";     } catch (Exception e)     {        System.out.println("Exception was thrown: " + e.getMessage());        return "UnSuccessful Retrieval";     } finally     {        try        {           if (statement1 != null)              statement1.close();           if (dbConn != null)              dbConn.close();        } catch (SQLException sqle)        {           System.out.println("SQLException during close(): " +                                             sqle.getMessage());        }     }  }     

This method presumes that the Bean has already been updated with the request custID. It uses that custID to do a SELECT on the TicketRequest table.

 String getBeanString =  "SELECT * FROM TicketRequest " +  "WHERE CustID = " + custID;  ResultSet results = statement1.executeQuery(getBeanString); 

The ResultSet object contains all the data that was retrieved. We need to step through it and get the data fields for this record.

 while (results.next())  {       lastName = results.getString("lastName");       firstName = results.getString("firstName");       cruiseID = results.getInt("cruiseID");       destination = results.getString("destination");       port = results.getString("port");       sailing = results.getString("sailing");       numberOfTickets = results.getInt("numberOfTickets");  }  return "Successful Retrieval"; 

We use a while loop even though there should be only one row that matches this custID. This is done commonly with JDBC retrievals to avoid an error message if more than one row or zero rows are returned. After running this, a string containing "Successful Retrieval" is returned.

We need an HTML form that asks for the custID that the user wants to see. Listing 23.24 shows this file.

Listing 23.24 The RetrieveTicketForm.html File
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  <HTML>    <HEAD>      <TITLE>Retrieve a Ticket</TITLE>    </HEAD>    <H1 align="CENTER">    Enter the customer ID    </H1>    <FORM action='/examples/jsp/jpp/TicketRetrievalProcessor.jsp'>       Customer ID:         <INPUT TYPE="TEXT" NAME="custID"><BR><BR>       <CENTER>          <INPUT TYPE="SUBMIT">       </CENTER>    </FORM>    </BODY>  </HTML> 

This form calls the action named TicketRetrievalProcessor.jsp, which is shown in Listing 23.25.

Listing 23.25 The TicketRetrievalProcessor.jsp File
 <jsp:useBean             scope="session" />  <html>  <head>  <title>Ticket Retrieval Processor </title>  <body>  <h2>Ticket Retrieval Processor </h2>  <br>  <jsp:setProperty name="trb2" property="custID"       param="custID" />  Here is the data that you entered  <br>  <br>  <jsp:getProperty name="trb2" property="custID"/>  <br>  Here is the result of the retrieval attempt  <br>  <%=     trb2.retrieveFromDB()  %>  <br>  <br>  Here is the data that was retrieved  <br>  <jsp:getProperty name="trb2" property="firstName"/>  <br>  <jsp:getProperty name="trb2" property="lastName"/>  <br>  <jsp:getProperty name="trb2" property="cruiseID"/>  <br>  <jsp:getProperty name="trb2" property="destination"/>  <br>  <jsp:getProperty name="trb2" property="port"/>  <br>  <jsp:getProperty name="trb2" property="sailing"/>  <br>  <jsp:getProperty name="trb2" property="numberOfTickets"/>  <br>  <br>  </body>  </html> 

The first action is to set property:

 <jsp:setProperty name="trb2" property="custID"       param="custID" /> 

Following that, a call is made to the retrieveFromDB() method in the JavaBean.

 Here is the result of the retrieval attempt  <br>  <%=     trb2.retrieveFromDB()  %> 

Finally, the data is displayed.

 <jsp:getProperty name="trb2" property="firstName"/>  <br>  <jsp:getProperty name="trb2" property="lastName"/>  <br>  … 

You run this program by typing the following in your browser's address box:

 http://localhost:1776/examples/RetrieveTicketForm.html 

The result of running this program is shown in Figure 23.13.

Figure 23.13. You can retrieve data from a database using JavaBeans and JSP.

graphics/23fig13.gif

Notice that the data that appears is identical to that which you entered earlier in the chapter. Listing 23.26 shows the entire listing for the TicketRequestBean2.

Listing 23.26 The TicketRequestBean2.java File
 /*   * TicketRequestBean2.java   *   * Created on July 9, 2002, 11:22 AM   */  package com.samspublishing.jpp.ch23;  import java.sql.*;  import java.util.*;  /**   *   * @author  Stephen Potts   * @version   */  public class TicketRequestBean2 implements java.io.Serializable  {     //information about the customer     private int custID;     private String lastName;     private String firstName;     //information about the cruise     private int cruiseID;     private String destination;     private String port;     private String sailing;     private int numberOfTickets;     public TicketRequestBean2()     {     }     /** Constructor */     public TicketRequestBean2(int custID, String lastName,                 String firstName, int cruiseID, String destination,                 String port, String sailing, int numberOfTickets,                                            boolean isCommissionable)      {        //set the information about the customer        this.custID = custID;        this.lastName = lastName;        this.firstName = firstName;        //set the information about the cruise        this.cruiseID = cruiseID;        this.destination = destination;        this.port = port;        this.sailing = sailing;         this.numberOfTickets = numberOfTickets;     }     public int getCustID()     {        return this.custID = custID;     }     public String getLastName()     {        return this.lastName = lastName;     }     public String getFirstName()     {        return this.firstName = firstName;     }     public int getCruiseID()     {        return this.cruiseID;     }     public String getDestination()     {        return this.destination;     }     public String getPort()     {        return this.port;     }     public String getSailing()     {        return this.sailing;     }     public int getNumberOfTickets()     {        return this.numberOfTickets;     }     public void setCustID(int custID)     {        this.custID = custID;     }     public void setLastName(String lastName)     {        this.lastName = lastName;      }     public void setFirstName(String firstName)     {        this.firstName = firstName;     }     public void setCruiseID(int cruiseID)      {        this.cruiseID = cruiseID;     }     public void setDestination(String destination)     {        this.destination = destination;     }     public void setPort(String port)     {        this.port = port;     }     public void setSailing(String sailing)     {        this.sailing = sailing;     }     public void setNumberOfTickets(int numberOfTickets)     {        this.numberOfTickets = numberOfTickets;      }     public String toString()     {        String outString;        outString = "-------------------------------------------" + "\n";        //information about the customer        outString += "custID = " + this.custID + "\n";        outString += "lastName = " + this.lastName + "\n";        outString += "firstName = " + this.firstName + "\n";        outString += "-------------------------------------------" + "\n";        //information about the cruise        outString += "cruiseID = " + this.cruiseID + "\n";        outString += "destination = " + this.destination + "\n";        outString += "port = " + this.port + "\n";        outString += "sailing = " + this.sailing + "\n";        outString += "numberOfTickets = " + this.numberOfTickets + "\n";        outString += "-------------------------------------------" + "\n";        return outString;      }     public String updateDB()     {        java.sql.Connection dbConn = null;        Statement statement1 = null;        String createStatement;        String insertStatement;        try        {           // ============== Make connection to database ==================           //load the driver class           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");           //Specify the ODBC data source           String sourceURL = "jdbc:odbc:TicketRequest";           //get a connection to the database           dbConn = DriverManager.getConnection(sourceURL);           //If we get to here, no exception was thrown           System.out.println("The database connection is " + dbConn);           System.out.println("Making connection...\n");           //Create the statement           statement1 = dbConn.createStatement();           //                       Create the tables in the database           insertStatement = "INSERT INTO TicketRequest VALUES(" +           custID + "," +           "'" + lastName + "'," +           "'" + firstName + "'," +           cruiseID + "," +           "'" + destination + "'," +           "'" + port + "'," +           "'" + sailing + "',"  +           numberOfTickets + ")";           System.out.println(insertStatement);           statement1.executeUpdate(insertStatement);           return "Successful Update";        } catch (Exception e)        {           System.out.println("Exception was thrown: " + e.getMessage());           return "UnSuccessful Update";        } finally        {           try           {              if (statement1 != null)                  statement1.close();              if (dbConn != null)                 dbConn.close();           } catch (SQLException sqle)           {              System.out.println("SQLException during close(): " +                                                    sqle.getMessage());           }        }     }     public String retrieveFromDB()     {        java.sql.Connection dbConn = null;        Statement statement1 = null;        String createStatement;        String insertStatement;         try        {           // ============== Make connection to database ==================           //load the driver class           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");           //Specify the ODBC data source           String sourceURL = "jdbc:odbc:TicketRequest";           //get a connection to the database           dbConn = DriverManager.getConnection(sourceURL);           //If we get to here, no exception was thrown           System.out.println("The database connection is " + dbConn);           System.out.println("Making connection...\n");           //Create the statement           statement1 = dbConn.createStatement();           //                       Create the tables in the database          //Populate the bean          String getBeanString =          "SELECT * FROM TicketRequest " +          "WHERE CustID = " + custID;          ResultSet results = statement1.executeQuery(getBeanString);          while (results.next())          {               lastName = results.getString("lastName");               firstName = results.getString("firstName");                cruiseID = results.getInt("cruiseID");               destination = results.getString("destination");               port = results.getString("port");               sailing = results.getString("sailing");               numberOfTickets = results.getInt("numberOfTickets");          }          return "Successful Retrieval";        } catch (Exception e)        {           System.out.println("Exception was thrown: " + e.getMessage());           return "UnSuccessful Retrieval";        } finally        {           try           {              if (statement1 != null)                 statement1.close();              if (dbConn != null)                 dbConn.close();           } catch (SQLException sqle)           {              System.out.println("SQLException during close(): " +                                                      sqle.getMessage());           }        }     }  } 

This version of the JavaBean contains the code to update the database, as well as the code to retrieve the data from the database. Notice the simplicity of the JSP and HTML for this example. All the complexity of hitting the database is delegated to the Bean where it belongs.


       
    Top
     



    Java 2 Primer Plus
    Java 2 Primer Plus
    ISBN: 0672324156
    EAN: 2147483647
    Year: 2001
    Pages: 332

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net