Scroll API in Spring Data JPA

1. Overview

Spring Data Commons is a part of the umbrella Spring Data project that contains interfaces and implementations to manage the persistence layer. Scroll API is one of the functions provided by Spring Data Commons to handle large results that are read from the database.

In this tutorial, we’ll explore Scroll API along with an example.

2. Dependency

Scroll API support is added in Spring Boot 3.1 version. Spring Data Commons is already included in Spring Data JPA. So, adding Spring Data JPA 3.1 version would be enough to get Scroll API features:

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jpa</artifactId>
    <version>3.1.0</version>
</dependency>

The newest library version can be found in the Maven Central repository.

3. Entity Class

For example purposes, we’ll use the BookReview entity, which contains book review ratings for various books reviewed by various users:

@Entity
@Table(name="BOOK_REVIEWS")
public class BookReview {
    @Id
    @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "book_reviews_reviews_id_seq")
    @SequenceGenerator(name = "book_reviews_reviews_id_seq", sequenceName = "book_reviews_reviews_id_seq", allocationSize = 1)
    private Long reviewsId;
    private String userId;
    private String isbn;
    private String bookRating;

    // getters and setters
}

4. Scroll API

Scroll API provides functionality to iterate through large results in chunks. It provides stable sort, scroll type, and result limiting.

We can define simple sorting expressions by using property names and define static result limiting using Top or First through query derivation.

4.1. Scrolling Using Offset-Filtering

In the below example, we’re using query derivation to find the first five books by rating parameter and OffsetScrollPosition:

public interface BookRepository extends Repository<BookReview, Long> {
    Window<BookReview> findFirst5ByBookRating(String bookRating, OffsetScrollPosition position);
    Window<BookReview> findFirst10ByBookRating(String bookRating, OffsetScrollPosition position);
    Window<BookReview> findFirst3ByBookRating(String bookRating, KeysetScrollPosition position);
}

Since we’ve defined our repository methods, we can use them inside the logic class to get the first five books and keep iterating until we get to the last result.

While iterating, we need to check for the presence of the next window by querying for it:

public List<BookReview> getBooksUsingOffset(String rating) {
    OffsetScrollPosition offset = ScrollPosition.offset();

    Window<BookReview> bookReviews = bookRepository.findFirst5ByBookRating(rating, offset);
    List<BookReview> bookReviewsResult = new ArrayList<>();
    do {
        bookReviews.forEach(bookReviewsResult::add);
        bookReviews = bookRepository.findFirst5ByBookRating(rating, (OffsetScrollPosition) bookReviews.positionAt(bookReviews.size() - 1));
    } while (!bookReviews.isEmpty() && bookReviews.hasNext());

    return bookReviewsResult;
}

We can simplify our logic by using WindowIterator, which provides the utility to scroll through the large result without the need to check for the next window and ScrollPosition:

public List<BookReview> getBooksUsingOffSetFilteringAndWindowIterator(String rating) {
    WindowIterator<BookReview> bookReviews = WindowIterator.of(position -> bookRepository
      .findFirst5ByBookRating("3.5", (OffsetScrollPosition) position)).startingAt(ScrollPosition.offset());
    List<BookReview> bookReviewsResult = new ArrayList<>();
    bookReviews.forEachRemaining(bookReviewsResult::add);

    return bookReviewsResult;
}

Offset scrolling works like pagination, which returns expected results by skipping a certain number of records from a large result. While we only see a portion of the requested results, the server needs to build the full result, which causes additional load.

We can avoid this behavior using keyset filtering.

4.2. Scrolling Using Keyset-Filtering

Keyset filtering helps the retrieval of a subset of results using the built-in capabilities of the database aiming to reduce the computation and IO requirements for individual queries.

The database only needs to construct smaller results from the given keyset position without materializing a large full result:

public List<BookReview> getBooksUsingKeySetFiltering(String rating) {
    WindowIterator<BookReview> bookReviews = WindowIterator.of(position -> bookRepository
      .findFirst5ByBookRating(rating, (KeysetScrollPosition) position))
      .startingAt(ScrollPosition.keyset());
    List<BookReview> bookReviewsResult = new ArrayList<>();
    bookReviews.forEachRemaining(bookReviewsResult::add);

    return bookReviewsResult;
}

5. Conclusion

In this article, we explored Scroll API provided by the Spring Data Commons library. Scroll API provides support for reading large results in smaller chunks that are based on offset position and filter condition.

Scroll API supports filtering using offset and keyset. While offset-based filtering requires materializing the entire results in the database, the keyset helps reduce computation and IO load on the database by constructing smaller results.