How to query data via Spring data JPA with user defined offset and limit (Range)

Question

Is it possible to fetch data in user defined ranges [int starting record -int last record]?

In my case user will define in query String in which range he wants to fetch data. I have tried something like this

Pageable pageable = new PageRequest(0, 10);
    Page<Project> list = projectRepository.findAll(spec, pageable);

Where spec is my defined specification but unfortunately this do not help. May be I am doing something wrong here.

I have seen other spring jpa provided methods but nothing are of much help.

user can enter something like this localhost:8080/Section/employee? range{"columnName":name,"from":6,"to":20}

So this says to fetch employee data and it will fetch the first 15 records (sorted by columnName ) does not matter as of now.

If you can suggest me something better that would be great.if you think I have not provided enough information please let me know, I will provide required information.

Update :I do not want to use native or Create query statements (until I don't have any other option). May be something like this:

Pageable pageable = new PageRequest(0, 10);
        Page<Project> list = projectRepository.findAll(spec, new pageable(int startIndex,int endIndex){
// here my logic.

});

If you have better options, you can suggest me that as well.

Thanks.

Answer 1

Your approach didn't work, because new PageRequest(0, 10); doens't do what you think. As stated in docs, the input arguments are page and size, not limit and offset.

As far as I know (and somebody correct me if I'm wrong), there is no "out of the box" support for what you need in default SrpingData repositories. But you can create custom implementation of Pagable, that will take limit/offset parameters. Here is basic example - Spring data Pageable and LIMIT/OFFSET

Answer 2

We can do this with Pagination and by setting the database table column name, value & row counts as below:

 @Transactional(readOnly=true)
 public List<String> queryEmployeeDetails(String columnName,String columnData, int startRecord, int endRecord) {
    Query query =  sessionFactory.getCurrentSession().createQuery(" from Employee emp where emp.col= :"+columnName);
    query.setParameter(columnName, columnData);
    query.setFirstResult(startRecord);
    query.setMaxResults(endRecord);
    List<String> list = (List<String>)query.list();
    return list;
}

Answer 3

If I am understanding your problem correctly, you want your repository to allow user to

1. Provide criteria for query (through Specification)
2. Provide column to sort
3. Provide the range of result to retrieve.

If my understanding is correctly, then:

In order to achieve 1., you can make use of JpaSpecificationExecutor from Spring Data JPA, which allow you to pass in Specificiation for query.

Both 2 and 3 is achievable in JpaSpecificationExecutor by use of Pagable. Pageable allow you to provide the starting index, number of record, and sorting columns for your query. You will need to implement your range-based Pageable. PageRequest is a good reference on what you can implement (or you can extend it I believe).

Answer 4

So i got this working as one of the answer suggested ,i implemented my own Pageable and overrided getPagesize(),getOffset(),getSort() thats it.(In my case i did not need more)

public Range(int startIndex, int endIndex, String sortBy) {
        this.startIndex = startIndex;
        this.endIndex = endIndex;
        this.sortBy = sortBy;
    }

    @Override
    public int getPageSize() {
        if (endIndex == 0)
            return 0;
        return endIndex - startIndex;
    }

    @Override
    public int getOffset() {
        // TODO Auto-generated method stub
        return startIndex;
    }

    @Override
    public Sort getSort() {
        // TODO Auto-generated method stub
        if (sortBy != null && !sortBy.equalsIgnoreCase(""))
            return new Sort(Direction.ASC, sortBy);
        else
            return new Sort(Direction.ASC, "id");
    }

where startIndex ,endIndex are starting and last index of record.

to access it :

repository.findAll(spec,new Range(0,20,"id");

Answer 5

There is no offset parameter you can simply pass. However there is a very simple solution for this:

int pageNumber = Math.floor(offset / limit) + ( offset % limit );
PageRequest pReq = PageRequest.of(pageNumber, limit);

The client just have to keep track on the offset instead of page number. By this I mean your controller would receive the offset instead of the page number.

Hope this helps!