title: Complex order-by clause into .hbm hibernate files
body: <p>I have a question about hibernate and order-by clause. 
  I have a mySQL database containing 3 tables: A, B and C. The Java code corresponding to those tables is hereafter.</p>
  
  <p>Class A code:</p>
  
  <pre><code>package database;
  
  import java.util.Set;
  
  public class A implements java.io.Serializable {
  
      private Integer id;
      private Set&lt;B&gt; listB;
  
      public A() {
      }
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public Set&lt;B&gt; getListB() {
          return listB;
      }
  
      public void setListB(Set&lt;B&gt; listB) {
          this.listB = listB;
      }
  
  }
  </code></pre>
  
  <p>Class B code:</p>
  
  <pre><code>package database;
  
  public class B implements java.io.Serializable {
  
      private Integer id;
      private A a;
      private C c;
  
      public B() {
      }
  
      public Integer getId() {
          return this.id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public A getA() {
          return a;
      }
  
      public void setA(A a) {
          this.a = a;
      }
  
      public C getC() {
          return c;
      }
  
      public void setC(C c) {
          this.c = c;
      }
  }
  </code></pre>
  
  <p>Class C code:</p>
  
  <pre><code>package database;
  
  public class C implements java.io.Serializable {
  
      private Integer id;
      private int num;
  
      public C() {
      }
  
      public Integer getId() {
          return this.id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public int getNum() {
          return this.num;
      }
  
      public void setNum(int num) {
          this.num = num;
      }
  }
  </code></pre>
  
  <p>The associated .hbm files are here</p>
  
  <p>Table A mapping:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" &gt;
  &lt;hibernate-mapping package="database"&gt;
      &lt;class name="A" table="A"&gt;
          &lt;id
              column="id"
              name="Id"
              type="integer"
          &gt;
              &lt;generator class="increment" /&gt;
          &lt;/id&gt;
          &lt;set name="listB" cascade="all, delete-orphan" inverse="true" lazy="true" fetch="select" order-by="c.Id asc"&gt;
              &lt;key column="a_id"/&gt;
              &lt;one-to-many class="B"/&gt;
          &lt;/set&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Table B mapping:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" &gt;
  &lt;hibernate-mapping package="database"&gt;
      &lt;class name="B" table="B"&gt;
          &lt;id
              column="id"
              name="Id"
              type="integer"
          &gt;
              &lt;generator class="increment" /&gt;
          &lt;/id&gt;
          &lt;many-to-one name="a" class="A" fetch="select"&gt;
              &lt;column name="a_id" not-null="true" /&gt;
          &lt;/many-to-one&gt;
          &lt;many-to-one name="c" class="C" fetch="select"&gt;
              &lt;column name="c_id" not-null="true" /&gt;
          &lt;/many-to-one&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Table C mapping:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" &gt;
  &lt;hibernate-mapping package="database"&gt;
      &lt;class name="C" table="C"&gt;
          &lt;id
              name="Id"
              type="integer"
              column="id"
          &gt;
              &lt;generator class="increment"/&gt;
          &lt;/id&gt;
          &lt;property
              name="Num"
              column="num"
              type="integer"
              not-null="true"
              length="10"
          /&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>My question is about the "order-by" clause in the A.hbm file. I would like to order not following c.Id, but c.Num</p>
  
  <pre><code>&lt;set name="listB" cascade="all, delete-orphan" inverse="true" lazy="true" fetch="select" order-by="c.Num asc"&gt;
  </code></pre>
  
  <p>Unfortunatly, when I do so, I get the following exception:</p>
  
  <pre><code>Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not initialize a collection: [database.A.listB#1]
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
      at org.hibernate.loader.Loader.loadCollection(Loader.java:2069)
      at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:62)
      at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:628)
      at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:83)
      at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1853)
      at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:366)
      at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:108)
      at org.hibernate.collection.PersistentSet.iterator(PersistentSet.java:186)
      at Test.main(Test.java:36)
  Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'listb0_.c.Num' in 'order clause'
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
      at java.lang.reflect.Constructor.newInstance(Unknown Source)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.Util.getInstance(Util.java:386)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
      at org.hibernate.loader.Loader.doQuery(Loader.java:718)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
      at org.hibernate.loader.Loader.loadCollection(Loader.java:2062)
      ... 8 more
  </code></pre>
  
  <p>I am new to hibernate, I read some things about criteria that could help me, but I don't see how to use them into hbm files.</p>
  
  <p>Thanks for helping,</p>
  
  <p>Lauriane</p>
  
  <p>After trying the ideas you gave me, I still don't have the answer. First, I wrote the following method to enter data into the database:</p>
  
  <pre><code>private static void create() {
      A a = new A();
      HibernateUtil.save(a);
  
      C c1 = new C();
      c1.setNum(5);
      HibernateUtil.save(c1);
  
      C c2 = new C();
      c2.setNum(2);
      HibernateUtil.save(c2);
  
      C c3 = new C();
      c3.setNum(7);
      HibernateUtil.save(c3);
  
      C c4 = new C();
      c4.setNum(3);
      HibernateUtil.save(c4);
  
      B b1 = new B();
      b1.setA(a);
      b1.setC(c1);
      HibernateUtil.save(b1);
  
      B b2 = new B();
      b2.setA(a);
      b2.setC(c4);
      HibernateUtil.save(b2);
  
      B b3 = new B();
      b3.setA(a);
      b3.setC(c3);
      HibernateUtil.save(b3);
  
      B b4 = new B();
      b4.setA(a);
      b4.setC(c2);
      HibernateUtil.save(b4);
  
      A a2 = new A();
      HibernateUtil.save(a2);
  
      C c5 = new C();
      c5.setNum(13);
      HibernateUtil.save(c5);
  
      C c6 = new C();
      c6.setNum(11);
      HibernateUtil.save(c6);
  
      C c7 = new C();
      c7.setNum(10);
      HibernateUtil.save(c7);
  
      C c8 = new C();
      c8.setNum(14);
      HibernateUtil.save(c8);
  
      B b5 = new B();
      b5.setA(a2);
      b5.setC(c5);
      HibernateUtil.save(b5);
  
      B b6 = new B();
      b6.setA(a2);
      b6.setC(c8);
      HibernateUtil.save(b6);
  
      B b7 = new B();
      b7.setA(a2);
      b7.setC(c7);
      HibernateUtil.save(b7);
  
      B b8 = new B();
      b8.setA(a2);
      b8.setC(c6);
      HibernateUtil.save(b8);
  }
  </code></pre>
  
  <p>It gives me 2 instances of A, each one with a list of 4 instances of B.</p>
  
  <p>Then, I tried this code:</p>
  
  <pre><code>Criteria crit = HibernateUtil.currentSession().createCriteria(A.class);
  Criteria critb = crit.createCriteria("listB");
  Criteria critc = critb.createCriteria("c");
  critc.addOrder(Order.asc("num"));
  List&lt;A&gt; list = critc.list();
      // Code to iterate over listA
      for (A a : list) {
  
           List&lt;B&gt; listB = a.getListB();
          for (B b : listB) {
  
               System.out.print(b.getC().getNum() + ", ");
  
          }
          System.out.println();
      }
  </code></pre>
  
  <p>The list I obtain contains 4 times each instance of A, and the output is:</p>
  
  <pre><code>2, 7, 3, 5, 
  2, 7, 3, 5, 
  2, 7, 3, 5, 
  2, 7, 3, 5, 
  14, 13, 10, 11, 
  14, 13, 10, 11, 
  14, 13, 10, 11, 
  14, 13, 10, 11, 
  </code></pre>
  
  <p>So I tried to change the getter of listB in the A class, to:</p>
  
  <pre><code>public List&lt;B&gt; getListB() {
               Criteria crit =
       HibernateUtil.currentSession().createCriteria(B.class);
       Criteria critc = crit.createCriteria("c");
       critc.addOrder(Order.asc("num"));
       return critc.list();
  }
  </code></pre>
  
  <p>and run the following code:</p>
  
  <pre><code> List&lt;A&gt; list = (List&lt;A&gt;) HibernateUtil.getList("from A");
      for (A a : list) {
  
          List&lt;B&gt; listB = a.getListB();
          for (B b : listB) {
  
              System.out.print(b.getC().getNum() + ", ");
  
          }
          System.out.println();
  
      }
  </code></pre>
  
  <p>And the output was:</p>
  
  <pre><code>2, 3, 5, 7, 10, 11, 13, 14, 
  2, 3, 5, 7, 10, 11, 13, 14, 
  </code></pre>
  
  <p>There is no difference made between the instances of B refering to one or the other instance of A, and I don't see how it could work...</p>
  
  <p>Thanks for your help,</p>
  
  <p>Lauriane</p>