Spring Boot / JPA / mySQL - many to one relationship creates too many SQL queries

Question

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!

Answer 1

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.