public List<Message> getMessagesWithCounterOrder(User user, int limit,String orderType){
      
      final String query = "SELECT msg.* from msg_info msg,`transaction` t,account a where t.id=msg.transaction_id and t.account_id=a.id  and flag in ('displayed','validated') and a.id="+user.getId()+" order by flag,counter "+ orderType +" limit "+limit;
      
      System.out.println("Query"+query);
      List<Message> messages=(List<Message>) template.execute(new HibernateCallback(){

         public Object doInHibernate(Session session)
               throws HibernateException, SQLException {
            
               return session.createSQLQuery(query).addEntity(Message.class).list();
            
         }
         
      });
      return messages;
   }

<?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 package="com.lit.screen.backoffice.api.account">
<class name="User" table="bo_user">
  <id access="field" name="id">
   <generator class="native"/>
  </id>
  <property  generated="never" lazy="true"  name="createdDate" >
 
  </property>
 
  <property generated="never" lazy="true" name="loginId"
   not-null="true" unique="true"/>
   <property generated="never" lazy="true" name="firstName" not-null="true"/>
   <property generated="never" lazy="true" name="lastName" not-null="true"/>
   <property generated="never" lazy="true" name="password" not-null="true"/>
   <property generated="never" lazy="true" name="phone" not-null="true"/>
   <property generated="never" lazy="true" name="role" not-null="true"/>
    <property generated="never" lazy="true" name="status" not-null="true"/>
   
   <joined-subclass name="Account" table="account">
   <key column="id" />
   <property generated="never" lazy="true" name="expiryDate" not-null="true"/>
   <property generated="never" lazy="true" name="occasionLocation" not-null="true"/>
   <property generated="never" lazy="true" name="infoMessage" not-null="true"/>
    <many-to-one name="occasionType" class="OccasionType" column="occasionType_id" lazy="false">
   
   </many-to-one>
   <property generated="never" lazy="true" name="startDate" not-null="true"/>
   <property generated="never" lazy="true" name="topSenders" not-null="false"/>
   <set name="transactions" access="field" table="transaction" lazy="true" inverse="true" cascade="all" >
   <key column="account_id" on-delete="cascade">
   </key>
   <one-to-many class="Transaction" />
   </set>
   </joined-subclass>
</class>
</hibernate-mapping>

<?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 package="com.lit.screen.backoffice.api.account">
<class name="Transaction" table="transaction">
  <id access="field" name="id">
   <generator class="native"/>
  </id>
 
 
   <many-to-one name="shortCode" class="ShortCode" column="shortcode_id" lazy="false">
   
   </many-to-one>
   
     
   <many-to-one name="account" class="Account" column="account_id" lazy="false">
   
   </many-to-one>
 
     <set name="messages" access="field" table="msg_info" lazy="true" inverse="true" cascade="all" >
   <key column="transaction_id" on-delete="cascade">
   </key>
   <one-to-many class="com.lit.screen.view.api.Message" />
   </set>
 
</class>
</hibernate-mapping>

<?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 package="com.lit.screen.view.api">
<class name="Message" table="msg_info">
  <id access="field" column="msg_id" name="id">
   <generator class="native"/>
  </id>

  <property name="coding" column="coding" generated="never" lazy="true" not-null="true" />
 
  <property column="msg" generated="never" lazy="true" name="content" not-null="true"/>
  <property column="user_phone" generated="never" lazy="true"
   name="from" not-null="true"/>
  <property column="time_submission" generated="never" lazy="true"
   name="date" not-null="true"/>
   
     <property column="date_flag" generated="never" lazy="true"
   name="dateFlag" not-null="false"/>
 
  <property column="flag" generated="never" lazy="true" name="status" not-null="true"/>
 
  <many-to-one name="transaction" class="com.lit.screen.backoffice.api.account.Transaction" column="transaction_id" lazy="false">
   
   </many-to-one>
     <property column="COUNTER" generated="never" lazy="true" name="counter" not-null="true"/>
</class>
</hibernate-mapping>