Spring boot query sorted in memory and limit not working

I have an entity class that has a lazy join:

@Entity  public class Landlord {    @Id  String id;    ...    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)  @JoinColumn(name="landlordId")  Collection<Tenant> tenants;  

Now I am trying to fetch the data lazily in a Criteria query:

@Override  public List<Landlord> findTenants(String username, String filter, Pageable pageable) {        EntityGraph<Landlord> landlordEntityGraph = entityManager.createEntityGraph(Landlord.class);      landlordEntityGraph.addAttributeNodes("tenants");        Query query = entityManager.createQuery(              "select l from Landlord l " +                      "where l.username=:username " +                      "and l.filter like :filter " +                      "order by l.createdOn desc")              .setFirstResult(Math.toIntExact(pageable.getOffset()))              .setMaxResults(pageable.getPageSize())              .setHint("javax.persistence.fetchgraph", landlordEntityGraph);      query.setParameter("username", username);      query.setParameter("filter", "%" + filter + "%");        var result = query.getResultList();      return result;  }  

However, I am getting the warning:

firstResult/maxResults specified with collection fetch; applying in memory

Here is the generated SQL. I abbreviated the shortnames just for readability:

SELECT          <all column values>  FROM            landlord l0  LEFT OUTER JOIN tenant t1  ON              l0.id= t1.landlord_id  WHERE           l0.username=?  AND             (                                  l0.filter LIKE ?)  ORDER BY        l0.created_on   

It looks like the offset and limit are completely dropped from the query too!

Hopefully someone can help i have been stuck on this for a few days and am now out of ideas.

Cheers

https://stackoverflow.com/questions/66701224/spring-boot-query-sorted-in-memory-and-limit-not-working March 19, 2021 at 09:04AM