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. 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. 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. 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. 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. 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. 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. |