NHibernate使用记录之一

　　近段时间，项目使用了NHibernate，数据库为Oracle和NHibernate，期间踩过不少坑，在这里做一个系列，用三，两个章节记录一下。由于生性疏懒，本系列文章都是简单为主~

　　1、先建两个表，分别是：Student学生表，Classes班级表。其中，Student表的CId列是Classes表的外键。

　　2、配置关系映射，这里面配置了一个多对一的关系。

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Ye.Nhibernate.Entity" namespace="Ye.Nhibernate.Entity">
  <class name="StudentEntity" lazy="true" table="Student">
    <id name="Id" type="Int32">
      <column name="Id" sql-type="int" length="4" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name" type="System.String">
      <column name="Name" length="30" sql-type="varchar" not-null="true" />
    </property>
    <property name="StudentNo" type="System.String">
      <column name="StudentNo" length="30" sql-type="varchar" not-null="true" />
    </property>
    <property name="Age" type="System.Int32">
      <column name="Age" sql-type="int" not-null="true" />
    </property>
    <property name="CId" type="System.Int32">
      <column name="CId" sql-type="int" not-null="true" />
    </property>
    <many-to-one name="Classes" column="CId" class="Ye.Nhibernate.Entity.ClassesEntity,Ye.Nhibernate.Entity"
                 cascade="none" not-found="ignore" insert="false" update="false" />
  </class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Ye.Nhibernate.Entity" namespace="Ye.Nhibernate.Entity">
  <class name="ClassesEntity" lazy="true" table="Classes">
    <id name="Id" type="Int32">
      <column name="Id" sql-type="int" length="4" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="ClassesName" type="System.String">
      <column name="ClassesName" length="30" sql-type="varchar" not-null="true" />
    </property>
    <property name="ClassesNo" type="System.String">
      <column name="ClassesNo" length="30" sql-type="varchar" not-null="true" />
    </property>
  </class>
</hibernate-mapping>

　　3、写一个简单的SessionHelper。（偷个懒，大虾勿喷= =||）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NHibernate;

namespace Ye.Nhibernate
{
    public class NHSessionHelper
    {
        public static ISessionFactory SessionFactory { get; private set; }

        static NHSessionHelper()
        {
            SessionFactory = new NHibernate.Cfg.Configuration().Configure().BuildSessionFactory();
        }
    }
}

　　4、贴出来App.config文件

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
  </configSections>
  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.connection_string">Data Source=DESKTOP-4S0IVRP;User Id=sa;password=big@jack;database=TestDB</property>
      <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
      <property name='proxyfactory.factory_class'>NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
      <property name="use_proxy_validator">False</property>
      <mapping assembly="Ye.Nhibernate.Entity" />
    </session-factory>
  </hibernate-configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
</configuration>

　　5、使用JoinQueryOver进行连接查询

　　这个是接收数据的实体类，Name是Student表的字段，表示学生姓名，CName是Classes表的字段，表示班级名称。

public class StuCls
    {
        public string Name { get; set; }

        public string CName { get; set; }
    }

　　下面贴上核心代码

　　使用JoinQueryOver进行连接，默认是inner join，使用TransformUsing进行结果集转换。

using (ISession session = NHSessionHelper.SessionFactory.OpenSession())
            {
var query = session.QueryOver<StudentEntity>();
                ClassesEntity classes = null;
                StuCls sc = null;
                var joinQuery = query.JoinQueryOver<ClassesEntity>(p => p.Classes, () => classes);
                var list = joinQuery.SelectList(
                    l => l.Select(p => p.Name).WithAlias(() => sc.Name)
                    .Select(() => classes.ClassesName).WithAlias(() => sc.CName)
                    ).TransformUsing(Transformers.AliasToBean<StuCls>()).List<StuCls>();
｝

　　6、使用ICriteria进行exists查询

　　语句含义是，查询出班级名称为“一年1班”的学生信息，由于Student.hbm.xml配置了多对一关系，会自动带出来班级数据。

 using (ISession session = NHSessionHelper.SessionFactory.OpenSession())
            {
DetachedCriteria dCriteria = DetachedCriteria.For<ClassesEntity>("cls")
                    .SetProjection(Projections.Property("cls.Id"))
                    .Add(Expression.Eq(Projections.Property<ClassesEntity>(t => t.ClassesName), "一年1班"))
                    .Add(Expression.EqProperty("cls.Id", "stu.CId"));

                var stuList = session.CreateCriteria<StudentEntity>("stu")
                        .Add(Subqueries.Exists(dCriteria)).List<StudentEntity>();
｝

　　利用SQL Server Profiler进行监测，发现生成的SQL语句如下：

exec sp_executesql N'SELECT this_.Id as Id0_0_, this_.Name as Name0_0_, this_.StudentNo as StudentNo0_0_, this_.Age as Age0_0_, this_.CId as CId0_0_ FROM Student this_ WHERE exists (SELECT this_0_.Id as y0_ FROM Classes this_0_ WHERE this_0_.ClassesName = @p0 and this_0_.Id = this_.CId)',N'@p0 nvarchar(30)',@p0=N'一年1班'

　　好了，本篇在此结束，后续会继续整理一些不常见的坑。