1 Reply Latest reply on Dec 5, 2001 4:09 PM by Dan Christopherson

    CMP: Finder using two tables

    Branko Vitas Newbie

      Sorry for this topic again, but I can't find the wright solution inside the answers in this forum.

      I'm using CMP in ejb 1.1.(JBOSS 2.4). I need to get information from two tables (entity beans)

      Table1: id (PK), name
      Table2: code(PK), id (FK),data

      Result of query must be data collection with this element:
      id, data, name
      or in SQL:

      SELECT table2.id,table2.data,table1.name FROM table1,table2 WHERE table2.id=table1.id

      In jaws.xml under the entity tag of TABLE2 bean's I created this finder:
      finder
      namefindXXYYZZ/name
      query ,table1 WHERE table2.id=table1.id/query
      read-aheadtrue/read-ahead
      /finder

        • 1. Re: CMP: Finder using two tables
          Dan Christopherson Newbie

          OK, the first thing is: a finder's SQL only returns keys, not data. The result of calling a finder is that the caller will have a collection of entity beans to call.

          EJB isn't like a RAD tool, where you define a screen around a query - you'll only really leverage EJB if you think about your problem domain in terms of objects with relationships between them. And then you'll have a whole different set of frustrations 8^})

          Is the finder on the for table2? That's why that's all the data you get.
          Now solving your issue:
          What's the context that you need this from? Do you have an entry in Table1 and you need all the table2 data for that, or is it more of a search. I assume it's a search.
          Unfortunately, entity beans don't do real well in that context. You _could_ define entities for both table1 and table2, then add a relation ship from one to the other. The problem there is that you'll get a lot more database activity that you probably want.

          To really get this to work right (and perform decently), I'd define a 'SearchSummary' object (serializable) with id, data, name as attributes and define a stateless session bean with a method taking whatever parameters you're searching by and just use JDBC to get the data and return a collection of those SearchSummary things.
          Hope this helps.