How to determine where to start fetching data using Spring data query?
Is there a way to target where to apply a query Page
with Spring data? I have some problems to find a suitable solution for this problem:
I have an sql table and I would like to get 3 rows from a given id. I've tried something like this:
Pageable pageable = new PageRequest( 0, 3, Sort.Direction.DESC, "id_user" );
Page<User> users = userReposiotry.findById_user(20L,pageable);
I want to start from 20 to 18, but that only gives me one line. How can I achieve this?
I could create an algorithm with a loop to retrieve this data, but in some case it id_user
doesn't order.
If this is not possible with a solution like this, how can you get 3 lines (or plus) from a given line?
the launch line number is limited by the page size for PageRequest
. Because Pageable.getOffset()
, and Pageable.getPageSize()
are used for performing SQL-query, such as select * from userTable order by id_user limit 3 offset 17
. Pageable.getOffset()
return the start line number , but it is calculated pageNumber*pageSize
in the default implementation. (@see AbstractPageRequest , superclass PageRequest
)
If you want to retrieve data for any line number, the solution creates your own Pageable
. Here's an example:
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
public class MyPageable implements Pageable {
private final int offset;//first record is 0
private final int size;
private final Sort sort;
public MyPageable(int offset, int size, Sort sort) {
if (offset < 0) {
throw new IllegalArgumentException("Page offset must not be less than zero!");
}
if (size < 1) {
throw new IllegalArgumentException("Page size must not be less than one!");
}
this.offset = offset;
this.size = size;
this.sort = sort;
}
public int getPageNumber() {
int page = offset / size;//TBD
return page;
}
public Pageable next() {
return new MyPageable(offset+size, size, sort);
}
public Pageable previousOrFirst() {
int prevoffset = offset-size;//TBD
return new MyPageable((prevoffset<0?0:prevoffset), size, sort);
}
public Pageable first() {
return new MyPageable(0, size, sort);
}
public boolean hasPrevious() {
return offset > 0;
}
public int getOffset() {
return offset;
}
public int getPageSize() {
return size;
}
public Sort getSort() {
return sort;
}
userReposiotry.findById_user(20L, new MyPageable(18-1, 20-18+1, new Sort(Sort.Direction.DESC, "id_user"));
will return 18-20 rows as you wish.