I am building an application using a legacy database. For one of the application modules, I need to extract the usecase ids and usecase names for a given user with a userid and an actor id. The classes "User", "Actor" and "Usecase" have many-to-many associations among themselves. I have placed the HBM.XML files for the three at the end of this posting.
I attempted to a number of HQL variations; I get the right tuple but repeated 8 times. It seems that I am a victim of "over-join". Here is one such HQL query and the associated hibernate-generated SQL. I ran the generated SQL by hand and sure enough I get the same tuple 8 time.
HQL
----
Code:
select uc.usecaseID,uc.usecaseName
from com.cgc.esd.pages.csys.Usecase as uc
join uc.users as u join uc.actors as a,
where u.userID=2 and a.actorID=13
Generated SQL
------------------
Code:
select usecase0_.USE_CASE_ID as x0_0_,
usecase0_.USE_CASE_NAME as x1_0_
from CSYS5.CSYS_USECASES usecase0_,
CSYS5.CSYS_USER_USECASES users1_,
CSYS5.CSYS_USERS user2_,
CSYS5.CSYS_ACTOR_USECASES actors3_,
CSYS5.CSYS_ACTORS actor4_
where usecase0_.USE_CASE_ID=users1_.USE_CASE_ID
and users1_.USER_ID=user2_.USER_ID
and usecase0_.USE_CASE_ID=actors3_.USE_CASE_ID
and actors3_.ACTOR_ID=actor4_.ACTOR_ID
and ((user2_.USER_ID=2 )and(actor4_.ACTOR_ID=13 ))
x0_0_ x1_0_
----- ----------------------------
8 Create Request For Quotation
8 Create Request For Quotation
8 Create Request For Quotation
8 Create Request For Quotation
8 Create Request For Quotation
8 Create Request For Quotation
8 Create Request For Quotation
8 Create Request For Quotation
***** returns the same tuple 8 times *****
Here is the query that was present in the legacy application. It generates only one tuple.
Hand woven SQL
--------------
Code:
SELECT uc.use_case_name, uuc.use_case_id
FROM csys5.csys_user_usecases uuc, csys5.csys_usecases uc
WHERE ( (uuc.USER_ID=2)
AND (uc.use_case_id = uuc.use_case_id)
AND (uuc.actor_id = 13) )
use_case_name use_case_id
---------------------------- ------------
Create Request For Quotation 8
***** returns only one tuple (this is how it should be) *****
How should I formulate the HQL to get what I want? Perhaps something needs to be fixed in the HBM files. I am using hibernate-2.1beta1.Here are the HBm files.
====================
Usecase.hbm.xml
====================
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping schema="CSYS5">
<class name="com.cgc.esd.pages.csys.Usecase" table="CSYS_USECASES">
<id name="usecaseID" column="USE_CASE_ID" type="long">
<generator class="assigned">
</generator>
</id>
<property name="usecaseName" column="USE_CASE_NAME" type="string"/>
<set name="users" table="CSYS_USER_USECASES" lazy="true" inverse="true">
<key column="USE_CASE_ID"/>
<many-to-many class="com.cgc.esd.pages.csys.User" column="USER_ID"/>
</set>
<set name="actors" table="CSYS_ACTOR_USECASES" lazy="true" inverse="true">
<key column="USE_CASE_ID"/>
<many-to-many class="com.cgc.esd.pages.csys.Actor" column="ACTOR_ID"/>
</set>
</class>
</hibernate-mapping>
====================
User.hbm.xml
====================
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping schema="CSYS5">
<class name="com.cgc.esd.pages.csys.User" table="CSYS_USERS">
<id name="userID" column="USER_ID" type="long">
<generator class="assigned">
</generator>
</id>
<property name="userName" column="USER_NAME" type="string"/>
<property name="password" column="ENCRYPTED_PASSWORD" type="string"/>
<property name="emailAddress" column="EMAIL" type="string"/>
<property name="costCenters" column="COSTCENTRES" type="string"/>
<property name="units" column="UNITS" type="string"/>
<property name="accountCreationDate" column="CREATION_DATE" type="date"/>
<set name="actors" table="CSYS_USER_ACTORS" lazy="false">
<key column="USER_ID"/>
<many-to-many class="com.cgc.esd.pages.csys.Actor" column="ACTOR_ID"/>
</set>
<set name="usecases" table="CSYS_USER_USECASES" lazy="false" inverse="true">
<key column="USER_ID"/>
<many-to-many class="com.cgc.esd.pages.csys.Usecase" column="USE_CASE_ID"/>
</set>
</class>
</hibernate-mapping>
====================
Actor.hbm.xml
====================
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping schema="CSYS5">
<class name="com.cgc.esd.pages.csys.Actor" table="CSYS_ACTORS">
<id name="actorID" column="ACTOR_ID" type="long">
<generator class="assigned">
</generator>
</id>
<property name="actorName" column="ACTOR_NAME" type="string"/>
<set name="users" table="CSYS_USER_ACTORS" lazy="true" inverse="true">
<key column="ACTOR_ID"/>
<many-to-many class="com.cgc.esd.pages.csys.User" column="USER_ID"/>
</set>
<set name="usecases" table="CSYS_ACTOR_USECASES" lazy="true" inverse="true">
<key column="ACTOR_ID"/>
<many-to-many class="com.cgc.esd.pages.csys.Usecase" column="USE_CASE_ID"/>
</set>
</class>
</hibernate-mapping>
Login
FAQ
Search
