3 Replies Latest reply on Feb 25, 2007 6:49 PM by Chris Malan

    How can I use a EJB Query with this?

    dornus Novice

      I am wondering how to effectively use an EJB query in the following situation.

      I have 2 tables. Each table has a corresponding @Entity java class.

      TableA
      ---------
      int tableAPrimaryKey
      varchar(20) tableAField
      ...etc, plus many more fields
      
      TableB
      ---------
      int tableBPrimaryKey
      int tableAForeignKey
      varchar(20) tableBField
      ...etc, plus many more fields
      


      I would like to run a query that gets each row from Table A, and a count from Table B (dependant on tableAForeignKey). So for each row my html output would look something like this:
      "tableAField1", "tableAField2", "tableBCount"

      Using EJB, I can get each row of TableA by running:
       Query q = em.createQuery("from TableA o ");
       return q.getResultList();
      


      Using EJB, I can get a count for each row of TableA from TableB
      Query q = em.createQuery(
       "select count(o) " +
       "from TableB o " +
       "where o.tableAForeignKey = :currentRow"
      );
      



      Is there a better way to do this using EJB? Looping through each result of Table A and executing a count query seems pretty inefficient.

      The correct way seems to be to create a native query and manually build all these objects. This is extremely tedious as there are a lot of columns in both tables that are needed.

      Are there any suggestions of a better way to do this using EJB?


        • 1. Re: How can I use a EJB Query with this?
          dornus Novice

          Also, if I did it the EJB way that I showed in my previous post, that would require me to create an

          @Transient int count
          in MyTableA's java file to store the count.

          Also, what if I wanted to sort the results by the count? This would require a lot more work instead of a simple order by clause.

          Is there a better way to restructure the Entity classes? Is there a better way to nest EJB queries?

          • 2. Re: How can I use a EJB Query with this?
            dornus Novice

            Maybe I should create a view in the database and create a POJO from that?

            I'm thinking that would be my best bet.

            • 3. Re: How can I use a EJB Query with this?
              Chris Malan Novice

              Hi Dornus,

              There are two solutions I can think of. The first side-steps an EJB query.

              One of class A's attributes will be a collections of some sort of class B. Simply get an instance of class A by primary key, get an instance of the collection of Bs and do a bees.size().

              The second is to do a join. Something like select ab from A as a join a.b as ab where a.PrimaryKey=PK and then again a size() on ab.