I have problem with JPA/Hibernate when fetching certain entity from database. Problem is that it executes two queries, even thou first one is sufficient to gather or data that I need for the entity.
The type of entity I am taling has self reference. Here it is:
@Data
@Entity
@Table(name = "franchises")
@EntityListeners(AuditingEntityListener.class)
@NamedEntityGraphs({
@NamedEntityGraph(
name = "Franchise.Parent.Country",
attributeNodes = {
@NamedAttributeNode("parent"),
@NamedAttributeNode("country"),
}
),
})
public class Franchise {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(name = "name")
private String name;
@Enumerated(EnumType.STRING)
@Column(name = "type")
private FranchiseType type;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "parent_id")
@EqualsAndHashCode.Exclude
@ToString.Exclude
private Franchise parent;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "country_id")
@EqualsAndHashCode.Exclude
@ToString.Exclude
private Country country;
}
And here is the repository:
@Repository
public interface IFranchiseRepository extends JpaRepository<Franchise, Long>,
JpaSpecificationExecutor<Franchise>,
QuerydslPredicateExecutor<Franchise>,
FranchiseRepositoryCustom
{
@EntityGraph("Franchise.Parent.Country")
Optional<Franchise> findWithParentAndCountryById(long id);
}
Now in the controller I have this code:
@GetMapping("/api/franchises/{franchise}")
public FranchiseDTO getFranchise(@PathVariable("franchise") long franchiseId) {
var f = franchiseRepository.findWithParentAndCountryById(franchiseId).get();
return null; // Do not worry about this statement, I am yet to map ti to DTO
}
When I call this api endpoint, i can see in console log that it is executng two queries:
SELECT
franchise0_.id AS id1_6_0_,
franchise1_.id AS id1_6_1_,
country2_.id AS id1_3_2_,
franchise0_.country_id AS country_6_6_0_,
franchise0_.created_at AS created_2_6_0_,
franchise0_.name AS name3_6_0_,
franchise0_.parent_id AS parent_i7_6_0_,
franchise0_.type AS type4_6_0_,
franchise0_.updated_at AS updated_5_6_0_,
franchise1_.country_id AS country_6_6_1_,
franchise1_.created_at AS created_2_6_1_,
franchise1_.name AS name3_6_1_,
franchise1_.parent_id AS parent_i7_6_1_,
franchise1_.type AS type4_6_1_,
franchise1_.updated_at AS updated_5_6_1_,
country2_.alpha2_code AS alpha2_3_2_,
country2_.alpha3_code AS alpha3_3_2_,
country2_.created_at AS created_4_3_2_,
country2_.name AS name5_3_2_,
country2_.updated_at AS updated_6_3_2_
FROM
franchises franchise0_
LEFT OUTER JOIN franchises franchise1_ ON
franchise0_.parent_id = franchise1_.id
LEFT OUTER JOIN countries country2_ ON
franchise0_.country_id = country2_.id
WHERE
franchise0_.id = 3;
SELECT
franchise0_.id AS id1_6_0_,
franchise0_.country_id AS country_6_6_0_,
franchise0_.created_at AS created_2_6_0_,
franchise0_.name AS name3_6_0_,
franchise0_.parent_id AS parent_i7_6_0_,
franchise0_.type AS type4_6_0_,
franchise0_.updated_at AS updated_5_6_0_
FROM
franchises franchise0_
WHERE
franchise0_.id = 135
And this is the data that I have in database:
|-----|-------------|---------------|-----------|------------|
| id | name | type | parent_id | country_id |
|-----|-------------|---------------|-----------|------------|
| 135 | Franchise A | INTERNATIONAL | NULL | NULL |
| 2 | Franchise B | MASTER | 135 | 1 |
| 3 | Franchise C | REGIONAL | 2 | 1 |
| 4 | Franchise D | REGIONAL | 2 | 1 |
|-----|-------------|---------------|-----------|------------|
So by looking at the executed queries I can see (and test) that all data that is needed for me is fetched using first query Franchise+Parent+Country. However then the second query got executed to load parent of the parent which I dont need...
Can someone tell me what am I doing wrong and why are two queries executed instead of only one?