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 http://sun.calstatela.edu/~cysun/www/videos/cs320-mysql.wmv.

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:

In the 1st screen, choose Standard Configuration
In the 2nd screen, check Install As Windows Service.
In the 3rd screen, enter a new root password, and leave both Enable root access from remote machines and Create An Anonymous Account unchecked.

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:

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.

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

Server Host: localhost
Port: 3306
Username: root
Password: <the root password you created during MySQL server installation>
Default Schema: mysql

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 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

Use MySQL Query Browser to connect to your MySQL database on CS3:

Server Host: cs3.calstatela.edu
Port: 3306
Username: <your account name, e.g. cs320stu31>
Password: <your password>
Default Schema: <your database name, e.g. cs320stu31>

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.