Почему Spring Data делает лишние запросы?

15
26 июня 2019, 05:20

Есть следующий метод JPA репозитория:

@Query("select new com.example.sweater.domain.dto.MessageDto(" +
        "   m, " +
        "   count(ml), " +
        "   sum(case when ml = :user then 1 else 0 end) > 0" +
        ") " +
        "from Message m left join m.likes ml " +
        "group by m")
Page<MessageDto> findAll(Pageable pageable, @Param("user") User user);

Проблема: помимо одного запроса, который указан в аннотации @Query, в базу еще летят запросы на выборку КАЖДОГО сообщения отдельно.

Вопрос: почему так происходит и как с этим бороться?

Весь код доступен на github.

UPD1: Лог запросов:

Hibernate: select message0_.id as col_0_0_, count(user2_.id) as col_1_0_, sum(case when user2_.id=? then 1 else 0 end)>0 as col_2_0_ from message message0_ left outer join message_likes likes1_ on message0_.id=likes1_.message_id left outer join usr user2_ on likes1_.user_id=user2_.id group by message0_.id order by message0_.id desc limit ?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select count(message0_.id) as col_0_0_ from message message0_ left outer join message_likes likes1_ on message0_.id=likes1_.message_id left outer join usr user2_ on likes1_.user_id=user2_.id group by message0_.id

UPD2: Еще заметил, что когда я пытаюсь передать в MessageDto(m,...), то вместо m передается не объект сообщения, как мне надо, а id этого сообщения. Может по этому hibernate ходит в базу за каждым сообщением?

Answer 1

Похоже на проблему N+1 selects. Она возникает, когда вы делаете select связанной, родительской, сущности у которой также имеется ссылка на данный объект. Hibernate делает дополнительные селекты для того чтобы вытащить все связи. Поэтому сначала Hibernate вытащит все N записией родителей, а затем, для каждого по отдельности запросит связные сущности.

Для того чтобы решить данную проблему можно либо явно указать join всех связей в @Query или же для всех связей включить кэш второго уровня:

@Entity
@Cacheable
@Table(name = "usr")
public class User implements UserDetails {
    @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<Message> messages;
}

Проблема в том, что ваши Message и User содержит ссылку друг на друга.

Когда вы указываете FetchType.LAZY, это не означает что он вообще не будет запрашивать все N связей, просто он сделает N select'ов, когда вы обратитесь к этому полю.

Answer 2

Попробуйте использовать аннотацию @NamedEntityGraph

@Entity
@Table(name = "usr")
@NamedEntityGraph(name="allMessages", attributeNodes = {
            @NamedAttributeNode("messages")
    })
public class User implements UserDetails {
...
    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<Message> messages;
...
}

затем используйте этот граф в репозитории

@EntityGraph(value = "allMessages" , type=EntityGraph.EntityGraphType.FETCH)
Page<MessageDto> findAll(Pageable pageable, @Param("user") User user);

Вы можете использовать несколько NamedEntityGraph с разным набором полей, тем самым получая всегда только те поля которые вам сейчас действительно нужны за один запрос

READ ALSO
Закрепить приложение на Android

Закрепить приложение на Android

День! Есть планшет на android с установленным на нём приложением обратной связиСуть такая - лежит такой планшет на кассе в магазине и каждый желающий...

22
Почему не конвертирует

Почему не конвертирует

Ребята я создал конвертор с кириллицы на латиницуНиже предоставил код, он не выдает ошибку правильно компилирует

13
Spring Integration. Пакетная запись JPA сущностей в БД

Spring Integration. Пакетная запись JPA сущностей в БД

В проекте на Spring Integration есть канал, в который поступают JPA сущности и записываются в БД при помощи jpa outbound channel adapterНо jpa outbound channel adapter пишет сущности...

18
Invalid JDK version in profile &#39;java8-and-higher&#39;

Invalid JDK version in profile 'java8-and-higher'

Мавен при инстале дает такую ошибку:

38