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 QL on a Many-to-Many

 
Scott Duke
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am relatively new to JPA QL and am trying to create a select statement joining a couple of tables together, but cannot get it to work. Here is an abbreviated look at what my entities look like.



What I am trying to do is:

SELECT * FROM member m inner join memberroles mr on m.id = mr.member_id where mr.role_id = ?;

I am able to use this type of select within MySQL, but am not able to use this using JPA QL. I have tried several ways to get this to work, but am unable to get this to work.

Please help.

Thanks,
Scott
 
Shailesh Kini
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Scott,

JPA QL deals with objects so it won't understand ANSI SQL. You could use ANSI SQL using the NativeQuery in JPA.

JPA QL works with objects so it won't understand "*". You should replace it with 'm' (because you has it as an alias for Member class).

It would be easier if you could attach the error message/exception.
 
Scott Duke
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shailesh,

First I want to thank you for the reply. I do realize JPA QL will not use ANSI SQL. I have replaced the "*" with "m" and was able to get a straight query to work on the Member database table. It is the "join" I am having trouble with. I have tried various ways to get the join to work like:




I don't know if moving the MemberRoles class into its own class would resolve this or not.

I would have included an error if I was receiving one. The program just hangs when it reaches the:

Query query = entityManager.createQuery(querySB.toString());
 
Edvins Reisons
Ranch Hand
Posts: 364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try like this:

 
Scott Duke
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Edvins,

Thanks for the SQL statement. When I tried it, it hung on the entityManager.createQuery("select m from Member m join m.roles mr where mr.role_id = 2"); Is there something I am missing?

I am able to use the nativeQuery on the ANSI SQL, but would like to use JPA QL.

Is there a good website that explains in detail the JPA QL?
 
Shailesh Kini
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Scott,

Maybe this should help -> Understanding JPA QL
 
Scott Duke
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic