CISC474 H03

CISC474 H03b: Getting started with MySQL.

In the first part (H03a) you

accessed your account on the MySQL implementation at /jaguar/cisc474/mysql
changed your password
accessed your personal database
added a table, added rows to the table, and executed a SQL query against that table.

This file (H03b) is the second part, we will link that SQL table together with your Java Servlet and JSP code from Beer-v1, to make an application called BeerDB-v1. (We'll also rename other parts of the servlet and JSP code to ensure they stay separate from Beer-v1.)

We'll first do something "quick and dirty" to make the application work. Then, we'll think about how we "should" do things to better stay with the MVC design pattern.

In the third part of the assignment, you will use what you have learned do something similar for your Prod-v1 application, creating a ProdDB-v1 application.

The ProdDB-v1 is the part you will turn in for credit. You should script and submit that in a similar fashion to what you did for H02 (see H02 for detailed submission requirements), except that you should also include in your printout, a sample session using /jaguar/cisc474/mysql/bin/mysql, where you list out the tables, columns, and rows you created.

Due (whole assignment, tentative): Thursday March 24th
MIDTERM EXAM POSTPONED TO APRIL 14th

Useful links:

MySQL Documentation: http://dev.mysql.com/doc/mysql/en/index.html

For reference, a reminder of your `beer` table

We are asssuming here that you have a database called username (where username is your strauss username) and that it contains a table called beer.

That table should return the following from a SQL query "select * from beer;"

jaguar[51] > /jaguar/cisc474/mysql/bin/mysql -u pconrad -p
Enter password: xxxxx
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 230 to server version: 4.1.10-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use username
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from beer;
  +---------------+-------+
  | name          | color |
  +---------------+-------+
  | Jack Amber    | amber |
  | Red Moose     | amber |
  | Jail Pale Ale | light |
  | Gout Stout    | dark  |
  +---------------+-------+
  4 rows in set (0.03 sec)
mysql>

Notice that the colors in the Beer-v1 app are amber, light, dark, and brown. I've deliberately put nothing in the table for Brown, so that we can learn how to handle the case of "no results being returned" from a SQL query.

First step: Make BeerDB-v1 a clone of Beer-v1.

The first step is to make a clone of your Beer-v1 app, calling the new one BeerDB-v1.

(Note:BeerDB-v1 is the web-app name and the root directory name on the deployment side. On the development side, we call the new one beerDBV1, following the convention of the original. Why have two different names? I think the book is just trying to emphasize the difference between the two environments, so that you are aware that they are different.)

cd to my development directory, ~/cisc474/web_dev
make a duplicate copy of my beerV1 development directory called beerDBV1 using this command:
```
mkdir beerDBV1
cp -r beerV1/* beerDBV1
```
The -r on the cp command does a recursive copy, and thus duplicates the entire directory structure.
Make the changes needed to cause BeerDB-v1 to be a separate web-app. These include (but are not necessarily limited to*)
1. Adding a line into your resin.conf file as follows:
```
 <web-app id='BeerDB-v1/'/>
```
2. Changing the <servlet-name> and <url-pattern> elements in your web.xml file. For example, you might change SelectBeer.do to SelectBeerDB.do.
3. Changing the form.html file to direct the POST request to the SelectBeerDB.do action instead of the SelectBeer.do action.
4. Changing the headers on your form.html and result.jsp files so that you know for sure that you are accesssing the new versions instead of the old versions (even though at your first step).
*When I say "not neccessarily limited to"... I'm not trying to be tricky. This list is everything I remember doing when cloning a new web-app from an old one. It's possible I left something out; if so, it wasn't on purpose. But if I did, then so much the better... it will be an extra opportunity for you to practice some debugging skills and test your knowledge of servlets and JSPs.

Before going on, make sure that your new "cloned" web-app works just like the old one. To do that,. you need to compile your classes, and copy everything to the deployment directory. Here's an example shell script to do that. (Later, we'll talk about using tools like Ant and/or Eclipse to do this in a more sophisticated manner.)

#!/bin/sh
# deploy.sh
# P. Conrad for CISC474, 02.25.2005
# Example shell script to deploy application based on 
#  chapter 3 of "Head First Servlets and JSP" 

DEVELOP=/usa/username/cisc474/web_dev/beerDBV1
DEPLOY=/usa/username/cisc474/public_html/BeerDB-v1

MODEL=classes/com/example/model
WEB=classes/com/example/web

MODELSRC=src/com/example/model
WEBSRC=src/com/example/web

RESIN_HOME=/usa/username/cisc474/resin-2.1.16

CLASSPATH=$RESIN_HOME/lib/jsdk23.jar:$DEVELOP/classes:$DEVELOP:.

CP=/bin/cp

JAVAC=javac

MKDIR="/bin/mkdir -p"

$MKDIR $DEPLOY/WEB-INF/$MODEL
$MKDIR $DEPLOY/WEB-INF/$WEB

$JAVAC -classpath $CLASSPATH  -d $DEVELOP/classes $DEVELOP/$WEBSRC/BeerSelect.java
$JAVAC -classpath $CLASSPATH  -d $DEVELOP/classes $DEVELOP/$MODELSRC/BeerExpert.java

