Fetch all relationships with JPQL

Question

I am trying to use JPQL to generate a single SQL statement to fetch multiple different entities all linked to each other with foreign keys.

Model

The model looks like this:

@Entity
@Table(name = "mainentity")
@Data
@IdClass(MainEntityPrimaryKey.class)
public class MainEntity {

    @Id
    @Column(updatable = false)
    private String foo;

    @Id
    @Column(updatable = false)
    private String bar;

    @OneToMany(cascade = {CascadeType.ALL}, orphanRemoval = true)
    @JoinColumns({
            @JoinColumn(name = "foo", referencedColumnName = "foo"),
            @JoinColumn(name = "bar", referencedColumnName = "bar")
    })
    private List<SubEntity> subs;
}

@Entity
@Table(name = "subentity")
@Data
@IdClass(SubEntityPrimaryKey.class)
public class SubEntity {

    @Id
    @Column(updatable = false)
    private String foo;

    @Id
    @Column(updatable = false)
    private String bar;

    @OneToOne(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "first_possibility_id", referencedColumnName = "first_possibility")
    private FirstPossibility firstPossibility;

    @OneToOne(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "second_possibility_id", referencedColumnName = "second_possibility")
    private SecondPossibility secondPossibility;
}

@Entity
@Table(name = "firstpossibility")
@Data
public class FirstPossibility {

    @Id
    @Column(name = "first_possibility_id", nullable = false, updatable = false)
    private String id;

    @Column
    private String something;
}

@Entity
@Table(name = "secondpossibility")
@Data
public class SecondPossibility {

    @Id
    @Column(name = "second_possibility_id", nullable = false, updatable = false)
    private String id;

    @Column
    private String stuff;
}

A SubEntity will never have both a FirstPossibility and SecondPossibility linked at the same time.

Repository

The Spring-Data interface:

public interface MainEntityCRUDRepository extends CrudRepository<MainEntity, MainEntityPrimaryKey> {

    @Query("SELECT main FROM MainEntity main WHERE CONCAT(main.foo, '~', main.bar) IN :ids")
    List<MainEntity> getAllMainWithConcatenatedIds(@Param("ids") Collection<String> ids);
}

Current state

As it is, it fetches all the MainEntity properly in a single call to the database (I can see the binding happening correctly with the spring.jpa.show-sql=true property), but without any SubEntity.

The SubEntity get fetched when something ends up trying to access them later in the code, and that also ends up fetching the First or Second entities. All of this, with separated DB calls for each entity (which is, performance-wise, something that we want to avoid).

Important note: we use EclipseLink as our JPA Provider.

See https://stackoverflow.com/a/37596251/496099 and https://stackoverflow.com/a/16693590/496099 for nested fetch joining in JPA/EclipseLink, but be careful what is in the parent rows when joining via OneToMany and ManyToMany, as some data can cause overhead that outweighs the benefits of joining. If it has large lobs/clob data for instance, you might want to look at batch fetching instead to reduce network having to handle duplicate rows when cross joining those relationships. — Chris, Nov 16 '21 at 16:01

score 0 · Accepted Answer · answered Nov 18 '21 at 15:24

Thanks to Chris's comment and help, I reached this solution:

public interface MainEntityCRUDRepository extends CrudRepository<MainEntity, MainEntityPrimaryKey> {

    @QueryHints({
            @QueryHint(name = org.eclipse.persistence.config.QueryHints.LEFT_FETCH, value = "main.subs.firstPossibility"),
            @QueryHint(name = org.eclipse.persistence.config.QueryHints.LEFT_FETCH, value = "main.subs.secondPossibility")
    })
    @Query("SELECT main FROM MainEntity main WHERE CONCAT(main.foo, '~', main.bar) IN :ids")
    List<MainEntity> getAllMainWithConcatenatedIds(@Param("ids") Collection<String> ids);
}

And the generated SQL looks like this:

SELECT *
FROM mainentity t1
LEFT OUTER JOIN subentity t0 ON ((t0.foo = t1.foo) AND (t0.bar = t1.bar))
LEFT OUTER JOIN firstpossibility t2 ON (t2.id = t0.first_possibility_id)
LEFT OUTER JOIN secondpossibility t3 ON (t3.id = t0.second_possibility_id)
WHERE (t1.foo || '~' || t1.bar IN (...))

Notice how there is no JOIN FETCH main.subs sub in the @Query : that is implied by the hints navigating through main.subs already.

If you add the JOIN FETCH, you will actually have duplicates in your result List. Those duplicates are due to the fact that this is how the DB actually returns the result (each FirstPossibility and SecondPossibility found would create one entry in the result set.

If you wanted to use the QueryHints.BATCH instead, then the JOIN FETCH main.subs sub is actually required, but you will not have the entire result returned through a single DB call: multiple SELECT statements would be issued.

Fetch all relationships with JPQL

Model

Repository

Current state

1 Answers1