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.