Spring.net下的nHibernate数据访问模板

　　好处是不少，但首先要学会配置。为了这个集成的环境，建立一个配置文件 applicationContext.xml :

Code
<?xml version="1.0" encoding="utf-8" ?>
<objects xmlns="http://www.springframework.net"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.net http://www.springframework.net/xsd/spring-objects.xsd"
>
  <!--spring集合nHibernate-->
  <object id="DbProvider" type="woodigg.DAO.SQLProvider,woodigg.DAO">
    <property name="ConnectionString"
          value="Server=(local);database=Music;User Id=sa;Password=******;Trusted_Connection=False" />
  </object>

  <!--session工厂-->
  <object id="SessionFactory"
          type="Spring.Data.NHibernate.LocalSessionFactoryObject, Spring.Data.NHibernate12">
    <property name="DbProvider" ref="DbProvider" />
    <property name="MappingAssemblies">
      <list>
        <value>woodigg.DAO</value>
        <value>woodigg.model</value>
      </list>
    </property>
    <property name="HibernateProperties">
      <dictionary>
        <entry key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
        <entry key="hibernate.dialect" value="NHibernate.Dialect.MsSql2000Dialect" />
        <entry key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
        <entry key="show_sql" value="false" />
        <entry key="hibernate.current_session_context_class" value="Spring.Data.NHibernate.SpringSessionContext, Spring.Data.NHibernate12"/>
        <entry key="hibernate.query.factory_class" value="NHibernate.Hql.Classic.ClassicQueryTranslatorFactory" />
        <entry key="hibernate.cache.provider_class" value="NHibernate.Caches.SysCache.SysCacheProvider, NHibernate.Caches.SysCache" />
        <entry key="relativeExpiration" value="5" />
      </dictionary>
    </property>
  </object>
  <!--事务管理器-->
  <object id="HibernateTransactionManager"
          type="Spring.Data.NHibernate.HibernateTransactionManager, Spring.Data.NHibernate12">
    <property name="DbProvider" ref="DbProvider" />
    <property name="SessionFactory" ref="SessionFactory" />
  </object>
  <!--事务拦截器-->
  <object id="TransactionInterceptor"
          type="Spring.Transaction.Interceptor.TransactionInterceptor, Spring.Data">
    <property name="TransactionManager" ref="HibernateTransactionManager" />
    <property name="TransactionAttributeSource">
      <object type="Spring.Transaction.Interceptor.AttributesTransactionAttributeSource, Spring.Data" />
    </property>
  </object>
  <!--HibernateTemplate-->
  <object id="HibernateTemplate"
          type="Spring.Data.NHibernate.HibernateTemplate,Spring.Data.NHibernate12">
    <property name="SessionFactory" ref="SessionFactory" />
  </object>
  <!--Dao代理模板-->
  <object id="DaoTemplate" type="woodigg.DAO.DaoTemplate, woodigg.DAO">
    <property name="SessionFactory" ref="SessionFactory" />
  </object>
  
</objects>

　　ＯＫ，这里有几处需要说明：
 　　一、woodigg.DAO.SQLProvider　是一个数据结构类，用以描述物理数据库的相关信息，诸如连接串、元数据信息等。这里其实就用到了连接串，在配置中植入位置、帐号信息等就能连接到数据源。这个SQLProvider类结构如下：

Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Spring.Data.Common;

namespace woodigg.DAO
{
    public class SQLProvider : IDbProvider
    {
        #region IDbProvider 成员
        private string _connectionString = "";
        public string ConnectionString
        {
            get
            {
                return this._connectionString;
            }
            set
            {
                this._connectionString = value;
            }
        }

        public IDbCommand CreateCommand()
        {
            return null;
        }

        public object CreateCommandBuilder()
        {
            return null;
        }

        public IDbConnection CreateConnection()
        {
            return null;
        }

        public IDbDataAdapter CreateDataAdapter()
        {
            return null;
        }

        public IDbDataParameter CreateParameter()
        {
            return null;
        }

        public string CreateParameterName(string name)
        {
            return null;
        }

        public string CreateParameterNameForCollection(string name)
        {
            return null;
        }

        public IDbMetadata DbMetadata
        {
            get
            {
                return null;
            }
        }

        public string ExtractError(Exception e)
        {
            return null;
        }

        public bool IsDataAccessException(Exception e)
        {
            return false;
        }

        #endregion
    }

}

