I have a problem with subquery and JPA that took me a lot of time and I can't find a direct solution, so I used two different queries to solve it.
With Spring and JPA I have two entities similar to this:
public Class Entity{
@Id
private Long id;
@OneToMany(mappedBy = "entity", fetch = FetchType.LAZY)
private Set<SecondEntity> secondEntities;
}
public class SecondEntity{
@Id
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn
private Entity entityId;
@Column
private Integer number;
}
And I want to use JPA and specification to run a query like this:
SELECT *
FROM Entity
WHERE id IN (SELECT id FROM Entity LEFT JOIN SecondEntity ON Entity.id = SecondEntity.entityId WHERE number = ?)
Please, don't focus on another way to solve it, but help me to understand if we can make Hibernate do exactly what I want.
The problem comes because initializing subroot like this:
Subquery<Entity> subquery = query.subquery(Entity.class);
Root<Entity> subroot = subquery.from(Entity.class);
The method from() causes Hibernate to add a crossJoin to the query and it appears like this:
SELECT *
FROM Entity
WHERE id IN (SELECT id FROM Entity LEFT OUTER JOIN SecondEntity ON Entity.id = SecondEntity.entityId CROSS JOIN Employee WHERE crossjoincondition AND number = ?)
Does anyone know how to initialize subquery with another method to avoid crossJoin? I know other implementations, like doing a different query and mixing the result, or writing an @Query dedicated can work better, but I just want to know how to manage this for future purposes?
The only solution I could find is one that recommends removing the @JoinColumn annotation and the mappedBy attribute, but I can't modify this mapping because I need it for another method.