Как решить проблему N+1 в Spring Data JPA? ⇐ JAVA

[Anonymous]

Я использую Spring Data JPA в качестве уровня сохраняемости и столкнулся с проблемой N+1. Я также использую API спецификаций, из-за чего мне трудно решить проблему N+1. Пожалуйста, помогите.

@Entity
public class PopulationHealth {

@Id
private int caseId;

@OneToMany(mappedBy = "caseId", fetch = FetchType.LAZY)
private List costSavings;
}


public class CostSaving {

@Id
private int caseId;
}


@Transactional(readOnly = true)
public interface PopulationHealthRepository extends JpaRepository
, JpaSpecificationExecutor {

Page findAll(Specification spec, Pageable pageable);
}


В приведенном ниже классе метод root.join(), который я добавил позже, создает левое соединение в запросе, но проблема N+1 все еще возникает. См. вывод журнала ниже:

public class PopulationHealthSearchSpec implements Specification
{

private List criteria;

public PopulationHealthSearchSpec() {
criteria = new ArrayList();
}

public void addCriteria(List criteria) {
this.criteria.addAll(criteria);
}

@Override
public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
List predicates = new ArrayList();
criteria.forEach(p -> {
SearchOperation operation = p.getOperation();
root.join("costSavings", JoinType.LEFT);
switch (operation) {
case GREATER_THAN:
predicates.add(cb.greaterThan(root.get(p.getKey().toString()), convertToDate(p.getValue())));
break;
case GREATER_THAN_EQUAL:
predicates.add(cb.greaterThanOrEqualTo(root.get(p.getKey().toString()), convertToDate(p.getValue())));
break;
case LESS_THAN:
predicates.add(cb.lessThan(root.get(p.getKey().toString()), convertToDate(p.getValue())));
break;
case LESS_THAN_EQUAL:
predicates.add(cb.lessThanOrEqualTo(root.get(p.getKey().toString()), convertToDate(p.getValue())));
break;
case EQUAL:
predicates.add(cb.equal(root.get(p.getKey().toString()), p.getValue()));
break;
case NOT_EQUAL:
predicates.add(cb.notEqual(root.get(p.getKey().toString()), p.getValue()));
break;
case IN:
predicates.add(getInPredicates(cb, root, p));
break;
case NOT_IN:
predicates.add(getInPredicates(cb, root, p).not());
break;
}
});
return cb.and(predicates.toArray(new Predicate[0]));
}


Вывод журнала с запросами N+1. В первом запросе есть левое соединение. Но все равно остается проблема N+1.

2020-05-06 19:46:41,527 DEBUG org.hibernate.SQL : select population0_.CaseId as CaseId1_3_, population0_.CaseCreateDate as CaseCrea2_3_ from POPULATION_HEALTH_UNMASKED population0_ left outer join COST_SAVINGS costsaving1_ on population0_.CaseId=costsaving1_.CaseId where population0_.CaseId in (3098584 , 3098587 , 3098591) order by population0_.CaseCreateDate asc limit ?
2020-05-06 19:46:41,709 DEBUG org.hibernate.SQL : select costsaving0_.CaseId as CaseId1_1_0_, costsaving0_.CaseId as CaseId1_1_1_ from COST_SAVINGS costsaving0_ where costsaving0_.CaseId=?
2020-05-06 19:46:41,744 DEBUG org.hibernate.SQL : select costsaving0_.CaseId as CaseId1_1_0_, costsaving0_.CaseId as CaseId1_1_1_ from COST_SAVINGS costsaving0_ where costsaving0_.CaseId=?
2020-05-06 19:46:41,781 DEBUG org.hibernate.SQL : select costsaving0_.CaseId as CaseId1_1_0_, costsaving0_.CaseId as CaseId1_1_1_ from COST_SAVINGS costsaving0_ where costsaving0_.CaseId=?


После использования fetch() вместо join() в классе спецификации я получаю следующую проблему:

2020-05-06 20:29:25,315 ERROR org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is 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 [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.cambia.mmt.cdqs.api.entity.PopulationHealth.costSavings,tableName=COST_SAVINGS,tableAlias=costsaving1_,origin=POPULATION_HEALTH_UNMASKED population0_,columns={population0_.CaseId ,className=com.cambia.mmt.cdqs.api.entity.CostSaving}}] [select count(generatedAlias0) from com.cambia.mmt.cdqs.api.entity.PopulationHealth as generatedAlias0 left join fetch generatedAlias0.costSavings as generatedAlias1 where generatedAlias0.caseCreateDate>:param0]; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.cambia.mmt.cdqs.api.entity.PopulationHealth.costSavings,tableName=COST_SAVINGS,tableAlias=costsaving1_,origin=POPULATION_HEALTH_UNMASKED population0_,columns={population0_.CaseId ,className=com.cambia.mmt.cdqs.api.entity.CostSaving}}] [select count(generatedAlias0) from com.cambia.mmt.cdqs.api.entity.PopulationHealth as generatedAlias0 left join fetch generatedAlias0.costSavings as generatedAlias1 where generatedAlias0.caseCreateDate>:param0]] with root cause
org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.cambia.mmt.cdqs.api.entity.PopulationHealth.costSavings,tableName=COST_SAVINGS,tableAlias=costsaving1_,origin=POPULATION_HEALTH_UNMASKED population0_,columns={population0_.CaseId ,className=com.cambia.mmt.cdqs.api.entity.CostSaving}}]


Подробнее здесь: https://stackoverflow.com/questions/616 ... g-data-jpa