How to determine the page number of a record using Spring-data-jpa?

Question

I want to return the page number of the page where a certain record is located. I used a conditional query to return all the pagination results, so I couldn’t determine the page where it was based on the recorded id. How can I solve this problem?

I am searching for a long time on net. But no use. Please help or try to give some ideas how to achieve this.

My temporary solution is to query the records of each page until it contains the records I need, but doing so will cause the query to be slow.

ps: I am using MySQL database.

Answer 1

Assuming you have a record with some kind of id and what to know at what position it appears in your table when the table is ordered by some criteria you can do this with analytic functions. From this value you can trivially calculate the page it appears on, given a page size.

Example Schema (MySQL v8.0)

create table example (
  id integer not null primary key,
  text varchar(20));

insert into example(id, text) values (23,"Alfred");
insert into example(id, text) values (47,"Berta");
insert into example(id, text) values (11,"Carl");
insert into example(id, text) values (42,"Diana");
insert into example(id, text) values (17,"Ephraim");
insert into example(id, text) values (1,"Fiona");
insert into example(id, text) values (3,"Gerald");

---

Query

select * 
from (
    select id, text, 
        count(*) over (order by text) cnt // change the order by according to your needs 
    from example
    // a where clause here limits the table before counting
) x where id = 42; // this where clause finds the row you are interested in

| id  | text  | cnt |
| --- | ----- | --- |
| 42  | Diana | 4   |

---

In order to use it with Spring Data JPA you put this query in a @Query annotation and mark it as a native query.