Could not extract ResultSet when @Formula (hibernate annotation) is used

Question

In my app there are courses and course_ratings tables (I use MySQL). In course_ratings table is foreign key to courses table and a column named rating that is INT.

I will show my problem on an example: When I download a course which has id 1 from db I want to get all the rows from course_ratings where the foreign key to the courses table is 1 then count the average of the rating columns from all those rows.

For this, I decided to use the @Formula annotation

Take a look at my entity:

    @Entity
    @Table(name = "courses")
    public class Course {
        @Id
        @GeneratedValue(generator = "inc")
        @GenericGenerator(name = "inc", strategy = "increment")
        private int courseId;
        @NotBlank(message = "Add the course's title!")
        private String title;
        private String description;

        @Formula("SELECT AVG(cr.rating) FROM course_ratings cr WHERE cr.course_id = courseId")
        private double averageRating;
    
        @OneToMany(mappedBy = "course")
        private Set<CourseRating> ratings; 
}

This SQL inside @Formula is correct but after calling method findById(Integer id) from JpaRepository I get this error:

2021-07-14 20:26:26.314  WARN 15268 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2021-07-14 20:26:26.314 ERROR 15268 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : (conn=248) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT AVG(cr.rating) FROM course_ratings cr WHERE cr.course_id = 1 as formul...' at line 1
2021-07-14 20:26:26.323  INFO 15268 --- [nio-8080-exec-3] o.h.e.internal.DefaultLoadEventListener  : HHH000327: Error performing load command

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

EDIT: I don't if it's important but I have added average_rating column as DECIMAL:

ALTER TABLE courses ADD COLUMN average_rating DECIMAL(3, 2);

Answer 1

This is my solution that works !

@Formula("SELECT AVG(cr.rating) FROM course_ratings cr WHERE cr.course_id = course_id")

I've changed courseId to course_id because it's a raw SQL, not JPQL