EJB-QL and jboss3.0: Erroneous query evaluation?

Discussions

EJB programming & troubleshooting: EJB-QL and jboss3.0: Erroneous query evaluation?

  1. Hi,

    I am trying to write a finder method for the ejb inheritance "pattern" by Daniel O'Connor (I know, some of you don't like it at all). In the example, there is a general bean called "PartyBean" and two "derived" beans "PersonBean" and "CorporationBean". The inheritance relationship is modelled using cmr: bean instances of the PartyBean are either connected to an instance of PersonBean or CorporationBean (i.e. PartyBean is an "abstract" bean). My idea for a finder method in the PartyBean is to check whether a PartyBean instance is connected to a PersonBean / CorporationBean and then to compare attributes that are specific to the specialized beans:

    <ejb-ql>
       select object(pb)
       from PartyBean pb
       where ((pb.corporation IS NOT NULL) and (pb.corporation.name = ?1))
          or ((pb.person IS NOT NULL) and (pb.person.lastName = ?1))
    </ejb-ql>

    As far as I understand the ejb-ql specification, the expressions (pb.<cmr> IS NOT NULL) evaluate to FALSE if there is no such relationship. According to the definition of the AND operator in the spec, the expression (pb.<cmr> IS NOT NULL) and (pb.<cmr>.<attr> = ?1) should evaluate to (FALSE and UNKNOWN) which is equivalent to FALSE. However, the query above does not work! It seems the UNKNOWN value
    introduced by accessing a non-existent cmr does not get eliminated by ANDing it with FALSE.

    Surprisingly, the following query works (although it does not really achieve what I am looking for):

    <ejb-ql>
       select object(pb)
       from PartyBean pb
       where ((pb.corporation IS NOT NULL) and (pb.corporation.name = ?1))
    </ejb-ql>

    I am using jboss 3.0.

    I hope some of you can give me some advice?

    Best regards,

    Thorsten
  2. Hi Thorsten,

    I may be misunderstanding your intention here, but I think the query you really want is (I think checking for null relations is not required):

    <ejb-ql>
       select object(pb)
       from PartyBean pb
       where (pb.corporation.name = ?1
        or pb.person.lastName = ?1)
    </ejb-ql>

    If jboss does what I think it should, this query should be converted to someling like the following SQL:

    SELECT *
    FROM party, corporation, person
    WHERE ((party.party_id = corporation.party_id
    AND coporation.name = <input variable>)
    OR (party.party_id = person.party_id
    AND person.last_name = <input_variable>
    )

    Make sure you check the sql that JBOSS generates. This is a good way to see what jboss is _really_ doing. You can use p6spy to do this.

    Daniel.
  3. Hi Daniel,

    thanks for your reply, I think you perfectly understood my problem. Unfortunately, your solution doesn't work either.

    I downloaded p6spy and tried to make it run with jboss 3.0 - and failed :-( It seems p6spy so far has only been integrated with jboss 2.4.4, and I could not figure out how to make it run with jboss 3.0 since quite a few configuration details seem to have changed. Do you happen to know of any other tools that provide similar SQL statement logging?

    Best regards,

    Thorsten