Spring Boot / JPA / mySQL - 多对一关系会创建太多的SQL查询

问题描述

我有一个简单的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来收集配置文件。
 
 
 
是否有可能推入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？（伪代码） 
 
 
谢谢！  
 
解决方案
 问题。
 
 
 
您可以使用在您的JPA查询吨o修复此问题。
 $ b 
像这样更新您的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.
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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！