• Nhibernate中CreateSQLQuery用法实例


    说明:

    使用原生SQL查询时,若要通过addEntity方法引入对象,则查询结果列中必须包含该对象的所有属性,否则会抛出System.IndexOutOfRangeException异常。

    结论:

    若要使用原生的SQL,建议直接用SQL的方式写语句,不要用addEntity引入对象!

    http://blog.163.com/it_yinliqing/blog/static/78497271201111253443113/


    涉及的表:
    Cake{
    Id ,
    CakeName

    }

    CakeSize{

    CakeId,-为外键,对应Cake表的字段Id

    Size

    }

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

    用法一(返回数值):
    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执行了两次,故不推荐使用。

    用法二(返回对象实体):
    ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("CAKE.DataTransfer.Entities.Cake");
    或ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", "CAKE.DataTransfer.Entities.Cake");
    或ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity(typeof(Cake));
    或ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", typeof(Cake));
    或ISQLQuery query = session.CreateSQLQuery("select * from cake c").AddEntity("c", typeof(Cake), LockMode.Write);

    IList<Cake> c = query.List<Cake>();

    用法三(连表查询):
    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语句:

    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
    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...
    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...

    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();
    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方法的参数里一个属性改了一下:

    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();
    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了。

    [最后修改由 hansong, 于 2010-09-30 15:12:42]
  • 相关阅读:
    Wine 的安装与解决微信文字不可见,无法聚焦问题。#Wine教程
    Utuntu / CentOS设置环境变量 #java #maven
    真机安装Linux系统 (CentOS与Ubuntu)
    scrollUtils.js #触底触发上拉加载更多 #越线 #上下滚动事件
    微信小程序 bus.js
    svchost.exe占网速的解决办法
    Linux常用的解压缩命令
    本博客标题指示灯代码
    Jenkins持续集成
    jenkins.plugins.publish_over.BapPublisherException: Failed to connect and initialize SSH connection. Message: [Failed to connect session for config [master_server]. Message [Auth fail]]
  • 原文地址:https://www.cnblogs.com/seesky/p/3169478.html
Copyright © 2020-2023  润新知