-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
Wilco Vertegaal
 Post subject: Beginner's question: how to map "inner join" to Criteria?
PostPosted: Thu Sep 01, 2016 5:41 am 
Newbie

Joined: Thu Sep 01, 2016 5:21 am
Posts: 5
Hi, I'm new to the forum, and sort of new to Hibernate (and to make things worse: sort of new to SQL ... ).

Using v4.3, I would like to map the following SQL query:

Code:
select hqa1.x, hqa2.x, hqa1.user
   from table1 as hqa1
    inner join table1 as hqa2
    on hqa1.user = hqa2.user
where hqa1.content_id = 15 and hqa2.content_id = 16;

But I can't figure out how this "inner join" should be translated to Criteria. I mean,

Code:
Criteria criteria = HibernateUtil.getSessionFactory().getCurrentSession().createCriteria(qClass).add(Restrictions.eq("content_id", id));

is easy enough for hqa1 and hqa2, but how to inner join them on hqa1.user = hqa2.user? I know it should be a basic question, and I'm not here to lay flat on my back until anyone hands me the correct answer, but a link to an example so I can figure this out would be great. Thanks!
Top
 Profile  
 
vlad
 Post subject: Re: Beginner's question: how to map "inner join" to Criteria?
PostPosted: Thu Sep 01, 2016 5:56 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Can you post your entity mappings?
Top
 Profile  
 
Wilco Vertegaal
 Post subject: Re: Beginner's question: how to map "inner join" to Criteria?
PostPosted: Thu Sep 01, 2016 6:06 am 
Newbie

Joined: Thu Sep 01, 2016 5:21 am
Posts: 5
mihalcea_vlad wrote:
Can you post your entity mappings?

I've simplified my actual class/table, but it would be like this:

Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="q" table="table1">
      <id name="id" type="long">
         <generator class="native"/>
      </id>
      <property name="content_id"/>
      <many-to-one name="user" class="User" />
      <property name="x" />
   </class>
</hibernate-mapping>
Top
 Profile  
 
vlad
 Post subject: Re: Beginner's question: how to map "inner join" to Criteria?
PostPosted: Thu Sep 01, 2016 7:17 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Check out this SO answer.
Top
 Profile  
 
Wilco Vertegaal
 Post subject: Re: Beginner's question: how to map "inner join" to Criteria?
PostPosted: Thu Sep 01, 2016 8:07 am 
Newbie

Joined: Thu Sep 01, 2016 5:21 am
Posts: 5
mihalcea_vlad wrote:
Check out this SO answer.

Thanks, but that answer (and a lot of similar ones that I have seen) is applicable to joining two different tables where one holds a many-to-one relation to the other. In this particular case, I would like to join different rows of the same table, where one particular property must be equal.

So we have a subset of table1 records for which content_id = 15, and another subset where content_id = 16. Now I want to retrieve all pairs of records that share the same user.

Suppose we have this:

Code:
+----+---+----------+
|user| x |content_id|
+----+---+----------+
|a   | 1 |15        |
+----+---+----------+
|a   | 0 |16        |
+----+---+----------+
|b   | 2 |15        |
+----+---+----------+
|b   | 0 |16        |
+----+---+----------+
|c   | 1 |15        |
+----+---+----------+
|d   | 2 |16        |
+----+---+----------+


Then I am looking for this result:
Code:
+------+------+----+
|hqa1.x|hqa2.x|user|
+------+------+----+
| 1    | 0    |a   |
+------+------+----+
| 2    | 0    |b   |
+------+------+----+


Does that make sense?
Top
 Profile  
 
Wilco Vertegaal
 Post subject: Re: Beginner's question: how to map "inner join" to Criteria?
PostPosted: Fri Sep 02, 2016 6:22 am 
Newbie

Joined: Thu Sep 01, 2016 5:21 am
Posts: 5
Or should I remove "Beginner's question:" from the subject title? ;-)
Top
 Profile  
 
vlad
 Post subject: Re: Beginner's question: how to map "inner join" to Criteria?
PostPosted: Sat Sep 03, 2016 5:43 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Legacy Criteria does not support self join. It might be that JPA Criteria API has some support for that. Sometimes, you just have to use SQL to take advantage of the underlying DB capabilities.
Top
 Profile  
 
Wilco Vertegaal
 Post subject: Re: Beginner's question: how to map "inner join" to Criteria?
PostPosted: Mon Sep 05, 2016 7:54 am 
Newbie

Joined: Thu Sep 01, 2016 5:21 am
Posts: 5
mihalcea_vlad wrote:
Legacy Criteria does not support self join. It might be that JPA Criteria API has some support for that. Sometimes, you just have to use SQL to take advantage of the underlying DB capabilities.

OK, thank. I would have imagined that this was sort of standard functionality. I'll take the SQL approach.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.