　二、在SessionFactory配置中，指明需要环境映射的程序集名称，通俗说法是：哪些层会在集成环境中，被直接引用？这里以示例项目来说是：woodigg.DAO和woodigg.Model，分别为实体（上一节中生成的一堆.cs实体）层，和数据映射文件（被嵌入在项目中的hbm.xml文件）所在的数据访问层。
　三、HibernateProperties节中，可以指定调试时是否显示生成的sql语句。同时，能配置缓存事宜：此处用到的是NHibernate.Caches.SysCache。
　四、配置HibernateTemplate。nHibernate的模板，既nHibernate项目已经为开发者写好了一套通用的方法，能便捷的操作数据库，此处将SessionFactory植入引用即能让它工作起来。（并不是所有的复杂SQL，它都能做到，不能完成的功能，我们得自己写，这个马上会交待）。
　五、DaoTemplate，就是我自己写的一个基于以上配置的复杂模板，能完成诸如Distinct，top，调用分页存储过程等一干复杂SQL功能，抛出来做点贡献吧：

Code
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Engine;
using NHibernate.Expression;
using Spring.Dao;
using Spring.Data.NHibernate.Support;
using woodigg.model;
using log4net;

namespace woodigg.DAO
{
    #region ParamInfo结构
    public struct ParamInfo
    {
        public string Name;
        public object Value;
    }
    #endregion

    /// <summary>
    /// 继续自HibernateDaoSupport抽象类
    /// HibernateDaoSupport基类拥有HibernateTemplate
    /// </summary>
    public class DaoTemplate : HibernateDaoSupport
    {        
        /// <summary>
        /// 泛型读取
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="id"></param>
        #region T LoadFromId<T>(object id)
        public T LoadFromId<T>(object id)
        {
            try
            {                
                T obj =
                    (T)HibernateTemplate.Load(typeof(T), id);
                return obj;
            }
            catch (Exception ex)
            {
                ILog log = LogManager.GetLogger(typeof(T));
                log.Error(ex.Message, ex);
                return default(T);
            }
        }
        #endregion

        /// <summary>
        /// 泛型存储
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        #region bool Save<T>(T obj)
        public bool Save<T>(T obj)
        {
            try
            {
                HibernateTemplate.Save(obj);
                return true;
            }
            catch (DataAccessException ex)
            {
                ILog log = LogManager.GetLogger(typeof(T));
                log.Error(ex.Message, ex);
                return false;
            }
        }
        #endregion

        /// <summary>
        /// 泛型更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        #region bool Update<T>(T obj)
        public bool Update<T>(T obj)
        {
            try
            {
                HibernateTemplate.Update(obj);
                return true;
            }
            catch (DataAccessException ex)
            {
                ILog log = LogManager.GetLogger(typeof(T));
                log.Error(ex.Message, ex);
                return false;
            }
        }
        #endregion

        /// <summary>
        /// 泛型删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        #region bool Delete<T>(T obj)
        public bool Delete<T>(T obj)
        {
            try
            {
                HibernateTemplate.Delete(obj);
                return true;
            }
            catch (DataAccessException ex)
            {
                ILog log = LogManager.GetLogger(typeof(T));
                log.Error(ex.Message, ex);
                return false;
            }
        }
        #endregion

        /// <summary>
        /// 条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        #region bool Delete<T>(string where)
        public bool Delete<T>(string where)
        {
            try
            {
                string sql =string.Format("from {0} {1}",
                    typeof(T).ToString(),
                    where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);
                HibernateTemplate.Delete(sql);
                return true;
            }
            catch (DataAccessException ex)
            {
                ILog log = LogManager.GetLogger(typeof(T));
                log.Error(ex.Message, ex);
                return false;
            }
        }
        #endregion

        /// <summary>
        /// 泛型搜索
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        #region IList<T> Search<T>(string where)
        public IList<T> Search<T>(string where)
        {
            try
            {
                //有意思的模板反射哟~
                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());

                string hql = string.Format("from {0} {1}",
                    obj.GetType().ToString(),
                    where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);

                IList alist =  HibernateTemplate.Find(hql);

                IList<T> list = new List<T>();
                if (alist != null && alist.Count > 0)
                {
                    foreach (T t in alist)
                    { list.Add(t); }
                    return list;
                }
                else
                    return null;
                
            }
            catch (Exception ex)
            {
                ILog log = LogManager.GetLogger(typeof(T));
                log.Error(ex.Message, ex);
                return null;
            }
        }
        #endregion

