JPQL Query error with Spring JPA and Hibernate

Question

I am trying to fetch all the Skills relating to a specific Person.

Getting the following error when trying to fetch data from MySQL DB using JPQL in my Spring Boot application:

org.hibernate.QueryException: could not resolve property: person_skill of: com.skilltrack.app.Domain.Skill [SELECT s FROM com.skilltrack.app.Domain.Skill s JOIN s.person_skill ps WHERE ps.fk_person = ?1 ]

Here is my code:

Person.java

@Data
@EqualsAndHashCode(exclude = "personCourses")
@Entity(name = "person")
@Table(name = "person")
public class Person implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer person_id;

    @NotBlank
    private String name;

    @NotBlank
    private String surname;

    @NotBlank
    private String email;

    @NotBlank
    private String password;

    @NotBlank
    private String personType; //admin or user

    @JsonIgnore
    @ManyToMany  // this mapping is referred to in the Course class
    @JoinTable(name = "person_course",
            joinColumns = @JoinColumn(name = "fk_person", referencedColumnName = "person_id"),
            inverseJoinColumns = @JoinColumn(name = "fk_course", referencedColumnName = "course_id"))
    private List<Course> personCourses;

    @JsonIgnore
    @ManyToMany  // this mapping is referred to in the Skill class
    @JoinTable(name = "person_skill",
            joinColumns = @JoinColumn(name = "fk_person", referencedColumnName = "person_id"),
            inverseJoinColumns = @JoinColumn(name = "fk_skill", referencedColumnName = "skill_id"))
    private List<Skill> personSkills;

Course.java

@Data
@EqualsAndHashCode(exclude = "skills")
@Entity(name = "course")
@Table(name = "course")
public class Course {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer course_id;

    @NotBlank
    private String name;

    @NotBlank
    private String description;

    @JsonIgnore
    @ManyToMany(mappedBy = "courses") // mapping defined in Skill already
    private List<Skill> skills;

    @NotBlank
    private Boolean completed; // 0 no 1 yes

    @JsonIgnore
    @ManyToMany(mappedBy = "personCourses")
    private List<Person> coursePerson;

Skill.java

@Data
@EqualsAndHashCode(exclude = "courses")
@Entity(name = "skill")
@Table(name = "skill")
public class Skill {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer skill_id;

    @NotBlank
    private String name;

    @NotBlank
    private String description;

    @JsonIgnore
    @ManyToMany(cascade = CascadeType.ALL)  // this mapping is referred to in the Course class
    @JoinTable(name = "course_skills",
            joinColumns = @JoinColumn(name = "fk_skill", referencedColumnName = "skill_id"),
            inverseJoinColumns = @JoinColumn(name = "fk_course", referencedColumnName = "course_id"))
    private List<Course> courses;

    @JsonIgnore
    @ManyToMany(mappedBy = "personSkills")
    private List<Person> skill_person;

PersonRepository.java

@Repository
public interface PersonRepository extends JpaRepository<Person, Integer> {

    @Query(value =
            "SELECT s " +
            "FROM skill s JOIN s.person_skill ps " +
            "WHERE ps.fk_person = ?1 ")
    List<Skill> getPersonSkills(Integer personID);
}


The issue is with the JPQL statement:
"SELECT s FROM skill s JOIN s.person_skill ps WHERE ps.fk_person = ?1 "

I have tried the following variations:

"SELECT s FROM skill s INNER JOIN s.person_skill ps WHERE ps.fk_person = ?1"

"SELECT s FROM skill s JOIN FETCH s.person p WHERE ps.fk_person = ?1"

"SELECT s FROM skill s JOIN s.person p JOIN s.person_skill ps WHERE ps.fk_person = ?1"

"SELECT s FROM skill s JOIN person p JOIN person_skill ps WHERE ps.fk_person = ?1"

How should I change my JPQL query to return a list of Skills relating to a specific Person?

Answer 1

It is possible to acieve the result without writing a custom query. Spring builds endpoints like "skills" and "persons" by default, when used, they show other endpoints like "http://localhost:8080/skills/{id}/skill_person"

Playing around with those will lead you to find any combination of results. This is possible since the many to many mappings are defined bidirectionally the way thy are in the current project and spring is awesome.

Answer 2

Change your wrongly typed entity name from person_skill to skill_person defined in Skill.java

Answer 3

You have this error org.hibernate.QueryException because you have wrong name of field in JPQL.
Skill entity has field with name: skill_person.
You have another name of field in your JPQL query: person_skill.