In this lab, you will be using the MySQL database server both through its command line interface, mysql, and through JDBC commands on JSP pages. Start by logging in to one of the cslab computers, or onto your own computer using VNC or remote desktop. If you are working on a cslab computer, you should check with me about which machine to use before logging on. (This is so that we can avoid having too many servers running on one machine.) MySQL is already running on each of the cslab computers. If you are using your own computer, you can install a copy of MySQL on your computer, or you can set your JSP pages to access MySQL on one of the cslab machines. If you need some review about how to run your own Tomcat server, refer to the first lab.
Part 1 of this lab should be completed and graded in class. The remainder is due next Friday at the beginning of class. You should turn in a printout of any material you write for Parts 3 and 4. If you are using a cslab machine, you should note which cslab computer contains your databases. If you are using your own computer, you should be prepared to demonstrate your work running on your computer at the beginning of class on Friday. You should also turn in your short write-up from Part 5.
In this part of the lab, you will create a user and a database on the MySQL database server. As soon as you have done this part of the lab, show me the working database.
The MySQL database servers on the cslab machine have a single user, named root with a password that will be announced in class. It's not a good idea to use the root user, who has total control of the server, for day-to-day tasks, so the first thing you should do is log in as the root user and create another, non-root user. Use the command-line tool mysql. Open a console window and give the command
mysql -u root -p
The "-u root" says that you want to log into MySQL with user name root, and the "-p" says that you will enter a password. You will be asked to enter the root password. After logging in successfully, you can give mysql commands. You want to create a user and at the same time specify that the user has control of a certain database. To do this, give the command
GRANT ALL PRIVILEGES ON databasename.* TO username@localhost IDENTIFIED by "password";
You can select the databasename, username, and password. The ".*" means that the user will control all the tables in the database. Note that the database does not even exist at this point. The "@localhost" after the user name means that the user must access the database from the same machine on which MySQL is running. This is OK if you are running the Tomcat server on the same machine. If you want to allow access from any computer, replace username@localhost with username@"%". The "%" is a wild card that matches any host. (The quote marks here are required in the input!).
Later, if you want to give the same user access to another database, you can use the same command, but leave off the "IDENTIFIED BY" part, since the user has already been assigned a password.
As soon as you have created the user successfully, exit from mysql using the exit command.
Now, log in as the new user with mysql -u username -p. Once in mysql, create the database with the command
CREATE DATABASE databasename;
To say that you want to work with this particular database, say
USE DATABASE databasename;
Now, you are ready to create a table in the database. This is the table that you will be working with in the rest of the lab. It is a table that stores ratings for various items. The table has 7 columns. Here is a CREATE command that will create the table, with table name ratings:
CREATE TABLE ratings (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
count1 INT NOT NULL,
count2 INT NOT NULL,
count3 INT NOT NULL,
count4 INT NOT NULL,
count5 INT NOT NULL,
INDEX(name)
);
The AUTO_INCREMENT allows the id field to be automatically assigned a unique value, if no other value is specified. The INDEX(name) would make it more efficient to search the database for an item with a given name. The VARCHAR(60) is a type that allows the value to be up to 60 characters long.
Now, insert some items into the table. (You should decide on what types of items you want to rate---movies, actors, restaurants, beers,...) Only the name of the item needs to be specified.
INSERT INTO ratings (name) VALUES ("item name");
Where it says (name) here, you would ordinarily have a list of several column names, and you would provide a value for each of the specified columns. (Or, if you want to provide a value for every column in the table, you can leave out the list of column names altogether.)
You should use the INSERT command several times, to insert at least six items in the table. You can check the contents of the table with a SELECT command:
SELECT * FROM ratings;
You should see one row for each line that you have inserted. At this point, you should show me that you have successfully created the table.
The next step is to access your database from Tomcat. You can try this using the JSP MySQL interface page, mysql.jsp, that was handed out as an example in class. You can find a copy in the file /home/cs371/mysql.jsp. Copy this file into the ROOT directory of your Tomcat server's webapps directory. Start Tomcat. (On a cslab machine, use the command "catalina.sh run" in Tomcat's bin directory; in Windows, use the command "startup.bat".) Try accessing mysql.jsp with a URL of the form http://cslabxx.hws.edu:8080/mysql.jsp. Once you've done that, try accessing your database and sending it the same SELECT command that you used above to view the table.
(If you are using your own computer and if you get an error because of a missing JDBC driver, you need to install the JDBC driver for MySQL. The easiest place to put it is in your basic Java installation, where it will be accessible both to your compiler and to Tomcat. Goto the Java installation directory, and then into the directory jre\lib\ext. Copy the file mm.mysql-2.0.8-bin.jar into this directory. You can get a copy from the Murach CD; there is also a copy in the directory /home/cs371 on math.hws.edu.)
Now, it's time to create your own page that will let users enter their ratings for items in your database. You have several options. One is to use an HTML page with a form, and have that form submit its data to a JSP page or to a servlet for processing. Another is to have a single JSP page that submits the data to itself in the same way that the login page did in the previous lab. Also, you can decide whether you want to work in the Tomcat ROOT directory or create a new Web application specifically for ratings.
In any case, you will have a Web form that looks something like this non-functional version:
(1 is the highest rating; 5 is the lowest)
You could look at the HTML source of this page, at http://math.hws.edu/eck/cs371/lab3.html, to see how this form was created. Remember in particular that all radio buttons in a group will have the same name, which identifies the group, but will have a different value. In the request object that contains the data submitted from the form, The parameter value associated with the name tells you which button was selected. If no button in a group was selected, then the parameter value for the group name will be null. You can make life easy on yourself by choosing names and values intelligently. (In my form, I use an item ID number for the name, and I use a column number for the value.)
When the user submits the data from this form, the user's input should be recorded in your ratings database, as we discussed in class. For each rating that the user has submitted, you should send an UPDATE command to the database. For example, if the user gives a rating of 2 to the item with an id of 7, the corresponding command would be
UPDATE ratings SET count2 = count2 + 1 WHERE id=7;
Of course, you have to construct the command from the actual data received from the user.
If you want extra credit for this part of the lab, create the form on the first page dynamically, based on the data in the ratings table. That is, read the item id's and names from the database and create a form that lets the user rate each of the items.
Of course, you need another JSP page if you want to view the ratings that have been entered into the database. Write a JSP page that can be used to view the data in the ratings table. You can send the query "SELECT * FROM ratings" to the database to retrieve all the data as a ResultSet. Then, just display the data on the page. (This is pretty easy if you just follow the pattern used in the mysql.jsp file.)
For the rest of the term, in addition to lab exercises, you should be working on a final project. The project is to create a Tomcat Web site. The site should have users, with users and passwords (and possibly their preferences) stored in a database. You might want to allow anonymous uses access to part of the site, but there should be some features that only users have access to. You might want to have different types of users, who would have access to different capabilities. For example, there might be an administrative user who can create other users.
Of course, the major decisions that you have to make is what your Web site will be about and what features it will have. It's time to start making decisions!
As part of the assignment for this lab, you should develop a short, initial plan for your Web site. The plan should not be more than one page. The plan should describe the features that will be offered to users. Some possibilities include message board, a ratings or recommendation service of some type, on-line polls, some sort of trading or auction site, news aggregation, leaving messages for other individual users, customizable pages, photo gallery, and lots other things I haven't thought of...
Strong suggestion: Keep a file that contains all the CREATE statements that you use to make tables for your database, so that it can be recreated if necessary.