Hi,
I have the following tables in a database:
Code:
------------------ -------------------
| tbl_locations | | tbl_site_status |
|----------------| |-----------------|
| loc_id (pk) | | ss_site_id(pk) |
| loc_name | | ss_type(pk) |
------------------ | ss_from(pk) |
| ss_to |
| ss_status |
-------------------
where ss_site_id is foreign key to tbl_locations if ss_type = "2"
Sample data in tbl_site_status
+------------+---------+------------+------------+-----------+
| ss_iste_id | ss_type | ss_from | ss_to | ss_status |
+------------+---------+------------+------------+-----------+
| 1 | 1 | 2003-01-01 | 2003-12-31 | 3 |
| 2 | 2 | 2003-01-01 | 2003-12-31 | 0 |
| 2 | 2 | 2004-01-01 | NULL | 1 |
| 3 | 2 | 2003-01-01 | NULL | 0 |
+------------+---------+------------+------------+-----------+
I Want to transform the following query into Hibernate syntax:
Code:
"select * from tbl_locations
left join tbl_site_status on ss_site_id = loc_id and ss_type = 2 and ss_from <= :currDate and (ss_to >= :currDate or ss_to is null)"
Sample Resultset of query (currDate='2004-09-01'):
+--------+-------------------------+---------+------------+------------+-----------+
| loc_id | loc_companyname | ss_type | ss_from | ss_to | ss_status |
+--------+-------------------------+---------+------------+------------+-----------+
| 1 | LOCATION 01 | NULL | NULL | NULL | NULL |
| 2 | LOCATION 02 | 2 | 2004-01-01 | NULL | 1 |
| 3 | LOCATION 03 | 2 | 2003-01-01 | NULL | 0 |
+--------+-------------------------+---------+------------+------------+-----------+
When I write the following query in HQL:
Code:
select loc from LocationData as loc
left join fetch loc.statusList as sl
I was expecting Hibernate to discover both "ss_site_id" and "ss_type" (discriminator) and put these into the on-part of the left-join.
Instead the following query is produced:
Code:
select * from tbl_locations left join tbl_site_status on ss_site_id = loc_id
If I put the "ss_type" constaints into the where-part of the query, to many records will be filtered away.
As an example, record number 1 will be removed from the sample resultset.
Is there any way to instruct Hibernate to put extra constraints into the on-part of a left join (without the use of native sql)?
Thanks in advance for any comments.
Kind regards,
Bjarte Andre Eide
Unique Promotions AS
Bergen, Norway
Hibernate version: 2.1.6 Mapping documents:Code:
<class name="com.up.camis.data.LocationData" table="tbl_locations">
<id name="id" column="loc_id">
<generator class="native"></generator>
</id>
<property name="name" column="loc_companyname"></property>
<set name="statusList">
<key column="ss_site_id"></key>
<one-to-many class="com.up.camis.data.LocationStatus"></one-to-many>
</set>
</class>
<class name="com.up.camis.data.StatusData" table="tbl_site_status">
<composite-id class="com.up.camis.data.StatusDataPK" name="primaryKey">
<key-property name="type" column="ss_type"></key-property>
<key-property name="id" column="ss_site_id"></key-property>
<key-property name="from" column="ss_from"></key-property>
</composite-id>
<discriminator column="ss_type" type="string" not-null="true" insert="false"></discriminator>
<property name="to" column="ss_to"></property>
<property name="status" column="ss_status"></property>
<subclass name="com.up.camis.data.LocationStatus" discriminator-value="2">
<many-to-one name="location" column="ss_site_id" class="com.up.camis.data.LocationData" insert="false" update="false"></many-to-one>
</subclass>
</class>
Login
FAQ
Search
