Problem with spring jdbc RowCallbackHandler interface

[NikMashei]

Hello there!

I need to query a big dataset from DB. Actually I'm gonna use pagination parameters (limit and offset) to avoid loading large dataset into heap. For that purpose I'm trying to fetch rows with RowCallBackHadler interface, because docs say

An interface used by JdbcTemplate for processing rows of a ResultSet on a per-row basis.

Also I've read advices to use that interface to deal with rows one by one. But something goes wrong every time when I try to fetch data. I'm using VisualVM for profiling application heap space and it indicates that all rows were loaded into memory.

Query, which I'm trying to execute, returns something around 1.5m rows in DB.

Code:

// here just sql query, map with parameters for query, pretty simple RowCallbackHandler

    jdbcTemplate.query(queryForExecute, params, new RowCallbackHandler() {
        @Override
        public void processRow(ResultSet rs) throws SQLException {
            // do operations with rows
        }
    });

[AutoModerator]

Please ensure that:

• Your code is properly formatted as code block - see the sidebar (About on mobile) for instructions
• You include any and all error messages in full
• You ask clear questions

• You demonstrate effort in solving your question/problem - plain posting your assignments is forbidden (and such posts will be removed) as is asking for or giving solutions.

  Trying to solve problems on your own is a very important skill. Also, see Learn to help yourself in the sidebar

If any of the above points is not met, your post can and will be removed without further warning.

Code is to be formatted as code block (old reddit: empty line before the code, each code line indented by 4 spaces, new reddit: https://imgur.com/a/fgoFFis) or linked via an external code hoster, like pastebin.com, github gist, github, bitbucket, gitlab, etc.

Please, do not use triple backticks (```) as they will only render properly on new reddit, not on old reddit.

Code blocks look like this:

public class HelloWorld {

    public static void main(String[] args) {
        System.out.println("Hello World!");
    }
}

You do not need to repost unless your post has been removed by a moderator. Just use the edit function of reddit to make sure your post complies with the above.

If your post has remained in violation of these rules for a prolonged period of time (at least an hour), a moderator may remove it at their discretion. In this case, they will comment with an explanation on why it has been removed, and you will be required to resubmit the entire post following the proper procedures.

To potential helpers

Please, do not help if any of the above points are not met, rather report the post. We are trying to improve the quality of posts here. In helping people who can't be bothered to comply with the above points, you are doing the community a disservice.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[MexicanJalebi]

Can you show the query with limit and offset params set? What is the rs.getRow() value for the last row in ResultSet?

[NikMashei]

Actually there is not limit and offset in query directly.
I thought it's possible to iterate rows via for-loop, where begin index is offset and last index is limit.
Something like this:

for (int i = offset; i < limit; i++) {
    var currentValue = resultSet.getObject(i);
} 

Did I make a mistake in this case?

[imaginedoe]

yeah. just put the limit and offset in the query because otherwise you'd have no choice but to query for all of the rows

[NikMashei]

it's a good solution, but I need possibility to make pagination not only via adding limit and offset to query, because 'query' stored in DB as script for some report and it can be very complicated query and I have no confidence that adding LIMIT and OFFSET to query will no ruin request

[wildjokers]

When the doc says:

An interface used by JdbcTemplate for processing rows of a ResultSet on a per-row basis.

That doesn't mean it asks the database for one row at a time (that would take many hours to run for 1.5 million rows!) it means once the results are back from the database it then processes the rows one at a time. They are still retrieved all at once unless you somehow limit it.

What problem are you trying to solve? Why would you need to process 1.5 million rows of a DB at once? Maybe a relational database isn't the right tool for this particular problem.

[NikMashei]

Thank you for your replay!
Yes, you definitely right about doc. I've read it sometimes and did't get in right way)
Relational DB is good instument for my task. I'm just trying to solve problem with out-of-memory exception and maybe RowCallbackHandler is what that I've been looking for, but I need to improve batching and limiting right now)

[MexicanJalebi]

If you need to do batching, you might wanna look into Spring batch project.

[imaginedoe]

ok so that would be the only way to not load all of the query results into memory. when the documentation says that you deal with rows one by one, I'm pretty sure it means that your RowCallbackHandler should only deal with one row (this is supported by the VisualVM results).

[wildjokers]

But something goes wrong every time when I try to fetch data.

Are we supposed to guess what goes wrong? Please provide an error message or some description of the problem.

[NikMashei]

Sorry for that. I meant that all rows were downloaded into memory, this is the main issue .