Pagination for update. Is it possible?

Question

Are they exist some methods to handle pagination for update? For example i have 100 rows of type:

@Id
private Integer id;
@Column
private boolean flag;
@Column
private Date last;

In start they looks like: id, false, null

And i have method in persistence:

@Query("SELECT t FROM Test t WHERE (t.last < :processStart OR t.last IS NULL)")
Page<Test> find(Pageable pageable, @Param("processStart") Date processStart);

I need take 10, set flag true and last = new Date() for each and save it back to DB.

Implementation looks like:

Date start = new Date();
for (int i = 0; i < 10; i++) {
    Pageable request = new PageRequest(i, 10, SortDirectuin.ASC, "id");
    Page page = testPersistence.find(request, start);
    page.getContext().forEach(t -> {
        ..huge part of another operation..
        t.setFlag(true);
        t.setLast(new Date());
        testPersistence.save(t);
    });
}

First page context should be with id: 0..9; Second: 10..19;

But in my case second page returns with id: 20..29;

And in end of pagination i lost half of data. Returns: 0..9, 20..29, 40..49, etc.

How to prevent this?

You are chancing the data and as such the query result will change. You are setting a date so the results returned is different at that moment. Instead of 100 results you suddenly get 90 (and get the second page of those, which means you are skipping the first). But why not simply use an update query to update all records in one go? — M. Deinum, Jul 12 '16 at 09:31
Because this is only example, in real i need to do some operations with another model, set flag true as is and save it, so update query does not deal with my case. — DamienMiheev, Jul 12 '16 at 10:56

score 6 · Answer 1 · answered Jul 13 '16 at 11:19

I invented one bicycle method, and I try to explain it:

Pagination works like this:

1) row1 |        2) row1
   row2 | get1      row2
   row3 |           row3
   row4             row4 |
   row5             row5 | get2
   row6             row6 |
   row7             row7
   row8             row8
   row9             row9

But in my case my rows are changed and they do not comply with the request. So pagination looks like:

1) row1 |        2) row4
   row2 | get1      row5
   row3 |           row6
   row4             row7 |
   row5             row8 | get2
   row6             row9 |
   row7             
   row8             
   row9

Oops, i lose one pagination cycle set.

My solution is not increment page. And get first page all time It will be looks like:

1) row1 |        2) row4 |       3) row7 |
   row2 | get1      row5 | get1     row8 | get1
   row3 |           row6 |          row9 |
   row4             row7
   row5             row8
   row6             row9
   row7             
   row8             
   row9

In this case i don't lose any rows. But i repeat, it is so bicycle solution and it maybe does not work in another case.

Pagination for update. Is it possible?

1 Answers1

Linked