Jooq Java Code to handle more than 200k records from a table

Question

The table in my database has more than 200k records and I don't want to fetch all of them at once in the resultSet to process. I am trying to write a function in a service that calls another persistence class function to bring only 1000 records to process. Once it processes the 1000 records, it should fetch the following thousand records. I am using the JOOQ database library in my Java code. I am just sharing a sample skeleton code that I had in my mind.

class RecordService {
    RecordPersistence recordPersistence = new RecordPersistence();

    public void processRecords() {

        List<Record> records = recordPersistence.fetchRecords();
        // Code to process them
     }
} 


class RecordPersistence {
    public List<Record>  fetchRecords(){
        
        Cursor<Record> records = dsl.select...fetchLazy();

        while (records.hasNext()) {
           records.fetchNext(1000);
        }
        return records
    }
}

How to return only a few records from the fetchRecords() function? Should I write this is an async function? Is this the correct way to process result? or is there a better way to handle my case?

Answer 1

As mentioned in the comments, the problem is that you're keeping the processing logic outside of the fetch logic. Just move it inside, e.g. as follows:

class RecordService {
    RecordPersistence recordPersistence = new RecordPersistence();

    public void processRecords() {

        recordPersistence.fetchRecords(
            (List<Record> records) -> {
                // Code to process them
            }
        );
     }
} 

class RecordPersistence {
    public List<Record> fetchRecords(Consumer<? super List<Record>> consumer) {
        try (Cursor<Record> cursor = dsl.select...fetchLazy()) {
            while (cursor.hasNext()) {
                consumer.accept(cursor.fetchNext(1000));
            }
        }
    }
}

You don't need any paradigm shift (e.g. async, reactive, etc.) for this type of streaming to work. Just switch from external to internal iteration