CODEWITHSUNDEEP
javasqlhibernatehql
user784540
user784540

Reputation:

HQL query analog for the SQL expression

There are two entities. Meeting and Topic (corresponding tables are Meetings and Topics)

Where one meeting may contain a set of topics.

Example table structure is as follows:

tables

In my java code Meeting class has the following declaration:

@OneToMany(cascade = CascadeType.ALL)
@JoinTable(name = "MeetingTopics",
joinColumns = {
    @JoinColumn(name = "meetingId")},
inverseJoinColumns = {
    @JoinColumn(name = "topicId")})    
@Expose
private Set<Topic> topicsList;

I want to get a list of meetings (with topics inside of them), where someTopicField of a topic is equal to a particular value, let say it is 11

SQL expression to get what I want looks like that:

SELECT meetings.meetingcomment, 
       topics.topicsubject, 
       topics.sometopicfield 
FROM   topics 
       INNER JOIN (meetings 
                   INNER JOIN meetingtopics 
                           ON meetings.meetingid = meetingtopics.meetingid) 
               ON topics.topicid = meetingtopics.topicid 
WHERE  ( topics.sometopicfield  = 11 );

But I would like to use HQL expression to solve this task, and I did not figure it out, how could I do that.

I've read this documentation but did not understand, how to apply those examples to my question.

What HQL expression will do the same as above described SQL does?

Thank you.

Upvotes: 3

Views: 379

Answers (2)

dcernahoschi
dcernahoschi

Reputation: 15250

Try something like this: 

select m.meetingcomment,
       t.topicsubject,
       t.sometopicfield
from Meeting as m
inner join m.topicsList as t
with t.sometopicfield = 11

If you need only the Meeting class fields the hql is:

select m from Meeting as m
inner join m.topicsList as t
with t.sometopicfield = 11

And you get a list of Meeting objects.

Upvotes: 1

Timo Westk&#228;mper
Timo Westk&#228;mper

Reputation: 22200

Something like this should work

select meeting.meetingcomment,
       topic.topicsubject,
       topic.sometopicfield
from Meeting meeting
inner join meeetings.topicsList as topic
where topic.sometopicfield = 11

Upvotes: 1

Related Questions