How to do paged request - Spring boot

Question

in my system (Spring boot project) I need to make a request to every 350 people that I search for my data, I need to page and go sending. I looked for a lot of ways to do it and found a lot of it with JPA but I'm using Jooq, so I asked for help with the user's tool and they guided me to use the options of limit and offset.

This is the method where I do the research, I set up my DTO and in the end I return the list of people.

 public static ArrayList getAllPeople(Connection connection) {
    ArrayList<peopleDto> peopleList = new ArrayList<>();
    DSLContext ctx = null;
    peopleDto peopleDto;
    try {
        ctx = DSL.using(connection, SQLDialect.MYSQL);
        Result<Record> result = ctx.select()
                .from(people)
                .orderBy(people.GNUM)
                .offset(0)
                .limit(350)
                .fetch();

        for (Record r : result) {

            peopleDto = new peopleDto();
            peopleDto.setpeopleID(r.getValue(people.GNUM));
            peopleDto.setName(r.get(people.SNAME));
            peopleDto.setRM(r.get(people.SRM));
            peopleDto.setRG(r.get(people.SRG));
            peopleDto.setCertidaoLivro(r.get(people.SCERT));
            peopleDto.setCertidaoDistrito(r.get(people.SCERTD));
            peopleList.add(peopleDto);
        }
    } catch (Exception e) {
        log.error(e.toString());
    } finally {
        if (ctx != null) {
            ctx.close();
        }
    }
    return peopleList;
}

This search without the limitations returns 1,400 people. The question is how do I send up the limit number then return to this method to continue where I left off last until I reach the total value of records?

Answer 1

Feed your method with a Pageable parameter and return a Page from your method. Something along the lines of ...

public static ArrayList getAllPeople(Connection connection, Pageable pageable) {
    ArrayList<peopleDto> peopleList = new ArrayList<>();
    DSLContext ctx = null;
    peopleDto peopleDto;
    try {
        ctx = DSL.using(connection, SQLDialect.MYSQL);
        Result<Record> result = ctx.select()
                .from(people)
                .orderBy(people.GNUM)
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();

        for (Record r : result) {

            peopleDto = new peopleDto();
            peopleDto.setpeopleID(r.getValue(people.GNUM));
            peopleDto.setName(r.get(people.SNAME));
            peopleDto.setRM(r.get(people.SRM));
            peopleDto.setRG(r.get(people.SRG));
            peopleDto.setCertidaoLivro(r.get(people.SCERT));
            peopleDto.setCertidaoDistrito(r.get(people.SCERTD));
            peopleList.add(peopleDto);
        }
    } catch (Exception e) {
        log.error(e.toString());
    } finally {
        if (ctx != null) {
        ctx.close();
        }
    }
    return new PageImpl(peopleList, pageable, hereyoushouldQueryTheTotalItemCount());
}

Now you can do something with those 350 Users. With the help of the page you can now iterate over the remaining people:

if(page.hasNext())
    getAllPeople(connection, page.nextPageable());

Inspired by this article Sorting and Pagination with Spring and Jooq