EclipseLink Batch Fetch Hint Not Working For Two Fields

Question

I'm using QueryHints in Spring Data JPA to use EclipseLink Batch Fetch with a type of IN. Ultimately, I need to use this around 30 fields but it doesn't seem to work right for 2 fields. Field A has a ManyToOne relationship and Field B has a ManyToMany. Based on the results of the initial query, I would expect the batch hint to generate an IN clause with 2 ids for Field A and 12 for Field B. This works fine when the hint is turned on for one field at a time. When it is enabled for both fields, the hint only applies to whichever field is the last hint in the list of QueryHints. I've tried EAGER and LAZY fetch on the fields as a shot in the dark, but it had not impact.

Is there a limitation with mixing batch fetch hints based on the relationship type? Is there something different going on? The EclipseLink documentation isn't very detailed for this feature.

EDIT: It seems it doesn't matter what fields I enable it only, it only works for one at at time. Here is sample code for two entities. The BaseEntity defines the PK id generation.

@Entity
@Table(name = "MainEntity")
public class MainEntity extends BaseEntity implements Cloneable {

    ...


    @ManyToMany(fetch=FetchType.LAZY, cascade=CascadeType.PERSIST)
    @JoinTable(
            name="EntityBMapping",
            joinColumns={@JoinColumn(name="mainId", referencedColumnName="id")},
            inverseJoinColumns={@JoinColumn(name="bId", referencedColumnName="id")})
    @JsonIgnore
    private Set<EntityB> bSet = new HashSet<>();

    @ManyToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL)
    @JoinTable(
            name="EntityAMapping",
            joinColumns={@JoinColumn(name="mainId", referencedColumnName="id")},
            inverseJoinColumns={@JoinColumn(name="aId", referencedColumnName="id")})
    @JsonIgnore
    @OrderColumn(name="order_index", columnDefinition="SMALLINT")
    private List<EntityA> aList = new ArrayList<>();

    ...

}

@Entity
@Cache(type=CacheType.FULL)
@Table(name = "EntityA")
public class EntityA extends BaseEntity {

@Column(name = "name", columnDefinition = "VARCHAR(100)")
private String name;

@ManyToMany(mappedBy = "entityASet", fetch=FetchType.LAZY)
@JsonIgnore
private Set<MainEntity> mainEntityList = new HashSet<>();

}



@Entity
@Cache(type=CacheType.FULL)
@Table(name = "EntityB")
public class EntityB extends BaseEntity {

@Column(name = "name", columnDefinition = "VARCHAR(100)")
private String name;

@ManyToMany(mappedBy = "entityBSet", cascade=CascadeType.ALL)
@JsonIgnore
private Set<MainEntity> mainEntityList = new HashSet<>();

}

The repository query:

@QueryHints(value = {
            @QueryHint(name = org.eclipse.persistence.config.QueryHints.BATCH_TYPE, value = "IN"),
            @QueryHint(name = org.eclipse.persistence.config.QueryHints.BATCH_SIZE, value = "250"),
            @QueryHint(name = org.eclipse.persistence.config.QueryHints.BATCH, value = "o.aList")},
            @QueryHint(name = org.eclipse.persistence.config.QueryHints.BATCH, value = "o.bSet")},  
    forCounting = false)
    List<MainEntity> findAll(Specification spec);

Generated queries:

SELECT id, STATUS, user_id FROM MainEntity WHERE ((STATUS = ?) OR ((STATUS = ?) AND (user_id = ?)))--bind => [ONESTAT, TWOSTAT, myuser]
..
SELECT t1.id, t1.name, t0.order_index FROM EntityAMapping t0, EntityA t1 WHERE ((t0.mainId = ?) AND (t1.id = t0.aId))--bind => [125e17d2-9327-4c6b-a65d-9d0bd8c040ac]
...
SELECT t1.id, t1.name, t0.mainId FROM EntityBMapping t0, EntityB t1 WHERE ((t1.id = t0.bId) AND (t0.mainId IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)))--bind => [125e17d2-9327-4c6b-a65d-9d0bd8c040ac, 1c07a3a9-7028-48ba-abe8-2296d58ebd57, 235bb4f2-d724-4237-b73b-725db2b9ca9f, 264f64b3-c355-4476-8530-11d2037b1f3c, 2d9a7044-73b3-491d-b5f1-d5b95cbb1fab, 31621c93-2b0b-4162-9e42-32705b7ba712, 39b33b19-c333-4523-a5a7-4ba0108fe9de, 40ba7706-4023-4b7e-9bd5-1641c5ed6498, 52eed760-9eaf-4f6a-a36f-076b3eae9297, 71797f0c-5528-4588-a82c-5e1d4d9c2a66, 89eda2ef-80ff-4f54-9e6a-cf69211dfa61, 930ba300-52fa-481c-a0ae-bd491e7dc631, 96dfadf9-2490-4584-b0d4-26757262266d, ae079d02-b0b5-4b85-8e6f-d3ff663afd6e, b2974160-33e8-4faf-ad06-902a8a0beb04, b86742d8-0368-4dde-8d17-231368796504, caeb79ce-2819-4295-948b-210514376f60, cafe838f-0993-4441-8b99-e012bbd4c5ee, da378482-27f9-40b7-990b-89778adc4a7e, e4d7d6b9-2b8f-40ab-95c1-33c6c98ec2ee, e557acf4-df01-4e66-9d5e-84742c99870d, ef55a83c-2f4c-47b9-99bb-6fa2f5c19a76, ef55a83c-2f4c-47b9-99bb-6fa2f5c19a77]
...
SELECT t1.id, t1.name, t0.order_index FROM EntityAMapping t0, EntityA t1 WHERE ((t0.mainId = ?) AND (t1.id = t0.aId))--bind => [1c07a3a9-7028-48ba-abe8-2296d58ebd57]

Answer 1

As Chris mentioned, Named Queries are the best work around for this issue. The other option is to use a custom repository and call setHint on the EntityManager yourself for each hint specified (plenty of examples out there for creating custom repos in Spring Data JPA). You could attempt to override findOne(...) and protected <S extends T> TypedQuery<S> getQuery(Specification<S> spec, Class<S> domainClass, Sort sort) on SimpleJpaRepository to try and create a generic way to properly set the hints but you'll likely want to check that you don't duplicate hint setting on getQuery(...) as you'll still want to call super() for that and then apply your additional hints before returning the query. I'm not sure what the behavior would be if you applied a duplicate hint. Save yourself the trouble and use Named Queries is my advice.