I have been involved with several large JDBC to Oracle projects prior to EJB. Typically these involve presenting lists of data to the user before they drill down and perform updates. The lists that are generated tend to be specific to the screen the user is currently in and business objects can become full of custom screen generators.
EJB then enters the picture, and I use fine grained beans, each mapping to a table row. The generation of result sets for user screens cannot use these entity beans because they are far too slow. i.e. display lists involve joins of reference data etc. etc.
The solution is stateless session beans, with value objects holding all the data needed for a certain screen. These are read only beans and are designed purely for the retrieval of information for display. When the user has drilled down and wants to store results the normal fine grained entity beans can be used.
This approach is optimised for search rather than update - but those are the kinds of systems I develop.
The next step in this is to use a code generator such as my LowRoad EJB generator. This means a simple XML spec can now generate all the search code you need for a screen. And if a new screen is needed it is very quick to generate all the code needed by that screen.
The disadvantage to this approach is multiple classes accing the base tables, so a change in schema needs updates to many classes. The code generator is again the solution - removing the vast amounts of work to update all the searchers.
I realise that this is hardly OO - many objects referencing the base tables. But it is a very practical approach, and saves time.
Yes, I have noticed the same results. For example, using a finder method to access 10,000 records through EJBs took 4 minutes. Using a bulk retrieval took 10s.
Wrapping these queries into a session bean is a good start, but I am not convinced that code generation is the solution. I think that what is needed is an "SQL Query Statement Builder" where the parts of the SQL statement (table schema) are owned by the module that manages the EJB (using a DB access class, perhaps). Then, complex joins are built up through calls between the DB access classes.
Therefore, the OO paradigm is preserved (one point of maintenance in the "object" that owns the table), and the performance improvements of lean, mean SQL statements is exploited. Furthermore, the developer doesn't have to figure out SQL statements on their own, which is a huge waste of time (required now everytime we want to optimize some query): they just write OO style code and out comes the appropriate SQL.
Now if we could write this pattern!
By the way, Sun proposes that CMP will eliminate this problem. Check out http://developer.java.sun.com/developer/technicalArticles/ebeans/sevenrules/index.html?frontpage-jdc. However, I am not sure that this promise will be fulfilled. I have no direct experience, however. Also, I like having visibility to SQL: you know what is going on. CMP takes that away... or does it?
Thanx for your time.
I Agree that staeless session beans are the way to go, these comined with a model-container paradigm can prove to be a really powerwol solution to working with large amount of data in a J2EE environment. However, dynamic SQL generation is not the way yet. I have tasted the dust and know to lay off the path for now. Yes it can be done, but it's not fast. I still find code generation to be much more suitable. What needs to be appreciated here is the avenue where generated code should be placed rather than if it sould be. I usually have persistance-helper-classes for every object-class (including entity beans) I need to persist, these exchange models (which are basically data capsules) with corresponding object-classes. classes processing ssimilar data share these model and persistance-helper classes... so lets say a session ben to look up a thousand records would use the helper to loadup all the models in to a TableModel, when the business needs to process any one of these recodrs (models) it passes the model as a parameter to a finder method for the entity bean, the bean loads any additional infromation it might need... and voilà! you just saved the additional db trips.... but then again YMMV based on the underlying application's requirements.
Sun itself seems to be split upon this issue: If you look at the ValueListHandler-Pattern (http://developer.java.sun.com/developer/restricted/patterns/ValueListHandler.html) they advocate for using direct SQL(at least this is how I understand "EJB finder methods are not suitable for browsing entire tables in the database or for searching large result sets from a table.") on the other hand, they say (in http://developer.java.sun.com/developer/technicalArticles/ebeans/sevenrules/): use container managed-persistence when you can. No doubt, that i can use CMP even for bulk-finders...