Есть следующий метод 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 ходит в базу за каждым сообщением?
Похоже на проблему 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'ов, когда вы обратитесь к этому полю.
Попробуйте использовать аннотацию @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 с разным набором полей, тем самым получая всегда только те поля которые вам сейчас действительно нужны за один запрос
Выделенный сервер, что это, для чего нужен и какие характеристики важны?
Современные решения для бизнеса: как облачные и виртуальные технологии меняют рынок
Виртуальный выделенный сервер (VDS) становится отличным выбором
День! Есть планшет на android с установленным на нём приложением обратной связиСуть такая - лежит такой планшет на кассе в магазине и каждый желающий...
Ребята я создал конвертор с кириллицы на латиницуНиже предоставил код, он не выдает ошибку правильно компилирует
В проекте на Spring Integration есть канал, в который поступают JPA сущности и записываются в БД при помощи jpa outbound channel adapterНо jpa outbound channel adapter пишет сущности...