VideoTape Rent Application Project

                       Wenzhong Cao


   I have made an VideoTape rent application through web interfaces.
Through the web pages, customers can reserve the available tapes
interesting for them. Such simple project can be visited at my homepage's
subsite www.cs.fsu.edu/~wcao/bighit.

   In order to use Java to link databases for video tape rent, the server
need to contain the database connection class. Bellow list out the main
database connection parts. The first thing need to do is establish a
connection with the DBMS we want to use. This involves two steps: (1)
loading the driver and (2) making the connection. 

   You can see the step 1 is on the code line
         Class.forName(theDriver);
The variable "theDriver" is given by request, like "jdbc.DriverXYZ" or
"sun.jdbc.odbc.JdbcOdbcDriver".

   The step 2 is on code line
        theConnection = DriverManager.getConnection(DBUrl);
or
        theConnection = DriverManager.getConnection(DBUrl, connectInfo);
See the connection codes. 

------------Begin:  Database connection -------------
public class DBConnect
{
   /**
    * ... ... the variables declaration omitted here
    */
   public Connection makeConnection()
   {
      messages = new StringBuffer("");
      /**
       * check for existing connection
       *  if already connected, don't need to do further 
       *  while return this connection
       */
      if (theConnection != null) return theConnection;

      /**
       * Connect the required database
       *  first of all, the database driver should exist
       */
      DBUrl = url + database;
      try
      {
         Class.forName(theDriver);
      }
      catch(Exception e)
      {
         messages.append("Message[" + (numMessages++) + "]: (DBmanager)");
         messages.append(e.getMessage() + "\n");
         return null;
      }
      Properties connectInfo = null;

      try
      {
         if (connectInfo==null){

            theConnection = DriverManager.getConnection(DBUrl);
         }
         else {
            theConnection = DriverManager.getConnection(DBUrl, connectInfo);
         }
         DatabaseMetaData meta = theConnection.getMetaData();
      }
      catch(Exception e)
      {
         e.printStackTrace();
         return null;
      }

      /**
       * Everything is correct, then give connection
       */
      return theConnection;
   }
} 
------------End:  Database connection -------------

  For security, the database server can require identity for accesser
like following code. 

    ------------Begin: secure request -------------
    if (userID != null) {
       connectInfo = new Properties();
       connectInfo.put("user", userID);
       if (password != null) connectInfo.put("password", password);
    }
    else {
       messages.append("ID with password is required.\n");
       return null;
    }
    ------------End: secure request -------------

   Before we request for the connection, we can make a middle servlet media
to extend the DBMS with suitable request identity and the target. For the
video tape rent applications, the following codes can be used as such
servlet.

------------Begin: secure request middle servlet -------------
public class BigHitDBConnect extends DBConnect {
   public BigHitDBConnect () {

       super();

       /**
        * let url = "jdbc:z1MySQL://enp01.enp.fsu.edu:3306/"
        */
       setUrl("jdbc:z1MySQL://enp01.enp.fsu.edu:3306/");

       /**
        * let database = "bighit"
        */
       setDatabase("bighit");

       /**
        * let userID = "wenzhong"
        */
       setUserID("wenzhong");

       /**
        * let password = "password"
        */
       setPassword("password");

       /**
        * let driver = "twz1.jdbc.mysql.jdbcMysqlDriver"
        */
       setDriver("twz1.jdbc.mysql.jdbcMysqlDriver");
   }
}
------------End: secure request middle servlet -------------

   In the video tape rent application, we need to connect the related
database in the check out (just rent) request as follows before showing
up the available tapes for reserve.

    ------------Begin: DB connection for Video tape reserve -------------
    public class VideoTapeReserve() {

      protected DBConnect db;
      protected Statement stmt;
      HtmlWriter htmlOut;
      java.util.Hashtable request;

      public void init() {
        db = new BigHitDBConnect();
        db.makeConnection();
        stmt = db.getStatement();
      }
    }
    ------------End: DB connection for Video tape reserve -------------

   When a customer visit the video tape rent interface (you can take a
look at www.cs.fsu.edu/~wcao/bighit), the available tape should appear.
This appearance should be loaded from the database storage. We can get the
information with SQL query like bellow. 

    ------------Begin: send available Video tape for reserve -------------
    public class VideoTapeReserve() {

      public void makeReservation (Customer customer, String storeId) {

        // form to allow selection of video to reserve

        String movieSQL = "select v.videoId, title, m.movieId from "
            + " Movie m, Videotape v " + " left join Rental r "
            + " using (videoId) "
            +" where m.movieId=v.movieId "
            + " and storeId="+storeId
            + " and isnull(r.videoId) "
            + " order by title ";
        videoTapeAvailable = stmt.executeQuery(movieSQL);
      }
    }
    ------------End: send available Video tape for reserve -------------

   The above description about database connection, database driver
request and SQL execution is just the mandatory requirement for linking 
and getting needed database information. The above is also the main parts
of Java link to database for project video tape rent.