QueryDSL fetchJoin does not fetch the data

Question

I am facing this issue:

Lets assume I have 3 entities like this:

Entity A:

long id

String someField

// No bidirectional linkage to B entity via hibernate

Entity B:

long id

String someBField

@ManyToOne(optional = false, fetch = FetchType.LAZY)
@JoinColumn(name="b_id")
A entityA

@ManyToOne(optional = true, fetch = FetchType.LAZY)
@JoinColumn(name="b_id")
C entityC;

Entity C:

long id

String someCField

// No bidirectional linkage to B entity via hibernate

Now, the goal is to (for simplicity, there are some ordering and filtering but that does not affect my question) return all B records, every one with A and C record fetched

So I am doing something like this (I am used to use spring-data-jpa to (LEFT) JOIN FETCH the properties to avoid lazy loading on demand to prevent firing useless queries into database and I want to do exactly same thing in QueryDSL)

    JPAQuery<DealBo> query = new JPAQuery<>(entityManager);

    query.select(qB)
            .from(qB)
            .innerJoin(qA).on(qA.a_id.eq(qB.id)).fetchJoin()
            .innerJoin(qC).on(qC.a_id.eq(qB.id)).fetchJoin()
            .fetch()

And I expect one SQL where in select clause there are data from all 3 tables (entities), where QueryDSL (or Hibernate, I am not completely sure what tool will do SQL -> Entity mapping) map the result to Entity objects. But what I am really getting is just select like

select b.id, b.someBfield from b
inner join a // join clause is right and omitted for simplicity
inner join b // join clause is right and omitted for simplicity

So when I call on one item what QueryDSL returned for example

b.getC() or b.getA(), I am firing another queries into database, what is a thing I want to avoid in first place.

What am I doing wrong?

I should reread the JPA spec to be sure, but I think fetch joins are only applicable if the joined entity is also projected in the tuple. Otherwise, perhaps this is a Hibernate bug? In the end Hibernate is responsible for generating the SQL. — Jan-Willem Gmelig Meyling, Mar 11 '21 at 21:04
I don't know what kind of HQL query is generated but Hibernate supports this properly. Could you share the generated HQL query? — Christian Beikov, Mar 12 '21 at 10:52
@ChristianBeikov I am not really sure If there is HQL beeing generated, I´ve written the generated "raw" SQL at the end of the post and there should be in "select" section much more columns (c.someCField, a.someField, ...) but it isnt. Which makes sense then that calling getA() or getC() will produce another query — Johnczek, Mar 12 '21 at 17:35
@Jan-WillemGmeligMeyling are you sure about these select? When I was looking for answer, I saw nothing like this. Everyone selected just entity that should be fetched. I´ve been looking into doc and there was nothing that should answer to my question. — Johnczek, Mar 12 '21 at 17:37
Please share the generated HQL. You can obtain the generated query by using the JPQLSerializer. See for example the lines of code at https://github.com/querydsl/querydsl/blob/c75ef4cafd1f79acd5fef13b7b76c8d91a97533a/querydsl-jpa/src/test/java/com/querydsl/jpa/JPQLSerializerTest.java#L47-L49 . Please also report the used Hibernate version. — Jan-Willem Gmelig Meyling, Mar 12 '21 at 17:39
@Jan-WillemGmeligMeyling as mentioned below under the answer, changing join to "Hibernate like" cause fetch join work. Joining "SQL like" will cause fetch join did not work in my case. Thank you for your time. — Johnczek, Mar 16 '21 at 15:27

score 2 · Accepted Answer · answered Mar 13 '21 at 14:39

I think, the definitions of the join conditions are inappropriate.

Hopefully I have recreated the described constellation with UserEntity <- UserRoleEntity -> RoleEntity:

@Entity
@Table(name = "t_user")
public class UserEntity {

    @Id
    @Column(name = "id")
    private Integer id;
    @Column(name = "name") 

    // ..
}

@Entity
@Table(name = "t_user_role")
public class UserRoleEntity {
    @Id
    @Column(name = "id")
    private Integer id;
    @ManyToOne
    @JoinColumn(name = "user_id")
    private UserEntity user;
    @ManyToOne
    @JoinColumn(name = "role_id")
    private RoleEntity role;

    // ..
}

@Entity
@Table(name = "t_role")
public class RoleEntity {
    @Id
    @Column(name = "id")
    private Integer id;
    @Column(name = "name")
    private String name;

    // ..
}

The query

List<UserRoleEntity> findAll() {
    JPAQuery<UserRoleEntity> query = new JPAQuery<>(entityManager);

    return query.select(QUserRoleEntity.userRoleEntity)
            .from(QUserRoleEntity.userRoleEntity)
            .innerJoin(QUserRoleEntity.userRoleEntity.user).fetchJoin()
            .innerJoin(QUserRoleEntity.userRoleEntity.role).fetchJoin()
            .fetch();

}

fetches the associated tables and a subsequent iteration over the user-association does not load the user entity from database.

The generated SQL looks like

    select 
        userroleen0_.id as id1_5_0_, 
        userentity1_.id as id1_4_1_, 
        roleentity2_.id as id1_2_2_, 
        userroleen0_.role_id as role_id2_5_0_, 
        userroleen0_.user_id as user_id3_5_0_, 
        userentity1_.name as name2_4_1_, 
        roleentity2_.name as name2_2_2_ 
    from t_user_role userroleen0_ 
    inner join t_user userentity1_ on userroleen0_.user_id=userentity1_.id 
    inner join t_role roleentity2_ on userroleen0_.role_id=roleentity2_.id

Thank you for your answer. Changing join type from "Hibernate like" (so without specifing join fields) will cause that fetchjoin work now. Thank you for answer. — Johnczek, Mar 16 '21 at 15:26

QueryDSL fetchJoin does not fetch the data

1 Answers1