**Last Update:** 09.02.2025
***
> [!INFO]
> All code examples discussed in these notes are available here on [GitHub](https://github.com/ehayik/spring-processing-large-results-sets).
> [!INFO]
> This technique is particularly suited for batch operations where you need to process daily sales data, reconcile transactions, or transform huge datasets without overwhelming the memory.
> [!HINT]
> Before diving into techniques like `ScrollableResults` and `StatelessSession`, make sure to read [[What Is the JDBC Fetch Size and Why Does It Matter?]] to understand the basics of JDBC result set fetching and its impact on memory and performance.
We will use:
1. [StatelessSession](https://docs.jboss.org/hibernate/stable/orm/javadocs/org/hibernate/StatelessSession.html): **Specially made for performing bulk operations** on a DB. It bypasses caching and many validations.
2. [ScrollableResults](https://docs.jboss.org/hibernate/stable/core/javadocs/org/hibernate/ScrollableResults.html): Helps in fetching the result at arbitrary index of the results. Ideally, **it loads the results in memory on 'as-needed'** basis however, **it depends on your driver implementation**. Due to this as-needed loading of results, the *ScrollableResults* have a very small memory footprint.
Additionally:
- Set `ScrollMode` to `FORWARD_ONLY`. This means once you have called `next()`, you would not be able to move back.
- Set `readOnly` is set to `true`. This would help as it causes no transactions to be created.
- 1. Set `lockMode` to `NONE`. As we are not writing anything, there is no lock required.
- [[What Is the JDBC Fetch Size and Why Does It Matter?| Set fetchSize]] to avoid `OutOfMemoryError`.
> [!HINT]
> in OLTP applications, you should always strive for keeping the JDBC `ResultSet` as small as possible. That’s why batch processing and pagination queries are usually a better alternative than streaming a large result set.
Prior to version [5.3](https://hibernate.org/orm/releases/5.3/)
```java
var statelessSession =
((Session) entityManager.getDelegate()).getSessionFactory().openStatelessSession();
try (statelessSession) {
var query = statelessSession.createQuery("FROM Employee e", Employee.class);
query.setReadOnly(true);
query.setFetchSize(Integer.MIN_VALUE);
query.setLockMode("e", NONE);
try (var employees = query.scroll(FORWARD_ONLY)) {
while (employees.next()) {
log.debug("Employee's name is: {}", employees.get().getFullName());
}
}
}
```
Version 5.3+
```java
var statelessSession =
((Session) entityManager.getDelegate()).getSessionFactory().openStatelessSession();
try (statelessSession) {
var query = statelessSession
.createQuery("FROM Employee e", Employee.class)
.setReadOnly(true)
.setFetchSize(Integer.MIN_VALUE)
.setLockMode("e", NONE);
try (var employeesStream = query.stream()) {
employeesStream.forEach(employee -> log.debug("Employee's name is: {}", employee.getFullName()));
}
}
```
***
**References**:
- [Spring Hibernate : Processing a huge table](https://medium.com/@venkateshshukla/spring-hibernate-processing-a-huge-table-68ebad17cd08)
- [What’s new in JPA 2.2 – Stream the result of a Query execution](https://vladmihalcea.com/whats-new-in-jpa-2-2-stream-the-result-of-a-query-execution/)
- [How does MySQL result set streaming perform vs fetching the whole JDBC ResultSet at once](https://vladmihalcea.com/how-does-mysql-result-set-streaming-perform-vs-fetching-the-whole-jdbc-resultset-at-once/)