7

I am implementing search/filtering service for list of entities, using Spring Data JPA repository with specifications and pagination features. I am trying to reduce number of queries (n+1 problem) and fetch nested data using criteria fetch mechanism.

I have two entity classes:

@Entity
@Table(name = "delegations")
public class Delegation {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    @ManyToOne
    private Customer customer;

    // more fields, getters, setters, business logic...

}

and

@Entity
@Table(name = "customers")
public class Customer {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    // more fields, getters, setters, business logic...
}

DTO filter class:

public class DelegationFilter {

    private String customerName;

    // more filters, getters, setters...
}

And search / filtering service: 

public class DelegationService {
    public Page<Delegation> findAll(DelegationFilter filter, Pageable page) {
        Specifications<Delegation> spec = Specifications.where(
                customerLike(filter.getCustomerName())
        );
        return delegationRepository.findAll(spec, page);
    }

    public List<Delegation> findAll(DelegationFilter filter) {
        Specifications<Delegation> spec = Specifications.where(
                customerLike(filter.getCustomerName())
        );
        return delegationRepository.findAll(spec);
    }

    private Specification<Delegation> customerLike(String customerName) {
        return (root, query, cb) -> {
            Join<Delegation,Customer> join = (Join) root.fetch(Delegation_.customer);
            return cb.like(cb.lower(join.get(Customer_.name)), addWildCards(customerName.toLowerCase()));
        };
    }

    private static String addWildCards(String param) {
        return '%' + param + '%';
    }
}

Problem:

When I call findAll(DelegationFilter filter, Pageable page) I am getting exception:

org.springframework.dao.InvalidDataAccessApiUsageException: 
org.hibernate.QueryException: query specified join fetching, but the owner 
of the fetched association was not present in the select list

Is there a way to solve this problem?

findAll(DelegationFilter filter) (method without pagination) works like charm... Using join only (without fetch) also works fine (even with pagination)

I know that there is solution for JPQL: Spring-Data FETCH JOIN with Paging is not working But I want to stick with criteria api...

I am using Spring Boot 1.4 (spring 4.3.2, spring-data-jpa 1.10.2) and Hibernate 5.0.9

Community
  • 1
  • 1
Maciej Marczuk
  • 3,593
  • 29
  • 28

1 Answers1

11

I was facing the same problem, and I found a workaround (source).

You can check the query's return type at runtime, so that if it is Long (the type the count query returns) you join and otherwise you fetch. In your code it will look like this:

...
private Specification<Delegation> customerLike(String customerName) {
    return (root, query, cb) -> {
        if (query.getResultType() != Long.class && query.getResultType() != long.class) {
          Join<Delegation,Customer> join = (Join) root.fetch(Delegation_.customer);
        } else {
          Join<Delegation,Customer> join = root.join(Delegation_.customer);
        }
        return cb.like(cb.lower(join.get(Customer_.name)), addWildCards(customerName.toLowerCase()));
    };
}
...

I know it's not very clean, but it's the only solution I've ofund ATM.

gmc
  • 3,910
  • 2
  • 31
  • 44