I have a "deliveries" table which contains all my deliveries. From these, I need to report on various statistics - for example: how many were late, how many were damaged, how many customers were satisfied. I am trying to determine whether I should do a different query for each statistic ( select * from delivery where damaged = true ) OR should I select all the deliveries and do the totalling in java code ( if (delivery.isDamaged()) damaged++) ...).

In the first case it will be ~6 seperate database queries with a single result back. In the second it will be a single query with anywhere from 10-2000 results.

Any suggestions on which will be quicker? Or, is there a better way to do this...

Querying at the database level will almost surely be much faster than in Java code as relational databases have been around for years, and have query performance enhancements built in. The cost of each query much be very high to justify going with a single query and in memory Java sorts.

You can write the SQL queries, test it against the database, optimize if necessary, then wire them in Hibernate as mapped sql-queries.

That's what I thought. My unit tests just go a bit slower with the non-in-memory version. That might be because I am using HSQLDB though :).

Is there a way to map native sql queries in hibernate? Yes, I know there is this: http://www.hibernate.org/hib_docs/refer ... rysql.html, but that doesn't work for me because I'm using sql functions like time() and count(). Anyway, thats a small issues. I just pull out a JDBC connection and it works.