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]

You mentioned you are using it on 30 fields; is it working on others in the same entity/query? Show your entity, query and the resulting SQL statements. — Chris, Mar 14 '16 at 13:12
Code added - It doesn't seem to matter what fields, it's only working for one at a time. — DSS, Mar 14 '16 at 14:05
It seems to be completely ignoring any batch fetch hint except the last one. If I change o.aList to something invalid like o.a.List.id or o.iDontExist, I'm not getting an error. I suppose when you set the hint, it keeps override the previous until it hits the last instead of combining them. Is there any way to set a hint for multiple batch fetch fields? — DSS, Mar 14 '16 at 14:29
You will have to check how spring-data creates the query. My guess is it is passing the hints all at once through a map to the query. If you were to call setHint("eclipselink.batch", "o.bSet"), setHint("eclipselink.batch", "o.aList") it would work, but because Spring is building a map, only the last gets passed to the JPA query. An alternative would be to build a named query in the entity, specify the hints on it and then use Spring to call it instead. — Chris, Mar 14 '16 at 14:48
Good call on the map. I checked the source and that is in fact what Spring is doing. I opened a JIRA ticket. I'll give named queries a shot. Thanks for the help. — DSS, Mar 14 '16 at 19:18

score 1 · Accepted Answer · answered Mar 28 '16 at 16:19

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.

For info, this bug has been fixed recently in Spring data (don't know the exact version of spring-data-jpa, but Spring boot 2.4 and onwards allows multiple query-hints with the same name) — Guillaume Polet, May 05 '21 at 12:14

EclipseLink Batch Fetch Hint Not Working For Two Fields

1 Answers1