Using native SQL in nHibernate

Today I ran into a situation in a nHibernate based project where I needed to execute a very specialized query. Using HQL was rather not practical since I had to query tables which were not "directly" mapped to entities.

So, if you need execute queries behind nHibernate's back and map the results to a Bean / Object not specified in the mappings, You might come up with a solution like this:

  1. string someComplexQuery = @"select ... from ...";
  2.  
  3. IQuery sqlQuery =
  4. sessionFactory.GetCurrentSession().CreateSQLQuery(someComplexQuery).
  5. AddScalar("Id", NHibernateUtil.Int32).
  6. AddScalar("Firstname", NHibernateUtil.String).
  7. AddScalar("Lastname", NHibernateUtil.String).SetResultTransformer(
  8. Transformers.AliasToBean(typeof(Person)));
  9.  
  10. IList<Person> people = sqlQuery.List<Person>();
  11. foreach (Person person in people)
  12. {
  13. // ...
  14. }

For a good measure, here is the Person class:
  1. class Person
  2. {
  3. private long id;
  4. private string firstname;
  5. private string lastname;
  6.  
  7. public long Id
  8. {
  9. get { return id; }
  10. set { id = value; }
  11. }
  12.  
  13. public string Firstname
  14. {
  15. get { return firstname; }
  16. set { firstname = value; }
  17. }
  18.  
  19. public string Lastname
  20. {
  21. get { return lastname; }
  22. set { lastname = value; }
  23. }
  24. }

Works like a breeze, /me likes!

Comments

pulsar's picture

java variant

here goes an example for the java variant of hibernate:

  1. getSession().
  2. createSQLQuery("select distinct album_artist as albumArtist, album from media_files order by album_artist;").
  3. addScalar("album_artist", Hibernate.STRING).
  4. addScalar("album",Hibernate.STRING).
  5. setResultTransformer(Transformers.aliasToBean(AlbumArtistToAlbumItem.class));

pulsar's picture

P.S.

I ran into an issue with the column name aliasing and hibernate 3.1 - got me a unknown column name afterwards. perhaps a bug, not sure. bottom line is: you might need to name you bean properties after the column names and not use "AS" in the sql query.