NHibernate执行原始SQL代码的方法小结 .

在使用NHibernate过程中经常会使用到复杂的sql查询，但是使用hql又比较麻烦的情况下，我们往往都会想到采用原始的sql来执行。但是如何利用NHibernate来执行sql呢？问题来了，在NHibernate中也有AdoTemplate的方法可以执行sql的，但是这里要介绍的是另外一种方法：CreateSQLQuery。以下部分例子源自于网络。

 

 

实例一（源自于http://blog.csdn.net/canduecho/archive/2009/05/04/4149930.aspx，感谢博主分享）：

 

 

Nhibernate中CreateSQLQuery用法实例：

 

涉及的表：

 

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. Cake{  
2.   
3. Id ，  
4.   
5. CakeName  
6.   
7. 。。。。  
8.   
9. }  
10.   
11.   
12. CakeSize{  
13.   
14. CakeId，-为外键，对应Cake表的字段Id  
15.   
16. Size  
17.   
18. }  

Cake{ Id ， CakeName 。。。。 } CakeSize{ CakeId，-为外键，对应Cake表的字段Id Size }

 

 

（其中ISession session = NHibernateHelper.GetCurrentSession();）

 

用法一（返回数值）：

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISQLQuery query = session.CreateSQLQuery("SELECT COUNT(Id) AS C FROM Cake").AddScalar("C", NHibernateUtil.Int32);  
2. int c = Convert.ToInt32(query.UniqueResult());   

ISQLQuery query = session.CreateSQLQuery("SELECT COUNT(Id) AS C FROM Cake").AddScalar("C", NHibernateUtil.Int32); int c = Convert.ToInt32(query.UniqueResult());
或int c = query.UniqueResult<int>;//使用此方法发现sql执行了两次，故不推荐使用。

 

用法二（返回对象实体）：

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("CAKE.DataTransfer.Entities.Cake");  

ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("CAKE.DataTransfer.Entities.Cake");
或

 

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", "CAKE.DataTransfer.Entities.Cake");  

ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", "CAKE.DataTransfer.Entities.Cake");
或

 

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity(typeof(Cake));  

ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity(typeof(Cake));
或

 

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", typeof(Cake));  

ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", typeof(Cake));

 

或

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", typeof(Cake), LockMode.Write);  
2.   
3. IList<Cake> c = query.List<Cake>();  

ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", typeof(Cake), LockMode.Write); IList<Cake> c = query.List<Cake>();

 

 

 

用法三（连表查询）：

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISQLQuery query = session.CreateSQLQuery("select cs.* from cake c join CakeSize cs on cs.CakeId=c.Id")  
2.     .AddEntity("cs", typeof(CakeSize));  
3. IList<CakeSize> cs = query.List<CakeSize>();  

ISQLQuery query = session.CreateSQLQuery("select cs.* from cake c join CakeSize cs on cs.CakeId=c.Id") .AddEntity("cs", typeof(CakeSize)); IList<CakeSize> cs = query.List<CakeSize>();

 

 

 

以上是一种常见的用法，通过该例子，相信大家心里也已经有个数了，也应该知道怎么操作这个sql了：）

 

但是过程中难免会遇到些问题，比如：

 

实例二（问题说明）：

 

我需要执行例如这条sql语句：

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo  

select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo

 

 

1. 如果使用session.CreateQuery方法执行的话，你可能会遇到这样的错误：

 

错误：undefined alias or unknown mapping

 

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISession session = DAORepository.Instrance.DbSession;  
2. string queryString = "select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo where status=0 Order by CreateTime desc";  
3. IQuery query = session.CreateQuery(queryString;  
4. IList lst = query.List();  

ISession session = DAORepository.Instrance.DbSession; string queryString = "select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo where status=0 Order by CreateTime desc"; IQuery query = session.CreateQuery(queryString; IList lst = query.List();

 

 

 

2. 使用CreateSQLQuery方法执行：

 

错误：Return types of SQL query were not specified...

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISession session = DAORepository.Instrance.DbSession;  
2. string queryString = "select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo where status=0 Order by CreateTime desc";  
3. ISQLQuery query = session.CreateSQLQuery(queryString);  
4. IList lst = query.List();  

ISession session = DAORepository.Instrance.DbSession; string queryString = "select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo where status=0 Order by CreateTime desc"; ISQLQuery query = session.CreateSQLQuery(queryString); IList lst = query.List();

 

 

3. 使用CreateSQLQuery方法并指定返回字段值的类型，即使用AddScalar方法指定字段值类型：

 

错误：could not execute query...

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISession session = DAORepository.Instrance.DbSession;  
2. string queryString = "select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo where status=0 Order by CreateTime desc";  
3. ISQLQuery query = session.CreateSQLQuery(queryString).AddScalar("CreateTime",NHibernateUtil.String)  
4. .AddScalar("Address",NHibernateUtil.String)  
5. .AddScalar("Password",NHibernateUtil.String)  
6. .AddScalar("EmailType",NHibernateUtil.Int32);  
7. IList lst = query.List();  

ISession session = DAORepository.Instrance.DbSession; string queryString = "select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo where status=0 Order by CreateTime desc"; ISQLQuery query = session.CreateSQLQuery(queryString).AddScalar("CreateTime",NHibernateUtil.String) .AddScalar("Address",NHibernateUtil.String) .AddScalar("Password",NHibernateUtil.String) .AddScalar("EmailType",NHibernateUtil.Int32); IList lst = query.List();

 

 

注意：EmailType字段对应数据库的类型是int，这里我就误解为也应该映射为NHibernateUtil.Int32；

 

 

4. 解决问题：

把AddScalar方法的参数里一个属性改了一下：

 

[c-sharp:showcolumns] view plain copy print ?

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

1. ISession session = DAORepository.Instrance.DbSession;  
2. string queryString = "select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo where status=0 Order by CreateTime desc";  
3. ISQLQuery query = session.CreateSQLQuery(queryString).AddScalar("CreateTime",NHibernateUtil.String)  
4. .AddScalar("Address",NHibernateUtil.String)  
5. .AddScalar("Password",NHibernateUtil.String)  
6. .AddScalar("EmailType",NHibernateUtil.String);  
7. IList lst = query.List();  

ISession session = DAORepository.Instrance.DbSession; string queryString = "select CreateTime,Address,Password, (case when EmailType = 0 then 'Gmail' when EmailType = 1 then 'Yahoo' when EmailType = 2 then 'Hotmail' else 'Other' end) as EmailType from MailInfo where status=0 Order by CreateTime desc"; ISQLQuery query = session.CreateSQLQuery(queryString).AddScalar("CreateTime",NHibernateUtil.String) .AddScalar("Address",NHibernateUtil.String) .AddScalar("Password",NHibernateUtil.String) .AddScalar("EmailType",NHibernateUtil.String); IList lst = query.List();

 

 

注意：这里我将EmailType的类型映射改为了NHibernateUtil.String，实际上是与返回结果的EmailType值类型保持一致就OK了。

 

好了，相信大家看了上面的例子，应该也有所感触吧，最后，再次感谢文中引用的作者，感谢他们给他们的经验的分享。

 

 

 

 

 

 

 

 

 

文中主要引自：

http://blog.csdn.net/canduecho/archive/2009/05/04/4149930.aspx

http://hi.baidu.com/shuhaicaiyun/blog/item/62f7118112eee1dfbd3e1e51.html

 

 

转载于:https://www.cnblogs.com/aaa6818162/archive/2012/02/26/2368607.html