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

Question

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?

Fridell

I'm seeing same behavior, basically JPA executes the left join query correctly but when object is null (data not present) it will execute another query for the object (always returning null). Feel free to ping me also if you need additional details. – Basidiospore

Curious behavior. Does the +1 query to user happen when you actually dereference the user or does it happen immediately alongside the initial find all query? – Gravelblind

@Gravelblind In my tests it happens immediately (you don't need to reference) – Basidiospore

Haven't seen an explanation for this behavior yet but staring at your query more I also want to ask why your queries are joining post.id to user.id. I think there may be a problem with your mapping that is leading to confusing results. Even the second query you posted is trying to do the same.... May have to add a MappedBy or something to clarify the relationship. – Gravelblind

Also I read somewhere that the entity graph behavior is to tell JPA what result set to guarantee projection into but NOT how the fetch it so if there's a way in your code either in JPQL or criteria to ALSO specify a fetch join syntax then that might also do the trick. – Gravelblind

Answer 1

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.

Gravelblind