Hello, 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?
Joined: May 24, 2003
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 ?
Joined: May 30, 2005
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.