英文:
How do you limit the fetch query size in Hibernate JPA?
问题
我遇到了这个错误 https://stackoverflow.com/questions/13759418/com-mysql-jdbc-packettoobigexception，并且在跟踪代码时，发现它出现在以下场景中（但仅在测试时出现）
public interface Students extends JpaRepository<Student, Integer> {
@EntityGraph("Student.withProgramAndSchedule")
@Query("from Student s")
Iterable<Student> findAllWithProgramAndSchedule();
}
@Entity
@NamedEntityGraph(
name = "Student.withProgramAndSchedule",
attributeNodes = {
@NamedAttributeNode("programEnrollments"),
@NamedAttributeNode("schedules")
}
)
public class Student implements Serializable {
...
}
仅在 DataJpaTest 期间，即使我已经清除了实体管理器，它似乎没有清除实体管理器上下文
@After
public void clearEntityManager() {
entityManager.clear();
}
由于这是使用匿名化的真实数据，它有数千条记录，发生的情况是它创建了一个类似于以下查询
select ... from ... where studentSchedule_0.id in (?, ?, ... 数千个稍后的 ?, ... ?)
然后它超过了1MB的数据包限制。
因此我的问题是（因为我在参考文献中找不到这个问题），在执行带有这些 ID 的获取操作时，是否有可能限制查询大小？
英文:
I am getting this error https://stackoverflow.com/questions/13759418/com-mysql-jdbc-packettoobigexception and tracing through the code, it appears that it occurs on the following scenario (but only on tests)
public interface Students extends JpaRepository<Student, Integer> {
@EntityGraph("Student.withProgramAndSchedule")
@Query("from Student s")
Iterable<Student> findAllWithProgramAndSchedule();
}
@Entity
@NamedEntityGraph(
name = "Student.withProgramAndSchedule",
attributeNodes = {
@NamedAttributeNode("programEnrollments"),
@NamedAttributeNode("schedules")
}
)
public class Student implements Serializable {
...
}
Only during DataJpaTest it appears it does not clean up the Entity Manager context even when I had cleared the entity manager
@After
public void clearEntityManager() {
entityManager.clear();
}
Since this is with anonymized real data, it has thousands of records and what happens is it creates a query that looks like
select ... from ... where studentSchedule_0.id in (?, ?, ... thousands of ? later, ... ?)
Then it blows the 1MB packet limit.
So my question is (because I can't find it in the references) is it possible to limit the query size when it does the fetch with the IDs?
答案1
得分: 1
以下是您要翻译的内容：
我现在不在一个可以编写代码来确认我认为会起作用的地方。
使用如下的findFirst：
findFirstWithProgramAndSchedule() // 获取一个
或者限制为前10个：
findFirst10WithProgramAndSchedule()
尽管我今天不能编写代码，但我尝试提供帮助。因此，我等待看是否有其他人有答案，但我相信这将按照参考文献的说明起作用。
参考文献：
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.limit-query-result
英文:
I am not in a place that let's me code up to confirm what I think will work.
Using findFirst as below:
findFirstWithProgramAndSchedule() // gets one
Or to limit to the first 10
findFirst10WithProgramAndSchedule()
I am trying to be helpful, even though I can't code it up today. Thus, I waited to see if others had the answer, but I believe this will work per the references.
Reference:
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.limit-query-result
答案2
得分: 1
你可以使用JPA分页来限制查询结果：
Iterable<Student> findAllWithProgramAndSchedule(Pageable pageable);
而对于测试：
Pageable page = PageRequest.of(1, 5);
英文:
You can use jpa pagination to limit query result :
Iterable<Student> findAllWithProgramAndSchedule(Pageable pageable);
and for test:
Pageable page= PageRequest.of(1, 5);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论