$CP $DEVELOP/$MODEL/*.class $DEPLOY/WEB-INF/$MODEL
$CP $DEVELOP/$WEB/*.class   $DEPLOY/WEB-INF/$WEB
$CP $DEVELOP/etc/web.xml    $DEPLOY/WEB-INF
$CP $DEVELOP/web/*.html     $DEPLOY
$CP $DEVELOP/web/*.jsp      $DEPLOY

echo "Deployed"

Next: add in access to a database....

Next, you need to add in the following into the web.xml file for your new app. This goes in the /etc directory of your development environment, and gets copied to the WEB-INF directory in the deployment environment.

This <resource-ref> element allows you to look up the database connection stuff from inside your Java code. We'll see an example of that inside the results.jsp file.

<resource-ref>
<res-ref-name>jdbc/pconrad</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<init-param driver-name="com.caucho.jdbc.mysql.Driver"/>
<init-param url="jdbc:mysql_caucho://localhost:8099/pconrad"/>
<init-param user="pconrad"/>
<init-param password="foobar"/>
<init-param max-connections="20"/>
<init-param max-idle-time="30"/>
</resource-ref>

Now add code in your result.jsp file to query the database. Note that this violates the MVC design, since in the original app it was the "model" code that was responsible for getting a list of "matching beers". The next step will be for you to research how to move this code to where it "should be", i.e. in the model part of the application.

Here is some sample jsp code that should do the trick:

<%-- result.jsp P. Conrad CISC474 Spring 2005 --%>
<%-- P. 87 "Head First Servlets and JSP, 1st Edition, O'Reilly 2004 --%>
<%-- View component of MVC-based beer advisor --%>

<%@ page import="java.util.*" %>

<%-- ************** FOR THE DATABASE STUFF ************************ --%>

<%@ page import="java.sql.*" %> <%-- for class DriverManager, and interfaces
                                     Connection, Statement, and ResultSet --%>

<!-- Import javax.naming.* for classes Context and InitialContext -->

<%@ page import="javax.naming.*, javax.sql.DataSource" %>

<%-- ************** END FOR THE DATABASE STUFF ************************ --%>

<html>
<body>
<h1 align="center">Beer Recommendations JSP (with double quotes)</h1>
<p>
<%-- This gets an attribute from the request object.
  This is a bit mysterious at this point (i.e. Chapter 3) the book
  promises to explain more later --%>
<%
 List styles = (List) request.getAttribute("styles");
  Iterator it = styles.iterator();
  while (it.hasNext()) {
  out.print("<br>try: " + it.next());
  }
%>
<hr>
<h2>Now, for some results from the database...</h2>

<TABLE BORDER="1">
<TR> <TH>name</TH> <TH>color</TH> </TR>
<%
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{

   Context ic = new InitialContext();
   DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/pconrad");
   conn = ds.getConnection();

   // get the color from the request using "getParameter" method
   String c = request.getParameter("color");

   st = conn.createStatement();
   rs = st.executeQuery("select * from beer where color='" + c + "'");

   boolean noData = true;
   while( rs.next() )
    {
     noData = false;

%>
     <TR> <TD><%= rs.getString("name") %></TD>
     <TD><%=      rs.getString("color") %></TD> </TR>
<%
     } // end while loop
   if (noData)
    {
  %>
      <tr><td colspan=2>No results found</td></tr>
<%
    } // end noData
  %>


</TABLE> <!-- normal end to table -->
</CENTER>
<%
  } // end of try clause
// Exception occurred: print stack trace and end table
catch (Exception ex){
  ex.printStackTrace();
%>
</TABLE>
</CENTER>
   Ooops, exception occurred: <%= "The exception:" + ex %>
<%
  } // end of catch clause

finally{
  if ( rs != null ) rs.close();
  if ( st != null ) st.close();
  if ( conn != null ) conn.close();
}
%>
 
</body>
</html>

There are a couple of more details that we will cover in lecture, including the fact that you need to reenter your password using set password = OLD_PASSWORD("mypassword"); (because of an incompatibility between this version of MySQL and this version of resin), and some stuff dealing with CLASSPATH and the Java Compiler. We'll cover that stuff in lecture.

Next Step

Can you move the database code into the model class where it belongs, so that the JSP file "doesn't know" whether the answers are coming from (whether that be a MySQL database as in this example, or hard coded as in the original Chapter 3 example, or are retrieved from XML files on another server, or whatever?) That is, can you restore the MVC design pattern?

Try that on your own. You may also discuss it among the members of your group, and/or between and among groups using the Discussion boards on WebCT and/or the chat room.
Then, make a "ProdDB-v1 application" based on your product from H02. This one should have the MVC design right from the start. That is what you'll turn in on or before Thursday April 24th.. but hopefully sooner, so that you can get started with the database component of Hilfbar.
Then, start working on your Hilfbar code. Put the database for your Hilfbar code into the account of the team member who has responsibility for the "database" part of your project. It is important that in your final project, that all team members have played an identifiable role in developing your final Hilfbar code.

Sometime this evening, send me one email per group with subject line "CISC474 TGnn 3/17", indicating the status of each group member w.r.t. this assignment, and also your Hilfbar app.

CISC474 H03b: Getting started with MySQL.

For reference, a reminder of your beer table

First step: Make BeerDB-v1 a clone of Beer-v1.

Next: add in access to a database....

Next Step

For reference, a reminder of your `beer` table