Search code examples
javamysqlresultsetlarge-data

How to fetch limited data from mysql java

Can I limit the number of rows in result set? My table contains some 800000 rows, if I fetch them in result set, this will definitely lead to OOM exception. each row has 40 columns. I do not want to want work on them at the same time, but each row is to be filtered out for some data.

Thank you in advance.

Solution

  • Something like following should be a SQL solution but albeit rather ineffective, since each time you will have to fetch the increasing amount of rows.

    Assuming that you have your ORDER BY is based on unique int and that you will be fetching 1000 rows at a time.

    SET currenttop = 0;
SET cuurentid = 0;
SELECT * FROM YourTable t
WHERE t1.ID > @currentID AND (@currentid := t1.ID) IS NOT NULL; 
LIMIT (@currenttop:=@currenttop+1000);

    Of course you can choose to handle variable from your Java code.