Java Tutorial - Java Scipt :
Creating a Test Database
Before configuring Tomcat, we need a database. For our purposes at this time, a simple database will suffice, so we will create a test database called quotes. Quotes has four columns:
id. An integer to serve as a primary key
author. A name attributed to the quote
quote. A quote
stardate. An optional string representing the time or circumstances when the quote might have been said
First, we need to create a database. The following lines will do this in MySQL.
CREATE DATABASE mysqltest;
USE mysqltest;
The first line creates the database; the next one makes sure it is the database we are using. We don’t need to do this for HSqlDb because it automatically creates a database based on the connection URL. Next, we create the table in the database using the following SQL script:
CREATE TABLE quotes(
ID INTEGER PRIMARY KEY,
AUTHOR VARCHAR(64),
QUOTE VARCHAR(128),
STARDATE VARCHAR(64));
The next script loads some test data into the table:
insert into quotes (id, author, quote, stardate)
VALUES (1, ‘Dorothy’, ‘This does not look like Kansas
insert into quotes (id, author, quote, stardate)
VALUES (2, ‘Hamlet’, ‘2B or !2B’, ‘When IMing’);
insert into quotes (id, author, quote, stardate)
VALUES (3, ‘Macdonald’, ‘E I E I O’, ‘When bitten’);
insert into quotes (id, author, quote, stardate)
VALUES (4, ‘Marcel Marceau’, ‘Nothing’, ‘Most of the time’);
This adds four records into our test database. These scripts can be executed either through the MySQL command-line tool or by using tools such as Squirrel, the MySQL Control Panel, or even the HSqlDb-provided database manager. The scripts are designed to be compatible with both databases. We may want to add specific users and grant them privileges to access the database. In MySQL, this is done with the following command:
GRANT ALL PRIVILEGES ON *.* TO tomcat@localhost
IDENTIFIED BY ‘passwd5’ WITH GRANT OPTION;
If you are using HSqlDb, then you need to use the following commands to create a user and give the user access to the table:
CREATE USER tomcat PASSWORD passwd5
GRANT ALL ON quotes TO tomcat
Now that we have a database, we need to be able to access it through a servlet. To do this, we need to configure a DataSource in Tomcat. There are two ways to do this, through the Tomcat Admin tool or by editing Tomcat’s configuration file server.xml.
