JPA select Query to return Entity with @ManyToOne Mapping

Question

I am a beginner, learning JPA, for practice I was working on this problem where I have two entity classes Person and Gym.

Person has : - id (auto-generated) - name - age - Gym (Many to One mapping)

Gym has : - id (auto-generated) - name - rating - fee - List of Person (One to Many mapping)

Now, I have my PersonRepository which extends JpaRepository and have this following JPQL query where I am try to retrieve all persons with age < (some user input value)

The problem is the retrieved person list is always empty. I tried used fetch join but still it returns empty list.

What should be the appropriate JPQL query for this scenario ?

Thanks ! Balasubramanyam

Gym Entity

@Entity
public class Gym {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int gym_id;

    @NotNull
    private String name;

    @NotNull
    private String city;

    @NotNull
    @Max(5)
    private double rating;

    @NotNull
    private double fee;

    @OneToMany(mappedBy="gym", 
                cascade= {CascadeType.MERGE, CascadeType.PERSIST,
                        CascadeType.REFRESH}, fetch=FetchType.EAGER)
    @JsonManagedReference
    private List<Person> personList;

    public Gym() {
        super();
    }

    public Gym(int gym_id, @NotNull String name, @NotNull double rating, @NotNull double fee, List<Person> personList,
            @NotNull String city) {
        super();
        this.gym_id = gym_id;
        this.name = name;
        this.rating = rating;
        this.fee = fee;
        this.personList = personList;
        this.city = city;
    }
// getters and setters

Person Entity

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;

    @NotNull
    private String name;

    @NotNull
    private int age;

    @ManyToOne (cascade={CascadeType.MERGE, CascadeType.PERSIST,
        CascadeType.REFRESH, CascadeType.DETACH})
    @JoinColumn
    @JsonBackReference
    private Gym gym;

    public Person() {
        super();
    }

    public Person(int id, @NotNull String name, @NotNull int age, Gym gym) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.gym = gym;
    }
// getters and setters

PersonRepository

public interface PersonRepository extends JpaRepository<Person, Integer>{

@Query("select p from Person p join fetch p.gym where p.age<=(:age)")
List<Person> filterByAge(@Param("age") int age);
}

In my service class this is what I am doing

List<Person> filteredPersonList = personRepository.filterByAge(age);
System.out.println(filteredPersonList); // prints empty

It should be left join fetch(or you can simply remove that clause if you don't want to fetch gyms). But anyway, if the list is empty, the most probable cause, by a large margin, is that there is no such person in the database used hy Hibernate. — JB Nizet
Hi !, thank you for the quick response. The person table and gym table in the database does have entries in it, I have checked it. I have checked the connection too, I am able to insert values from my application. — BE01
Then try debugging to find what happens. Enable SQL logging to know whiwh query is being executed. Call findAll() and print what it returns, to see hat actual persons exist. — JB Nizet

Guilherme Guilherme · Accepted Answer · 2019-08-15T19:27:20

If you change your repository to this you wont need to construct the query and will work.

public interface PersonRepository extends JpaRepository<Person, Integer>{

     List<Person> findByAgeLessThanEqual(int age);

}

JPA select Query to return Entity with @ManyToOne Mapping

1 Answers