Project 2 Requirements

• Entertainment Planning Web Site -- Phase II

Entertainment Planning Web Site Enhancements (Phase II)

1. A New Entertainer Form (that will create a new Entertainer record in the database) for the Entertainer
2. A Feedback Form (that will create a new Rating record in the database) for the Attendee or Entertainer (your choice).
3. A New Show Form (that will create a new Show record in the database) for when an Entertainer plays at a Venue
4. A New Venue Form (that will create a new Venue record in the database).
5. A New Attendee Form (that will create a new Attendee record in the database) for an Attendee that attends one or more shows.
6. An enhanced Show Report similar to what you delivered for Project 1 (showing all shows existing for a venue in the Venue table)
7. An Average Attendance Report showing the average attendance at each Venue ordered from highest to lowest.
8. A Top Entertainer Report showing the entertainers who have booked the most dollars per show (showing number of shows, total revenue, and the average).
9. An Average Age at Underage Shows (that will show the average age for underage shows (under 21) for each location).
10. An Innovative Report of your own choosing that shows something interesting and useful for the entertainer, venue manager, or attendees related to ratings.

Visual Examples of Forms (but challenge yourself to make them look/function even better):

Visual Examples of Reports (but challenge yourself to make them look/function even better):

Of course, some of your reports will end up longer than the examples, but the examples show you the kind of data I expect for each report (your particular data choices can be different). Remember that you only have to code the header row and one row of the detail because you will put your format in a loop that the Java Server Page will fill with your SQL Command (query) results.

Turning in Your HTML Forms

<FORM ACTION="http://triton.cev.washington.edu:8080/addshowattendee_bdc.jsp" method=GET>

The ACTION attribute points to the URL where the FORM can be processed by a Java Server Page engine. I will differentiate between student forms through your initials that will be placed after the form name (note how I used _bdc to differentiate mine). Your forms can be placed anywhere within your Web site but they must be processed by a respective .jsp file in the /var/www/jakarta/webapps/ROOT directory (the Java Server Page engine - from package jakarta - root).

If I had asked you to process a New Show_Attendee Form, you would have wanted to write the following executeUpdate command to put the form data into the entertain database (entertain is the name of the database on our class Web server, triton.cev.washington.edu). The SQL statements are embedded in JSP files so you can add dynamic processing within your Web site. Note how the parameter names used in the request.getParameter() function calls have the same EXACT names as the NAME attribute values in the Add Show Attendee Form.

stmt.executeUpdate("INSERT INTO Show_Attendee VALUES (" + 
                  getUniqueID(con, "Show_Attendee_ID", "Show_Attendee") + "," +
                  request.getParameter("Attendee_ID") + "," + request.getParameter("Show_ID") + "," + 
                  request.getParameter("Price_Paid") + ",'" + request.getParameter("Owner") + "')");

Don't panic if Java Server Page code looks completely foreign to you. Just think it through with me here. Remember that a CGI script can connect a Web server to a database and execute SQL commands through a database interface. We just happen to be using Java as our CGI language to make the connection. So, by the time your code runs, the Java has already connected to the database and created a valid Statement object (referenced by the stmt for which our executeUpdate() routine will run). Now the CGI code must also be able to get the data from our form into the database. With Java, we can use the getParameter() routine that is available from the Java Server Page request object (which is created automatially by the JSP engine running on the server). So, all you have to do is use the request.getParameter() routine to process each value from your form and put its value into the SQL command that will add a new record to the table you wish to append. Any other text you need between the form data you can put together using the + operator (the + operator in Java is called a concatenation operator when you are using text values (called Strings)).

Look closely at where the quotes (single and double) are placed in the update statement. The single quotes are passed to the database to properly identify text and date fields (remember text and dates need to be enclosed in single quotes). Now try doing some simple substitutions with me. Imagine an attendee registered for an event. The form included INPUT element controls with the following NAME attributes and control contents:

NAME	CONTENT
Show	Cirque De Soleil
Date	5-14-2003
UserID	bobby145
Price_Paid	28
Comments	Should be great

The getUniqueID() routine gets a new autogenerated Show_Attendee_ID for the new Show_Attendee table record. You just need to pass it the database connection (conn), attribute name ("Show_Attendee_ID"), and table name ("Show_Attendee") and the getUniqueID() will return the next sequentially available ID for that table (the JSP example below uses another approach that is perhaps simpler while self-contained). The form INPUT control names match with the parameter in the getParameter() routine so that the proper value will be placed into the INSERT INTO command in the right place. The system will take the values from the CONTENT column above, look up the proper show id (233 for example) and user id (23232 for example) and substitute them into the appropriate getParameter() routine in the executeUpdate() above. You will see the Java actually sends:

INSERT INTO Show_Attendee VALUES (47, 233, 23232, 28, 'Should be Great')

to the database. Since the command is a valid command, the record is gladly inserted into the database. You should write 5 similar stmt.executeUpdate() commands for your five forms and embed them in 5 separate JSP files (see the example below). Then, upload your files to your JSP directory on triton.cev.washington.edu with the following names:

FORM	File Name	URL
New Venue	addvenue_xxx.jsp	http://triton.cev.washington.edu:8080/
New Entertainer	addentertainer_xxx.jsp	http://triton.cev.washington.edu:8080/
New Rating	addrating_xxx.jsp	http://triton.cev.washington.edu:8080/
New Show	addshow_xxx.jsp	http://triton.cev.washington.edu:8080/
New Attendee	addattendee_xxx.jsp	http://triton.cev.washington.edu:8080/

