HqlBuilder

在使用NHibernate中，经常使用查询，又不愿意去写，就写了这个东东，依对象的属性，通过反射来生成查询。

先来看一下使用方式：

一个Filter类：

 

    public class UserInfoFilter
    {
        private string logName = null;
        private string name = null;


        [HqlCondition( EnumHqlCondition.Like)]
        public string LogName
        {
            get
            {
                if (string.IsNullOrEmpty(logName))
                    return null;
                else
                    return logName;
            }
            set
            {
                logName = value;
            }
        }

        [HqlCondition( EnumHqlCondition.Like)]
        public string Name
        {
            get
            {
                if (string.IsNullOrEmpty(name))
                    return null;
                else
                    return name;
            }
            set
            {
                name = value;
            }
        }

        [HqlCondition]
        public DicDept DicDept = null;

        [HqlCondition("UserRole.RoleInfo", EnumHqlCondition.EQ)]
        public RoleInfo RoleInfo = null;

        [HqlCondition("DicUseFlag", EnumHqlCondition.NE)]
        public DicUseFlag Deleted = DicUseFlag.CreateInstance((int)Enums.EnumUseFlag.Deleted);

        [HqlOrder]
        public string Order = "Id asc";
    }

使用查询：

 

        public static IList<UserInfo> List(UserInfoFilter filter)
        {
            Debug.Assert(filter != null);
            {
                
                IQuery query = HqlQueryBuilder.BuildFilter<UserInfo>(filter);

                return query.List<UserInfo>();
            }
        }

