0 Replies Latest reply on Nov 19, 2002 8:42 PM by miso

    database joins

    miso Newbie

      Hello. I'm having problems tuning SQL that is being generated by Jboss3.0.3 using CMP beans.

      I have 3 CMP entity beans, that map to 3 tables. The central entity has a one-to-many relationship with each of the other two entities. I have defined all the relevant CMR relations.

      I wrote a finder method that uses the simplest ever jbossql query that tries to select all A entities. When I run the finder I see lots of inefficient SQL queries that jboss is running. My ideal query would look something like:

      select A.bla, B.bla, C.bla
      from A, B, C
      where A.b_id = B.id and
      A.c_id = C.id

      Jboss is generating all sorts of queries instead. It eventually gets all the data by running 3 separate queries for each row from A. I can't make it do the join! This is not acceptable for any serious application. I am trying all the eager load groups, paging etc.

      My question is: Can jboss generate an SQL similiar to the one above? I would be very grateful for any examples on how you achieved it. If not what are my alternatives. I dont want to do BMP as then I might not use jboss at all.