I have a query that aggregates and groupes from 2 different tables:
SELECT co.name AS companyName, f.destination_id, COUNT(f.id) AS numberOfFlights FROM companies co INNER JOIN flights f ON co.c_id = f.company_id
GROUP BY co.id, co.name , f.destination_d
and, i would like to save it on the xml mapping file as a named query. My questions are: 1. While, as you can see, not all the result fields are actual columns in any table - how can I tell that to the map file? 2. In which xml should I save it - on the compant.hbm.xml or the flight.hbm.xml, or - is there a way to save it on the hibernate.cfg.xml?
I searched the web for advanced examples but couldn't find anything matches this kind of complication (although it is not that complcated...).
Can anyone provide me with a good example I can learn from, or can guide me himself?
For question 1, take a look at ResultTransformer. Just create a bean with the same names as the results of your query, and pass it to the ResultTransformer in a Transformers.aliasToBean call, like mentioned here: Hibernate: Mapping custom column names in stored procedure named query
For question 2, at least on our project, we've been putting our named queries into an XML file that contains only queries and referring to it from the hibernate.cfg.xml with this line:
<mapping resource="queries.hbm.xml" />