java - 多对多关联中的<收集元素的属性>排序

我有三张桌子。 Order，Location，Order_Location其中，Order_Location是具有多对多关系的表。

Order具有List<Location>。 Location具有称为city的属性。我想使用HQL（Java的Hibernate 3.6）来获取特定订单的所有位置，并按city进行排序。

在hbm文件中，List<Location>是使用idbag映射的。尽管我得到了HQL，但生成的SQL查询却两次连接到Location和Order_Location表，这对我来说很麻烦。

我在这里做错了什么？

SELECT o.locationList FROM Order o
join o.locationList locList
where o.orderId = 1
order by locList.city desc

转换为以下内容

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

=========编辑

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="locationList" 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>

====编辑
注意，当我们提供order by时，转换后的查询从表的第一个实例获取select，而order by是使用表的第二个实例完成的。我想，如果我们避免这些表的重复实例，那将不会发生。

最佳答案

以下工作按预期方式进行。在选择中使用别名locList代替o.locationList

SELECT locList  FROM Order o join o.locationList locList  where o.orderId = 1 order by locList.city desc