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);

}