I need to rate a movie in my Java program with my movies database, but when I ru
ID: 3717126 • Letter: I
Question
I need to rate a movie in my Java program with my movies database, but when I run my program and try to rate a movie, I get several SQL exceptions. I'm not sure what I am doing wrong.My program needs to 1) ask for a rating for a specific movie from the user, 2) calculate the average rating for that movie and 3) insert the updated rating/score value back into the movies database. My code for my program is below. My mySQL script below as well.
MySQL Code
-----------------------------------
CREATE DATABASE moviesdatabase;
#drop database moviesdatabase;
USE moviesdatabase;
CREATE TABLE MOVIE
(movieID CHAR(15),
movie_title VARCHAR(45) NOT NULL,
score decimal(4,2),
#sumScore decimal(4,2),
#AvgScore decimal(4,2),
description VARCHAR(50) NOT NULL,
countMovie int NOT NULL auto_increment Primary key);
CREATE TABLE COMMENTS
(movieID CHAR(15),
comments CHAR(65),
commentID CHAR(15) PRIMARY KEY);
CREATE TABLE RATING
(movieID CHAR(15), #foreign key
rating decimal(3,2),
ratingID CHAR(15) PRIMARY KEY);
My Java Code for rating part of program
------------------------------------
//add rating: add to running sum of rating when user inputs rating
//use count to divide by sum to get total average
System.out.println("Enter movie you want to rate: ");
movie_title = input.nextLine();
System.out.println("Enter your rating (1-10): ");
score = input.nextDouble();
try {
//Search movie
String sqlStatement = "SELECT * FROM movie WHERE movie_title like"%" + movie_title+"%"";
//String sqlStatment2 = "SELECT AVG(rating) FROM Rating WHERE movieID =" + movieID;
PreparedStatement pstmt = connection.prepareStatement(sqlStatement);
// PreparedStatement pstmt2 = connection.prepareStatement(sqlStatement);
ResultSet resultSet = pstmt.executeQuery();
//ResultSet resultSet2 = pstmt2.executeQuery();
while(resultSet.next()) {
String data = resultSet.getString("movieID") +", " + resultSet.getString("movie_title") +
", " + resultSet.getDouble("score") + ", " + resultSet.getDouble("sumScore") + ", "
+ resultSet.getDouble("description")+ resultSet.getDouble("countMovie") /*+ resultSet2.getDouble("rating")*/;
sumScore = resultSet.getDouble("sumScore");
countMovie = resultSet.getDouble("countMovie");
avgScore = (score+ sumScore)/countMovie;
}//end of while loop
}catch(SQLException ex) {
System.out.println("Not selected. Error: " + ex);
}
catch(Exception e) {
System.out.println("Error: " + e);
}
try {
PreparedStatement pstmt = connection.prepareStatement("insert into movie (movie_title, score, sumScore, avgScore) values (? , ? , ?, ?) ");
pstmt.setString(1, movie_title);
pstmt.setDouble(2, score);
pstmt.setDouble(3, sumScore);
pstmt.setDouble(3, avgScore);
pstmt.executeUpdate();
System.out.println( "Successfully inserted");
}catch(SQLException ex) {
System.out.println("Not inserted. Error: " + ex);
}
catch(Exception e) {
System.out.println("Error: " + e);
}
Explanation / Answer
import java.io.*;
class moviemagic
{
int year;
String title;
float rating;
moviemagic()
{ year=0;
title="";
rating=0.00f;
}
void input() throws IOException
{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
System.out.println("enter the movie name");
title=br.readLine();
System.out.println("enter the year of release");
year = Integer.parseInt(br.readLine());
System.out.println("enter the ratings of the movie");
rating=Float.parseFloat(br.readLine());
}
void calculate ()
{
if(rating>=0.0 && rating <=2.0)
{
System.out.println("the m,ovie is flop");
}
else if(rating >=2.1&&rating<=3.4)
{
System.out.println("the m,ovie is semi hit");
}
else if(rating >=3.5&&rating<=4.5)
{ System.out.println("the m,ovie is hit");
}
else if(rating >=4.6&& rating <=5.0)
{
System.out.println("the m,ovie is blockbusterr");
}
}
void display()
{
System.out.println("movie name "+"year "+"ratings");
System.out.println(title+" "+year+" "+rating);
}
public static void main (String []args) throws IOException
{
moviemagic obj= new moviemagic ();
obj.input();
obj.calculate();
obj.display();
}
}