Spring Data JPA - 使用ElementCollection进行查询 - 性能低下

Question

我在Spring Data JPA中遇到了一些简单查询的性能问题。

型号：

@Entity 
public class LogEntry { 
    .. 
    @NotNull 
    @ElementCollection(fetch = FetchType.EAGER) 
    private List<String> parameters; 

    @ManyToOne 
    private Guest guest; 
    .. 
} 

库：

Page<LogEntry> findByGuestOrderByCreationDateDesc(Guest guest, Pageable pageable); 

我想显示的所有参数的日志条目列表。但查询速度非常慢。找到所有条目后，它开始查询每个条目的参数。

日志显示大量的这些行：

Hibernate: select parameters0_.LogEntry_id as LogEntry1_8_0_, parameters0_.parameters as paramete2_9_0_ from LogEntry_parameters parameters0_ where parameters0_.LogEntry_id=? 

我正在寻找，以提高查询的方式。我尝试使用联合抓取而没有成功。

@Query("select l from LogEntry l join fetch l.parameters where l.guest = ?1 order by l.creationDate desc") 


Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,collection join,fetch join,fetch non-lazy properties,classAlias=null,role=domain.guest.LogEntry.parameters,tableName={none},tableAlias=parameters1_,origin=null,columns={,className=null}}] [select count(l) from domain.guest.LogEntry l join fetch l.parameters where l.guest = ?1] 

Answer 1

您可以尝试下面的代码。

@Query("select logEntry from LogEntry logEntry join fetch logEntry.parameters as params join fetch logEntry.guest as guest where guest.id = :guestId order by logEntry.creationDate desc") 
Page<LogEntry> findByGuestOrderByCreationDateDesc(@Param Long guestId, Pageable pageable);