Search code examples
javajpajoinjpql

Why does JPA join return too many results?

If I query in SQL:

select * from Profesor
inner join Estudiantes on Profesor.id = Estudiante.id
where Profesor.nombre = 'juan'
  and Estudiante.nombre = 'jose'

This query return the profesor and the student. One profesor and one student. Just Profesor Juan with Jose as Student.

Then if I Query in JPA:

select p from Profesor p
inner join p.estudiantes e
where p.nombre = juan
  and e.nombre = jose.

JPA will return profesor Juan with all the students not just the one I wanted and profesor.estudiantes will have the list with all the students.

My types are:

class Profesor{
    private List<Estudiante> estudiantes;
}

class Estudiante{
    String matricula;
}

Sorry I code in spanish. I just figure out this.

I dont know if I were clear on my question, please tell me.

Solution

  • You need to understand two things.

    First: when you say Select p from Profesor, JPA only selects the columns from the Profesor table, and returns a Profesor instance containing a collection of students that is not loaded yet. It's loaded lazily the first time you actually access the collection. And when it loads the collection, it has forgotten which query you used to load the professor. What it loads is the collection of students of the professor. And since a professor has many students, it ;oads them all. The initial query is similar to

    select p.* from Profesor inner join ...

    Execute it in SQL, and you'll see that it doesn't load a professor and its student. It only loads a professor.

    Second: an entity is supposed to represent the data in the database. It isn't supposed to represent the result of a query. So, the collection of students in a Professor entity is always the collection of all the students of the professor.

    To do what you want, you have several choices:

    1. select p, s from Profesor inner join p.students s...: this will return an array containing the found profesor, and the found student.
    2. if the association is bidirectional: select s from Profesor p inner join p.students s ...: this will load the student, and it references its professor
    3. if using Hibernate, which violates the JPA spec in this area: select p from Profesor inner join fetch p.students...: the fetch makes hibernate load the professor and its students in a single query. But since you added a where clause on the student, it only loads the matching students of the professor.

    Note that the third solution is a very dangerous one, and I wouldn't advise using it. First because it's not valid JPQL. And more importantly, because the code getting a Professor entity loaded by such a query expects that professor.getStudents() will return all the students of the professor, and not just one of them. It could thus display false results, or modify the collection and cause incoherences in the database.