I have three tables.
Order,
Location,
Order_Location where Order_Location is table that holds
many-to-many relation.
Order has
List<Location>. Location has property called
city. Using
HQL (Hibernate 3.6 for Java), I want to get all the locations for a particular
order,
ordered by the
city.
In hbm file, List<Location> is mapped using idbag. Though I got the HQL, the generated SQL query is having join to [i]Location [/u]and
Order_Location table twice which I feel is overhead. Also, because of this,
SELECT is happening from first instance of table and
ORDER BY is happening using the second instance of
Location table
What am I doing wrong here?
Code:
SELECT o.locationList FROM Order o
join o.locationList locList
where o.orderId = 1
order by locList.city desc
translates to following which has inner join that is unnecessary
Code:
select
order4_.LOC_ID as order1_355_,
order4_.LOC_CODE as order2_355_,
order4_.CITY as order3_355_,
order4_.CITY_LONG_NAME as order4_355_
from
sche.order order0_
inner join
sche.order_location order1_
on order0_.ORDER_ID=order1_.ORDER_ID
inner join
sche.location order2_
on order1_.LOC_ID=order2_.LOC_ID
inner join
sche.order_location order3_
on order0_.ORDER_ID=order3_.ORDER_ID
inner join
sche.location order4_
on order3_.LOC_ID=order4_.LOC_ID
where
order0_.ORDER_ID=1
order by
order2_.city desc
Following are the mapping files
Code:
Order.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"
>
<hibernate-mapping>
<class name="collectionorderby.Order" table="ORDER">
<id name="orderId" type="string">
<column name="ORDER_ID" length="32" />
<generator class="uuid" />
</id>
<idbag name="locList" lazy="false" table="ORDER_LOCATION" fetch="select">
<collection-id column="ORDER_LOCATION_ID" type="string">
<generator class="uuid" />
</collection-id>
<key>
<column name="ORDER_ID" length="32" not-null="true" />
</key>
<many-to-many column="LOC_ID" class="collectionorderby.Location"
fetch="join" />
</idbag>
</class>
</hibernate-mapping>
Location.hbm.xml
<?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="collectionorderby.Location" table="LOCATION">
<id name="locId" type="string">
<column name="LOC_ID" length="50" />
</id>
<property name="locCode" type="string">
<column name="LOC_CODE" length="50" />
</property>
<property name="city" type="string">
<column name="CITY" length="50" />
</property>
<property name="cityLongName" type="string">
<column name="CITY_LONG_NAME" length="500" />
</property>
</class>
</hibernate-mapping>
Login
FAQ
Search