Issue
My problem is the following,
There are two entity classes, let's call them Entity1
and Entity2
with One-to-Many relationship in between, i.e. one Entity1
contains multiple Entity2
s, and Entity2
may have only one Entity1
:
@Entity
@Table(name = "entity1")
public class Entity1 {
int x;
int y;
...
@LazyCollection(LazyCollectionOption.TRUE)
@OneToMany(mappedBy = "e1", cascade = CascadeType.ALL, orphanRemoval = true)
private Set<Entity2> entity2s = new HashSet<>();
}
@Entity
@Table(name = "entity2")
public class Entity2 {
int a;
int b;
...
@ManyToOne
@JoinColumn(name = "entity1_id")
@JsonBackReference
private Entity1 e1;
}
Now I would like to issue a query for retrieving Entity2
s with WHERE conditions for both Entity2
and its corresponding Entity1
:
@Query("SELECT " +
" e2 " +
"FROM " +
" Entity2 e2 " +
"WHERE " +
" e2.a = '<val1>' AND e2.b = '<val2>' AND e2.e1.x = '<val3>' AND e2.e1.y ='<val4>'")
List<Entity2> findMyEntity2s(
@Param...,
@Param...,
);
So the problem with this approach is that, it indeed gets desired Entity2
s by cross joining entity1
and entity2
tables with specified WHERE conditions BUT it fetches e1
s for each of those Entity2
s in the result with a separate query.
So for example if the result of join is 5 Entity2
s, there will be 5 additional queries to entity1
table.
I tried to set @ManyToOne
in Entity2
as @ManyToOne(fetch = FetchType.LAZY)
but it didn't help. I guess that's expected because LAZY
would simply postpone the retrieval of e1
s but wouldn't eliminate it completely.
Next, I read about @EntityGraph
, and added it to Entity2
:
@Entity
@Table(name = "entity2")
@NamedEntityGraph(name = "graph.entity2.entity1",
attributeNodes = { @NamedAttributeNode("e1") })
public class Entity2 {
int a;
int b;
...
@ManyToOne
@JoinColumn(name = "entity1_id")
@JsonBackReference
private Entity1 e1;
}
and in the repository, I added it as:
@EntityGraph(value = "graph.entity2.entity1")
@Query("SELECT " +
" e2 " +
"FROM " +
" Entity2 e2 " +
"WHERE " +
" e2.a = '<val1>' AND e2.b = '<val2>' AND e2.e1.x = '<val3>' AND e2.e1.y ='<val4>'")
List<Entity2> findMyEntity2s(
@Param...,
@Param...,
);
In this case, the separate SQL queries disappear, EntityGraph does left join and its result contains columns from both entity1
and entity2
, BUT because the conditions for e2.e1
are still in WHERE clause, it adds ONE MORE unnecessary cross join with entity1
table (e2.e1
conditions are checked in that cross join).
I couldn't find a way to get rid of that extra cross join, so now I'm using the following query:
@EntityGraph(value = "graph.entity2.entity1")
@Query("SELECT " +
" e2 " +
"FROM " +
" Entity2 e2 " +
"WHERE " +
" e2.a = '<val1>' AND e2.b = '<val2>'")
List<Entity2> findMyEntity2s(
@Param...,
@Param...,
);
So basically I get Entity2
s and in the application I filter out based on conditions of Entity1
(e2.e1.x = '<val3>' AND e2.e1.y ='<val4>'
).
Is there a way to make it work with a single join only, for both entity's conditions, not only Entity2
conditions? The way I'm doing it now, does not seem correct and efficient to me, and I feel there's a way to do that using repository method only, without involving the app. Would appreciate any help on this
UPD. Read about nativeQuery
option (nativeQuery = true
) for @Query
annotation, which allows specifying a raw query and thus bypassing entity-based query, but the query still fetches many-to-one e1
field, using entity1_id
(entity graph was disabled). I tried to enable entity graph but it dropped exception stating that entity graph cannot be used with native query, which is expected
Solution
This is the classic n + 1 query problem. You can read the detail here: https://vladmihalcea.com/n-plus-1-query-problem/
In your query, append:
LEFT JOIN FETCH e2.e1 e2e1
This will fetch e1 with e2 in the first and single query.
Don't forget; always use FetchType.LAZY and fetch your entities with JOIN FETCH
. Otherwise, you will get into a big mass while the scope of the project enlarges.
In addition, why do you use Jaxson annotations in your Entity classes? Use entities for only DAO access and map them to another DTOs to use elsewhere.
Answered By - Bahadir Tasdemir
Answer Checked By - Pedro (JavaFixing Volunteer)