Fetch join with pagination fails to append LIMIT in the generated HQL

[pshingavi]

Hi,

I have an entity

Order 
{
    @Id
    @GeneratedValue
    @Column(name="order_id")
    private BigInteger orderId;

   @OneToMany(mappedBy = "order")
    private Set<OrderLine> orderLines;
}

and bi-directional mapping in entity

OrderLine 
{
    @Id
    @Column(name="order_id")
    @GeneratedValue(generator="gen")
    @GenericGenerator(name="gen", strategy="foreign", parameters=@org.hibernate.annotations.Parameter(name="property", value="order"))
    private BigInteger orderId;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "order_id", updatable = false, insertable = false)
    @JsonIgnore
    private Order order;
}

My order repository extends the DataTablesRepository interface. When I had OneToOne mapping the results were fast and the HQL generated had LIMIT in the query. But with the above mapping LIMIT is missing in the query and it takes a long time to fetch all records and then do in memory mapping to return the paginated result.

It's been more than 3 weeks I am trying to find a work around but not able to figure out how. Can you suggest how can I solve this issue.

[pshingavi]

This is similar to the problem here : http://stackoverflow.com/questions/26901010/spring-data-jpa-eager-fetch-with-join-and-using-pagination-not-working

I see the below WARNING in my logs :

Log message is WARN org.hibernate.hql.internal.ast.QueryTranslatorImpl - HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

How can I achieve pagination in such case without any performance hit ?

[darrachequesne]

Are you applying any filtering/sorting on the lines? If not, you could lazily fetch the lines, after you've retrieved a list of Orders.

What's happening here (as you've certainly understood) is that, since Hibernate doesn't know how many lines it eventually needs, it fetches all those lines in memory.

[pshingavi]

Thank you for the quick response :)

Yes I have a Specification which filters on the line items too.

[darrachequesne]

In that case, that line here https://github.com/darrachequesne/spring-data-jpa-datatables/blob/master/src/main/java/org/springframework/data/jpa/datatables/repository/SpecificationFactory.java#L106 might cause your issue, since it add a FETCH JOIN for every related attributes.

[pshingavi]

That's correct.

Adamantly backtracking to use custom query using @Query and Pageable in my Repository :(

[darrachequesne]

@pshingavi Hi! I've just published v3.0, could you please try it, and see whether it fixes your issue please?

[pshingavi]

@darrachequesne Yes, I got this working with the v3.0 version. Thanks a ton for the fix !

[svnsiva]

@pshingavi @darrachequesne I am facing the same problem. Can any of you provide the solution. I couldn't find anything in the GitHub link available above.