        /// <summary>
        /// 泛型搜索 - DISTINCT
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="field">列名，用","分开，不带别名</param>
        /// <param name="where"></param>
        /// <param name="alias">别名</param>
        #region IList<T> SearchDistinct<T>(string where,string field,string alias)
        public IList<T> SearchDistinct<T>(string where, string field, string alias)
        {
            try
            {
                //有意思的模板反射哟~
                T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
                // 反射DTO对象的各字段,必须把字段和DB中字段同名
                System.Reflection.PropertyInfo[] pps = obj.GetType().GetProperties();

                //拆分成别名+列名
                string[] cols = field.Split(',');
                string columns = string.Empty;
                foreach (string col in cols)
                    columns += string.Format("{0}.{1},", alias, col);
                columns = columns.TrimEnd(',');

                //hql
                string hql = string.Format("select distinct {2} from {0} {3} {1}",
                    obj.GetType().ToString(),
                    where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where
                    , columns
                    , alias);

                IList alist = HibernateTemplate.Find(hql);

                IList<T> list = new List<T>();
                if (alist != null && alist.Count > 0)
                {
                    //是否为数组
                    bool isArray = (cols.Length == 1 ? false : true);

                    foreach (object arr in alist)
                    {
                        //产生一个类实例
                        T t = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());


                        for (int i = 0; i < cols.Length; i++)
                        {
                            //逐字段检查名称
                            foreach (System.Reflection.PropertyInfo pi in pps)
                            {
                                if(pi.Name.Equals(cols[i]))
                                {
                                    //数组与object对象
                                    pi.SetValue(t, (isArray ? (arr as object[])[i] : arr), null);
                                }
                            }
                        }                      

                        list.Add(t);
                    }

                    return list;
                }
                else
                    return null;

            }
            catch (Exception ex)
            {
                ILog log = LogManager.GetLogger(typeof(T));
                log.Error(ex.Message, ex);
                return null;
            }
        }
        #endregion

        /// <summary>
        /// 基于表达式的排序查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="propertyName"></param>
        /// <param name="ascending"></param>
        #region IList<T> SearchWithOrder<T>(string where, string propertyName, bool ascending)
        public IList<T> SearchWithOrder<T>(string where, string propertyName, bool ascending)
        {
            try
            {
                //排序
                Order order = new Order(propertyName, ascending);
                //排序
                ICriteria ic = Session.CreateCriteria(typeof(T));
                ic.AddOrder(order);
                //表达式
                ICriterion exp = Expression.Sql(where);
                ic.Add(exp);

                return ic.List<T>();
            }
            catch (Exception ex)
            {
                ILog log = LogManager.GetLogger(typeof(T));
                log.Error(ex.Message, ex);
                return null;
            }
        }
        #endregion

        /// <summary>
        /// 执行存储过程(返回bool)
        /// </summary>
        /// <param name="spName">名称</param>
        /// <param name="paramInfos">参数表</param>
        #region bool ExecuteStoredProc2(string spName, ICollection paramInfos)
        public bool ExecuteStoredProc2(string spName, ICollection paramInfos)
        {
            bool result = true;

            IDbCommand cmd = Session.Connection.CreateCommand();

            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;

            // 加入参数 
            if (paramInfos != null)
            {
                foreach (ParamInfo info in paramInfos)
                {
                    IDbDataParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = info.Name; // driver.FormatNameForSql( info.Name ); 
                    parameter.Value = info.Value;
                    cmd.Parameters.Add(parameter);
                }
            }

            IDbConnection conn = Session.Connection;
            if(conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                cmd.Connection = conn;
                IDataReader rs = cmd.ExecuteReader();
                result = true;
            }
            catch (Exception ex)
            {
                ILog log = LogManager.GetLogger(typeof(DaoTemplate));
                log.Error(ex.Message, ex);
                result = false;
            }
            finally
            {
                Session.Connection.Close();
            }

            return result;
        }
        #endregion

        /// <summary>
        /// 执行存储过程(返回ILIST)
        /// </summary>
        /// <param name="spName">名称</param>
        /// <param name="paramInfos">参数表</param>
        #region IList ExecuteStoredProc(string spName, ICollection paramInfos)
        public IList ExecuteStoredProc(string spName, ICollection paramInfos)
        {
            IList result = new ArrayList();

            IDbCommand cmd = Session.Connection.CreateCommand();                

            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;            

            // 加入参数 
            if (paramInfos != null)
            {
                foreach (ParamInfo info in paramInfos)
                {
                    IDbDataParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = info.Name; // driver.FormatNameForSql( info.Name ); 
                    parameter.Value = info.Value;
                    cmd.Parameters.Add(parameter);
                }
            }

            IDbConnection conn = Session.Connection;
            conn.Open();
            try
            {
                cmd.Connection = conn;
                IDataReader rs = cmd.ExecuteReader();

                while (rs.Read())
                {
                    int fieldCount = rs.FieldCount;
                    object[] values = new Object[fieldCount];
                    for (int i = 0; i < fieldCount; i++)
                        values[i] = rs.GetValue(i);
                    result.Add(values);
                }
            }
            finally
            {
                Session.Connection.Close();
            }

            return result;
        }
        #endregion

        /// <summary>
        /// 获取记录数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        #region  int GetRecordCount<T>(string where)
        public int GetRecordCount<T>(string where)
        {
            return GetRecordCount<T>(where, "*");
        }
        #endregion

        /// <summary>
        /// 获取记录数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        #region  int GetRecordCount<T>(string where,string cols)
        public int GetRecordCount<T>(string where,string cols)
        {
            try
            {
                //DISTINCT统计
                bool distinct = false;
                if (cols.ToLower().StartsWith("distinct"))
                {
                    distinct = true;
                    string[] columns = cols.Replace("distinct", "").Split('