Home / Questions / 389684
Accepted
isaac
Asked: 2020-09-15 22:03:53 +0800 CST 2020-09-15 22:03:53 +0800 CST

left join in jpa without multiple queries

  • 772

I have two tables in the database, one COUNTRIES and the other CITIES, they have a relationship of ONE to MANY, a country has many cities. I require a query that obtains all the countries with their cities, but if the country does not have associated cities, it should also be in the response of the query. I am doing the following query

select distinct p FROM Pais p LEFT JOIN p.ciudades c

This query meets the objective, however when I look at the log I find that n + 1queries are made according to the number of existing countries. To avoid multiple queries I do afetch join

select distinct p FROM Pais p JOIN FETCH p.ciudades

With this sentence, when looking at the log, you only see one query made, this reduces nthe number of queries made with respect to the previous sentence, but it does not bring me the countries that do not have associated cities.

How can I bring the countries that do not have associated cities but in such a way that only one query is made and not n +1queries?

hibernate

1 Answers

  1. Best Answer

    In JPA, JOINs by default are always INNER JOINs. In the first case you have declared a LEFT JOIN, but in the second case you have not.

    If you add LEFT to your second query

    select distinct p FROM Pais p LEFT JOIN FETCH p.ciudades

    you should get all countries, whether they have cities or not.

    • 1