Home › Blogs › pulsar's blog

Using native SQL in nHibernate

Fri, 03/28/2008 - 17:34 — pulsar

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!

Your rating: None Average: 2.7 (3 votes)

Comments

Sun, 05/02/2010 - 14:50 — pulsar
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));

Sun, 05/02/2010 - 15:31 — pulsar
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.