Note that there are two books on MYSQL in the monitor room: one is a tutorial and the other is a language reference. Additionally, there is documentation for MySQL on the web as well as a tutorial, also linked from the main MCS-270 page.
In particular, start by replicating the example I gave. As
described below, you will create a database (which must start with
your username, followed by and underscore and the rest of its name) on
mcs-jsp.gac.edu, and connect to it using
mysql, the interactive SQL interface. This is done with the two
commands below, each of which will prompt you for for your username
and MySQL password. Note that your password should be your MySQL
password, which you selected yourself, not your usual login password.
Following are the commands to create and connect to your database. The first command connects to the mysql server. (Here, you'll be prompted for your mysql password. ) The second command and third commands are used to create a database and begin using it. Once it's created, the database should remain in place between connections to the server until you (or root) drops the database.
mysql -h mcs-mysql.gac.edu -u dummy -p create database dummy_movies; use dummy_movies;Naturally, you'll want to replace the occurences of 'dummy' above with your username, and you have permissions to create any database starting with your username and an underscore. While you could
-h mcs-jspargument), I'd rather you run
mysqlfrom the lab machine.
Now use SQL commands to create the movies, persons, and acts_in tables, put the data into the tables, and do a couple sample queries to make sure it works. To put the data in, you can use commands like the following:
load data local infile '/Net/gac/home2/k/a/karl/public/270/movies/persons.data' into table persons; load data local infile '/Net/gac/home2/k/a/karl/public/270/movies/movies.data' into table movies; load data local infile '/Net/gac/home2/k/a/karl/public/270/movies/acts_in.data' into table acts_in;(It appears
~karlis only expanded when running
mysqllocally on the host server
Having reproduced the basic movie database functionality I demonstrated in class, you are now to extend it so as to track the specific tapes that our movie store chain owns, which are copies of the movies listed in the movies table. Those tapes may be owned by either of our two stores (St. Peter and Mankato, with the possibility of more stores later), and may be checked out to any of our customers or checked out to no customer, i.e., in the store. To track this information, add two tables:
Now, here is the crux of the assignment: formulate a SQL query to find what movies store X has one or more copies of on the shelf that include actor Y. For example, you might want to know what movies with Claude Rains in them are currently available (not all rented out) at St. Peter.
This part should be started after I talk about JDBC in class. You may want to look the two JDBC examples I gave (Introductory JDBC Example and JDBC Prepared Statement Example).
Once you have this working in mysql, you can try programming it in Java using JDBC. First you should configure your environment for loading the necessary JDBC classes to access mysql, by giving the following commands to the shell. (This assumes you are using csh or tcsh as your shell.)
setenv KARL ~karl setenv CLASSPATH $KARL/public/270/mysql.jar:.or if you are running
export CLASSPATH=/Net/solen/home/w/o/karl/public/270/mysql.jar:.(To have these commands done each time you start a csh or tcsh, you can put them in the file
.bashrc) Now you should be able to run the Java programs that make JDBC access to MySQL.
I am providing a skeletal Java client program, which provides the graphical user interface (primitive, admittedly) but not the JDBC calls to actually get the data. (Instead, it has two fixed sample movies that it always claims are the answer, just to show how the output should be done once you retrieve the real movies.) The code is linked from the web version of this lab handout. You should save it in a file called FindActor.java, because the class is named FindActor. To compile and run it, you would use the commands
javac FindActor.java java FindActorTry it first as is, then add the JDBC calls to access your MySQL database for the answers. (You should be able to confine your attention to the DatabaseController class. In addition to modifying its getMovies method, you may want to add one or more instance variables and a constructor to initialize them.) Your code should be based on the SQL query you directly used in the prior portion of the lab. Getting the query to work directly in mysql will get you most of the credit for the lab, but for full credit you need to get it to work from Java as well. Be sure your code works for Peter O'Toole, despite the apostrophe in his name. The easy way to do that is by using a PreparedStatement.
If you are looking for a little extra to do, you can rectify one of the FindActor program's shortcomings. Namely, it has hard-coded into it the names of the two stores (St. Peter and Mankato). This obviously will be a problem as the chain expands - we'll need to upgrade the client software and redistribute it to all the machines it is installed on. Instead, you should have FindActor query the database to find the names of the stores.
Lab2, now type: