Hibernate and HSQL (group by and order by)

I was experimenting the last few days with the three big embedded Java databases Derby, McKoy, and HSQL which are supported by Hibernate. Though I had high hopes for Derby it just didn't work. McKoy was slightly better and HSQL actually worked except for the well documentes "group by" Problem:

The query : "select cat, count(*) from Cat cat group by cat" does not work.
ERROR: column "xxxx" must appear in the GROUP BY clause or be used in an aggregate function

I fixed that in my code and it still dind't work. I had something like the following:
select cat, count(*) from Cat cat group by cat oder by cat.Name

which should according to SQL Syntax work. However HSQL doesn't like it when you use "order by" with "group by". (I also tries to group y cat.Name without any success). Anyway I ended up sorting the list in memory with java's built in Collections.sort...

Hibernate 2.0 -> Hibernate 3.0

I spent the last two weeks (on and off) on migrating my flagship application e-nnovate e-notebook (www.e-nnovate.com) from Hibernate 2 to Hibernate 3 so I can finally support the Derby Database. There is a migration guide at the hibernate site but here are some additional tips from me:

1. Unless you are using JTA don't use their new
   

   currentSession = HibernateUtil.getSessionFactory().getCurrentSession();

   because this only works with JTA. Instead stick to the ThreadLocal implementation. (In my case I always got weired errors like the Connection wasn't initialized or so)
2. You have to replace all session.find(...) with session.generateQuery(...).list() in your code
3. No database thing is allowed outside a transaction -- so you will have to open a transaction everywhere and in case you are doing a web application keep it open until the JSP is rendered. See here for an example which shows the principle but doesn't work. I wasn't able to open the transcation in the filter so I opened it in my code and kept it open and just used the filter to close it if it was open. In any case make sure that the filter is only running when actual jsp with database stuff are rendered.
4. Oh, and in some cases Hibernate 3 doesn't like it if you begin another transaction inside a transaction.
5. They got rid of dbcp instead use the cpo or proxool cache.
6. Big bummer: they screwed up polymorphic queries with some proxy stuff. To avoid working with proxises add to the class declaration in the mapping file lazy="false". This will switch of the CGLIB optimization for that class and its subclasses.
   
7. Some code like
   class bla {
   Test test = new Test();
   ....
   }
   doesn't work anymore. Instead you can't initialize Test anymore. Hibernate 3 is pretty sneeky about this and doesn't give you an error message. It just won't work.
8. Don't forget to update XDoclet in case you are using it;-)