Limit and Offset with Spring Data JPA Repositories

Limit and offset are two properties often requested for requesting data out of a database. Implementing them with a SQL query is also not that difficult. In this article I will describe how to achieve this with the magic of Spring Data JPA repositories (without SQL).

The Entity

First, we need a class describing the entities which should be retrieved from the database. We annotate the class with @Entity to mark it as a representative of a database table. The class describes how the table in the database should look like. Each object of this class would be stored as one row in this table (in a relational database).

@Entity
@Data
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String name;

}

• @Data is an annotation of Lombok generating automatically i.a. constructor, getter and setter methods.
• @Id marks the primary key. (The data type could also be different.)
• @GeneratedValue generates an id automatically. The user does not have to set an id manually.

The Repository

As a next step, we would have to implement an interface which has the name of the class plus the term Repository. Additionally, it has to extend either CrudRepository, PagingAndSortingRepository or JpaRepository. The first repository does not offer the options for paging and sorting while the last one offers the most functionality.

public interface EmployeeRepository extends JpaRepository<Employee, Integer> { }

• For the extending repository, it has to be specified for which class this interface functions and which datatype the primary key has.

In the repository, no method has to be defined and no class has to implement any method. It automatically offers the typical CRUD functions (create, read, update and delete) and find functions with paging and sorting. Therefore, we could just say things like repository.findAll(PageRequest.of(1,5). This would return a Page<Employee> which does not offer the requested limit and offset directly.

Limit and Offset

To send limit and request and get a List<Employee>, we will have to translate the given limit and request into a new Pageable object. After receiving the Page as a result from the repository, we would expose the requested list.

For translating limit and offset into a Pageable, we create a new class implementing Pageable.

public class OffsetBasedPageRequest implements Pageable {

    private int limit;

    private int offset;
    
    // Constructor could be expanded if sorting is needed
    private Sort sort = new Sort(Sort.Direction.DESC, "id");

    public OffsetBasedPageRequest(int limit, int offset) {
        if (limit < 1) {
            throw new IllegalArgumentException("Limit must not be less than one!");
        }
        if (offset < 0) {
            throw new IllegalArgumentException("Offset index must not be less than zero!");
        }
        this.limit = limit;
        this.offset = offset;
    }

    @Override
    public int getPageNumber() {
        return offset / limit;
    }

    @Override
    public int getPageSize() {
        return limit;
    }

    @Override
    public long getOffset() {
        return offset;
    }

    @Override
    public Sort getSort() {
        return sort;
    }

    @Override
    public Pageable next() {
        // Typecast possible because number of entries cannot be bigger than integer (primary key is integer)
        return new OffsetBasedPageRequest(getPageSize(), (int) (getOffset() + getPageSize()));
    }

    public Pageable previous() {
        // The integers are positive. Subtracting does not let them become bigger than integer.
        return hasPrevious() ?
                new OffsetBasedPageRequest(getPageSize(), (int) (getOffset() - getPageSize())): this;
    }

    @Override
    public Pageable previousOrFirst() {
        return hasPrevious() ? previous() : first();
    }

    @Override
    public Pageable first() {
        return new OffsetBasedPageRequest(getPageSize(), 0);
    }

    @Override
    public boolean hasPrevious() {
        return offset > limit;
    }
}

Then, we could create objects of this class in the service layer, call the repository and expose the list.

@Service
@Slf4j
public class EmployeeServiceImpl implements EmployeeService {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Override
    public List<Employee> getAllEmployees(int limit, int offset) {
        log.debug("Get all Employees with limit {} and offset {}", limit, offset);
        Pageable pageable = new OffsetBasedPageRequest(limit, offset);
        return employeeRepository.findAll(pageable).getContent();
    }
}

Connection to Database

For the whole solution to function, the app should connect to a database. Connections to different types of databases are possible. To keep it easy, we would just mention the option to connect to a H2 in-memory database.

First step would be adding the H2 dependencies in the dependency management programme (e.g. Gradle or Maven). Afterwards, I usually add only the information about enabling the console to application.properties.

spring.h2.console.enabled=true
spring.h2.console.path=/h2

Have some success with providing the options of limit and offset.