I need to implement a search mechanism where the user can enter criteria into a form and click a button to execute the search in a database. If the user enters broad criteria it is possible that there will be hundreds of records returned by this query that the user can then browse. (Edits to the records will not be performed here, if the user needs to edit records they go to another screen which will do a findbyprimarykey).
I want to implement the search as a session bean which directly accesses the database and returns a collection to the client.
My question is - is it possible/good design to 'trickle' the returned rows back to the client in batches of say 50 by using a stateful session bean that remains connected to the database for the duration of the users query. That is, the session bean will be fetching the actual rows from the database in batches across client calls too.
I would appreciate any help/advice on this problem
what I understand from your question is you want to display batch by batch record to the client who queryed to database. that's right. In my opinion I think that you opt for servlet or JSP to do this . For which there is no need that you've to open the connection to database but you can provide push-button which will call the same servlet or jsp with some hidden value so that servlet or Jsp can connect at that time to database and get batch by batch records to the client instead going to the session-bean concept.
Thanks for the quick reply :)
I should have mentioned that my client is a JFC/Swing client and that the records will be displayed in a JTable. If the user scrolls down the JTable there will be a worker thread that keeps fetching the records just ahead of where the user is navigating. A bit like JBuilder's QueryDataSet (Load.AS_NEEDED) functionality.
i recommend that you start with the manual on the jdbc version and the behavior of the ResultSet implementation. The server-side instance of the RS may actually hold say 500 rows while your page object ( a drop down ) asks for say 50 at a time. In this scenario, depending on what JDBC release you are on, you may be able to go back to the server with 9 more page requests without actually reconnecting to the DB and invoking more SQL. That would mean that some form of the RS would have to be preserved even if you close the connection.
For other vendor products addressing this problem space, try Versant.com and there are some others.
You really don't need to go for Stateful session beans concept in your problem. You can very well manage with servlet,jsp and jdbc.
Using ResultSet interface you can very well iterate for next sets of records