The Scroll API offers filtering capabilities through both offset and keyset methods. Unlike offset-based filtering, which necessitates the retrieval of the entire result set from the database, keyset filtering is designed to optimize performance by constructing smaller, more efficient result sets, thereby reducing the computation and IO load on the database. Let us explore the Spring Data JPA Scroll API.
1. Introduction
The Scroll API represents a valuable enhancement within Spring Data JPA, catering to the requirement of handling extensive datasets without the burden of loading everything into memory. It facilitates the streamlined traversal of database records, akin to a streaming approach, effectively averting memory depletion issues when managing substantial result sets.
2. Use-case
Imagine situations where your application is tasked with managing a substantial volume of database records. Retrieving all these records simultaneously into memory can result in performance bottlenecks and an escalation in memory consumption. The Scroll API offers a remedy by enabling you to sequentially stream records, mitigating these concerns effectively.
3. Developing a Spring boot Scroll API application
Let’s delve into some practical exercises. Please note that for simplicity I will skip the main implementation class and focus on the code relevant to this article. In case anyone is interested in understanding the project structure:
3.1 Setting up PostgreSQL on Docker
Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.
Docker commands
-- command to run Postgres on docker – -- Remember to change the password -- docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the postgresql database server will be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool to connect to the server.
3.1.1 Setting up pre-requisite data
To play with the application add the mock data to the database. You can use the below sql script to create a table car and insert mock data into it.
create table car (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
make VARCHAR(50),
model_year VARCHAR(50)
);
insert into car (name, make, model_year) values ('Malibu', 'Chevrolet', 2004);
insert into car (name, make, model_year) values ('Silverado 2500', 'Chevrolet', 2008);
insert into car (name, make, model_year) values ('Reno', 'Suzuki', 2006);
insert into car (name, make, model_year) values ('Wrangler', 'Jeep', 2006);
insert into car (name, make, model_year) values ('LaCrosse', 'Buick', 2010);
insert into car (name, make, model_year) values ('Grand Vitara', 'Suzuki', 2010);
insert into car (name, make, model_year) values ('Rio5', 'Kia', 2008);
insert into car (name, make, model_year) values ('Ram Van 1500', 'Dodge', 2000);
insert into car (name, make, model_year) values ('4000CS Quattro', 'Audi', 1987);
insert into car (name, make, model_year) values ('MP4-12C', 'McLaren', 2012);
insert into car (name, make, model_year) values ('Chariot', 'Mitsubishi', 1995);
insert into car (name, make, model_year) values ('ZX2', 'Ford', 2001);
insert into car (name, make, model_year) values ('Grand Marquis', 'Mercury', 2003);
insert into car (name, make, model_year) values ('Equinox', 'Chevrolet', 2009);
insert into car (name, make, model_year) values ('Range Rover', 'Land Rover', 2005);
insert into car (name, make, model_year) values ('Corvette', 'Chevrolet', 2009);
insert into car (name, make, model_year) values ('62', 'Maybach', 2009);
insert into car (name, make, model_year) values ('8 Series', 'BMW', 1995);
insert into car (name, make, model_year) values ('Concorde', 'Chrysler', 1993);
insert into car (name, make, model_year) values ('Cutlass Supreme', 'Oldsmobile', 1992);
insert into car (name, make, model_year) values ('Freestar', 'Ford', 2004);
insert into car (name, make, model_year) values ('Laser', 'Ford', 1989);
insert into car (name, make, model_year) values ('Cougar', 'Mercury', 1997);
insert into car (name, make, model_year) values ('1 Series', 'BMW', 2009);
insert into car (name, make, model_year) values ('Suburban 2500', 'Chevrolet', 1993);
insert into car (name, make, model_year) values ('Summit', 'Eagle', 1994);
insert into car (name, make, model_year) values ('Quest', 'Nissan', 1998);
insert into car (name, make, model_year) values ('Enclave', 'Buick', 2011);
insert into car (name, make, model_year) values ('A6', 'Audi', 2010);
insert into car (name, make, model_year) values ('Aztek', 'Pontiac', 2003);
insert into car (name, make, model_year) values ('X-Type', 'Jaguar', 2006);
insert into car (name, make, model_year) values ('J', 'Infiniti', 1996);
insert into car (name, make, model_year) values ('F-Series', 'Ford', 1988);
insert into car (name, make, model_year) values ('Blazer', 'Chevrolet', 2004);
insert into car (name, make, model_year) values ('Ranger', 'Ford', 1986);
insert into car (name, make, model_year) values ('Sunbird', 'Pontiac', 1992);
insert into car (name, make, model_year) values ('Sonata', 'Hyundai', 2011);
insert into car (name, make, model_year) values ('ES', 'Lexus', 2011);
insert into car (name, make, model_year) values ('Town Car', 'Lincoln', 1991);
insert into car (name, make, model_year) values ('Protege', 'Mazda', 1993);
insert into car (name, make, model_year) values ('F150', 'Ford', 2001);
insert into car (name, make, model_year) values ('Mustang', 'Ford', 1966);
insert into car (name, make, model_year) values ('Eclipse', 'Mitsubishi', 2006);
insert into car (name, make, model_year) values ('Eclipse', 'Mitsubishi', 2011);
insert into car (name, make, model_year) values ('90', 'Audi', 1994);
insert into car (name, make, model_year) values ('A4', 'Audi', 2003);
insert into car (name, make, model_year) values ('CC', 'Volkswagen', 2013);
insert into car (name, make, model_year) values ('Jetta', 'Volkswagen', 1990);
insert into car (name, make, model_year) values ('LeBaron', 'Chrysler', 1993);
insert into car (name, make, model_year) values ('Seville', 'Cadillac', 1999);
3.2 Incorporating Dependencies into pom.xml
Establish a fresh Spring Boot project using Spring Initialzr or leverage an existing one. Include the necessary dependencies in your project’s pom.xml file.
pom.xml
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
3.3 Configure Application Properties
Let us understand the application.properties.
server.port: Specifies the port number on which the server will listen. In this case, it’s set to 4444.spring.datasource.url: Defines the URL for the PostgreSQL database to which your application will connect. It includes the host (localhost), port (5432), and the database name (mydatabase).spring.datasource.username: Specifies the username used to authenticate with the PostgreSQL database. Here, it’s set to Postgres.spring.datasource.password: Represents the password used for database authentication. In this case, it’s set to admin@1234.spring.jpa.properties.hibernate.dialect: Indicates the Hibernate dialect for PostgreSQL, ensuring compatibility between Spring JPA and the database.spring.jpa.hibernate.ddl-auto: Controls the behavior of Hibernate regarding database schema creation and modification. It’s set to update, meaning that Hibernate will automatically update the schema based on entity changes.spring.jpa.show-sql: When set to true, this property instructs Spring JPA to log SQL statements executed by Hibernate for debugging and analysis purposes.spring.jpa.open-in-view: Setting this property to false disables the “open-in-view” feature, which controls whether database connections are kept open throughout the entire request-response cycle. Disabling it can help prevent potential resource leaks.
application.properties
# spring property server.port=4444 # spring jpa properties spring.datasource.url=jdbc:postgresql://localhost:5432/mydatabase spring.datasource.username=postgres spring.datasource.password=admin@1234 spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect spring.jpa.hibernate.ddl-auto=update spring.jpa.show-sql=true spring.jpa.open-in-view=false
3.4 Create a Model Class
The Java class Car serves as a model entity that encapsulates car-related information. Annotated with Java Persistence API (JPA) annotations, it establishes a mapping between the Java class and a corresponding database table. The @Entity annotation signifies its status as a JPA entity, signifying its instances’ connection to database rows. The @Table annotation specifies the table name as “car.” Furthermore, the @Id annotation designates the id field as the primary key, uniquely identifying each car record. Simple fields like name and make are implicitly mapped to corresponding database columns. The @Column annotation customizes the mapping for the modelYear field, linking it to a database column named “model_year.” Getters and setters are provided for property access, and a custom toString method furnishes a formatted string representation of a Car object. This class is integral in facilitating interactions between a Java application, a relational database, and Spring Data JPA, enabling CRUD operations on car records within the “car” table.
Car.java
package com.example.demo.model;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
@Entity
@Table(name = "car")
public class Car {
@Id
private int id;
private String name;
private String make;
@Column(name = "model_year")
private String modelYear;
//getters and setters methods
@Override
public String toString() {
return "Car{" +
"id=" + id +
", name='" + name + '\'' +
", make='" + make + '\'' +
", modelYear='" + modelYear + '\'' +
'}';
}
}
3.5 Create a Repository Interface
Create an interface to interact with the database. The findFirst3ByModelYear method is a custom query method typically found in a Spring Data JPA repository interface. It serves the purpose of retrieving a window of up to three Car entities from a database based on the specified modelYear and provided OffsetScrollPosition. The modelYear parameter acts as a filter, allowing you to narrow down the results to Car entities with a specific model year. The OffsetScrollPosition position parameter determines the starting point or scroll position for fetching the data, and its type suggests it’s used for specifying an offset or the initial position when dealing with potentially large result sets. The method returns a Window object containing the selected Car entities. This approach is precious for implementing pagination or windowing in applications, as it enables data retrieval in manageable batches, improving performance and memory efficiency when working with extensive datasets. The OffsetScrollPosition parameter allows for efficient navigation through large sets of records.
CarRepository.java
package com.example.demo.repository;
import com.example.demo.model.Car;
import org.springframework.data.domain.OffsetScrollPosition;
import org.springframework.data.domain.Window;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface CarRepository extends JpaRepository<Car, Integer> {
Window<Car> findFirst3ByModelYear(String modelYear, OffsetScrollPosition position);
}
3.6 Create a Service Layer Class
The provided method, findCarsUsingOffsetFilteringAndWindowIterator, is designed to retrieve a list of cars based on a specified year while implementing a form of paging or windowing mechanism. It takes a single parameter, year representing the target year for car retrieval. The method employs a custom WindowIterator to abstract the process of fetching data in manageable chunks or windows. Inside the WindowIterator.of(...), there’s a lambda expression responsible for retrieving the next window of car data from the repository. This lambda invokes the repository.findFirst3ByModelYear(year, (OffsetScrollPosition) scrollPosition) method, which appears to fetch the first three cars for the given year, utilizing a scrollPosition to determine the starting point for the next window of data. The initial position is set using ScrollPosition.offset(). The method iterates through the windows, accumulating cars in an ArrayList named cars. Finally, it returns the cars list, which should contain all the cars retrieved from the database. This approach enables the retrieval of potentially large datasets in smaller, memory-efficient windows, improving performance when dealing with substantial data volumes.
CarService.java
package com.example.demo.service;
import com.example.demo.model.Car;
import com.example.demo.repository.CarRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.OffsetScrollPosition;
import org.springframework.data.domain.ScrollPosition;
import org.springframework.data.support.WindowIterator;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
@Service
public class CarService {
@Autowired
private CarRepository repository;
public List<Car> findAll() {
return repository.findAll();
}
public List<Car> findCarsUsingOffsetFilteringAndWindowIterator(String year) {
final WindowIterator<Car> carWindowIterator =
WindowIterator.of(scrollPosition -> repository.findFirst3ByModelYear(year, (OffsetScrollPosition) scrollPosition))
.startingAt(ScrollPosition.offset());
final List<Car> cars = new ArrayList<>();
carWindowIterator.forEachRemaining(cars::add);
return cars;
}
}
3.7 Create a Controller Class
The Java class CarController is a pivotal component in a Spring Boot application, serving as a RESTful web service controller for managing car-related data. Annotated with @RestController, it signifies its role in handling HTTP requests. The @RequestMapping("/api") annotation establishes a base URL path for this controller. Within the controller, an instance of CarService is injected using @Autowired, enabling access to car-related operations. Two HTTP GET endpoints are defined: "/api/all" and "/api/scrollapi". The former, mapped to @GetMapping("/all"), retrieves a list of all cars by invoking the findAll method of CarService. The latter, mapped to @GetMapping("/scrollapi"), calls the findCarsUsingOffsetFilteringAndWindowIterator method with the year “2009” as a parameter to retrieve car data with a specific year filtering. Both methods return results encapsulated in ResponseEntity objects, ensuring appropriate HTTP status codes for successful responses. This CarController plays a pivotal role in exposing car-related functionalities via RESTful web services in the application.
CarController.java
package com.example.demo.controller;
import com.example.demo.model.Car;
import com.example.demo.service.CarService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/api")
public class CarController {
@Autowired
private CarService service;
// HTTP GET - http://localhost:4444/api/all
@GetMapping("/all")
public ResponseEntity<List<Car>> findAll() {
return ResponseEntity.ok(service.findAll());
}
// HTTP GET - http://localhost:4444/api/scrollapi
@GetMapping("/scrollapi")
public ResponseEntity<List<Car>> findByScrollApi() {
return ResponseEntity.ok(service.findCarsUsingOffsetFilteringAndWindowIterator("2009"));
}
}
3.8 Run the Application
Now you can run your Spring Boot application via the main class DemoApplication.java. You can test the different endpoints using tools like curl or a REST client like the Postman tool.
Curl Requests
-- GET all cars http://localhost:4444/api/all -- GET car using scroll api http://localhost:4444/api/scrollapi
4. Conclusion
In conclusion, leveraging the Scroll API within a Spring Boot application provides valuable benefits, primarily through the implementation of pagination. Pagination offers a range of advantages that enhance the application’s performance, usability, and scalability. Firstly, it significantly boosts performance by enabling the retrieval and display of data in smaller, more manageable portions, reducing database and server load and ensuring faster response times. Secondly, pagination conserves system resources by loading only the necessary data, making the application more memory-efficient and capable of handling extensive datasets without memory issues.
Moreover, it greatly enhances the user experience by facilitating seamless navigation through data, allowing users to swiftly access the specific information they seek. Additionally, as an application grows and accumulates more data, pagination becomes vital for maintaining consistent performance. Its division of data into pages ensures continued responsiveness, even with large and expanding databases. Furthermore, pagination minimizes network latency by reducing data transferred over the network, a crucial aspect when dealing with remote databases or distributed systems.
Customization options such as setting the number of items per page, sorting criteria, and filtering further tailor the user experience. Additionally, pagination can positively impact SEO, as it aids search engines in efficiently indexing paginated content, potentially enhancing a website’s search result visibility. It also promotes data integrity by processing data in smaller batches, reducing the risk of data inconsistencies and conflicts. In Spring Boot, implementing pagination with the Scroll API is straightforward, although striking a balance between page size and the number of pages is essential to avoid overwhelming the database. In summary, Spring Boot’s Scroll API and pagination offer an effective means of managing and presenting large datasets, resulting in improved performance, resource efficiency, and user satisfaction, all while ensuring scalability and responsiveness as data volumes increase.
5. Download the Project
This was a tutorial to understand Scroll API in Spring boot. You can download the source code from the below link.
You can download the full source code of this example here: Spring Data JPA Scroll API
