Hibernate Community • View topic - How to join tables on unidirectional many-to-one condition ?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

How to join tables on unidirectional many-to-one condition ?

Page 1 of 1

[ 8 posts ]

Previous topic | Next topic

Author

Message

cakebunny

Post subject: How to join tables on unidirectional many-to-one condition ?

Posted: Tue Mar 02, 2010 9:49 am

Newbie

Joined: Tue Mar 02, 2010 9:25 am
Posts: 7

I have two classes . for example

Code:

class A {
public Long id ;
}
class B {
public Long id ;
public A a ;
}

B and A has many to one relationship.
I can get List<B> like this :
"select b from B b left join b.a a where a.id > 10 and ... (other condtions )" .

But actually , I need List<A> , I want result like this :

Code:

class A {
public Long id ;
public Set<B> bs ;
}
class B {
public Long id ;
}

"select a from A a left join a.bs b where a.id > 10 and ... (other condtions ) ".

I can't change my classes . I can't navigate A to B , Is there any solutions ?
I also want to know if this is possible by using criteria .

Last edited by cakebunny on Thu Mar 04, 2010 12:45 am, edited 2 times in total.

Top

nordborg

Post subject: Re: How to join tables on unidirectional many-to-one condition ?

Posted: Wed Mar 03, 2010 3:47 am

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

Code:

select a from B b left join b.a a where a.id > 10

Top

cakebunny

Post subject: Re: How to join tables on unidirectional many-to-one condition ?

Posted: Wed Mar 03, 2010 10:28 pm

Newbie

Joined: Tue Mar 02, 2010 9:25 am
Posts: 7

This is not right.
You're still using B to join A , hibernate will select a subquery from B and use this subquery to join B.
If I have 10 records in A , only 1 record in B that have FK to A , I can only get 1 result.
What I want to do is to let A left join B in an exsiting condition.

Last edited by cakebunny on Thu Mar 04, 2010 12:43 am, edited 1 time in total.

Top

SIau_Tie

Post subject: Re: How to join tables on unidirectional many-to-one condition ?

Posted: Wed Mar 03, 2010 11:47 pm

Senior

Joined: Wed Sep 19, 2007 9:31 pm
Posts: 191
Location: Khuntien (Indonesia)

what you want is retrieve list of A where id > 10? correct?
why do you need join condition?

Code:

from A a where a.id > 10

Top

cakebunny

Post subject: Re: How to join tables on unidirectional many-to-one condition ?

Posted: Thu Mar 04, 2010 12:32 am

Newbie

Joined: Tue Mar 02, 2010 9:25 am
Posts: 7

SIau_Tie wrote:

what you want is retrieve list of A where id > 10? correct?
why do you need join condition?

Code:

from A a where a.id > 10

what I need is to join tables , I added where clause because there are lots of conditions need to be matched , id > 10 is just an example , I don't want to mix other complexities into this question.

Top

SIau_Tie

Post subject: Re: How to join tables on unidirectional many-to-one condition ?

Posted: Thu Mar 04, 2010 1:48 am

Senior

Joined: Wed Sep 19, 2007 9:31 pm
Posts: 191
Location: Khuntien (Indonesia)

how about this

Code:

DetachedCriteria sub = DetachedCriteria.forClass(BookStock.class);
sub.setProjection(Projections.property("book.id"));
      
DetachedCriteria dc = DetachedCriteria.forClass(Book.class);
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Subqueries.propertyIn("id", sub));
disjunction.add(Restrictions.gt("id", 2L));
dc.add(disjunction);

Result:

Code:

    select
        this_.id as id3_0_,
        this_.isbn as isbn3_0_,
        this_.price as price3_0_,
        this_.title as title3_0_ 
    from
        book this_ 
    where
        (
            this_.id in (
                select
                    this_.book_id as y0_ 
                from
                    book_stock this_
            ) 
            or this_.id>?
        )

Top

nordborg

Post subject: Re: How to join tables on unidirectional many-to-one condition ?

Posted: Thu Mar 04, 2010 3:50 am

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

Quote:

I can't change my classes . I can't navigate A to B

Quote:

What I want to do is to let A left join B in an exsiting condition.

This is a contradiction. A left join between A and B is a navigation from A to B. So unless you add this mapping to your A class you will not be able to do the left join.

Top

cakebunny

Post subject: Re: How to join tables on unidirectional many-to-one condition ?

Posted: Thu Mar 04, 2010 4:44 am

Newbie

Joined: Tue Mar 02, 2010 9:25 am
Posts: 7

nordborg wrote:

This is a contradiction. A left join between A and B is a navigation from A to B. So unless you add this mapping to your A class you will not be able to do the left join.

I don't know this is a contradiction , what I thought was I may lack of some knowledge about hibernate .
This is not true with SQL. whenever one-to-many , or many-to-one , the existing tables are the same . B manages FK . It's very easy to write this join query by using native SQL .

Can I say that HQL depends on object navigation , things that can be done using one-to-many doesn't guarantee that can also be done by many-to-one ?

No matter it's one-to-many or many-to-one , mapping on both sides can make writing code more easily ,
you can navigate from one to the other , does that mean bidirectional relationship is better than unidirectional when using hibernate ?

Top

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