Today I would like to talk a about the care that we need when to use FETCH JOIN in JPA / Hibernate. Despite being a very useful feature, the lack of their complete understanding can bring confusion and bad surprises. But, luckily, they can be easily outlined.
But first, a quick introduction about what makes the JOIN FETCH in a query. The JOIN FETCH is a JPA resource which allows a single query (in JPQL) bring the particular entity and also the associated entities. We can say that the JOIN FETCH is an alternative to mapping entities with EAGER fetch type, but having the great advantage that you can choose when apply this "EAGER" in the entity.
One example. Imagine that we have the Person and Address entities:
@Entity public class Pessoa { @Id private Integer id; @Column private String nome; @OneToMany(mappedBy= "pessoa") private Set<Endereco> enderecos; } @Entity public class Endereco { @Id private Integer id; @Column private String rua; @Column private Integer numero; @JoinColumn("pessoa_id") private Pessoa pessoa; }
So, with the FETCH JOIN we can make an query to bring the person and their addresses at once! This is what it is used. We can do this JPQL in this way:
SELECT pessoa FROM Pessoa pessoa JOIN FETCH enderecos endereco
Now we have the advantage of consulting person with their addresses, not needing to go to the database again only to retrieve the addresses!
But if we extend this example to something more elaborated, we have to pay attention. In writing the JPQL, we have to take into account a special feature from FETCH JOIN.
Let's say now that you want to bring all person and her addresses, where the street contains the word "mall". In SQL, you can write:
SELECT * from Pessoa pessoa JOIN Endereco endereco ON endereco.pessoa_id = pessoa.id WHERE endereco.rua LIKE "%Alameda%"
This query would bring all the person who have the street address that contains the word "mall".
Now, with the ease of JPQL, you want to do the same: bring person and their addresses, where the street contains the word "mall", but with all addresses loaded in a single query too, like in our first JPQL example.
Thus, it's natural to think that the JPQL could be something very close to:
SELECT pessoa FROM Pessoa pessoa JOIN FETCH enderecos endereco WHERE endereco.rua LIKE "%Alameda%"
This query will run? Yes. Bring results? Will. The result will be what you wanted? Probably not!
This query will bring all the person who have address containing the word "mall" on the street, but will not bring the other addresses that do not contain "Alameda"! For example, if the person has an address containing "Alameda" and "Avenue", it will bring the person only the address with "Alameda" in the street name.
What to do in this case? Now comes the "jump of the cat" (brazilian expression :P): the correct way it add another JOIN without the FETCH. Like this:
SELECT pessoa FROM Pessoa pessoa JOIN FETCH enderecos JOIN enderecos endereco WHERE endereco.rua LIKE "%Alameda%"
Now the query is performed using the JOIN condition that has no associated FETCH. The JOIN with FETCH will be in charge of bringing the person with their addresses. Notice that I also removed the alias "JOIN FETCH addresses" that existed in the previous JPQL, it is not necessary2.
1. Avoid using composite keys when you can, because they complicate consultations with the JPA. If you are not responsible for the database, negotiate with the DBA to create simple keys on the tables, the same ones that the composite key seems the right solution for the bank, because the creation of a constraint or unique index solve in most cases for DBA.↩ 2. Some JPA frameworks do not allow the alias to JOIN FETCH, precisely to avoid this problem we have illustrated. Hibernate allows, but does not recommend using alias with JOIN FETCH.↩
