Strange sql query generated from findAll ejbQL.

[jlmcknight]

Running JBoss 3.2.3 with an Oracle 8 database, I've defined a findAll EJBQL to get all rows from a table:

SELECT OBJECT(f) FROM FOO AS f

Then when I execute it, I see this:

Executing SQL: SELECT t0_f.KEY1, t0_f.KEY2 FROM FOO t0_f

But, then when I begin iterating through the returned collection, when I
make the first getter method call, it executes the query below, apparently to populate all of the entities:

Executing SQL: SELECT KEY1, KEY2, DATE, ORDER, TITLE, CODE FROM FOO
WHERE (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)
OR (KEY1=? AND KEY2=?)

I have several entites/tables, and it does this on each, apparently building an OR block for every row/key in the table (thousands in some cases), which is hanging the Oracle server. Why is it contructing the query like this, rather than as a simple:

SELECT KEY1, KEY2, DATE, ORDER, TITLE, CODE FROM FOO

Any ideas how to fix this?

Thanks,
Jason

[aloubyansky]

This "strange" behaviour is documented and you can tune it as you wish. See on-line docs.

[smilidon]

i had the same problem, by using jboss 3.2.5 the sql statements looks a bit better. but i have still a problem, every getMethod make one SQL-query. if you have a solution, please tell me. maybe these threads help you:

http://www.jboss.org/index.html?module=bb&op=viewtopic&t=51810

http://www.jboss.org/index.html?module=bb&op=viewtopic&t=52043

[triathlon98]

Try running your code inside a transaction.

Joachim