下面是实现代码：

 

  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using System.Reflection;
  6using System.Diagnostics;
  7using NHibernate;
  8using System.Collections;
  9
 10namespace Grain.Components.NHibernateHelper
 11{
 12
 13    public class HqlQueryBuildParameter
 14    {
 15        private string name;
 16
 17        public string Name
 18        {
 19            get { return name; }
 20            set { name = value; }
 21        }
 22
 23        private MemberInfo memberInfo;
 24
 25        public MemberInfo MemberInfo
 26        {
 27            get { return memberInfo; }
 28            set { memberInfo = value; }
 29        }
 30
 31        private HqlValidateAttribute hqlAttribute;
 32
 33        public HqlValidateAttribute HqlAttribute
 34        {
 35            get { return hqlAttribute; }
 36            set { hqlAttribute = value; }
 37        }
 38
 39
 40
 41        private object value;
 42
 43        public object Value
 44        {
 45            get { return this.value; }
 46            set { this.value = value; }
 47        }
 48
 49    }
 50
 51
 52    /**//// <summary>
 53    /// 依一个Filter对象来生成IQuery
 54    /// 
 55    /// </summary>
 56    public class HqlQueryBuilder
 57    {
 58
 59        private static object GetValue(MemberInfo mi,object obj)
 60        {
 61            if (mi is FieldInfo)
 62                return ((FieldInfo)mi).GetValue(obj);
 63            else if (mi is PropertyInfo)
 64                return ((PropertyInfo)mi).GetGetMethod().Invoke(obj, null);
 65            else
 66                return null;
 67        }
 68
 69        public static IQuery BuildFilter<T>(object filter)
 70        {
 71            return BuildFilter<T>(Database.Session, filter);
 72        }
 73
 74        public static IQuery BuildFilter<T>(object filter, string hqlWhereEx)
 75        {
 76            return BuildFilter<T>(Database.Session, filter, hqlWhereEx);
 77        }
 78
 79        public static IQuery BuildFilter<T>(ISession session, object filter)
 80        {
 81            return BuildFilter<T>(session, filter, string.Empty);
 82        }
 83
 84        public static IQuery BuildFilter<T>(ISession session, object filter, string hqlWhereEx)
 85        {
 86            return BuildFilter(session, typeof(T), filter, hqlWhereEx);
 87        }
 88
 89
 90        public static IQuery BuildFilter(ISession session, Type type, object filter, string hqlWhereEx)
 91        {
 92            Type typeFilter = filter.GetType();
 93            string typeClassName = type.Name;
 94            string typeClassPrefix = new string((from c in typeClassName where char.IsUpper(c) == true select c).ToArray<char>()).ToLower();
 95            string orderString = string.Empty;
 96            List<HqlQueryBuildParameter> prms = new List<HqlQueryBuildParameter>();
 97
 98            StringBuilder sb = new StringBuilder(string.Format("from {0} {1}", typeClassName, typeClassPrefix));
 99            MemberInfo[] fis = typeFilter.GetMembers();
            bool bAddedWhere = false;
            if (!string.IsNullOrEmpty(hqlWhereEx))
            {
                sb.AppendFormat(" where {0}", hqlWhereEx.Replace(typeClassName + ".", typeClassPrefix + "."));
                bAddedWhere = true;
            }
            foreach (MemberInfo fi in fis)
            {

                object value = GetValue(fi,filter);
                if (value == null)
                {
                    continue;
                }

                HqlAttribute hqlAttr = (HqlAttribute)Attribute.GetCustomAttribute(fi, typeof(HqlAttribute));
                if (hqlAttr != null)
                {
                    if (hqlAttr is HqlIgoreAttribute)
                    {
                        continue;
                    }
                    else if (hqlAttr is HqlValidateAttribute)
                    {
                        HqlValidateAttribute attrValidate = (HqlValidateAttribute)hqlAttr;

                        if (value is ValueType && IsNullValue(value))
                        {
                            continue;
                        }

                        if (!bAddedWhere)
                        {
                            sb.Append(" where 1=1");
                            bAddedWhere = true;
                        }
                        sb.AppendFormat(" and {0}.{1}", typeClassPrefix, attrValidate.BuildHql(fi, fi.Name));

                        prms.Add(new HqlQueryBuildParameter() { Name = fi.Name, Value = value, HqlAttribute = attrValidate, MemberInfo = fi });


                    }
                    else if (hqlAttr is HqlWhereAttribute)
                    {
                        if (!bAddedWhere)
                        {
                            sb.Append(" where 1=1");
                            bAddedWhere = true;
                        }
                        sb.AppendFormat(" and {0}", value.ToString().Replace(typeClassName + ".", typeClassPrefix + "."));
                    }
                    else if (hqlAttr is HqlOrderAttribute)
                    {
                        orderString = (string)value;
                    }
                    else
                    {
                        continue;
                    }

                }
            }

            if (!string.IsNullOrEmpty(orderString))
            {
                string hqlOrder = orderString.Replace(typeClassName + ".", typeClassPrefix + ".");
                if(!hqlOrder.StartsWith(typeClassPrefix + "."))
                    hqlOrder = typeClassPrefix + "." + hqlOrder;

                sb.AppendFormat(" order by {0}", hqlOrder);
            }

            IQuery query = session.CreateQuery(sb.ToString());


            //设置参数
            foreach(HqlQueryBuildParameter prm in prms)
            {
                prm.HqlAttribute.SetParameterValue(query, prm);
            }


            return query;

        }

        private static bool IsNullValue(object value)
        {
            if(
                (value is byte) ||
                (value is Int32) ||
                (value is Int16) ||
                (value is Int64))
            {
                if(value.Equals(0))
                    return true;

                return false;
            }
            if(value is DateTime)
            {
                if(value.Equals(DateTime.MinValue))
                    return true;

                return false;
            }

            if(value is Guid)
            {
                if(value.Equals(Guid.Empty))
                    return true;

                return false;
            }


            throw new NotImplementedException("未对此类型的默认值检查");
        }

    }

    /**//// <summary>
    /// HQL生成基类
    /// </summary>
    public abstract class HqlAttribute : Attribute
    {

    }

    /**//// <summary>
    /// 生成查询时,乎略这个字段
    /// </summary>
    public class HqlIgoreAttribute : HqlAttribute
    {
    }

    /**//// <summary>
    /// 表示这个字段要生成无参数的条件
    /// </summary>
    public class HqlWhereAttribute : HqlAttribute
    {


    }
    /**//// <summary>
    /// 表示这个字段用来执行排序
    /// </summary>
    public class HqlOrderAttribute : HqlAttribute
    {

    }

    public enum EnumHqlCondition
    {
        /**//// <summary>
        /// 等于
        /// </summary>
        EQ = 0,
        /**//// <summary>
        /// 大于等于
        /// </summary>
        GEQ = 1,
        /**//// <summary>
        /// 小于
        /// </summary>
        LT = 2,

        /**//// <summary>
        /// 不等于
        /// </summary>
        NE = 3,

        /**//// <summary>
        /// 小于等于
        /// </summary>
        LEQ = 4,

        /**//// <summary>
        /// 相当于SQL的IN运算符
        /// </summary>
        IN = 5,

        /**//// <summary>
        /// 使用Like查询,应用的字段要求是字符串,或者正确实现了ToString()方法
        /// </summary>
        Like = 6

    }



    /**//// <summary>
    /// 表示一个字段的查询
    /// </summary>
    public abstract class HqlValidateAttribute : HqlAttribute
    {
        /**//// <summary>
        /// 这个值作用的字段,可以为 PropertyA.ProperyBPropertyN
        /// </summary>
        private string targetName = string.Empty;
        private object nullValue = null;

        public object NullValue
        {
            get { return nullValue; }
            set { nullValue = value; }
        }
        public abstract string Operator { get; }


        public HqlValidateAttribute()
        {

        }
        public HqlValidateAttribute(object nullValue)
        {
            this.nullValue = nullValue;
        }


        public HqlValidateAttribute(string targetName)
        {
            this.targetName = targetName;
        }
        public HqlValidateAttribute(string targetName, object nullValue)
        {
            this.targetName = targetName;
            this.nullValue = nullValue;
        }



        public string BuildHql(MemberInfo fieldInfo, string paramName)
        {
            if (string.IsNullOrEmpty(this.targetName))
            {
                this.targetName = fieldInfo.Name;
            }

            return BuildHql(this.targetName, paramName);
        }

        public virtual void SetParameterValue(IQuery query, HqlQueryBuildParameter prm)
        {
            Type fieldType = null;
            if (prm.MemberInfo is FieldInfo)
            {
                FieldInfo fi = (FieldInfo)prm.MemberInfo;
                fieldType = fi.FieldType;
            }
            else if (prm.MemberInfo is PropertyInfo)
            {
                PropertyInfo pi = (PropertyInfo)prm.MemberInfo;
                fieldType = pi.PropertyType;
            }

            object value = prm.Value;
            string paramName = prm.Name;

            if (fieldType == typeof(byte[]))
            {
                query.SetBinary(paramName, (byte[])value);
            }
            else if (fieldType == typeof(bool))
            {
                query.SetBoolean(paramName, (bool)value);
            }
            else if (fieldType == typeof(byte))
            {
                query.SetByte(paramName, (byte)value);
            }
            else if (fieldType == typeof(char))
            {
                query.SetCharacter(paramName, (char)value);
            }
            else if (fieldType == typeof(DateTime))
            {
                query.SetDateTime(paramName, (DateTime)value);
            }
            else if (fieldType == typeof(decimal))
            {
                query.SetDecimal(paramName, (decimal)value);
            }
            else if (fieldType == typeof(double))
            {
                query.SetDouble(paramName, (double)value);
            }
            else if (fieldType == typeof(Int16))
            {
                query.SetInt16(paramName, (Int16)value);
            }
            else if (fieldType == typeof(Int32))
            {
                query.SetInt32(paramName, (Int32)value);
            }

            else if (fieldType == typeof(Int64))
            {
                query.SetInt64(paramName, (Int64)value);
            }

            else if (fieldType == typeof(float))
            {
                query.SetSingle(paramName, (float)value);
            }

            else if (fieldType == typeof(string))
            {
                query.SetString(paramName, (string)value);
            }
            else if (Database.Configuration.GetClassMapping(fieldType) != null)
            {
                query.SetEntity(paramName, value);
            }
            else
            {
                query.SetParameter(paramName, value);
            }
        }

        public virtual string BuildHql(string targetName, string paramName)
        {
            return string.Format("{0} {1} :{2}", targetName, this.Operator, paramName);
        }
    }


    /**//// <summary>
    /// 表示一个字段的查询时，要求Target的属性等于该值
    /// </summary>
    public class HqlConditionAttribute : HqlValidateAttribute
    {
        private EnumHqlCondition condition = EnumHqlCondition.EQ;
        public override string Operator
        {
            get
            {
                string oper = "=";
                switch (condition)
                {
                    case EnumHqlCondition.EQ:
                        oper = "=";
                        break;
                    case EnumHqlCondition.GEQ:
                        oper = ">=";
                        break;
                    case EnumHqlCondition.LEQ:
                        oper = "<=";
                        break;
                    case EnumHqlCondition.LT:
                        oper = "<";
                        break;
                    case EnumHqlCondition.NE:
                        oper = "!=";
                        break;
                    case EnumHqlCondition.IN:
                        oper = "in";
                        break;
                    case EnumHqlCondition.Like:
                        oper = "like";
                        break;
                    default:
                        break;

                }
                return oper;
            }
        }

        /**//// <summary>
        /// 依该字段的名称作为TargetName来进行相等条件的筛选
        /// </summary>
        public HqlConditionAttribute()
            : base()
        {

        }

        /**//// <summary>
        /// 依该字段的名称作为TargetName来筛选
        /// </summary>
        /// <param name="condition">条件</param>
        public HqlConditionAttribute(EnumHqlCondition condition)
            : base()
        {
            this.condition = condition;
        }


        /**//// <summary>
        ///  通过指定筛选属性和条件来筛选
        /// </summary>
        /// <param name="targetName">筛选属性,可以为 PropertyA.ProperyBPropertyN的形式</param>
        /// <param name="condition">条件</param>
        public HqlConditionAttribute(string targetName, EnumHqlCondition condition)
            : base(targetName)
        {
            this.condition = condition;
        }


        /**//// <summary>
        /// 依该字段的名称作为TargetName来筛选
        /// </summary>
        /// <param name="condition">条件</param>
        public HqlConditionAttribute(EnumHqlCondition condition, object nullValue)
            : base(nullValue)
        {
            this.condition = condition;
        }


        /**//// <summary>
        ///  通过指定筛选属性和条件来筛选
        /// </summary>
        /// <param name="targetName">筛选属性,可以为 PropertyA.ProperyBPropertyN的形式</param>
        /// <param name="condition">条件</param>
        public HqlConditionAttribute(string targetName, EnumHqlCondition condition, object nullValue)
            : base(targetName,nullValue)
        {
            this.condition = condition;
        }




        public override void SetParameterValue(IQuery query, HqlQueryBuildParameter prm)
        {

            switch (condition)
            {
                case EnumHqlCondition.IN:
                    query.SetParameterList(prm.Name, (IEnumerable)prm.Value);
                    break;
                case EnumHqlCondition.Like:
                    query.SetString(prm.Name, string.Format("%{0}%", (string)prm.Value));
                    break;
                default:
                    base.SetParameterValue(query, prm);
                    break;
            }
        }

        public override string BuildHql(string targetName, string paramName)
        {
            string hql = string.Empty;
            switch (condition)
            {
                case EnumHqlCondition.IN:
                    hql = string.Format("{0} {1} (:{2})", targetName, this.Operator, paramName);
                    break;
                default:
                    hql = base.BuildHql(targetName, paramName);
                    break;
            }

            return hql;
        }
    }




}