February 3, 2011

Part 2: The ModelingAgency Application

Part 1: The Setup

To see how Tomcat, MyBatis and MySQL work together, we're going to create a small Java web application in Eclipse called ModelingAgency.

The Modeling Agency web app does the following:

  • It lets a photographer (a client object) book a model (a model object) for a photography shoot at a particular place and time (a booking object).

The Interface

Our interface is going to look something like this when we're done:

You can think of the application as an appointment organizer for photographers. I'm sure this will make more sense when you see the actual tables.

The Tables

The client table

The model table

The booking table

The SQL

Here is the sql to create the MySQL database for our ModelingAgency, and populate with the data above:


create database modeling_agency;
use modeling_agency;
grant all on modeling_agency.* to 'test'@'localhost' identified by 'test' #feel free to rename this to whatever you want

CREATE TABLE modeling_agency.client
(
  client_id INTEGER NOT NULL AUTO_INCREMENT,
  client_name VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(client_id)
);

CREATE TABLE modeling_agency.model
(
  model_id INTEGER NOT NULL AUTO_INCREMENT,
  model_name VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(model_id)
);

CREATE TABLE modeling_agency.booking
(
 booking_id INTEGER NOT NULL AUTO_INCREMENT,
 booking_client_id INTEGER,
 booking_model_id INTEGER,
 booking_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 booking_location VARCHAR(100) NOT NULL DEFAULT '##NO LOCATION SELECTED##', 
 PRIMARY KEY (booking_id),
 CONSTRAINT `FK_booking_client` FOREIGN KEY (booking_client_id)
  REFERENCES modeling_agency.client (client_id),
 CONSTRAINT `FK_booking_model` FOREIGN KEY (booking_model_id)
  REFERENCES modeling_agency.model (model_id) 
);

insert into modeling_agency.client (client_name) values ("Michelangelo");
insert into modeling_agency.client (client_name) values ("Rafaelo");
insert into modeling_agency.client (client_name) values ("Leonardo");
insert into modeling_agency.client (client_name) values ("Donatello");

insert into modeling_agency.model (model_name) values ("Monalisa");
insert into modeling_agency.model (model_name) values ("Plain Jane");
insert into modeling_agency.model (model_name) values ("Funky Susan");
insert into modeling_agency.model (model_name) values ("Mary");

insert into booking (booking_client_id,booking_model_id,booking_location) values ((select client_id from client where client_name = 'Michelangelo'),(select model_id from model where model_name = 'Monalisa'),"Somewhere in Italy");
insert into booking (booking_client_id,booking_model_id,booking_location) values ((select client_id from client where client_name = 'Leonardo'),(select model_id from model where model_name = 'Plain Jane'),"Near the river");
insert into booking (booking_client_id,booking_model_id,booking_location) values ((select client_id from client where client_name = 'Donatello'),(select model_id from model where model_name = 'Mary'),"On the beach");

Note:Keep in mind the mysql test user we setup in the SQL above, as it will be the same you will use when you configure how Tomcat will work with your database

Now we're ready to setup the ModelingAgency project as a DynamicWeb project in eclipse.

Part 3: Creating the ModelingAgency project in Eclipse