Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

JP QL for selecting specific entities in a relationship

 
M Givney
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Folks,

I was hoping for some JP QL help. Given these two JPA entities:


if I wanted to select an Author and eagerly load only books which have an inPrint flag set to true, how would I represent that in JP-QL?

Thanks for your help.

- matt
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To select all Authors with books inPrint,

"Select a from Author a join a.books b where b.inPrint = true"

Now you OneToMany is lazy (by default) so the books will not be loaded until you access them. This is normally not an issue, if you need to serialize the objects then you can just loop over them and call author.getBooks().size() to force it to be read.

You could also switch the relationship to EAGER if you always want them loaded, or use a join fetch,


"Select a from Author a join fetch a.books, join a.books b where b.inPrint = true"

Now the authors you get back will always be the same objects, with all of their books. You cannot change the state of an object through a query, the objects must always have the same identity no matter how they are queried. But you can query all of the inPrint books for an author directly.

"Select b from Book b join b.author a where b.inPrint = true and a.id = :id"

If you want both in one query this could be,


"Select a, b from Book b, Author a where b.inPrint = true and a = b.author"


If you really want to get Author back with query specific data, then this is complicated but may be possible if you use a join fetch and alias it (not allowed by the JPA JPQL spec, but may work).

"Select a from Author a join fetch a.books b where b.inPrint = true"


 
Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!