Homework SourseServlet and JDBC Programming Make a moviehtml allowing the u
Servlet and JDBC Programming
Make a movie.html allowing the user to search information about actors, dierctors, and movies. Using servlets and JDBC
In this project you will create a web application to get information about actors, directors and movies. You must use the MySQL database called imdb (see below) to retrieve the information about actors, directors and movies.
A README.txt file providing the complete URL to use when invoking your application.
Project description:
Your application must have an HTML file called movie.html. This html file has an HTML form. The user should be able to use the HTML form to specify the their items of interest (i.e. actors, directors or movies) they need to get information about. You may use text fields and buttons as form elements. After the form has been submitted by the user, the application should retrieve the information about the selected item from the imdb database, and return the response HTML page with a listing of all information about the selected item.
In case a user enters an unknown term, the application should return an error page with a suitable error message.
It is your responsibility to discover the column names and create a suitable SQL query to retrieve the required data. For example, you may use the mysql command to connect to the imdb database and then discover the table names, as well as the defined columns and their types.
Your application must utilize a Java servlet and an HTML form.
How to use MySQL database
You require to download and install MySQL available at here.
You can connect to the MySQL database using the default username/password (root/given_password):
Create a database and call it imdb.
Import the this imdb database to your newly created imdb database.
Check to make sure the imdb database has been populated successfully.
Solution
Creating tables for movies and actor and directors:
create table Movie_info (
id integer not null primary key,
title varchar(150),
rating varchar(20),
len integer,
rDate date);
create table Person_info (
id integer not null primary key,
name varchar(150));
create table Director_info (
person_Id integer not null,
movie_Id integer not null,
primary key (personId, movie_Id),
foreign key (personId) references Person_id(id),
foreign key (movieId) references Movie_id(id));
create table Actor_info (
personId integer not null,
movieId integer not null,
characterName varchar(255),
primary key (personId, movieId),
foreign key (personId) references Person(id),
foreign key (movieId) references Movie(id));
insert into Movie (id, title, rating, length, releaseDate)
values (1, \'xxxxxxxxxxxxxxxxxxxxxx\', \'G\', 84, \'xxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (2, \'xxxxxxxxxxxxxx\', \'PG\', 85, \'xxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (3, \'xxxxxxxxxxxx\', \'G\', 75, \'xxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (4, \'xxxxxxxxxxx\', \'G\', 77, \'xxxxxxxxx\');
insert into Movie (id, title, length, releaseDate)
values (5, \'xxxxxxxxx\', 75, \'xxxxxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (6, \'xxxxxxxxxxxxx\', \'G\', 71, \'xxxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (7, \'xxxxxxxxxxxxxxx\', \'G\', 78, \'xxxxxxxxxx\');
insert into Person (id, name) values (1, \'xxxxxxxxxxxx\');
insert into Person (id, name) values (2, \'xxxxxxxx\');
insert into Person (id, name) values (3, \'xxxxxxxxxxxxxxxx\');
insert into Person (id, name) values (4, \'xxxx\');
insert into Person (id, name) values (5, \'xxxxxxxx\');
insert into Person (id, name) values (6, \'xxxxxx\');
insert into Person (id, name) values (7, \'xxxxxxxxxxxx\');
insert into Person (id, name) values (8, \'xxxxxxxxxxx\');
insert into Person (id, name) values (9, \'xxxxxxxxxx\');
insert into Person (id, name) values (10, \'xxxxxxxxxxx\');
insert into Person (id, name) values (11, \'xxxxxxxxxx\');
insert into Director (movieId, personId) values (1, 3);
insert into Director (movieId, personId) values (2, 10);
insert into Director (movieId, personId) values (6, 11);
insert into Director (movieId, personId) values (7, 8);
insert into Actor (movieId, personId, characterName)
values(1, 1, \'xxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(2, 9, \'xxxxx\');
insert into Actor (movieId, personId, characterName)
values(2, 10, \'xxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(1, 2, \'xxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(3, 6, \'xxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(7, 6, \'xxxxxxxxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(6, 5, \'xxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(3, 5, \'xxxxxxxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(7, 5, \'xxxxxxxxxxxx\');
create table Movie_info (
id integer not null primary key,
title varchar(150),
rating varchar(20),
len integer,
rDate date);
create table Person_info (
id integer not null primary key,
name varchar(150));
create table Director_info (
person_Id integer not null,
movie_Id integer not null,
primary key (personId, movie_Id),
foreign key (personId) references Person_id(id),
foreign key (movieId) references Movie_id(id));
create table Actor_info (
personId integer not null,
movieId integer not null,
characterName varchar(255),
primary key (personId, movieId),
foreign key (personId) references Person(id),
foreign key (movieId) references Movie(id));
insert into Movie (id, title, rating, length, releaseDate)
values (1, \'xxxxxxxxxxxxxxxxxxxxxx\', \'G\', 84, \'xxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (2, \'xxxxxxxxxxxxxx\', \'PG\', 85, \'xxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (3, \'xxxxxxxxxxxx\', \'G\', 75, \'xxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (4, \'xxxxxxxxxxx\', \'G\', 77, \'xxxxxxxxx\');
insert into Movie (id, title, length, releaseDate)
values (5, \'xxxxxxxxx\', 75, \'xxxxxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (6, \'xxxxxxxxxxxxx\', \'G\', 71, \'xxxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (7, \'xxxxxxxxxxxxxxx\', \'G\', 78, \'xxxxxxxxxx\');
insert into Person (id, name) values (1, \'xxxxxxxxxxxx\');
insert into Person (id, name) values (2, \'xxxxxxxx\');
insert into Person (id, name) values (3, \'xxxxxxxxxxxxxxxx\');
insert into Person (id, name) values (4, \'xxxx\');
insert into Person (id, name) values (5, \'xxxxxxxx\');
insert into Person (id, name) values (6, \'xxxxxx\');
insert into Person (id, name) values (7, \'xxxxxxxxxxxx\');
insert into Person (id, name) values (8, \'xxxxxxxxxxx\');
insert into Person (id, name) values (9, \'xxxxxxxxxx\');
insert into Person (id, name) values (10, \'xxxxxxxxxxx\');
insert into Person (id, name) values (11, \'xxxxxxxxxx\');
insert into Director (movieId, personId) values (1, 3);
insert into Director (movieId, personId) values (2, 10);
insert into Director (movieId, personId) values (6, 11);
insert into Director (movieId, personId) values (7, 8);
insert into Actor (movieId, personId, characterName)
values(1, 1, \'xxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(2, 9, \'xxxxx\');
insert into Actor (movieId, personId, characterName)
values(2, 10, \'xxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(1, 2, \'xxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(3, 6, \'xxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(7, 6, \'xxxxxxxxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(6, 5, \'xxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(3, 5, \'xxxxxxxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(7, 5, \'xxxxxxxxxxxx\');
create table Movie_info (
id integer not null primary key,
title varchar(150),
rating varchar(20),
len integer,
rDate date);
create table Person_info (
id integer not null primary key,
name varchar(150));
create table Director_info (
person_Id integer not null,
movie_Id integer not null,
primary key (personId, movie_Id),
foreign key (personId) references Person_id(id),
foreign key (movieId) references Movie_id(id));
create table Actor_info (
personId integer not null,
movieId integer not null,
characterName varchar(255),
primary key (personId, movieId),
foreign key (personId) references Person(id),
foreign key (movieId) references Movie(id));
insert into Movie (id, title, rating, length, releaseDate)
values (1, \'xxxxxxxxxxxxxxxxxxxxxx\', \'G\', 84, \'xxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (2, \'xxxxxxxxxxxxxx\', \'PG\', 85, \'xxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (3, \'xxxxxxxxxxxx\', \'G\', 75, \'xxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (4, \'xxxxxxxxxxx\', \'G\', 77, \'xxxxxxxxx\');
insert into Movie (id, title, length, releaseDate)
values (5, \'xxxxxxxxx\', 75, \'xxxxxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (6, \'xxxxxxxxxxxxx\', \'G\', 71, \'xxxxxxxx\');
insert into Movie (id, title, rating, length, releaseDate)
values (7, \'xxxxxxxxxxxxxxx\', \'G\', 78, \'xxxxxxxxxx\');
insert into Person (id, name) values (1, \'xxxxxxxxxxxx\');
insert into Person (id, name) values (2, \'xxxxxxxx\');
insert into Person (id, name) values (3, \'xxxxxxxxxxxxxxxx\');
insert into Person (id, name) values (4, \'xxxx\');
insert into Person (id, name) values (5, \'xxxxxxxx\');
insert into Person (id, name) values (6, \'xxxxxx\');
insert into Person (id, name) values (7, \'xxxxxxxxxxxx\');
insert into Person (id, name) values (8, \'xxxxxxxxxxx\');
insert into Person (id, name) values (9, \'xxxxxxxxxx\');
insert into Person (id, name) values (10, \'xxxxxxxxxxx\');
insert into Person (id, name) values (11, \'xxxxxxxxxx\');
insert into Director (movieId, personId) values (1, 3);
insert into Director (movieId, personId) values (2, 10);
insert into Director (movieId, personId) values (6, 11);
insert into Director (movieId, personId) values (7, 8);
insert into Actor (movieId, personId, characterName)
values(1, 1, \'xxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(2, 9, \'xxxxx\');
insert into Actor (movieId, personId, characterName)
values(2, 10, \'xxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(1, 2, \'xxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(3, 6, \'xxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(7, 6, \'xxxxxxxxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(6, 5, \'xxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(3, 5, \'xxxxxxxxxxxxxxxxx\');
insert into Actor (movieId, personId, characterName)
values(7, 5, \'xxxxxxxxxxxx\');
