1 Reply Latest reply on Apr 16, 2002 7:56 PM by davidjencks

    How to aggregate fetches by primary key?

    wchao

      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?