2 Replies Latest reply on Nov 12, 2008 7:14 PM by recampbell

    OrderBy collections


      Hello all,

      I am running SeamTests under Seam 2.1.0.SP1 against an in-memory h2 database, with the embedded jboss engine loading my annotated EJB3 enities.  When collections with an @OrderBy annotation are lazily initialized, the generated SQL does not alias the entity's table correctly.

      The collection is defined as:

          @OneToMany(fetch=FetchType.LAZY, mappedBy="ownerId")
          @OrderBy("dateCreated desc")
          private List<PersonNote> notes = new ArrayList<PersonNote>();

      When I try to initialize the collection in tahe invokeApplication phase, I get:

      Caused by: org.hibernate.exception.SQLGrammarException: could not initialize a collection: [com.debix.model.entities.Person.notes#3]
              at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
              at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
              at org.hibernate.loader.Loader.loadCollection(Loader.java:2001)
              at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:36)
      Caused by: org.h2.jdbc.JdbcSQLException: Column NOTES.DATECREATED not found; SQL statement:
      select notes0_.PersonOwnerId as PersonOw7_1_, notes0_.content as content89_0_, notes0_.dateCreated as dateCrea4_89_0_, 
      notes0_.username as username89_0_, notes0_.version as version89_0_, notes0_.PersonOwnerId as PersonOw7_89_0_ 
      from Notes notes0_ 
      where notes0_.PersonOwnerId=? order by Notes.dateCreated desc [42122-72]

      Clearly, the order by clause should use the notes0 alias, not the Notes table name.  Once this is fixed, the query runs successfully.  I have this problem with all collections that have an @OrderBy.

      I previously was running this test successfully under Seam 1.2.1, and the hibernate-all jars included therein.  Furthermore, this code runs without problem in JBoss 4.2.3, and the Hibernate version deployed there. 

      I tried to use the Hibernate jars from 4.2.3 in my SeamTest classpath, but they seem incompatible with the JBoss EJB3 deployer in the jboss embedded library.

      Is this a known issue, or do I have some configuration issue?  Or can I roll my own jboss embedded somehow?


        • 1. Re: OrderBy collections

          Sorry for the crappy subject.

          In tracking this down, I was looking at the various Hibernate libraries included in hibernate-all.jar and comparing them with the ones included with Seam.  Here are the differences:

          lib/test/hibernate-all.jar from Seam 2.1.0.SP1:

          Hibernate Annotations 3.3.1.Beta1 (from org.hibernate.cfg.annotations.Version)
          Hibernate EntityManager 3.3.2.Beta1 (from org.hibernate.ejb.Version)

          lib/hibernate-entitymanager.jar and lib/hibernate-annotations.jar from 2.1.0.SP1:

          Hibernate Annotations 3.3.0.GA 
          Hibernate EntityManager 3.3.1.GA

          So it seems like the embedded jboss hibernate jars were upgraded to beta versions beyond those included in the main Seam distribution. 

          • 2. Re: OrderBy collections

            So this seems that having beta versions in hibernate-all.jar is not the source of the problem.  I was able to replace hibernate-all.jar with the hibernate jars from /lib and I still had the problem.  Looking in to the hibernate JIRA, I found  http://opensource.atlassian.com/projects/hibernate/browse/ANN-651 which seems to be the cause of my problem.