I have mapped through JPA the following entities:
@Entity(name = "Parent")
@Table(name = "parent")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "type")
public abstract class Parent {
@Id
@GenericGenerator(name = "CustomUUIDGenerator", strategy = "package.CustomUUIDGenerator")
@GeneratedValue(generator = "CustomUUIDGenerator", strategy = GenerationType.AUTO)
private UUID id;
@ManyToMany
@Fetch(FetchMode.JOIN)
@JoinTable(name = "parent_country", joinColumns = @JoinColumn(name = "parent_id"),
inverseJoinColumns = @JoinColumn(name = "country_id"))
private Set<Country> countries;
}
@Entity(name = "ChildA")
@Table(name = "child_a")
@DiscriminatorValue(value = "CHILD_A")
public class ChildA extends Parent {
private String description;
}
This reflects to the following Postgres' table schema:
Table PARENT columns: ID, TYPE
Table COUNTRY columns: ID, CODE, LANGUAGE
Table PARENT_COUNTRY columns: PARENT_ID, COUNTRY_ID
Table CHILD_A columns: ID, DESCRIPTION
Based on that I'm trying to create an CriteriaQuery
using EntityGraph
to control the dynamic associated entities loading.
The following code presents how the given query:
EntityGraph<ChildA> entityGraph = entityManager.createEntityGraph(ChildA.class);
entityGraph.addAttributeNodes(Parent_.COUNTRIES);
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ChildA> criteriaQuery = criteriaBuilder.createQuery(ChildA.class);
Root<ChildA> root = criteriaQuery.from(ChildA.class);
List<Predicate> predicates = new ArrayList<>();
final Join<ChildA, Country> join = root.join(ChildA_.COUNTRIES, JoinType.LEFT);
predicates.add(criteriaBuilder.equal(join.get(Country_.CODE), "USA"));
criteriaQuery.where(predicates.toArray(Predicate[]::new));
TypedQuery<ChildA> finalQuery = entityManager
.createQuery(criteriaQuery)
.setHint(EntityGraphPersistence.LOAD_GRAPH, entityGraph);
List result = finalQuery.getResultList();
The issue is that, when I run the query above I get the following SQL printed on the console:
select
* -- All hibernate fields from all entities
from
child_a ca
inner join
parent pa
on ca.id=pa.id
left outer join
parent_country pc
on pc.parent_id = ca.id
left outer join
country co
on pc.country_id = co.id
left outer join
parent_country pc_2
on pc2.parent_id = ca.id
left outer join
country co2
on pc2.country_id = co2.id
where
(
co.code=?
)
The issue from the query above is the double join with PARENT_COUNTRY and COUNTRY. In this query I'm filtering the results based on the country code (equal to USA), so I don't want to get back from the database other countries (different from USA). However, due to the second left join to country, the parent is related again to countries and the filter done on the first relation to co.code=?
is ignored.
Does anybody know how to prevent the double join in this use case?
CodePudding user response:
Finally after struggling a whole day with this problem I found the solution.
Turns out that the solution is to use a feature called fetch-join
from JPA, it allows you to join a relation and use it to fetch the data that will be used to populate the entity.
However a few syntax tweaks had to be made to make it work. Also when I fetch-join a relation using criteria builder, I should also ignore them from the EntityGraph so I don't join the same entity twice. The snippet below shows how it works:
EntityGraph<ChildA> entityGraph = entityManager.createEntityGraph(ChildA.class);
//entityGraph.addAttributeNodes(Parent_.COUNTRIES);
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ChildA> criteriaQuery = criteriaBuilder.createQuery(ChildA.class);
Root<ChildA> root = criteriaQuery.from(ChildA.class);
List<Predicate> predicates = new ArrayList<>();
//final Join<ChildA, Country> join = root.join(ChildA_.COUNTRIES, JoinType.LEFT);
Fetch<ChildA, Countries> fetch = root.fetch(ChildA_.COUNTRIES, JoinType.LEFT); // new line
Join<ChildA, Countries> join = (Join<ChildA, Countries>) fetch; // new line
predicates.add(criteriaBuilder.equal(join.get(Country_.CODE), "USA"));
criteriaQuery.where(predicates.toArray(Predicate[]::new));
TypedQuery<ChildA> finalQuery = entityManager
.createQuery(criteriaQuery)
.setHint(EntityGraphPersistence.LOAD_GRAPH, entityGraph);
List result = finalQuery.getResultList();
You can find more details about this topic at this link: https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846/4