NHibernate使用sql自定义函数&HQL使用sql自定义函数：NHibernate-自定义方言

前提：近日使用了NHibernate的IUserType自定义类型，用于对字段进行处理（取完数据对数据处理），由于部分情况下需要使用sql语句进行查询，则用到了mysql自定义函数，使用HQL不指明自定义函数的话会报错，无法查询数据。

NHibernate使用自定义函数配置：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Dialect;
using NHibernate.Dialect.Function;
namespace Training.Models.Util
{
    public class Enstr_MySqlDialect : NHibernate.Dialect.MySQLDialect
    {
        public Enstr_MySqlDialect()
        {
            //SQLFunctionTemplate函数第一个参数是函数的输出类型，varchar对应NHibernate.NHibernateUtil.String
            //?1代表第一个参数,?2代表第二个参数,以下函数都是只需一个参数，所以写成nc_enstr(?1)

           this.RegisterFunction("nc_enstr", new SQLFunctionTemplate(NHibernate.NHibernateUtil.String, "nc_enstr(?1)"));
           this.RegisterFunction("nc_destr", new SQLFunctionTemplate(NHibernate.NHibernateUtil.String, "nc_destr(?1)"));
           this.RegisterFunction("ch_enstr", new SQLFunctionTemplate(NHibernate.NHibernateUtil.String, "ch_enstr(?1)"));
           this.RegisterFunction("ch_destr", new SQLFunctionTemplate(NHibernate.NHibernateUtil.String, "ch_destr(?1)"));
        }
    }
}

<?xml version='1.0' encoding='utf-8'?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory name="NHibernateTest">
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
    <property name="connection.connection_string">Server=127.0.0.1;Database=test1;User ID=root;Password=123456</property>
    <property name="dialect">TestMysqlDialect.Util.Enstr_MySqlDialect,TestMysqlDialect</property>
  </session-factory>
</hibernate-configuration>

其中<property name="dialect">TestMysqlDialect.Util.Enstr_MySqlDialect,TestMysqlDialect</property>

内容为自定义方言，内容由逗号，分隔，格式为：自定义方言，程序集名称；（NHibernate配置填写自定义类型的时候基本都是这个规范“自定义类型，程序集名称”）

使用NHibernate内置方言不需要程序集名称；参考：https://www.cnblogs.com/kissdodog/archive/2013/02/21/2919873.html

例：<property name="dialect">NHibernate.Dialect.MySQLDialect</property>

 至此，配置自定义方言就配置好了，HQL中就可以使用自定义函数了。

HQL使用自定义函数

 public IList<tableModel> getTableModelList(tableModel model)
        {
            ISession iSession = null;
            try
            {
                iSession = openSession();
                string sql = "select ta from tableModel as ta where ta.email like concat('%',nc_enstr('" + model.email+ "'),'%')";
                IQuery qry = iSession.CreateQuery(sql);
                
                IList<tableModel> list = qry.List<tableModel>();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.closeSession(iSession);
            }
        }

其中nc_enstr(str)为数据库函数，tableModel 为要表的映射model，因为我这是个like，使用数据库函数会降低查询效率，所以改了一版将参数进行处理后再like；

将sql改成：string sql = "select ta from tableModel as ta where ta.email like '%" + new CharEncar.MEncry().SenMEncry(model.email, CharEncar.MEncry.encry_type.type_nc) + "%'";

（个人备忘1：使用了自定义数据类型的处理函数，详情看上一篇）（个人备忘2：CharEncar是将自定义类型做了封装，便于其他项目复制）

个人注释：（与本文无关，个人备忘。好长时间不写代码了，都不会写了(´╥ω╥`)）

HQL使用自定义函数测试代码：

 public IList<tableModel> getTableModelList(tableModel model)
        {
            ISession iSession = null;
            try
            {
                iSession = openSession();
                string sql1 = "select nc_enstr('13800138000') from tableModel as ta where ta.id=1 ";
                IQuery query1 = iSession.CreateQuery(sql1);
                var v_1 = query1.List();

                string sql2 = "select nc_destr('" + v_1[0] + "') from tableModel as ta where ta.id=1";
                query1 = iSession.CreateQuery(sql2);
                var v_2 = query1.List();

                string sql3 = "select ch_enstr('张三李四') from tableModel as ta where ta.id=1";
                query1 = iSession.CreateQuery(sql3);
                var v_3 = query1.List();

                string sql4 = "select ch_destr('" + v_3[0] + "') from tableModel as ta where ta.id=1";
                query1 = iSession.CreateQuery(sql4);
                var v_4 = query1.List();
                reuturn null;//测试代码，所以返回null
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.closeSession(iSession);
            }
        }

也可以使用sql语句进行查询，由于需要对取回的值进行处理，而且model映射字段名和变量名不一致所以使用HQL方便些。

sql查询：

  public IList<tableModel> getTableModelList(tableModel model)
        {
            ISession iSession = null;
            try
            {
                iSession = openSession(); 
                string sql = "select ta.id,ta.name,ta.email from te_table as ta where ta.email like concat('%',nc_enstr('" + model.email+"'),'%')";
                IQuery qry = iSession.CreateSQLQuery(sql);//.AddEntity("ta",typeof(tableModel));
                qry.SetResultTransformer(Transformers.AliasToBean(typeof(TableModel)));
                IList<tableModel> list = qry.List<tableModel>();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.closeSession(iSession);
            }
        }

IQuery qry = iSession.CreateSQLQuery(sql).AddEntity("ta",typeof(tableModel));//这种方式需要使用 "select * from table"方式查询，或者是查询全部字段列表“select id,name,sex，…… from table”，不然会报错。

以下是查询部分字段的方式：sql语句使用“select id,name from table”,不可使用“select * from table”；其中SetResultTransformer(Transformers.AliasToBean(typeof(tableModel)))是为指定字段做映射；（查询部分字段映射不会使用自定义type，是按照sql中的字段名称进行的映射，查询全部字段（select * 和select全部字段）会使用自定义type）

 IQuery qry = iSession.CreateSQLQuery(sql).SetResultTransformer(Transformers.AliasToBean(typeof(tableModel)));
                //IQuery qry = iSession.CreateSQLQuery(sql)
                //qry.SetResultTransformer(Transformers.AliasToBean(typeof(tableModel)));

HQL查询部分字段，需要在model的构造函数内定义查询的部分字段，且顺序要相同。

比如 "select new tableModel(id,name) from tableModel as ta" 需要在tableModel 里定义顺序相同的，

   public tableModel(int id, string name) { 
        this.id = id;  
        this.name = name;  
    }  

如果再有查询其他字段的HQL则又要定义一个不同参数的构造函数，很不便捷，还不如使用sql来的快。