0

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?

Assaf
  • 788
  • 2
  • 9
  • 23

3 Answers3

3

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" />
Community
  • 1
  • 1
fairct
  • 96
  • 1
  • 5
1

I use JPA, which I beleive is also supported by Hibernate, and simply create an XML file in a suitable directory under META-INF, then refer to it from my persistence.xml

<persistence-unit name="xxx">
    <jta-data-source>jdbc/xxx</jta-data-source>
    <mapping-file>META-INF/jpql/Xxx.xml</mapping-file>

When I have a "extra" data such as your count I often define a results class with a suitable cnmstructor and then use

  select new MyClass( thing, computedValue etc ...)

in my query. It appears that Hibernate supports that approach.

djna
  • 54,992
  • 14
  • 74
  • 117
  • Nice, but I cannot use JPA (restrictions...). anyway, i learned a lot from your answer, os thanks. – Assaf Jul 18 '11 at 07:55
  • as I understand it, the select new is not specific to JPA, straight Hibernate supports it. – djna Jul 18 '11 at 08:03
0

You can put the named query in a Hibernate mapping file (http://www.javalobby.org/java/forums/m91885316.html) if you use XML mappings, or with an annotation (http://download.oracle.com/javaee/6/api/index.html?javax/persistence/NamedQuery.html) if you're using annotations.

Another option, especially if you're joining elements in the query with other mappings is to create a database VIEW an map it in Hibernate.

Nicolae Albu
  • 1,235
  • 6
  • 6