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

I have a simple spring boot rest app connected with mySQL db and I'm trying to optimize number of queries within simple function:

我有一个简单的spring boot rest应用程序与mySQL db连接,我正在尝试在简单函数中优化查询数量:

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:

执行上述方法后,hibernate生成大约40个SQL(基于40个配置文件),如下所示:

SQL Log - PasteBin

SQL日志 - PasteBin

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

所以首先收集消息,然后为每条消息创建另一个sql来收集配置文件。

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)

有没有可能推动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? (伪代码)

Thanks!

1 个解决方案

#1

This could be a n + 1 problem.

这可能是一个n + 1问题。

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

您可以在JPA查询中使用JOIN FETCH来解决此问题。

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.

“fetch”连接允许使用单个select来初始化值的关联或集合及其父对象。这对于集合来说尤其有用。它有效地覆盖了关联和集合的映射文件的外连接和延迟声明。

Update your JPA repository like so

像这样更新您的JPA存储库

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.

PS:我还没有测试过查询。

#1