Lab 4 Setup

The setup required for this lab is more difficult than for previous labs. We are now integrating tomcat, and MySQL, and both programs must be configured to work with the other in order for the lab to work. Be very careful when following the instructions here and in the book, or you will give yourself no end of headaches when you start testing your servlet.

Environment Setup

In previous labs, we had you add more information to your .cshrc file. In this lab, we are going to ask you to remove those lines, and replace them with a single reference to a class environment setup file: source /afs/cats.ucsc.edu/class/cmps183-jw.s03/cs183.cshrc If you do not use the csh or tcsh, then you will have to add the aliases, path settings, and variables into your shell configuration on your own. If you are unfamiliar with this process, you can start a different shell simply by typing tcsh (or the alternate shell of your choice) at the command prompt.

To check your environment, open a new terminal (if you are using a CATS Ultra, you should use the button labeled This Host; the Console does not always process the environment files correctly). Type the command: which javac the answer should be /usr/j2se/bin/javac Type the command: printenv PATH you should see the following path somewhere in the output. /afs/cats.ucsc.edu/class/cmps183-jw.s03/mysql/bin Type the command: printenv CLASSPATH you should see /afs/cats.ucsc.edu/class/cmps183-jw.s03/jakarta-tomcat-4.1.24/common/lib/mysql-connector-java-3.0.7-stable-bin.jar somewhere in the output.

MySQL Setup

The starting point for your MySQL setup file can be found at: /afs/cats.ucsc.edu/class/cmps183-jw.s03/mysql.cnf You should copy this file to a file called .my.cnf in your home directory: cp /afs/cats.ucsc.edu/class/cmps183-jw.s03/mysql.cnf ~/.my.cnf You will have to edit this file to use MySQL correctly.

Port numbers: You must change the port number from 7000 to 70xx where xx are the same numbers you have in your tomcat port (ie. If your tomcat port is 9054, your MySQL port will be 7054). There are two instances of the port number to change.
User name: You must change all instance of my username, mslater, to your username. There are four instances of the user name to change.

To make sure you have changed all required instances, run the following commands. You should get no output from either one:

grep 7000 ~/.my.cnf
grep mslater ~/.my.cnf

Initializing MySQL

Once you have your environment set up, and the .my.cnf file properly configured, you can initialize your MySQL database. We have provided an alias for this process called: mysql_init You should only have to run this command once.

Now, you need to set the password for the database's root account (this is not the same as the root account for the machine you are on; it only applies to your database). To set this password, start the MySQL database with the alias we provided: mysql_start Then, change the password for root by typing this command: mysqladmin -u root password <new_password> where <new_password> is the password you want to use. This password should not be the same as your CATS password.

Starting and Stopping MySQL

As we saw in the last section, you will start the MySQL server with the command: mysql_start Similarly, you will stop the MySQL server with the command: mysql_stop You must stop the server before you log out or you risk losing all data!

Using MySQL

When the MySQL database is running, you can log into it and manually edit databases and tables with the mysql command: mysql -u root -p You will be prompted for your MySQL password (remember, this should not be your CATS password). Once you are logged in, you will see the mysql> prompt where you can enter queries, inserts, and updates. The MySQL web site provides an online Language Reference.

MySQL lab setup

The MySQL initialization step provided a database called test that we will use for the lab (rather than creating another one). Any tables you create for this lab should inclue your user name in the table name. For example, CREATE TABLE mslater_names ... You should create your table(s) before you try to write and run your servlet. You might also include a small list of initial names to display.

Configuring Tomcat to use MySQL

The book provides a good description of how you can set Tomcat up to access the MySQL database. However, because we have a rather unique setup here, there are a few changes and details you need to be aware of.

Database access must be configured in the server.xml file (which you have not had to edit until now). You can find your individual configuration file at $CATALINA_BASE/conf

You configure the database resource in the DefaultContext section, making your database available to every servlet. The initial DefaultContext does not have any children, so it ends with />. You should remove the / character, and add </DefaultContext> on the next line. It should now look like: <DefaultContext reloadable="true">  </DefaultContext>

The first thing you should do is define the database resource: <Resource name="jdbc/<Database_Name>" auth="Container" type="javax.sql.DataSource" description="This is my JDBC Connection to MySQL" /> Where <Database_Name> is the name by which you want to refer to your database. This name does not have to match the name of your table, or the name of the database within the MySQL server. It will only be used within Tomcat to correlate references to the database.

To add the database parameters, you use the following code: <ResourceParams name="jdbc/<Database_Name>"> <parameter> <name>factory</name> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value> </parameter> <parameter> <name>url</name> <value>jdbc:mysql://localhost:<PORT>/test</value> </parameter> <parameter> <name>driverClassName</name> <value>com.mysql.jdbc.Driver</value> </parameter> <parameter> <name>serverName</name> <value>localhost</value> </parameter> <parameter> <name>databaseName</name> <value>test</value> </parameter> <parameter> <name>port</name> <value><PORT></value> </parameter> <parameter> <name>username</name> <value>root</value> </parameter> <parameter> <name>password</name> <value><MySQL_PASSWORD></value> </parameter> </ResourceParams>where <Database_Name> is the name of the database you gave in the <Resource ...> section, <PORT> is the port number you set in the .my.cnf file, and <MySQL_PASSWORD> is the password you gave when configuring the database.

Servlet Setup

With the setup given here, there is no additional setup needed in the servlet's web.xml file. You can configure it as you have for your last two labs. The only caveat here is that you cannot use the same URL name to refer to your servlet as you use for your database. So, if you called your database jdbc/name, you can not call your servlet /name in the <servlet-mapping> section of the servlet's web.xml file.

Writing the Servlet

The book provides two excellent examples of database access from a servlet. The first is on pages 168 - 170, and the second is on pages 172 - 174. You should structure your servlet in the same way. Note that you use the PreparedStatement class to issue both queries and updates to the database, however if you are querying, you call executeQuery() to run the command, and if you are updating, you call executeUpdate() to run the command.