Spring Boot / JPA / mySQL - 多对一关系会创建太多的SQL查询 [英] Spring Boot / JPA / mySQL - many to one relationship creates too many SQL queries

问题描述

我有一个简单的spring boot rest应用程序与mySQL db连接，我试图优化简单函数中的查询数量：

 列表与LT;消息> messages = messagesRepository.findBySenderIdOrReceiverIdOrderByTimeDesc（senderId，receiverId）; 
  

MessagesRepository：

  public interface MessagesRepository扩展CrudRepository< Message，Long> {
列表<消息> findBySenderIdOrReceiverIdOrderByTimeDesc（Long senderId，long receiverId）; 
} 
  

消息：

<$
@Table（name =s_messages）
public class Message实现Serializable
{
@Id
@ GeneratedValue（strategy = GenerationType.AUTO）
private Long id;

@Transient
private int internalId;

@ManyToOne（fetch = FetchType.EAGER）
@JoinColumn（name =senderId，referencedColumnName =id，updatable = false，insertable = false）
Private ProfileLite发件人;

@ManyToOne（fetch = FetchType.EAGER）
@JoinColumn（name =receiverId，referencedColumnName =id，updatable = false，insertable = false）
private ProfileLite接收器;

@Column（columnDefinition =TEXT）
私人字符串消息;私人长时间的

;
私人MessageStatus状态;
}

ProfileLite：

 @Entity 
 @Table（name =s_profiles）
 public class ProfileLite实现Comparable< ProfileLite> 
 {
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
 private Long id; 
 
私人字符串昵称; 
私人字符串国家; 
 private String thumb; 
私人性别性别; 
 
 
 
 
 
 
执行上面提到的方法后，hibernate会生成大约40个SQL（基于40个配置文件）像这样：
 
 
 
  SQL日志 -  PasteBin  
 
 
 
所以首先收集消息，然后为每条消息创建另一个sql来收集配置文件。
 
 
 
是否有可能推入hibernate来创建一个简单的sql而不是40，如下所示： select * from s_messages m join s_profiles s1 on m.sender_id = s1.id join s_profiles s2 m_receiver_id = s2.id？（伪代码） 
 
 
谢谢！  
 
解决方案
   n + 1  问题。
 
 
 
您可以使用  JOIN FETCH  在您的JPA查询吨o修复此问题。
 $ b 
 
 
fetch连接允许使用单个select将其关联或值集合与其父对象一起初始化。这在收集的情况下特别有用。它有效地覆盖了关联和集合的映射文件的外连接和惰性声明。
 
像这样更新您的JPA存储库
 公共接口MessagesRepository扩展了CrudRepository< Message，Long> {
 
 @Query（从消息中选择m m m join join m.sender ms m join join m.receiver mr其中ms.id =：senderId或mr.id =：receiverId order by m.time desc ）
列表<消息> findBySenderIdOrReceiverIdOrderByTimeDesc（Long senderId，long receiverId）; 
 
 
 $ / code> 
有关更详细的解释，请查看这答案。 
 
 
  PS：我没有测试过这个查询。
 
I have a simple spring boot rest app connected with mySQL db and I'm trying to optimize number of queries within simple function:
List<Message> messages = messagesRepository.findBySenderIdOrReceiverIdOrderByTimeDesc(senderId, receiverId);
MessagesRepository:
public interface MessagesRepository extends CrudRepository<Message, Long> { 
    List<Message> findBySenderIdOrReceiverIdOrderByTimeDesc(Long senderId, Long receiverId);
}
Message:
@Entity
@Table(name="s_messages")
public class Message implements Serializable
{
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    @Transient
    private int internalId;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name="senderId", referencedColumnName = "id", updatable=false, insertable=false)
    private ProfileLite sender;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name="receiverId", referencedColumnName = "id", updatable=false, insertable=false)
    private ProfileLite receiver;

    @Column(columnDefinition="TEXT")
    private String message;

    private long time;
    private MessageStatus status;
}
ProfileLite:
@Entity
@Table(name="s_profiles")
public class ProfileLite implements Comparable<ProfileLite>
{
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    private String nickname;
    private String country;
    private String thumb;
    private Gender gender;
}
After executing method mentioned above, hibernate generates about 40 SQL's (based on 40 profiles) like this:

SQL Log - PasteBin

so first collecting messages and then for each message creates another sql to gather profile.

Is it any possibility to push hibernate to create just one simple sql instead of 40 like: select * from s_messages m join s_profiles s1 on m.sender_id = s1.id join s_profiles s2 m_receiver_id = s2.id ? (pseudo code)

Thanks!
解决方案
This could be a n + 1 problem.

You can use a JOIN FETCH in your JPA query to fix this.

  
A "fetch" join allows associations or collections of values to be initialized along with their parent objects using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections.
Update your JPA repository like so
public interface MessagesRepository extends CrudRepository<Message, Long> { 

    @Query("Select m from Message m join fetch m.sender ms join fetch m.receiver mr where ms.id = :senderId or mr.id = :receiverId order by m.time desc")
    List<Message> findBySenderIdOrReceiverIdOrderByTimeDesc(Long senderId, Long receiverId);

}
For a more detailed explanation check out this answer.

PS: I havent tested the query.

                        
这篇关于Spring Boot / JPA / mySQL  - 多对一关系会创建太多的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！