Проблема спящего режима N + 1 с двунаправленным сопоставлением OneToOne

Я использую JPARepository со спящим режимом для представления моей базы данных. У меня есть несколько сущностей, и две из моих сущностей имеют двунаправленное сопоставление OneToOne. Всякий раз, когда я запускаю запрос к дочернему классу двунаправленной связи (или родительскому классу, чтобы получить дочерний класс), я сталкиваюсь с проблемой спящего режима N + 1.

Моя родительская сущность:

@Entity
@Table(name = "ORGANIZATION")
public class Organization {

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    @Id
    private Long id;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "organization_source_id")
    private OrganizationSource source;

    //other columns then getters and setters
}

Дочерний класс

@Entity
@Table(name = "ORGANIZATION_SOURCE")
public class OrganizationSource {

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    @Id
    private Long id;

    @OneToOne(mappedBy="source", fetch = FetchType.LAZY)
    @Cascade(value=org.hibernate.annotations.CascadeType.SAVE_UPDATE)
    private Organization organization;

    //other columns then getters and setters
}

Затем у меня есть класс DAO для создания запроса. Я использовал аннотацию @Query

public interface OrganizationSourceDao extends PagingAndSortingRepository<OrganizationSource, Long> {



@Query("SELECT source FROM OrganizationSource source RIGHT JOIN source.organization"
            + "WHERE source.name like %:name% "
    //   order by part of the query
    )
    Page<OrganizationSource> findByNameContaining(
            @Param("name") String name,
            @PageableDefault(size = 10) Pageable pageable);
    }

Выполнение запроса в DAO показывает следующее в журнале

Hibernate: select organizati0_.id as id1_6_, organizati0_.city as city2_6_, organizati0_.code as code3_6_, organizati0_.name as name4_6_, organizati0_.source_system_id as source_s5_6_, organizati0_.state as state6_6_, organizati0_.street as street7_6_, organizati0_.uuid as uuid8_6_, organizati0_.zip as zip9_6_ from organization_source organizati0_ right outer join organization organizati1_ on organizati0_.id=organizati1_.organization_source_id where (organizati0_.name like ?) and (organizati0_.state like ?) and (organizati0_.city like ?) order by case ? when 'ASC' then case ? when 'name' then organizati0_.name when 'state' then organizati0_.state when 'city' then organizati0_.city when 'zip' then organizati0_.zip when 'street' then organizati0_.street end end ASC, case ? when 'DESC' then case ? when 'name' then organizati0_.name when 'state' then organizati0_.state when 'city' then organizati0_.city when 'zip' then organizati0_.zip when 'street' then organizati0_.street end end DESC offset 0 rows fetch next ? rows only
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?

Я рассмотрел использование аннотации @Fetch(FetchMode.JOIN) и все те быстрые решения, доступные в Google, но, похоже, не могу решить эту проблему.

Любая помощь будет высоко ценится.


java hibernate jpa spring-data-jpa spring-repositories
person sometimes24    schedule 04.07.2018    source источник
comment
RIGHT JOIN FETCH - лучший способ   -  person Wilder Valera    schedule 05.07.2018
comment
Когда я пробовал это, я получаю следующую ошибку: 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=organization,role=OrganizationSource.organization,tableName=organization,tableAlias=organizati1_,origin=organization_source organizati0_,columns={organizati0_.id ,className=Organization}}]   -  person sometimes24    schedule 05.07.2018
comment
вы можете использовать JOIN FETCH или LEFT JOIN FETCH   -  person Wilder Valera    schedule 06.07.2018
comment
У меня ни одна форма выборки не сработала - даже после соблюдения надлежащих соглашений   -  person sometimes24    schedule 06.07.2018

Ответы (1)


arrow_upward
0
arrow_downward

Я знаю одну хитрость. Измените отображение организации с OneToOne на ManyToOne. У меня это все время работает нормально. На этот раз НЕТ запроса n + 1.

ОрганизацияSource

@Entity
@Table(name = "ORGANIZATION_SOURCE")
public class **OrganizationSource** {

 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id", nullable = false)
 @Id
 private Long id;

 @JsonBackReference
 @Cascade(value=org.hibernate.annotations.CascadeType.SAVE_UPDATE)
 @ManyToOne(fetch = FetchType.LAZY)
 private Organization organization;

 private String name;
}

И Организация

@Data
@Entity
@Table(name = "ORGANIZATION")
public class Organization {

 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id", nullable = false)
 @Id
 private Long id;

 @JsonManagedReference
 @OneToOne(fetch = FetchType.EAGER)
 @JoinColumn(name = "organization_source_id")
 private OrganizationSource source;

}
person maruf571    schedule 05.07.2018
comment
У меня это не сработало. Когда я попробовал это, я получил invalid column name organization_id. Я добавил @JoinColumn(name="id", updatable="false", insertable="false), чтобы указать на id (так как это то, что таблица организации использует в качестве внешнего ключа. Это привело к поиску с использованием идентификатора организации. Когда я поставил organization_source_id, он сказал бы, что не найден. Когда я использовал JoinTable, который работал, но значения организации в organizationSource были пустыми. - person sometimes24; 05.07.2018