Web Enabling Inventory Systems"Active Page"

Questions or
comments:
webmaster

Updated:
2003-06-13

Example Java Server Pages

Example JSPs

We are using Java Server Page (JSP) technology in our class to present dynamic data within our Project 2 Web pages. We are coding to perform two primary tasks: Adding new data to a server-side database and Retrieving and presenting data from a server-side database. The JSP engine (named Tomcat) continues to be improved and extended as this course evolves over the years. Because it is now possible to perform our main JSP tasks without seeing any actual Java code, I present an alternative way of doing Project 2 dynamic pages below the first two examples I suggest you pattern your work after.

The key to you learning JSP technology well is understanding what the different parts of the JSP do. Use a top-down investigation and then learn the details once you understand the major functionality. We will go over those categories in class. All you are responsible for in this class is recognizing these various patterns (just as I asked of you with basic XML understanding) and being able to substitute processing steps into the various examples I provide. Everyone grasps it eventually so keep at it. Keep going over the examples provided for you here on this page.

Adding New Data to a Server-Side Database

I am providing you with two examples of how to use JSP technology to add data to a database via the Web. The first example of adding a new supplier of file resources to your site is functional as written (so please do include it in your Web site). Take a look at the Add Supplier Form and view the source.

Note that I provide a style sheet for you to use which will let you change the look of my contributions to your site. The style sheet provides CSS instructions that appear as:

