I am developing a web application with Flex-blazeds-spring-mybatis-firebird stack. Its a port of existing desktop application with delphi-firebird stack running individually at branches. The web application will run with combining all data at branches to web server so at present I have copy of very large data for development and testing purpose.
One of the application form, which i did recently, I got millions of records which gave out of memory error with blazeds (mybatis returned all objects from joined query quite well) when it was copying from mybatis returned list to amf message since two copies of large set of data objects were being present in memory at the same time. Which made me to think about how could I implement pagination with above mentioned stack. Quick googling gave me options of Pagination at application server and pagination with multiple queries to be fired on database. each has its own pros and cons. in some cases where most of static data being queries from nested query maps I feel Pagination with help of spring-data can be used and when querying large data sets from single table I feel Database level paging can be implemented. Please correct me If I am wrong.
I would like to know how these two options of paginating can be implemented with mybatis. I am thinking of getting large result at once in one transaction (may be with pagination if a really very large dataset) from myBatis-Firebird stack and use disk to cache it and with subsequent request retrieve pages from disk cache and return them. Since there will be many users accessing data simultaneously storing data in memory will not be good in point of view of scalability. In case of objects resulted out of nested result maps I am thinking about serializing them and save to temp database like HSQLdb with one record per object and retrieve them on demand basis also. If somebody has experience about handling large result sets please share techniques apart form above mentioned ones I am open to that also.