Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA JPQL Doubts regarding querying Collections

 
Shekar Chandu
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All

Iam reading Pro JPA 2 and in chapter 7 of book we have the following lines

"The result type of a select query cannot be a collection; it must be a single valued object such as an entity instance
or persistent field type. Expressions such as e.phones are illegal in the SELECT clause because they would result in
Collection instances (each occurrence of e.phones is a collection, not an instance)
. Therefore, just as with SQL and
tables, if we want to navigate along a collection association and return elements of that collection, we must join the
two entities together. In the following code, a join between Employee and Phone entities is implicitly applied in order
to retrieve all the cell phone numbers for a specific department:

SELECT p.number
FROM Employee e, Phone p
WHERE e = p.employee AND
e.department.name = 'NA42' AND
p.type = 'Cell'"



For the same above Employee and Phone example if i write a query

select e.phones from Employee e,

then i didnt get any errors and i got the output correctly as follows

Hibernate:
select
phones1_.id as id3_,
phones1_.employee_id as employee4_3_,
phones1_.number as number3_,
phones1_.type as type3_
from
JPA.Employee employee0_
inner join
JPA.Phone phones1_
on employee0_.id=phones1_.employee_id
1 Phone[id=1,number=(212)555-1234,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@24802480]
2 Phone[id=2,number=(212)555-9843,type=Home,employee=com.ibm.sample.jpa.entitybeans.Employee@24802480]
3 Phone[id=3,number=(315)555-6253,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@26422642]
4 Phone[id=4,number=(516)555-9837,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@27882788]
5 Phone[id=5,number=(516)555-2034,type=Cell,employee=com.ibm.sample.jpa.entitybeans.Employee@27882788]
6 Phone[id=6,number=(650)555-7583,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@367e367e]
7 Phone[id=7,number=(650)555-5345,type=Home,employee=com.ibm.sample.jpa.entitybeans.Employee@367e367e]
8 Phone[id=8,number=(650)555-9386,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@606e606e]
9 Phone[id=9,number=(650)555-4885,type=Cell,employee=com.ibm.sample.jpa.entitybeans.Employee@606e606e]
10 Phone[id=10,number=(650)555-3836,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@61b661b6]
11 Phone[id=11,number=(650)555-0985,type=Home,employee=com.ibm.sample.jpa.entitybeans.Employee@61b661b6]
12 Phone[id=12,number=(650)555-1946,type=Cell,employee=com.ibm.sample.jpa.entitybeans.Employee@61b661b6]
13 Phone[id=13,number=(650)555-4759,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@62fc62fc]
14 Phone[id=14,number=(650)555-4757,type=Home,employee=com.ibm.sample.jpa.entitybeans.Employee@62fc62fc]
15 Phone[id=15,number=(650)555-6753,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@62fc62fc]
16 Phone[id=16,number=(585)555-0693,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@28d028d0]
17 Phone[id=17,number=(585)555-3098,type=Home,employee=com.ibm.sample.jpa.entitybeans.Employee@28d028d0]
18 Phone[id=18,number=(585)555-1457,type=Cell,employee=com.ibm.sample.jpa.entitybeans.Employee@28d028d0]
19 Phone[id=19,number=(650)555-9838,type=Office,employee=com.ibm.sample.jpa.entitybeans.Employee@646e646e]
20 Phone[id=20,number=(650)555-2346,type=Home,employee=com.ibm.sample.jpa.entitybeans.Employee@646e646e]
21 Phone[id=21,number=(650)555-9874,type=Cell,employee=com.ibm.sample.jpa.entitybeans.Employee@646e646e]

which is quite opposite to what the line above in chapter7 of book "The result type of a select query cannot be a collection; it must be a single valued object such as an entity instance
or persistent field type. Expressions such as e.phones are illegal in the SELECT clause because they would result in
Collection instances (each occurrence of e.phones is a collection, not an instance)


What exactly is behaviour over here?

Is it that i can run the query as stand alone but cannot be used as part JPA API methods to fetch collections in a instance/entity?

Please let me know where iam went wrong
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic