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.

CodePudding user response：

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.