Hi,
There are a lot of problems with querying a nested objects. Unfortunately I can not find any solution for my problem.
My object model is like the following:
Repository 1--* Device 1--* DevicePolicy *--1 Policy
So: there is a Repository. There are Devices in the Repository. Every Device has a collection of DevicePolicies (a Set). Every DevicePolicy is made of a global Policy (dictionary table) and a policy's value assigned to this device.
I want to select all Devices in particular Repository which contains specified Policies (by policy's name).
Hibernate does not generate 'inner join' clause for global policy table and the following error occurs: "relation "global_pol2_" does not exist".
Hibernate version: 3.1.3
Mapping documents:
Code:
<class name="Policy" table="policy">
<id name="id">
<generator class="native">
<param name="sequence">policy_id_seq</param>
</generator>
</id>
<many-to-one name="repository" column="repository_id" not-null="true" unique-key="policy_name"/>
<property name="repositoryId" column="repository_id" insert="false" update="false"/>
<property name="name" length="256" not-null="true" unique-key="policy_name"/>
</class>
<class name="Device" table="device">
<id name="id">
<generator class="native">
<param name="sequence">device_id_seq</param>
</generator>
</id>
<many-to-one name="repository" column="repository_id" not-null="true" unique-key="device_name"/>
<property name="repositoryId" column="repository_id" insert="false" update="false"/>
<property name="name" length="128" not-null="true" unique-key="device_name"/>
<set name="policies" table="device_policy" cascade="all" inverse="true">
<key column="device_id" not-null="true" on-delete="cascade"/>
<one-to-many class="DevicePolicy"/>
</set>
</class>
<class name="DevicePolicy" table="device_policy">
<composite-id>
<key-many-to-one name="device" column="device_id" />
<key-many-to-one name="policy" column="policy_id" />
</composite-id>
<bag name="values" table="device_policy_values" cascade="all">
<key>
<column name="device_id" not-null="true"/>
<column name="policy_id" not-null="true"/>
</key>
<element type="string" length="1024">
<column name="policy_value" index="policy_value_idx"/>
</element>
</bag>
</class>
Code:
String names[] = ....;
Long id = ....;
Criteria criteria = session.createCriteria(Device.class);
criteria.add(Restrictions.eq("repositoryId", id ))
.createCriteria("policies", "device_policy")
.createCriteria("device_policy.policy", "global_policy")
.add(Restrictions.in("global_policy.name", names ))
.add(Restrictions.eq("global_policy.repositoryId", id ));
Full stack trace of any exception that occurs:Code:
RROR 2006-10-25 15:45:52 JDBCExceptionReporter:logExceptions - ERROR: relation "global_pol2_" does not exist
INFO 2006-10-25 15:45:52 DeviceQueryService:findDevices - Cannot load devices
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at xxx.service.DeviceQueryService.findDevices(DeviceQueryService.java:40)
Name and version of the database you are using:Postgresql 7.4
The generated SQL (show_sql=true):Code:
Hibernate:
/* criteria query */ select
this_.id as id2_1_,
this_.repository_id as repository2_2_1_,
this_.name as name2_1_,
this_.file_name as file4_2_1_,
this_.parent_id as parent5_2_1_,
device_pol1_.device_id as device1_3_0_,
device_pol1_.policy_id as policy2_3_0_
from
device this_
inner join
device_policy device_pol1_
on this_.id=device_pol1_.device_id
where
this_.repository_id=?
and this_.name=?
and global_pol2_.name in (
?, ?
)
and global_pol2_.repository_id=?
Login
FAQ
Search