Win a copy of Spark in Action this week in the Open Source Projects forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
  • Piet Souris
  • Frits Walraven
  • Carey Brown


Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am a newbie to EJBs. I was tying a simple Entity bean example with 3 tables(titles, authors and titleauthor from Microsoft SQL Server's pubs database), with Container managed persitence and transactions. However, When I got down to writing EJB-QL for a home business method that retrieves the details of all the titles(books) with multiple authors, I needed to use GROUP BY and HAVING clause in the select statement. However EJB-QL is very simplistic as of Version 2.1 of EJB Spec. It does not allow these clauses.

My question is, in cases where complex joins and group operations are needed on Entities, it looks like BMP is the only choice, am I correct?

Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes to the large extent CMP is yet to catch up with BMP but if you want the Entity bean to be based on a view with GROUP BY and HAVING it defeats the whole purpose of the their existence (persistence). Ask once more is it necessary to create an Entity bean to be based on unupdatable source ?
Nirmala Rayudu
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Thambu,
Thanks for you reply. I was not attempting to create an entity bean for a non-updatable entity. What I was trying to do was 'Home business method' whose purpose in case of CMP, according to the Spec, is to make batch select operations possible and to return a sigle or collection of non-ejb type values(as opposed to findBy methods). Now it happens that the select operation that I am attempting requires GROUP BY and HAVING clause. TitleAuthor table is an updatable relationship table with two unidirectional One-Many relationships with tables Title and Author. To figure out titles with multiple authors, I need to execute the following query.

select t.title_id, t.title, t.price, t.pubdate, a.au_id,a.au_fname,a.au_lname, ta.royaltyper
from Title t, (
select ta.*
from TitleAuthor ta,
select ta.title_id
from TitleAuthor ta
group by ta.title_id
having count(ta.title_id) > 1
) tb
where ta.title_id = tb.title_id
) ta, Author a
where t.title_id = ta.title_id
and a.au_id = ta.au_id
order by t.title_id

There is no way I can do this query with EJB-QL.
However, in theory, I should not say that it is impossible to do this in CMP, but I would have to grab all the entities of TitleAuthor table and then perform the above logic programmatically in Java. Which is practically inefficient and would be asinine of me to do so.

In such scenarios looks like BMP, or if allowed to use Entity beans at all, DAO/JDBC would more efficient.

eat bricks! HA! And here's another one! And a tiny ad!
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    Bookmark Topic Watch Topic
  • New Topic