I installed p6spy here to get some concrete SQL and look at the result. It got scarier! JBossCMP is now repeating an extensive SQL command to load a single entity! What's happening? Here is the SQL issued by JBossCMP:

SELECT t0_o.DIVISAO_ID, t0_o.DESCRICAO, t0_o.FILIAL_ID, t0_o.DATASINC FROM DIVISOES t0_o ORDER BY t0_o.DESCRICAO ASC

This is issued on Divisao.findAll(). This should have loaded all the entities already. I got 9 entities an this table. However. take a look and the following 8 SQL statements:

SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='5') OR (DIVISAO_ID='3') OR (DIVISAO_ID='6') OR (DIVISAO_ID='9') OR (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='3') OR (DIVISAO_ID='6') OR (DIVISAO_ID='9') OR (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='6') OR (DIVISAO_ID='9') OR (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='9') OR (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='4') OR (DIVISAO_ID='8')

SELECT DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='8')

As you can see, there is one less OR clause on each statement. It seems that JBoss sucessfuly loads only a single entity at each statement, including the finder statement. This is nonsense. What's happening here?

Now imagine what happens if my table contains 1000+ entities. And yes, I have other tables that will contain much more than this.

I have CommitOption A, and everything is executed on a single transaction (I hope). Besides, all this SQL output is caused by a *single* call to Divisao.findAll(), so even with CommitOption B or C we shouldn't have all this SQL.

An update: I've checked the code, added some log lines and discovered that this whole SQL is not generated by a single call to findAll(). Here is the calling code:

Collection ifaces = divisaoHome.findAll();
Iterator it = ifaces.iterator();

while(it.hasNext())
 result.add(((DivisaoLocal)it.next()).getDivisaoValue());