Entity bean representing a query

[mini mehta]

Hi

I guess Entity bean just don't represents a single table in database, it could represent a query which is join of multiple tables. In the case of multiple tables, it is possible to use CMP? I guess it can be done using Relationships, but that will result in one entity bean representing one table and then having relationship between them in a DD.
Has anybody done this type of Entity bean (in CMP or BMP style) and what did they do?


Well the reason I am asking this question is because I don't think CMP will be of much use if it could only represent one table at a time.

Regards
Mini

[Dale Seng]

Originally posted by mini mehta:
...I don't think CMP will be of much use if it could only represent one table at a time.

I think if you make an EJBQL that goes like this:

SELECT Object(o)
FROM Orders o, IN(o.lineItems) i
WHERE i.description LIKE '%computer%'

There has to be a join going on. In fact, I think if you check the xml files that get built by your deployment tool, you will see SQL that affects this join.

--Dale--

[mini mehta]

Originally posted by Dale Seng:


I think if you make an EJBQL that goes like this:

SELECT Object(o)
FROM Orders o, IN(o.lineItems) i
WHERE i.description LIKE '%computer%'

There has to be a join going on. In fact, I think if you check the xml files that get built by your deployment tool, you will see SQL that affects this join.

--Dale--

But it is not joining other tables.

Mini

[Dale Seng]

In EJB-QL I think you get join behaviour without the JOIN keyword. The example above returns 'rows' from the order table, based on data in the items table. So there must be a foriegn key in the items table that is being used to 'join' with the orders table.

Here's a 'real' example...

I put this in my DD:

      <query>
        <description></description>
        <query-method>
          <method-name>ejbSelectPlayers</method-name>
          <method-params>
            <method-param>net.TeamLocal</method-param>
          </method-params>
        </query-method>
        <result-type-mapping>Local</result-type-mapping>
        <ejb-ql>SELECT OBJECT(P) from TeamBean T, IN(T.players) P WHERE T=?1</ejb-ql>
      </query>
  

And then I deploy my bean, and it works.

Then I go and poke around and find sun-j2ee-ri.xml and see this:

      <ejb20-cmp>
        <sql-statement>
          <method>
            <ejb-name>TeamBean</ejb-name>
            <method-intf>Bean</method-intf>
            <method-name>ejbSelectPlayers</method-name>
            <method-params>
              <method-param>net.TeamLocal</method-param>
            </method-params>
          </method>
          <sql>SELECT "@tmp0"."_PlayerBean_playerId" FROM "TeamBeanTable" "T" ,
          "PlayerBeanTable" "P" , "TeamBean_players_PlayerBean_playerTeamTable"
          "@tmp0" WHERE (("T"."teamName" = ? )) AND 
          (("T"."teamName" = "@tmp0"."_TeamBean_teamName" AND "@tmp0".
          "_PlayerBean_playerId" = "P"."playerId"))</sql>
        </sql-statement>
      </ejb20-cmp>

I don't know if I really know exactly how to read that, but there's a lot going on there whenever I use the "IN" keyword!

You can 'join' in regular sql without the keyword join:
select *
from orders o, items i
where o.orderNumber = i.orderNumber
and i.desc like '%computer%'

--Dale--