How to request data through Spring JPA data with user-defined offset and limit (Range)

Question

Is it possible to receive data in user ranges [int start record -int last record]?

In my case, the user will determine in the query line in which range he wants to receive data. I tried something like this

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

Where spec is my specific specification, but unfortunately this does not help. Maybe I'm doing something wrong here.

I saw other spring jpa methods, but nothing helped.

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

Thus, this suggests that to retrieve data about employees, he will retrieve the first 15 records (sorted by column name), at the moment it does not matter.

If you can offer me something better, that would be great. If you think that I have not provided sufficient information, please let me know, I will provide the necessary information.

Refresh . I do not want to use my own or create query requests (so far I have no other option). Maybe 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 the best options, you can also suggest me.

Thanks.

Answer 1

Your approach did not work, because new PageRequest(0, 10); Don't do what you think. As pointed out in the docs , the input arguments are page and size , not constraints and offsets.

As far as I know (and someone correct me if I am wrong), there is no support "out of the box" for what you need in the default SrpingData repositories. But you can create your own Pagable implementation that will accept the constraint / offset parameters. Here is a basic example - Spring data and LIMIT / OFFSET

Answer 2

We can do this with Pagination and setting the database table column name, values ​​and rows, as shown 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 understand your problem correctly, you want your repository to allow the user

• Provide request criteria (via Specification )
• Enter a column to sort
• Provide a range of results to retrieve.

If my understanding is correct, then:

To achieve 1. you can use the JpaSpecificationExecutor from Spring JPA data that allows you to pass Specificiation for the request.

Both 2 and 3 are reachable in the JpaSpecificationExecutor using Pagable . Pageable allows you to specify the starting index, number of records, and column sorting for your query. You will need to implement your Pageable range. PageRequest is a good reference to what you can implement (or you can extend it, I believe).

Answer 4

So, I got this work as one of the suggested answers, I implemented my own Pageable and redefined getPagesize () , getOffset () , getSort () thats it. (In my case, I no longer needed)

 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 the start and last index of the record.

to access it:

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