Using MySQL for CS320

This document describes how to setup 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 whole process is available at

1. Software Installation

Download MySQL Community Server binary release from the MySQL Community Server Download Page. There are several packages under the "Windows downloads" section. It's recommended that you download the one called "Windows Essentials". This package comes in as a single file mysql-essential-5.0.xx-win32.msi. Once you have the file, you may start the installation by double-clicking on the file, and follow the on-screen instructions. During the installation process, you may leave all the options to default. After the installation is completed, the MySQL configuration wizard will walk you through the configuration of the database server:

You may leave all other options to default.

Download MySQL GUI Tools from the MySQL GUI Tools Download Page. There are a couple of packages under the "Windows downloads" section. It's recommended that you download the one called "Without installer". After downloading the file, unzip it to a local directory, e.g C:\MySQL GUI Tools 5.0.

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.

First, download the mysql-postinst.sql script. This script performs the following operations:

  1. Create a new database cs320stu31.
  2. Create a new user cs320stu31 with password abcd, and grant the user full privileges on the database cs320stu31.
  3. 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.

To run this script, double-click on C:\MySQL GUI Tools 5.0\MySQLQueryBrowser.exe, and enter the following information at the startup screen:

Then load the script (File -> Open Script ...) and execute it (Script -> Execute or the Execute button).

3. Testing the Local Database Setup

If you have set up Tomcat as described in Setup a Development Environment for CS320, you would already have the MySQL JDBC driver under the lib folder of the Tomcat directory; otherwise you will need to install the driver first.

Create a servlet Use 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

Use MySQL Query Browser to 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.