Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

EJB-QL  RSS feed

 
Nirmala Rayudu
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Thanks
 
Purushoth Thambu
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
Greenhorn
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.

Thanks
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!