Hi!
I recognized the following:
JBoss doesn't generate sql statements using joins for 1:N Relations. The select statement uses list of OR operators wich can aggregate to an endless long statement. This is really bad sql code, because many databases can't deal with such a huge amount or OR's.
Here's an example. I want to find 1 Beleg with all it's 10 Belegposition's and that's what JBossCMP generated (from the logs):
Executing SQL: SELECT t0_b.belege_oid FROM BELEGE t0_b WHERE t0_b.belegNr = ?
Executing SQL: SELECT belegNr, kundenName FROM BELEGE WHERE (belege_oid=?)
Executing SQL: SELECT belegNr, kundenName FROM BELEGE WHERE (belege_oid=?)
Executing SQL: SELECT belegpositionen_oid FROM BELEGPOSITIONEN WHERE (BelegEB_belegPositionenEB=?)
Executing SQL: SELECT belegpositionen_oid,artikelnr, artikelbez, artikelpreis FROM BELEGPOSITIONEN WHERE (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?)
Is there a way to tell JBossCMP to generate the last statement like:
SELECT belegpositionen_oid,artikelnr, artikelbez, artikelpreis FROM BELEGPOSITIONEN WHERE BelegEB_belegPositionenEB=belege_oid
If this isn't possible, is than true, that for assuming 1000 Belegposition's, JBossCMP generates a statement with 1000(!!) OR's?
Thanks
Stefan