I have a servlet(FusionView.java) which calls an stateless EJB using jndi lookup.I have execute method in Bean implementation file(FusionEngineBean.java-- named according to our application).In execute method am retrieving 5.8 million records from the database and this data need to be displayed in jsp.As the number of records are very large its consuming more time and throwing outOfMemory exception.I need to fetch and display only 5000 records per page in jsp.When user says next i need to fetch next 5000 records and so on.(Currently am storing 5.8 million records in session object).
How can I achieve this using stateless EJB?
1.Will it work if i change to statefull EJB?
2.Can I write these records in textfile and fetch when user says next?
3.Can I use threads to do this.Say first thread to fetch first 5000 rec and next thread will keep writing remaining records into text file?
Please suggest me.
Thanks in advance
On your Query object use the setFirstResult(int startPosition) and setMaxResults(int maxResult) methods before getting the result list. Have the stateless EJB take two parameters for the page size and current page.
2. Not ideal. Writing out files is not a recommended practice for EJB3 apps because the file that is written is local to the server and if you have a cluster of servers you'll fall into difficulties.
3. Starting your own threads is also not recommended for EJB3 apps, it's much better to let the container manage all the threads of execution.
Hope this helps.
Thanks so much
Are you saying to write two methods to get first result set and set maximun size.But how does that help me?.I dont want to keep whole results in session object as it hits memory issue when number of users increases.I will not be able to use rowcount or rownumber in the query since my database wont support rownumber.
What database are you using ?
Are you using a sql query to retrieve the data ? if so, why not use the database specific features to retrieve the required set of data. In which case, you ejb method has to accept the pageNumber and numberOfRows as a parameter.
there are keywords like limit, offset, rownum etc based on which database you are using.
If you are using SQL and your DB supports rownum (oracle) u can write sql something like this, which will fetch only page specific data
select * from (
select rownum row, orginalresults.* from (
select a,b,c from XYZ where r=s order by a) originalresults ) where row between 1 and 5000
- pass the last two values, depending on what page u r, e.g for page 2 pass 5001, 10000
- if your are using MQSQl take a look at LIMIT BY clause, it serves similar purpose, without need of nested queries
Try using dynamic query. This way, you can build the query based on the search criteria and use forward cursor for navigation to next page and previous page.