Lab is due Sunday at 11:59pm. You must have an account on the CS MySql account.

Introduction: SELECT queries using Java

In Future labs and homeworks you will be writing JSPs which will query databases on behalf of a web client. In this lab you will learn how to use Java to query a MySQL database, and print the extracted information as an HTML table which can be placed directly in a HTML page.

I have written a Guestbook database, which stores records produced by visitors to my web page. So far, several of my favorite Looney Tune characters have already visited

Last	First	IPAddress	Timestamp	Message
Bunny	Bugs	0.0.0.0	20060504072253	What's up doc?
Duck	Daffy	0.0.0.0	20060504072456	Thuffer'in Thuccotash!
Runner	Road	0.0.0.0	20060504072954	Meep! Meep!
Pig	Porky	0.0.0.0	20060504074445	Bbbbbbbbut seriously now.
Coyote	Wile E.	0.0.0.0	20060504073201	As a representative of Acme, I invite you to dine with me on a fricasse of RoadRunner.
The Martian	Marvin	0.0.0.0	20060504074154	I will de-polarize you with my mighty megamalodorous minimizing infrared Acme-powered disintigrating ray gun.
Fudd	Elmer	0.0.0.0	20060504074247	wascawwy wabbit

This table was produced by a Java class, ResultTable, which you will write this week. This class accepts a SQL SELECT query,

SELECT * FROM Guestbook;

then passes this query to my MySQL database, Guestbook, which returned the data. The ResultTable class turns this data into an HTML table with the help of some class code you have already written for creating HTML tables.

Evaluating Your Lab

Warning: I will have access to you MySQL account password!! I promise to ever modify your account or access any database without your permission. For this lab I will be making queries to your Guestbook table, which you will be setting-up.

Your lab will be evaluated on the following characteristics

1. Testing: All classes you write must include a main routine and are thoroughly tested, including exceptional cases.
2. Javadoc comments: You must appropriately comment your ResultTable class.
3. Style: the ResultTable.getTable() method is the most complicated Java you have written for a lab. Consider creating private helper function

          private return-Type helper();
       

   to perform tasks and shorten the length of your method body. If this method runs over 20 lines (which it might) you need to create helper functions to take over some of the tasks.

Creating a MySQL Database

You will need to create your own Guestbook MySQL database. You may use the commandline MySQL server (which I prefer) or the window server (which requires X-Windows if you are on a remote server.)

• commandline:

  	mysql -h dbserver -u username -p
      

• window:

  	mysqlcc
      

Before you begin, follow these steps, to inform MySQL the Java datatypes

1. mysqlcc, to start MySQL window server.
2. Go To Options -> General
3. Select SQL Editor
4. Enter /mysqlcc-0.9.4-linux-glibc22/syntax.txt for the Syntax File.
5. Apply.

Now you are ready to enter data.

Your Guestbook will have five fields

1. Last, varchar(10)
2. First, varchar(10)
3. IPAddress, varchar(15)
4. Timestamp, TIMESTAMP
5. Message, varchar(255)

and will have seven entries, matching exactly those in my table. I have found that this MySQL tutorial is a very nice introduction to using the commandline mysql, MySQL server.

Creating your Java Project

Your lab6 Java project will contain two packages:

• html, which you have already written for earlier homeworks, and contains code for creating HTML tables.
• sql, partly written by me, and contains code for creating and formatting SQL queries.

Follow these steps to prepare to prepare your project

1. Create your lab6 project, and make sure it is Java 1.5 compliant.
2. Import your html package.
3. Import the sql package.
   1. Download sql
   2. Unpackage

      	    tar xfz sql.tgz
      	

   3. Import the sql package.
4. Add the Driver class to the Eclipse Build Path.
   1. Create a directory jdbc in your home directory
   2. Download the driver library files MySQL driver library.
   3. Move this .jar file to the jdbc directory.
   4. In Eclipse, Select Project -> Properties
   5. Select Java Build Path in the Property Wizard.
   6. Select Add External Jars, and browse to jdbc/mysql-connector-java-3.0.6-stable-bin.jar.
   7. Add this jar. Your Package Explorer should display this new library, along with the JRE System Library, and your sql and html libraries.

Creating the SelectQuery Class

You will be finishing the SelectQuery class I started. Recall, that in SQL the simplest select query is

	SELECT columns FROM table;

where

• columns is either Star, *, or a comma-separated list (a ColumnList) of Column names (of type String).
• Table is the name of a table in your database, which we will represent as a String.

You will need to write two methods in the sql package:

1. ColumnList.toString()
2. SelectQuery.toSQL()

Creating the ResultTable Class

The ResultTable class is created with a SelectQuery object, and will return an HTML Table object whenever the method getTable is called. Each call to this method must make a new query to the database, since the database table may have been updated. The class contains the following

• private SelectQuery query;
• private Connect connect, this is a Connect object created by the Connect class I created in sql.
• public ResultTable(SelectQuery q);, constructor which accepts the SelectQuery and creates a Connect object.
• public Table getTable();, makes a fresh query to the MySQL database and returns data formatted as an HTML Table object. The first row of the table consists of the column names, formatted using the <TH> tag.
• public String getQuery();, return SelectQuery formatted as a String, the SQL query command.

You will need to modify the Connect class to store your username and password

    final String USERNAME = username;
    final String PASSWD = password; 

This class establishes a remote connection with the MySQL database server dbserver. You use the Connect object by calling its method getStatement(), which returns a java.sql.Statement, which is used to send queries to dbserver, and return ResultSet.

You pass a SQL query to dbserver by

String query = "SQL SELECT query";
ResultSet rs= stmt.executeQuery(query);

The return value is a ResultSet. This object stores all the rows returned by the database. You can get a new row of data using the command

   boolean rs.next();

which returns true if there is a next row. Initially, there is no row stored in ResultSet, so you must call next(). You get columns using the command

   Type getType(int column);

where Type is the Java Type corresponding to the column entry, and the argument is the column number (Numbering starts at 1!!.) You will find examples in the Java API for ResultSet.

Your Guestbook is only a example to test your ResultTable code. In general you will have no idea how many rows and columns are contained in the database table. You can find-out the number of columns, and their names through the ResultSetMetaData object

   ResultSetMetaData rsmd = rs.GetMetaData();

There are methods for determining the number of columns and their names, but I leave this for you to discover.

There is one other problem. There is no acceptable way to determine how many rows were returned by your query, but your Table constructor requires a fixed-length Array. I recommend you modify your HTML Table code so that it can accept a variable length LinkedList<TR>. It is also possible to convert a LinkedList to an array, the Java API documentation specifies how.

Exception Handling

There are many different possible Exceptions thrown. One of the most common is SQLException, but the Class.forName() method in Connect throws several as well. All your methods will pass Exceptions along. This means that will not try to catch exceptions in the body of your code, but you will have signify that the method throws the relevant methods. To see an example of this, look at Connect.Connect() (the constructor method.) Fortunately, Eclipse will give you an error indicator, and suggest adding a throws to the method; accept this suggestion, and Eclipse will take care of all the work.

Your main test program will have to catch raised Exceptions. The easiest way is to use

try {
   // Java code
} catch (Exception e) {
   System.err.println(e);
}

You will need to test ResultTable with exceptional conditions. You should try passing non-existent tables and non-existent fields in your SelectQuery object. See how the MySQL server responds to these erroneous queries.

Handin

Your lab is due Sunday at 11:59pm. You will submit your Pizza project folder. You must be inside this directory to submit. See handin.