GameOne wrote:
Hibernate version:3.2
Hi, I am trying to write a named query where the query should return list of objects which contain all the ids in the passed parameterList.
I am new to SQL and HQL, so i could not find a right way to do this. Please help, here is the situation:
Object A and B have many to many relationship. I am trying to write a named query wherein I can pass list of 'ids of B' as parameter. the query should return list of A which contain all the values in paramterList.
How do I achieve this? Please help... :(
I did a little prototype that resembles your situation. Imagine a case of person and address many-to-many relationship. You can define a query to get a list of people who are associated to a certain list of addresses. The query would look like:
Code:
select distinct p from Person p inner join p.addresses address where address.address in ('300 Somewhere', '232 Here')
and size(p.addresses) = 2
The mapping for Person class is:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="test.model.data.Person" table="Person">
<id name="id" type="long" unsaved-value="null">
<column name="PERSONID"/>
<generator class="native"/>
</id>
<property name="name" type="string">
<column name="NAME" length="50" not-null="false"/>
</property>
<set name="addresses" cascade="all" table="PERSONADDRESS" inverse="false">
<key column="PERSIONID"/>
<many-to-many column="ADDRESSID" class="test.model.data.Address" />
</set>
</class>
<query name="test.model.data.Person.find" cacheable="true" cache-region="com.necho.platform.model.data.Entity.findByName">
<![CDATA[
select distinct p from Person p inner join p.addresses address where address.address in ('300 Somewhere', '232 Here')
and size(p.addresses) = 2
]]>
</query>
</hibernate-mapping>
and the mapping for Address is fairly straight forward:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="test.model.data.Address" table="ADDRESS">
<id name="id" type="long" unsaved-value="null">
<column name="ADDRESSID"/>
<generator class="native"/>
</id>
<property name="address" type="string">
<column name="TEXT" length="150" not-null="false"/>
</property>
</class>
</hibernate-mapping>
Note that the list of addresses and the size can be a parameter. I hope this helps.
Farzad-
Login
FAQ
Search
