Hibernate Community • View topic - Query many to many

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Query many to many - help needed

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

eranhauser

Post subject: Query many to many - help needed

Posted: Sun Feb 19, 2006 5:38 pm

Beginner

Joined: Thu Jun 30, 2005 5:07 am
Posts: 33

I do not know how to write the following query, help will be appriciate.

I have Class A with many to many of Class B.
Class B has a class member C and a String mem1.
A.getAssociatedB() will return the B that belongs to A.

Now I would like to get all classes A that has 2 differnt B classes with specific parameters (i.e. C.id = 5 and mem1 equals "kuku" as 1 restriction and the second restrication is that C.id = 4 and mem1 equals"lulu"). How should the query looks like ?

Thanks in advanced

Top

tenwit

Post subject:

Posted: Sun Feb 19, 2006 6:02 pm

Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008

Code:

select a from A a
join a.AssociatedB b1
join a.AssociatedB b2
where b1.AssociatedC.id = :firstId
  and b1.Mem1 = :firstMem
  and b2.AssociatedC.id = :secondId
  and b2.Mem1 = :secondMem

Top

eranhauser

Post subject:

Posted: Mon Feb 20, 2006 6:53 am

Beginner

Joined: Thu Jun 30, 2005 5:07 am
Posts: 33

Thanks, but how can I make in SQL ilike (i.e. case insensetive) for secondMem. Using %secondMem% will not make it case insensetive.

Top

tenwit

Post subject:

Posted: Mon Feb 20, 2006 4:38 pm

Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008

You can use "lower(b2.Mem1) like lower(:secondMem)". There's no ilike in HQL, but this simulates it well enough.

Top

eranhauser

Post subject:

Posted: Mon Feb 20, 2006 5:28 pm

Beginner

Joined: Thu Jun 30, 2005 5:07 am
Posts: 33

Thank it works :-)

Top

Page 1 of 1

[ 5 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