td {font-family: arial; font-size:18px; color:#000000; width:100px}
th {font-family: arial; font-size:12px; color:#000020;}

.white_border { font-family: "arial"; font-size:12px; color:#000000; height:20px; background-color: 
                #ffffff; margin-left:10px; margin-right:1px; margin-top:1px;}
.tableitem {font-family: arial; font-size:11px; margin-left:5px; margin-right:5px; margin-top:0px;}
.heading { font-family: "arial"; font-size:18px; margin-left:10px; margin-right:5px; margin-top:5px;}
.small  { font-family: arial; font-size:11px; }
A:link { color: #5C3900; text-decoration: underline; }
A:visited { color: #5C3900; text-decoration: underline; }
A:hover { color: white; background: #5C3900; text-decoration: none;}
.greySmall{font-family:Verdana, Arial, Helvetica, sans-serif; font-size:9px; color:#333333}

You should feel comfortable changing values in this style sheet to make your pages present differently than mine.

Back to the HTML form's page source. Please note that the HTML page form's ACTION attribute points to an addsupplier.jsp file on our project Web server. Our Web server (implemented using the apache Web server project software) intercepts the URL submitted by the form for processing, realizes it is a dynamic Web page request, and forwards it on to the Tomcat engine for processing. The Tomcat engine reads the JSP and processes it as instructed. The JSP code appears as presented here (with discussion below):

<%@ page contentType="text/html;charset=ISO-8859-1" language="java" import="java.util.Date" import="javax.naming.*" import="javax.sql.*" %>

<HTML>
<head>
    <TITLE>Moderator Event Report</TITLE>
    <META content="text/html; charset=unicode" http-equiv=Content-Type>
    <LINK REL=StyleSheet HREF=style.css TYPE=text/css>
</head>
<body bgcolor=#ffff99>

<%
    // Obtain our environment naming context 
    Context initContext = new InitialContext(); 

    // Look up our data source 
    DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/filedist");

    // Allocate and use a connection from the pool  
    java.sql.Connection conn = ds.getConnection();

    // create and execute the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String error = "Successful Add of New Customer on " + new Date().getMonth() + "-" + new Date().getDate() + "-" + (1900 + new Date().getYear());

    //Get the identifier of the menu item to display
      
    try {
            stmt.executeUpdate("INSERT INTO Supplier VALUES ('" + request.getParameter("Name") + "','" + 
                                                     request.getParameter("Password") + "'," + 
                                                     request.getParameter("Member") + ",'" + 
                                                     request.getParameter("Initials") + "')");            
            
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception exp) {
            if (exp.toString().compareTo("java.lang.NullPointerException")!=0) {
                error = exp.toString();
            }
        }
     
%>
    </TABLE>
<H3><%= error %></H3>
</body>
</HTML>

The first line above tells Tomcat which language to use to process the code as well as the various Java packages being used within the pages processing commands. This line is typical as a page processing directive. The next seven lines are static text that are delivered as written (note that the percent sign, %, is used heavily to distinguished between dynamic and static text components). A dynamic processing block then starts with five Java statements that identify the data source (our filedist database) we want to process against, get a connection to that database from the postgres database pool, and instantiate the typical Connection, Statement, and ResultSet objects used in SQL command processing.

I create an error String variable and instantiate it with a message that communicates a non-error condition (the error String gets changed whenever there is an error to report). The actual interaction block of code that processes database commands should be placed inside of a try-catch block as it is above. The SQL INSERT command is proper as listed for a Java coding style. All valid SQL statements can be placed in similar blocks (see the instructions from your Project 2 requirements).

The last four lines of the JSP end the static Web page with the exception of the error variable insertion as a processing tag (note the percent signs and equal sign involved). All variables can be accessed through this pattern of access (see the reporting examples below where variable access is done much more often).

You can see a second example running by filling out and submitting the form at http://www.cev.washington.edu/498a/atl/new_show_form.html (from 2003's course).

The key line in the HTML code for this form is:
<FORM ACTION="http://www.cev.washington.edu/498/addshow.jsp" method=GET>

<!--The JSP code-->

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

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

<%
    // Obtain our environment naming context 
    Context initContext = new InitialContext(); 

    // Look up our data source 
    DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/entertain");

    // Allocate and use a connection from the pool  
    java.sql.Connection conn = ds.getConnection();

    // create and execute the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String error="Successful Add of New Show";
    String new_id="-1";
    
    try {
            rs = stmt.executeQuery("SELECT MAX(Show_ID) AS MAX_ID FROM Show");
            
            if (rs.next()) {
                new_id = "" + (rs.getInt(1)+1);
            } else {
                new_id = "0";
            }
            rs.close();
            
            //create new set here
            stmt.executeUpdate("INSERT into Show values (" + new_id + "," + 
                  request.getParameter("entertainer_id") + "," + request.getParameter("venue_id") + "," + 
                  request.getParameter("revenue") + "," + request.getParameter("average_age") + "," + 
                  request.getParameter("average_ticket_price") + ",'" + request.getParameter("event_date") + "'," +
                  request.getParameter("start_time") + "," + request.getParameter("attendance") + ",'498')");
                      
    } catch (Exception exp) {
        error = exp.toString();
    }

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

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

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

Retrieving and Presenting Data from a Server-Side Database

The second example type I provide for you is a report JSP. Take a look at the example Moderator Event Report I provide you. The code that generates this page as a JSP follows. Take a look and then read the notes that follow it:

<%@ page contentType="text/html;charset=ISO-8859-1" language="java" import="java.util.Date" import="javax.naming.*" import="javax.sql.*" %>

<HTML>
<head>
    <TITLE>Moderator Event Report</TITLE>
    <META content="text/html; charset=unicode" http-equiv=Content-Type>
    <LINK REL=StyleSheet HREF=style.css TYPE=text/css>
</head>
<body bgcolor=#ffff99>

<%
    // Obtain our environment naming context 
    Context initContext = new InitialContext(); 

    // Look up our data source 
    DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/filedist");

    // Allocate and use a connection from the pool  
    java.sql.Connection conn = ds.getConnection();

    // create and execute the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String error = "";

    String heading = "Moderator Event Report for " + new Date().getMonth() + "-" + 
                      new Date().getDate() + "-" + (1900 + new Date().getYear());
    
    //Get the identifier of the menu item to display
    //String moderator = request.getParameter("Moderator");
    String query = "SELECT Moderator.Name AS Moderator, Review.Date AS Date, File.Rating AS Rating, " +
                   "File.Path AS Path, File.Name AS File, Review.Origname AS Original " + 
                   "FROM File, Moderator WHERE Review.ModeratorFK=Moderator.ModeratorID AND Review.FileFK=File.FileID";
    int x=1;
 %>
    <H3 class=heading><%=heading %></H3>
    <TABLE>
            <TR class=white_border>
                <TH>Moderator Name</TH>
                <TH>Review Date</TH>
                <TH>File Rating</TH>
                <TH>File</TH>
                <TH>Original Name</TH>
            </TR>
<%      
    try {
            rs = stmt.executeQuery(query);
            int columnCount = rs.getMetaData().getColumnCount();
            
            while(rs.next()) {
                x=1;
%>
                <TR class=white_border>
<%        
                    String name = rs.getString("Moderator");
                    String date = rs.getString("Date");	
                    int rating = rs.getInt("Rating");
                    String path =rs.getString("Path");
                    String file =rs.getString("File");
                    String original =rs.getString("Original");
%>
                    <TD class=tableitem align=center><%= name %></TD>
                    <TD class=tableitem align=center><%= date %></TD> 
                    <TD class=tableitem align=center><%= rating %></TD>
                    <TD class=tableitem align=center><%= path %>/<%= file %></TD>
                    <TD class=tableitem align=center><%= original %></TD>
                </TR>
<%                
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception exp) {
            error = exp.toString();
        }

%>
    </TABLE>
<H3><%= error %></H3>
</body>
</HTML>

Take a close look at the static text commands and the dynamic processing directives. Note the pattern of identifying the Java Server Page page directive first (as a single line of code), starting your static text, making a connection to the database where your organization stores data, setting up the start of your report (including column headers), running an SQL SELECT command to get reporting results, and then processing your results in a processing loop such that the report shows one line (view record) of results per loop. Then, note how the error processing is identical to the form processing JSP examples above.

You can see a second reporting example at URL: http://www.cev.washington.edu/498/showreport.jsp

Of course, each time you submit the entertainment form above, you add another line to the report here!

<!--The JSP code-->

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
 <head>
  <title>WOW - Entertainment Venue Planning</title>

  <STYLE TYPE="text/css">
  <!--
	td{ font-family: arial; font-size:11px; color:#000000;}
	.white_border { font-family: "arial"; font-size:12px; color:#000000; height:200px; background-color: #ffffff; 
                    margin-left:10px; margin-right:1px; margin-top:1px;}
	.white_border_text { font-family: "arial"; font-size:11px; margin-left:10px; margin-right:5px; margin-top:5px;}
	.small  { font-family: arial; font-size:9px; }
	.plain_text {font-family: arial; font-size:10px; margin-left:5px; margin-right:5px; margin-top:0px;}
	A:link { color: #5C3900; text-decoration: underline; }
	A:visited { color: #5C3900; text-decoration: underline; }
	A:hover { color: white; background: #5C3900; text-decoration: none;}
	.greySmall{font-family:Verdana, Arial, Helvetica, sans-serif; font-size:9px; color:#333333}
  -->
  </STYLE>

<%
    // Obtain our environment naming context 
    Context initContext = new InitialContext(); 

    // Look up our data source 
    DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/entertain");

    // Allocate and use a connection from the pool  
    java.sql.Connection conn = ds.getConnection();

    // create and execute the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String error="";
%> 


 </head>

<BODY BGCOLOR="#B1AD9C" LEFTMARGIN="0" TOPMARGIN="0" MARGINWIDTH="0" MARGINHEIGHT="0">

<CENTER>

<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0">
 <TR>
  <TD width="750" COLSPAN="2"><img src="http://www.cev.washington.edu/498a/atl/images/title.jpg" alt="wow 
    entertainment banner"></TD>
 </TR>
</TABLE>


<HR WIDTH="750" COLOR="#5C3900" SIZE="1">

<DIV CLASS="small">
www.wowentertainment.com
</DIV>

<br>

<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0">
 <TR>
  <TD WIDTH="25"><IMG SRC="../498a/atl/images/navarrow.gif" title="arrow"></td>
  <TD WIDTH="150"><img src="../498a/atl/images/navigation.jpg" title="Navigation"></td>
  <TD WIDTH="575" ROWSPAN="7" valign="top" align="left">
	<DIV CLASS="white_border">
	<DIV CLASS="white_border_text">
	
<font size="+1">All Shows Report</font>
<p>
<center>
	<table border="0" cellpadding="3" cellspacing="0">
	 <tr>
	  <td colspan="6" align="center" bgcolor="5B0110"><font color="#eeeeee"><b>Reports for 
        All Shows</b></font></td>
	 </tr>
	 <tr bgcolor="#eeeeee">
	  <td width="80">Show ID</td>
	  <td width="120">Entertainer</td>
	  <td width="120">Venue</td>
 	  <td width="80">Date</td>
 	  <td width="80">Time</td>
 	  <td width="80">Price</td>
	 </tr>
<%


    try {
        rs = stmt.executeQuery("SELECT Show_ID, Entertainer_FK AS eName, Venue_FK AS vName, Date, Start_time, 
                                       Ticket_Price FROM Show WHERE Owner='498'");
   
        while(rs.next()) {        
            
            String show_id = rs.getString("Show_ID");
            String entertainer_id = rs.getString("eName");	
            String venue = rs.getString("vname");
            String date =rs.getString("Date");
            String time =rs.getString("Start_time");
            String price =rs.getString("Ticket_price");

	    int time_digit = Integer.parseInt(time);

        if((time_digit<12 && time_digit>0) || (time_digit==24)) time = time_digit + ":00am"; 
   	else if(time_digit>=12 && time_digit<=23) time = (time_digit-12) + ":00pm";

%>
	<TR>
	<TD><%= show_id %></TD>
	<TD><%= entertainer_id %></TD> 
	<TD><%= venue %></TD>
	<TD><%= date %></TD>
	<TD><%= time %></TD>
	<TD>$<%= price %></TD>
	</TR>
<%
        }
    } catch (Exception exp) {
        error = exp.toString();
    }

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

</center>

	<br>
	</DIV>
 	</DIV></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/new_entertainer_form.html" title="new 
                            entertainer form" onMouseOver="over(0)" onMouseOut="out(0)"><img 
                            src="http://www.cev.washington.edu/498a/atl/images/entertainer_off.jpg" 
                            NAME="entertainer" border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/new_venue_form.html" title="new 
                            venue form" onMouseOver="over(1)" onMouseOut="out(1)"><img 
                            src="http://www.cev.washington.edu/498a/atl/images/venue_off.jpg" NAME="venue" 
                            border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/new_show_form.html" title="new show 
                            form" onMouseOver="over(2)" onMouseOut="out(2)"><img 
                            src="http://www.cev.washington.edu/498a/atl/images/show_off.jpg" 
                            NAME="show" border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/new_attendee_form.html" title="new 
                            attendee form" onMouseOver="over(3)" onMouseOut="out(3)"><img 
                            src="http://www.cev.washington.edu/498a/atl/images/attendee_off.jpg" 
                            NAME="attendee" border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/feedback_form.html" title="feedback 
                            form" onMouseOver="over(4)" onMouseOut="out(4)"><img 
                            src="http://www.cev.washington.edu/498a/atl/images/feedback_off.jpg" 
                            NAME="feedback" border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></TD>
  <TD WIDTH="150" HEIGHT="20"></TD>
 </TR>
 <TR>
  <TD WIDTH="25"><IMG SRC="http://www.cev.washington.edu/498a/atl/images/navarrow.gif" 
    title="arrow2"></td>
  <TD WIDTH="150"><IMG SRC="http://www.cev.washington.edu/498a/atl/images/reports.jpg" 
    title="reports"></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/shows.html" 
    title="shows report" onMouseOver="over(5)" onMouseOut="out(5)"><img 
    src="http://www.cev.washington.edu/498a/atl/images/showreport_off.jpg" NAME="showreport" 
    border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/attendance.html" title="average 
    attendance report" onMouseOver="over(6)" onMouseOut="out(6)"><img 
    src="http://www.cev.washington.edu/498a/atl/images/attendancereport_off.jpg" NAME="attendancereport" 
    border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/entertainers.html" 
    title="top entertainers report" onMouseOver="over(7)" onMouseOut="out(7)"><img 
    src="http://www.cev.washington.edu/498a/atl/images/entertainerreport_off.jpg" NAME="entertainerreport" 
    border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/underage.html" title="average 
    age at underage shows report" onMouseOver="over(8)" onMouseOut="out(8)"><img 
    src="http://www.cev.washington.edu/498a/atl/images/underagereport_off.jpg" NAME="underagereport" 
    border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></td>
  <TD WIDTH="150"><a href="http://www.cev.washington.edu/498a/atl/ratings.html" title="ratings 
    and comments report" onMouseOver="over(9)" onMouseOut="out(9)"><img 
    src="http://www.cev.washington.edu/498a/atl/images/ratingsreport_off.jpg" NAME="ratingsreport" 
    border="0"></a></TD>
 </TR>
 <TR>
  <TD WIDTH="25"></TD>
  <TD WIDTH="150" HEIGHT="200"></TD>
 </TR>
</TABLE>


<HR WIDTH="750" COLOR="#5C3900" SIZE="1">

<table border="0" cellpadding="0" cellspacing="0">
 <tr>
  <td width="750" valign="top" align="left">
	<div class="plain_text">
	© Copyright Wow Entertainment Inc.
	     
	<a href="">contact us</a>
	 | 
	<a href="">legal stuff</a>
	 | 
	<a href="">credits</a>
	</div>
  </td>
 </tr>
</table>
</CENTER>
</BODY>
</HTML>

Examples of Adding and Retrieving with XML directives

As seen with the class Cheese Database example, courtesy of Shawn Thomas.

Adding cheese data using the form at http://www.cev.washington.edu/498/form.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>

    <sql:update>
      INSERT INTO cheese 
        (name, qty, cost, location)
        VALUES(?, ?, ?, ?)
      <sql:param value="${param.name}" />
      <sql:param value="${param.qty}" />
      <sql:param value="${param.cost}" />
      <sql:param value="${param.location}" />
    </sql:update>
    
<%-- Get the new or updated data from the database --%>
<sql:query var="cheese" scope="session">
  SELECT * FROM cheese
    WHERE name = ?
  <sql:param value="${param.name}" />
</sql:query>

<%-- Redirect to the confirmation page --%>
<c:redirect url="confirmation.jsp" />

As seen with the class Cheese Database example, courtesy of Shawn Thomas.

Retrieving and presenting cheese data at http://www.cev.washington.edu/498/cheese.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

	<head>
		<meta http-equiv="content-type" content="text/html;charset=ISO-8859-1"/>
		<title>Cheese</title>
	</head>

	<body bgcolor="#ffffff">
		<sql:query var="cheese" scope="request">
			SELECT * FROM cheese ORDER BY cost 
		</sql:query>
		<c:choose>
			<c:when test="${cheese.rowCount == 0}">
				<p>No cheese available.</p>
			</c:when>
			<c:otherwise>
				<p>The following cheeses are available:</p>
				<table border="1">
					<th>Name</th>
					<th>Qty.</th>
					<th>Cost</th>
					<th>Location</th>
					<c:forEach items="${cheese.rows}" var="row">
						<tr>
							<td>${fn:escapeXml(row.name)}</td>
							<td>${fn:escapeXml(row.qty)}</td>
							<td>${fn:escapeXml(row.cost)}</td>
							<td>${fn:escapeXml(row.location)}</td>
						</tr>
					</c:forEach>
				</table>
			</c:otherwise>
		</c:choose>
		<p><a href="/498">Index</a></p>
	</body>

</html>