0 Replies Latest reply on Nov 20, 2002 6:54 AM by Stefan Jurk

    Database Problem with the generated CMP selects!!

    Stefan Jurk Newbie

      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