[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Hibernate, fetch-join + order by order problem

[virgo47]

Hello

I have normal master-detail table pair where master table has mapping like this (important snippets):

<hibernate-mapping>
  |         <class name="BE" table="be">
  | ...
  |                 <property name="stopTimestamp" type="java.util.Date" column="STOP_TS"
  | not-null="false"/>
  | 
  | ...
  |                 <bag name="analysis" cascade="all" order-by="start_ts">
  |                         <key column="be_id"/>
  |                         <one-to-many class="BEAnalysis"/>
  |                 </bag>
  | ...
  |         </class>
  | </hibernate-mapping>

So I have analyzes ordered by their start_ts column. Now we need process big portion of BEs along with their analysis and we used fetch join in order to do it quickly (lazy load was unacceptably slow - too many selects).

Code for listing those BE with analysis.

Criteria criteria = session.createCriteria(BE.class);
  | ...
  | criteria.addOrder(Order.asc("stopTimestamp"));
  | criteria.setFetchMode("analysis", FetchMode.JOIN);
  | return (Collection<BE>) criteria.list();

And now tail of the generated SQL:

... order by analysis2_.start_ts, this_.STOP_TS asc

(this_ is be table of course)

Problem is that I need to order by stop_ts first (be order is much  more important, than comes the order of related analysis). What's the cure for this? Thanks for hint

Virgo

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3995557#3995557

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3995557