Accessing multiple tables in a single finder method in CMp

Discussions

EJB programming & troubleshooting: Accessing multiple tables in a single finder method in CMp

  1. Can i access multiple tables in a single finder method i CMP?

    for ex, i want results from 3 tables using a single finder method in CMP?

    sumithra

  2. Quick answer: No.

    Long Answer:
    A finder belongs to 1 and only 1 entity bean. It will find, and return to you an interface to that 1 entity bean. So you cannot get information from more than 1 entity bean using a finder.

    Work Arounds:

    1. Often it is good enough to find 1 entity bean, and navigate using relationships to the other beans you require.

    2. Otherwise you'll have to implement your own Home method and do the searching using JDBC. Your own home method can return anything, including an object containing all of the DB columns you are interested in.

    Hope that helps,

    Daniel.
  3. Hang on, this is not right. Yes it's true that a finder is only associated with one entity bean, but nowhere does the EJB spec say that one entity bean must be associated with only one table, even in CMP. In EJB 2.0 you can have complex relationships i.e. multiple table entities. Even without this, you can set up a view on the database that exposes multiple tables via a single name.

    Isn't this what he's after?
  4. Yep you are right. Nowhere does the EJB spec say that an entity bean in CMP represents only one table. I just looked and couldn't find it anywhere - actually it hardly ever even mentions databases (got to keep it generic...)

    BUT do any of the application servers have an implemention of CMP which allows you to have more then 1 table per bean? I've only looked at weblogic (6.1) and here is their definition of part of the "weblogic-cmp-rdbms-jar.xml" file (the deployment descriptor which does the mapping between the bean and the DB:

    weblogic-rdbms-bean
    ejb-name
    data-source-name
    table-name
    field-name
    field-map*
    field-group*
    weblogic-query
    delay-database-insert-until?
    automatic-key-generation?

    Yep you can only have 1 table per ejb.

    As to using a view, yes that could work depending on your database structure & requirements. I've heard rumors that you can have problems with views within CMP, but have no 1st hand experience with that.

    But back to the main point, you don't have to redesign your ejb mappings just to implement a finder. Use JDBC - problem solved. Realistically you need to use JDBC for a fair number of finders due to the limitations of EJB QL (no order by, no max/min/count.

    Daniel.
  5. Hi Dan

    I have used CMP and views quite successfully, and there are no problems that I know of. If you think about it, there should never be a problem as CMP doesn't know it's talking to a view.

    I was using Websphere and Oracle. This should work for Weblogic too. You just have to replace the table name in your descriptor with the view name. As you know, the whole point of views is to make multiple tables look like one table.

    You still have a problem with JDBC though surely? Finder methods can ONLY return Entity Primary Keys. The container then takes those keys and builds the objects to return to the client. I accept what you say about using JDBC to make up for EJB QL's deficiencies, but it still doesn't address
    Sumithra's original request. What he wants is to write a finder which returns objects composed from multi-table data. Aren't views or EJB 2.0 Entities the only way to do this?

    If you write a home method which uses JDBC to return you a collection of arbitrarily composed objects, is it still CMP? The container is not building the objects, you are!

    I'm sure I must be missing something here.

    Dave
  6. Hi Dave,

    Good to know that CMP & views works ok.

    > If you write a home method which uses JDBC to return you
    > collection of arbitrarily composed objects, is it still
    > CMP? The container is not building the objects, you are!

    Yes, that's what I meant by using JDBC, and yes it breaks the concept of CMP. But building a view (and the associated EJB) every time you want a finder over several tables is also not exactly nice. (I hope you're good friends with your DBA)

    I guess I see it like this: You're going to have to write SQL either way (JDBC or View), I prefer to keep the logic in the beans not in the DB, but that is probably because I have more control over my code then over the DB. I can also see the reasons for putting it into a view.

    Pick your ugliness :-)

    Daniel.
  7. No Dan, that's not necessarily right (although it might be). If you can create a view, you're not going to have to write SQL unless the EJB QL is unable to render your query (see your earlier post).

    The normal CMP mechanisms can interface to the view via the descriptor.

    And yes, I am good friends with my DBA, and I understand that I need to be.

    Cheers
  8. Er, write me a view without using SQL. :-)

    Daniel.
  9. Are we talking at cross purposes here? You can't create a view without writing SQL, but you can access a view using CMP without writing SQL. That's why I'm STILL good friends with my DBA.

    By the way I thought of another way of solving the original problem. Webgain make a product "Toplink" that allows CMP and complex relationships i.e. multi-table Entity EJBs. Check it out!
  10. Hang on, this is not right. Yes it's true that a finder is only associated with one entity bean, but nowhere does the EJB spec say that one entity bean must be associated with only one table, even in CMP. In EJB 2.0 you can have complex relationships i.e. multiple table entities. Even without this, you can set up a view on the database that exposes multiple tables via a single name.

    Isn't this what he's after?