I'm sure others have (or have had) the same issue I have right now. I have working code that is inefficient when it comes to retrieving multiple EJB objects by primary key. This is a common occurrence when displaying lists of records that can be paged through.
I get a Vector or array of primary keys and want to fetch them all using one query similar to: SELECT * from tbl_name WHERE ((id=1) OR (id=2) OR (id=3) OR ... OR (id={n})). I'm using container managed persistence and would like to keep using it. What ends up happening is that for each EJB object (mapped to one database row), the container performs one select to get the id column, then another select to fetch all columns. Thus, there are 2*n queries, where n is the number of rows. I have this all wrapped in a transaction.
Is there a way to tell JBoss declaratively to fetch them all using one query? Alternatively, is there a way to code things so that the container ends up fetching them using one query, or at least a constant number of queries rather than a linear number of queries?
I think you can configure this using read-ahead in the cmp2 engine. I think there is also a page size to say how many at once.