CODEWITHSUNDEEP
javaspringjpaentityentitygraph
Allen Perry
Allen Perry

Reputation: 201

Spring Data JPA runs additional select queries when using entity graph and mapped object is not present (null)

I have 2 entities in a one to one relationship (it can be many to one but for this case I don't think it matters much) like such:

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
 
    //...
}
@Entity
public class Post {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String subject;
 
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn
    private User otherUser;
     
    //...
}

The repository looks like this
@EntityGraph(attributePaths = {"otherUser"})
Iterable<Post> findAll(Predicate predicate);

So I override the findAll method they provide by default because I still want to use the predicate and I still want to join the query to another table. This almost works perfectly. The query created looks like this

select
post0_.id as id1_4_0_,
user0_.id as id1_0_1_,
post0_.subject as subject2_4_0_,
user0_.name as name2_0_1_,
user0_.email as email3_0_1_
from
    Post post0_
    left outer join User user0_ on post0_.id = user0_.id
where
   ***contents of Predicate****

Now the problem is if a user comes back basically null (all the attributes of user being null, meaning one doesn't exist to match the condition) from this query then Spring creates additional select queries directly to the User table looking for the user. For example let's say there's a post with id=4. If there is no user with id = 4 then Spring will create and run a normal query like such:

select
user0_.id as id1_0_1_,
user0_.name as name2_0_1_,
user0_.email as email3_0_1_
from
    User user0_ 
    left outer join User user0_ on post0_.id = user0_.id
where
   user0_id = 4

This is a problem if the dataset is large and contains large amount records that don't fit the original join query then too many additional queries will be created and execute. If it doesn't fit the join criteria then I would like that entity record to be ignored. Using the example I would want any user's who id don't match a post id to be ignored, not another select statement.

Is there a way to prevent those additional select queries from being created?

Upvotes: 6

Views: 1784

Answers (1)

Atmas
Atmas

Reputation: 2393

Assuming you are using Hibernate by your log statements, I believe you are running into an n+1 sql load situation and need to add different/additional FETCH directives to make the otherUser association fetch eagerly so it doesn't require a follow-up select statement.

The most authoritative documentation I can find on the topic is here: https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#fetching

I would suggest first you change your FetchType.LAZY to FetchType.EAGER. If placing the JPA annotation FetchType.EAGER on the otherUser member variable does not solve your problem, consider utilizing @Fetch(FetchMode.JOIN) to further reinforce it.

Also, for completeness, if you choose to use a query based method to retrieve (i.e. JPQL) then you can include the FETCH directive in your JPQL to achieve the effect on an as-needed basis.

Upvotes: 1

Related Questions