Fetching size limitted rows from a large database table
posted 7 years ago
im using mysql jdbc driver 5.0. The database table to access has a large number of records.. say in millions... I will need to execute queries at times to fetch the rows from the table, but I may be interested in viewing only a limited set at of them at a time. say something like pagination in the UI.
I tried settign the useCursorFetch option to true and then the fetch size to 50. It worked as expected, but this restricted me from using methods like absolute(), last() etc on my result set, as the cursor fetch uses a forward read only.
If i specify the property from anything other then forward read only, the the query execution fetches all the data from the db at a single hit(leading to memory issues), instead of 50 at a time...
How do i solve this.. Im in need of executing a query once that will return me result set as defined number of row chunks only if needed.(say 50 rows first, then if needed another 50 etc).. Also i should be able to use all methods like last(), absolute() etc as well............