File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB Certification (SCBCD/OCPJBCD) and the fly likes Enthuware question on inner join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Certification » EJB Certification (SCBCD/OCPJBCD)
Bookmark "Enthuware question on inner join " Watch "Enthuware question on inner join " New topic
Author

Enthuware question on inner join

Ralph Jaus
Ranch Hand

Joined: Apr 27, 2008
Posts: 342
There is a one-to-many relationship between the entities Student
and Presentation (a student can have many presentations).

Student S1 refers to Presentations P1 and P2
Student S2 refers to Presentations P3 and P4

What's the size of the collection returned by the following query:

select s from Student s inner join s.presentations p

The given answer is: 2

Enthu's explanation: Since there is an inner join between Student and Presentation, it just selects all the
Students for which there is a presentation. Since there are 2 such Students, it returns a collection of size 2.

But to my understanding an inner join forms the cartesian product of the involved tables. So the join
should yield the table

S1 P1
S1 P2
S2 P3
S2 P4

Then, if the Students are selected the result should be S1, S1, S2, S2. So the answer would be 4. I also tried
this scenario with glassfish/toplink and the result was indeed 4.

What do you think ?


SCJP 5 (98%) - SCBCD 5 (98%)
Raf Szczypiorski
Ranch Hand

Joined: Aug 21, 2008
Posts: 383
Enthuware is right - it will perform the inner join, and since the select clause returns only s, it will return only students that have at least one presentation, and it either returns the presentation collection or not (you don't tell us if it is lazy). If you wanted to return 4 results:
S1 P1
S1 P2
S2 P3
S2 P4
the query would have to be: select s, p from Student s inner join s.presentations p. Are you sure you checked the very same scenario on Glassfish?
Amandeep Singh
Ranch Hand

Joined: Jul 17, 2008
Posts: 844
Inner join never form's cartesian product.


SCJP 1.4, SCWCD 5, SCBCD 5, OCPJWSD 5,SCEA-1, Started Assignment Part 2
My blog- http://rkydesigns.blogspot.com
Ralph Jaus
Ranch Hand

Joined: Apr 27, 2008
Posts: 342
Hi,

thanks for your replies. I tried again, but I didn't get the desired result. My output always consists of 4 objects. The details are Tables:Query:
SQL generated by toplink: Query result: The result is the same whether table Presentation contains a foreign key constraint on Student.ID or not.

Has anybody an idea why it doesn't return just the 2 students ? Thanks.

Paul Anilprem
Enthuware Software Support
Ranch Hand

Joined: Sep 23, 2000
Posts: 3313
    
    7
Hi Ralph,
I just ran the code and I am getting 2 rows. The code that I am running is given along with EJBPlus question bank (in Toy Application). The only difference that I see in the code that you've given here is that you are using private List<Presentation> presentations; while the code in the question (and in the sample program) is using private Collection<Presentation> presentations; But that shouldn't be an issue.

The sql that is being generated is:

SELECT DISTINCT t0.ID, t0.LASTNAME, t0.ROLLNUMBER, t0.FIRSTNAME, t0.SCORE FROM STUDENT t0, PRESENTATION t1 WHERE (t1.PRESENTER_ID = t0.ID)

Can you please tell me the version of the app server you are using?

Just realized that there is another difference in the query that you are using and the one given in question:
SELECT s from Student s INNER JOIN s.presentations p <-- INNER is missing in your query or is it just a typo here?

thanks,
Paul.

Enthuware - Best Mock Exams and Questions for Oracle/Sun Java Certifications
Quality Guaranteed - Pass or Full Refund!
Ralph Jaus
Ranch Hand

Joined: Apr 27, 2008
Posts: 342
Hi Paul,

I tried again with "inner" but that had no effect (I think it's optional anyway).

I'm using Glassfish version 9.1_02 (build b04-fcs).

Maybe it's an versional problem, but I don't really belive that. Because joins will surely be
frequently used in practise and I guess the problem would have been found / fixed years ago.
But who knows.

Anyway, thanks to showing me your generated SQL, I know how it should work. That's sufficient.

Thank you very much.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Enthuware question on inner join