How to Retereive data from table by ORDER BY on some column

whitesox99 · **Joined:** Wed Sep 06, 2006 7:34 pm **Posts:** 3

Hi I need a small help in getting the data as per the order by id. I am building a menu that has sub-menus.
In SUB_MENU table I also have a order_by column and in my hbm.xml file I specify order-by on that column it returns me data in correct order.

ParentMenu does not have order_by column how can I get the data ordered by on id column for my ParentMenu i.e. id column is a sequence. Or do I need a order_by column in ParentMenu table and then specify ordyby on that column too.

Code:

ParentMenu:

<?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="com.ref.menu.ParentMenu" table="MENU">    
<id name="id" column="ME_ID" type="long">      
<generator class="sequence">          
<param name="sequence">MENU_SEQ</param>       
</generator>      
</id>      
<property name="Title" type="string" length="50" column="TITLE" not-null="true"/>   
<property name="Url" type="string" length="250" column="URL" not-null="true"/>   
<property name="crDate" type="timestamp" column="CREATED_DATE"/>  
<property name="crBy" type="string" length="35" column="CREATED_BY"/>  
<property name="targ" type="string" length="10" column="TARGET"/>   
<set name="subMenu" table="SUB_MENU" order-by="ORDER_BY">    
<key>         
<column name="ME_ID"/>     
</key>       
<one-to-many class="com.ref.menu.SubMenu" />  
</set>   
</class></hibernate-mapping>


SubMenu:

<?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="com.ref.menu.SubMenu" table="SUB_MENU">   
<id name="id" column="SUB_ME_ID" type="long">   
<generator class="sequence">      
<param name="sequence">SUB_MENU_SEQ</param>   
</generator>      
</id>      
<many-to-one name="parentMenu" class="com.ref.menu.ParentMenu">   
<column name="ME_ID" />  
 </many-to-one>      
 <property name="subMenuTitle" type="string" length="50" column="SUB_MENU_TITLE" not-null="true"/>
 <property name="subMenuUrl" type="string" length="250" column="SUB_MENU_URL" not-null="true"/>      
 <property name="subMenuActive" type="string" length="1" column="SUB_MENU_ACTIVE" not-null="true"/>    
 <property name="createdDate" type="timestamp" column="CREATED_DATE"/> 
 <property name="createdBy" type="string" length="35" column="CREATED_BY"/>   
 <property name="target" type="string" length="35" column="TARGET"/>  
 <property name="orderBy" column="ORDER_BY" not-null="true"/>  
 </class></hibernate-mapping>

And here is the code to get the data:

Code:

List result = session.createQuery("from com.ref.menu.ParentMenu").list();

But the Parent Menu data is not ordered it shows as it is enetered in the table. How can I make it to return data
order-by id. id is a number field in Parent Table. Any help is appreciated.

Thanks

whitesox99 · **Joined:** Wed Sep 06, 2006 7:34 pm **Posts:** 3

Any help please???

mmasters · **Posted:** Thu Sep 07, 2006 10:25 am

according to http://www.hibernate.org/hib_docs/v3/re ... l-ordering

you might want to try something like:

Code:

List result = session.createQuery("from com.ref.menu.ParentMenu parentMenu order by parentMenu.id").list();