Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: How to query a many-to-many association with Hibernate
PostPosted: Tue Jan 31, 2017 4:14 am 
Newbie

Joined: Tue Jan 31, 2017 4:07 am
Posts: 4
Hi all,
I have following 3 tables in mysql db
[img]

mysql> select * from person;
+---------+--------+-----------------+-----------------+---------------------+----------+
| user_id | active | email | fullName | joined | password |
+---------+--------+-----------------+-----------------+---------------------+----------+
| bucky | 1 | bucky@abc.com | Bucky Wall | 2017-01-30 12:43:46 | bucky |
| james | 1 | james@abc.com | James Goedic | 2017-01-30 12:43:19 | james |
| praveen | 1 | praveen@abc.com | Praveen Reddy S | 2017-01-30 12:37:40 | praveen |
| priya | 1 | priya@abc.com | Priya Reddy | 2017-01-10 10:30:17 | priya |
+---------+--------+-----------------+-----------------+---------------------+----------+
4 rows in set (0.00 sec)

mysql> select * from tweet;
+----------+---------------------+----------------------------+---------+
| tweet_id | created | message | user_id |
+----------+---------------------+----------------------------+---------+
| 1 | 2017-01-30 12:45:37 | @Bucky!. Meet me in office | james |
| 2 | 2017-01-30 12:45:58 | @James!. How nare you? | praveen |
| 3 | 2017-01-30 12:46:10 | Hi, Every One | praveen |
| 4 | 2017-01-30 12:46:38 | @James: Hellow James | bucky |
| 5 | 2017-01-31 10:30:50 | This is Priya | priya |
+----------+---------------------+----------------------------+---------+
5 rows in set (0.00 sec)

mysql> select * from following;
+---------+--------------+
| user_id | following_id |
+---------+--------------+
| praveen | bucky |
| praveen | james |
+---------+--------------+
2 rows in set (0.00 sec)
[/img]

My Two Entity classes are

Person.java
Code:
@Entity
public class Person {

   @Id
   @Column(name = "user_id")
   private String userId;
   private String password;
   private String email;
   private String fullName;

   @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
   @JoinTable(name = "following", joinColumns = { @JoinColumn(name = "user_id") },
   inverseJoinColumns = { @JoinColumn(name = "following_id") })
   private List<Person> following = new ArrayList<Person>();


Tweet.java

Code:
@Entity
public class Tweet {

   @Id
   @GeneratedValue(strategy=GenerationType.AUTO)
   @Column(name="tweet_id")
   private int tweetId;
   private String message;
   private Date created;

   @ManyToOne(cascade=CascadeType.ALL,fetch=FetchType.EAGER)
   @JoinColumn(name="user_id")
   private Person person;


Now I want to display messages of user praveen, and also all the messages mapped to praveen user in the following table. That means praveen user have 2 messages and his following users have 2 messages . Total 4 messages should be displayed. I really don't know how to retrieve this info using any technique in Hibernate. Please help me


Top
 Profile  
 
 Post subject: Re: Hibernate ManyToMany HQL issue
PostPosted: Tue Jan 31, 2017 5:01 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1105
A native SQL query is like a Magic Wand. You only need HQL for fetching entities that you plan to modify.

Back to your problem, use this native query:

Code:
select *
from tweets t
where
    t.user_id = 1 or
    t.user_id in (
        select f.following_id from following f where f.user_id = 1
    )


You can also map it to an entity, as explained in the aforementioned article.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: How to query a many-to-many association with Hibernate
PostPosted: Tue Jan 31, 2017 5:48 am 
Newbie

Joined: Tue Jan 31, 2017 4:07 am
Posts: 4
Using native SQL i got the output. But my requirement is not use native sql. Please help


Top
 Profile  
 
 Post subject: Re: How to query a many-to-many association with Hibernate
PostPosted: Tue Jan 31, 2017 5:59 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1105
The query is just the same with HQL or JPQL:

Code:
select t
from Tweets t
join User t.person u
where
    u.id = 1 or
    u.id in (
        select f_f.id
        from Following f
        join f.user f_u
        join f.following f_f
        where f_u.id = 1
    )


This query assumes that the Following entity has 2 @ManyToOne associations:

Code:
@ManyToOne
private Person user;

@ManyToOne
private Person following;

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: How to query a many-to-many association with Hibernate
PostPosted: Tue Jan 31, 2017 6:00 am 
Newbie

Joined: Tue Jan 31, 2017 4:07 am
Posts: 4
Folowing is my native code. Its working good. But i need HQL

Code:
[b]select * from tweet t where t.user_id =(select following_id from following where following_id=t.user_id) or t.user_id='praveen';[/b]


Top
 Profile  
 
 Post subject: Re: How to query a many-to-many association with Hibernate
PostPosted: Tue Jan 31, 2017 6:04 am 
Newbie

Joined: Tue Jan 31, 2017 4:07 am
Posts: 4
Thanks for the reply. But that query works only for tweetid 1. But i need all tweets whose user name is some thing. So I wanted to list all tweet messages of a user and his following users tweet messages .


Top
 Profile  
 
 Post subject: Re: How to query a many-to-many association with Hibernate
PostPosted: Tue Jan 31, 2017 6:32 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1105
If you read the HQL and JPQL User Guide chapter, you'll find the answer for such a trivial question of how to use a named parameter in an entity query.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.