0 Replies Latest reply on Dec 20, 2007 7:02 AM by Vassiliy Dubkin

    Pattern for using entities and aggregate functions in EJBQL

    Vassiliy Dubkin Newbie

      Hello, I have a question about using entity beans and aggregate functions together in one EJBQL query. For example I have a pet shop and I want to get all Pets (entity Pet) and their corresponding sales figures. A simple query like

      select p, sum(...) from Pet p where ...

      is only possible if I put every attribute of Pet in the group by clause. In my opinion this is bad design.If I need the information for just one Pet at a time I would just execute a second query. But what if I must show the information for every Pet in a large table?

      In plain SQL I would just retrieve the fields of Pet which I need in that table. In EJB 2.1 I would execute the same native sql query and map the results into a special value object which contains only the attributes I need for that table. But what is the suggested way in EJB 3 where I shouldn't use value objects anymore?

      What other possibilities are available? I think it is a very common problem retieving entities together with aggregate funtion. What solution for that kind of problem do you prefer? Is there a common pattern available?

      Thanks in advance!