Using MySQL for CS320

This document describes how to set up a MySQL database server on your local computer for development, and how to use the MySQL server on CS3. A screen capture video demonstrating the process is available here.

1. Software Installation

Install the MySQL database server as described in MySQL Database Server Installation.

For client software, you can use the command line client mysql, which comes with the MySQL Database Server package. If you prefer a GUI client, download and install MySQL Workbench.

2. Post Installation Setup

The purpose of this step is to create a local database setup that matches the one on the CS3 server, so you can run the same servlet/JSP code on both your local computer and on CS3 without any changes.

Download the mysql-postinst.sql script. This script performs the following operations:

• Create a new database cs320stu31.
• Create a new user cs320stu31 with password abcd, and grant the user full privileges on the database cs320stu31.
• Create a table items with two records in the cs320stu31 database for testing purposes.

Edit the script so that the database name, the username, and the password match the ones for your account on the CS3 server, then run the script on your local MySQL.

3. Testing the Local Database Setup

If you have set up your development environment as described in Web Development with Eclipse and Tomcat, you would already have the MySQL JDBC driver installed; otherwise you will need to install the driver first.

Create a servlet HelloJDBC.java. Use HelloJDBC.java as a template, and modify the source code so the database name, username, and password match your setup. Compile and run the servlet, and you should see following output:

milk 3.89 2.0
beer 6.99 1.0

Create a JSP page HelloSQL.jsp. Use HelloSQL.jsp as a template, and again, modify the database name, username, and password to match your setup. Run the page, and you should see:

name	milk
price	3.89
quantity	2
name	beer
price	6.99
quantity	1

4. Testing Database Access on CS3

Connect to your MySQL database on CS3.

Create a table items, then insert two rows into the table as follows:

create table items (
	    name varchar(32),
	    price decimal(8,2),
	    quantity int
	);

	insert into items values ('milk', 3.89, 2);
	insert into items values ('beer', 6.99, 1);

Deploy your HelloJDBC servlet and HelloSQL.jsp on CS3, and they should produce the same results as in the previous step.