The JSP add record (insert) files above will match your HTML form names closely. The only difference should be the three initials you add to the end of the filename before the .jsp extension (for example addshowattendee_bdc.jsp). They should be put in the /var/www/jakarta/webapps/ROOT directory using your inde498a account in the SSH tool you used for project 1.

A JSP Example for Adding Records

<%@ page contentType="text/html;charset=ISO-8859-1" language="java" import="postgresql.*" %>

<HTML>
<HEAD>
<TITLE>Successful Add to Show_Attendee Table</TITLE>
<META content="text/html; charset=unicode" http-equiv=Content-Type>
</HEAD>
<body>

<%
    Class.forName("postgresql.Driver");
    java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:postgresql://triton.cev.washington.edu/entertain","postgres","si99raph");
    
    // create and execute the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String error="Successful Add of New Show Attendee";
    String new_id="-1";
    
    try {
            rs = stmt.executeQuery("SELECT MAX(Show_Attendee_ID) AS MAX_ID FROM Show_Attendee");
            
            if (rs.next()) {
                new_id = "" + (rs.getInt(1)+1);
            } else {
                new_id = "0";
            }
            rs.close();
            
            //create new set here
            stmt.executeUpdate("INSERT into Show_Attendee values (" + new_id + "," + 
                  request.getParameter("Attendee_ID") + "," + request.getParameter("Show_ID") + "," + 
                  request.getParameter("Price_Paid") + ",'" + request.getParameter("Owner") + "')");
                      
    } catch (Exception exp) {
        error = exp.toString();
    }

    stmt.close();
    conn.close();
%>

<!-- Put your visual HTML body content here -->

<H3><%= error %></H3>
</BODY>
</html>

REPORT	File Name	Servlet URL
Show Report	shows_xxx.txt	http://triton.cev.washington.edu:8080/
Average Attendance Report	attendance_xxx.jsp	http://triton.cev.washington.edu:8080/
Top Entertainer Report	entertainer_xxx.jsp	http://triton.cev.washington.edu:8080/
Average Age at Underage Shows	underage_xxx.jsp	http://triton.cev.washington.edu:8080/
Your Specified	other_xxx.jsp	http://triton.cev.washington.edu:8080/

Note that all of your report generating JSPs should have your initials included as well. They should be put in the /var/www/jakarta/webapps/ROOT directory using your inde498a account.

Reports are generated similarly to the SELECT command results Java code we reviewed together in class (and which you can always try out in the SQL Select tool). To generate a report you create a hyperlink in your HTML document that points to a valid URL for the proper JSP to run. For example, for my Show Attendee Report, I would create an A element like the following:

<A href=http://triton.cev.washington.edu:8080/showattendees_bdc.jsp?param1=value&param2=value2> Show Attendees Report </a>

You can put all of your report hyperlinks on your entertainment planning home page or you can put them on other pages within your site if you prefer. But, I will need to find them easily when reviewing your site in order to grade them as best as you will want me to. When someone clicks on a hyperlink like the example, the Java Server Page engine on the triton server will pass the request on to the JSP named showattendees_bdc.jsp. The parameters will be passed as parameters the report can us with a request.getParameter() statement just as you saw in the forms processing case.

Example Report JSP

<%@ page contentType="text/html;charset=ISO-8859-1" language="java" import="postgresql.*" %>

<HTML>
<HEAD><TITLE>Show Attendees Report</TITLE></HEAD>
<BODY bgcolor=#000080 text=WHITE link="#cc9933" vlink="#cc9933"><div align="center">

<%
    Class.forName("postgresql.Driver");
    java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:postgresql://triton.cev.washington.edu/entertain","postgres","si99raph");
    
    // create the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String error="";
%>

<!-- html body goes here -->
<FONT FACE=Arial>
<CENTER>
<H2>Show Attendees Report</H2>
<TABLE ALIGN=CENTER CELLSPACING=4>
<TR>
<TD><FONT FACE=Arial><CENTER><B>Attendee</B></CENTER></FONT></TD>
<TD><FONT FACE=Arial><CENTER><B>Show</B></CENTER></FONT></TD>
<TD><FONT FACE=Arial><CENTER><B>Price</B></CENTER></FONT></TD>
<TD><FONT FACE=Arial><CENTER><B>Owner</B></CENTER></FONT></TD>
</TR>
<%
    try {
        rs = stmt.executeQuery("SELECT * FROM show_attendees WHERE show_id=" + request.getParameter("show_id"));
   
        while(rs.next()) {        
            
            String attendee_id = rs.getString("attendee_id"); 
            String show_id = rs.getString("show_id");	
            String price = rs.getString("price_paid");
            String owner=rs.getString("owner");

            // To get back an integer, define int pp = Integer.parseInt(price_paid)
%>
	<TR>
	<TD><B><%= attendee_id %></B> 
	</TD>
	<TD><B><%= show_id %></B>
	</TD> 
	<TD><%= price %></TD>
	</TD> 
	<TD><%= owner %></TD>
	</TR>
<%
        }
    } catch (Exception exp) {
        error = exp.toString();
    }

    rs.close();
    stmt.close();
    conn.close();
%>
</TABLE>
<H3><%= error %></H3>
</CENTER>
</BODY>
</HTML>

Note the Java basically mixes your HTML tags with the results from your SQL SELECT command. Spend some time thinking this through. Print the code out and use highlighters to mark the parts that are HTML and the parts that come from the Java Server Page technology. Compare mixing SQL results and HTML tags using the CGI with mixing XML data and HTML tags using XSLT.