My java program is a movie rating system that is connected to a MySQL movies dat

Question

My java program is a movie rating system that is connected to a MySQL movies database. With my program, I need to be able to get the average rating score for a specific movie title. My program is creating a running sum of all the rating for a designated movie. I'm not sure where I'm going wrong. Any help would be appreicated.

My java code for the rating section of the program is below:

//Rate movie

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

int numRatings = 0;

try {

numRatings++;

//Search movie

String sqlStatement = "SELECT * FROM movie WHERE movie_title like"%" + movie_title+"%"";

PreparedStatement pstmt = connection.prepareStatement(sqlStatement);

ResultSet resultSet = pstmt.executeQuery();

while(resultSet.next()) {

String data = resultSet.getString("movieID") +", " + resultSet.getString("movie_title") +

", " + resultSet.getDouble("score") + ", " + resultSet.getDouble("sumScore") + ", "

+ resultSet.getDouble("countMovie");

sumScore = resultSet.getDouble("sumScore");

sumScore = sumScore + score;

countMovie = resultSet.getDouble("countMovie");

avgScore = (sumScore)/numRatings;

//numRatings ++;

}//end of while loop

}catch(SQLException ex) {

System.out.println("Not selected. Error: " + ex);

}

catch(Exception e) {

System.out.println("Error: " + e);

}

//Add movie rating

try {

PreparedStatement pstmt = connection.prepareStatement("update movie set score = ?, sumScore = ?, avgScore = ? where movie_title like"%" + movie_title + "%"");

pstmt.setDouble(1, score);

pstmt.setDouble(2, sumScore);

pstmt.setDouble(3, avgScore);

pstmt.executeUpdate();

//confirm rating was added

System.out.println("Sucessfully Added!");

}catch(SQLException ex) {

System.out.println("Error: " + ex);

}

catch(Exception e) {

System.out.println("Error: " + e);

}

break; //break out of case 2

My SQL Script is below:

CREATE 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),
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
(ratingID CHAR(15) NOT NULL,
rating decimal(3,2) NOT NULL,
movieID CHAR(15) Primary key #foreign key
);

Explanation / Answer

Check this While loop where you should be calculating the sum total of all ratings for a Movie and then add to it the User rating, and then finally calculate the average of all ratings:-

int totalScore = 0; // initialize the TotalScore to 0 outside the while loop

while(resultSet.next()) {

String data = resultSet.getString("movieID") +", " + resultSet.getString("movie_title") +

", " + resultSet.getDouble("score") + ", " + resultSet.getDouble("sumScore") + ", "

+ resultSet.getDouble("countMovie");

sumScore = resultSet.getDouble("sumScore"); // Fetch the Sum Score from DB Query

totalScore = totalScore + sumScore; // store the sum of all scores in totalScore Variable

//sumScore = sumScore + score; // omit this line

countMovie = resultSet.getDouble("countMovie");

numRatings ++; // Do this after your query has returned the Result Set, not at the beginning since if the Query returns no rows, you will still get numRating as 1, so dont do this before hand.

}//end of while loop

totalScore = totalScore + score; // Score is the one input by the user to obtain the final totalScore

avgScore = (totalScore) / (numRatings + 1); // Here +1 is including the user score that we are adding to totalScore.

Now use this avgScore in your program!!.

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.