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?

You need to keep that cursor around. Your error here is returning a `List` - that's not what you want. You want some other object that you can repeatedly ask for a list. You could write that, but, really, `Cursor` already does that. Your `fetchRecords` method should return `Cursor` directly. — rzwitserloot, Aug 09 '23 at 01:48
@rzwitserloot - Does the fetchrecords() keeps get calling with the new cursorfetch from next Time? — Harish, Aug 09 '23 at 02:39
No. `fetchLazy()` gets you a cursor. That's what you return, that's what you keep calling `.fetchNext` on. — rzwitserloot, Aug 09 '23 at 02:52

score 0 · Accepted Answer · answered Aug 09 '23 at 08:40

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

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

1 Answers1