LAB EXERCISE 11 Solution

Lab 11 gives students the opportunity to gain practice and experience writing Java applications that connect to a relational database using JDBC. After completing this exercise students should become familiar with:

 

●  using Java DB

●  using SQL to query a relational database

●  how to connect to and query a relational database using JDBC

●   how to use the java.util.Prorperties class to read configuration information from a properties file

 

 

 

For all programming exercises in this assignment you will need Java DB installed on your development system for use as your database management system. If you have installed JDK 7 or higher, you automcatically have Jave DB as part of the installation. For each JDBC program in this assignment, create a file called database.properties that holds the configuration information for the jdbc driver and the database url.  Your programs should read these properties from the file and use this information when connecting to the database. Your solution must use the java.util.Properties class to load and read the properties. Place the properties file directly under your Java Project folder in Eclipse.  Here is an example of the properties you might find in the database.properties file:

 

jdbc.url=jdbc:derby:Lab11DB;create=true jdbc.driver=org.apache.derby.jdbc.EmbeddedDriver

 

GETTING STARTED

 

Create a Java project in your Eclipse workspace called Lab11. Place all your java source files for this exercise under the Lab11 project src folder in your Eclipse workspace.

 

WHAT TO SUBMIT

 

When you have completed all of the programs in this assignment you will create a single zip file that includes your source code for each program.  You will submit a single zip file for this assignment called Lab11.zip. Please include only your Java source files with no sub folders.

EXERCISE 1: MOVIE DATABASE

Using the techniques and Java JDBC APIs introduced in chapter 28 of your text book define a movie database application that allows a user to look up information about a movie and provide a review for a movie.  Your application should provide the following features:

 

     List all movies that are rated a particular rating (PG, PG-13, R, etc) entered by the user.

     All a user to post a review for a particular movie.  A review consists of review text and a star rating with

o 5 stars = Excellent

o 4 stars = Good

o 3 stars = Neutral

o 2 stars = Poor

o 1 star = Very Poor

         List all reviews for a movie and include the average star rating for the movie

 

Your solution should read the database connection information (jdbc driver and connection url) from a properties file called database.properties.  You can create a console based application to implement this application or write a GUI interface to provide these movie database features described above.  The only requirement is that the class containing the main method should be called MovieDBApp.

 

Here is some information about the database table schema you will be using:

 



 

A set of commands to create and populate the MOVIE and MOVIE_REVIEW tables are provided below.

 

CONNECT 'jdbc:derby:C:\temp\CSC251\Lab11DB;create=true';

 

CREATE TABLE MOVIE (MOVIE_ID int not null primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), MOVIE_NAME varchar(100), RATED varchar(10));

 

CREATE TABLE MOVIE_REVIEW (REVIEW_ID int not null primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), MOVIE_ID int, REVIEW varchar(500), STARS int);

INSERT INTO MOVIE(MOVIE_NAME, RATED) values('Divergent', 'PG-13');

 

INSERT INTO MOVIE(MOVIE_NAME, RATED) values('Muppets Most Wanted', 'PG'); INSERT INTO MOVIE(MOVIE_NAME, RATED) values('The LEGO Movie', 'PG'); INSERT INTO MOVIE(MOVIE_NAME, RATED) values('Saving Mr. Banks', 'PG-13'); INSERT INTO MOVIE(MOVIE_NAME, RATED) values('Her', 'R');

Notice that in the CONNECT statement has a portion of the connection URL highlighted. This statement assumes that you will want to have the database files located in C:\temp\CSC251\Labb11DB. If you would like the database files to be located somewhere else on your system, you will need to update this statement. Also, when the MOVIE and MOVIE_REVIEW tables are created the MOVIE_ID and REVIEW_ID are set to be automatically generated by the database. As a result, any SQL statements to INSERT INTO the MOVIE or MOVIE_REVIEW table will not need to specify and ID. Instead, the database will automatically generate these values for you.