HqlBuilder

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

先来看一下使用方式：

一个Filter类：

 

Code
    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";
    }

使用查询：

 

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

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

下面是实现代码：

 

Code
  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();
100            bool bAddedWhere = false;
101            if (!string.IsNullOrEmpty(hqlWhereEx))
102            {
103                sb.AppendFormat(" where {0}", hqlWhereEx.Replace(typeClassName + ".", typeClassPrefix + "."));
104                bAddedWhere = true;
105            }
106            foreach (MemberInfo fi in fis)
107            {
108
109                object value = GetValue(fi,filter);
110                if (value == null)
111                {
112                    continue;
113                }
114
115                HqlAttribute hqlAttr = (HqlAttribute)Attribute.GetCustomAttribute(fi, typeof(HqlAttribute));
116                if (hqlAttr != null)
117                {
118                    if (hqlAttr is HqlIgoreAttribute)
119                    {
120                        continue;
121                    }
122                    else if (hqlAttr is HqlValidateAttribute)
123                    {
124                        HqlValidateAttribute attrValidate = (HqlValidateAttribute)hqlAttr;
125
126                        if (value is ValueType && IsNullValue(value))
127                        {
128                            continue;
129                        }
130
131                        if (!bAddedWhere)
132                        {
133                            sb.Append(" where 1=1");
134                            bAddedWhere = true;
135                        }
136                        sb.AppendFormat(" and {0}.{1}", typeClassPrefix, attrValidate.BuildHql(fi, fi.Name));
137
138                        prms.Add(new HqlQueryBuildParameter() { Name = fi.Name, Value = value, HqlAttribute = attrValidate, MemberInfo = fi });
139
140
141                    }
142                    else if (hqlAttr is HqlWhereAttribute)
143                    {
144                        if (!bAddedWhere)
145                        {
146                            sb.Append(" where 1=1");
147                            bAddedWhere = true;
148                        }
149                        sb.AppendFormat(" and {0}", value.ToString().Replace(typeClassName + ".", typeClassPrefix + "."));
150                    }
151                    else if (hqlAttr is HqlOrderAttribute)
152                    {
153                        orderString = (string)value;
154                    }
155                    else
156                    {
157                        continue;
158                    }
159
160                }
161            }
162
163            if (!string.IsNullOrEmpty(orderString))
164            {
165                string hqlOrder = orderString.Replace(typeClassName + ".", typeClassPrefix + ".");
166                if(!hqlOrder.StartsWith(typeClassPrefix + "."))
167                    hqlOrder = typeClassPrefix + "." + hqlOrder;
168
169                sb.AppendFormat(" order by {0}", hqlOrder);
170            }
171
172            IQuery query = session.CreateQuery(sb.ToString());
173
174
175            //设置参数
176            foreach(HqlQueryBuildParameter prm in prms)
177            {
178                prm.HqlAttribute.SetParameterValue(query, prm);
179            }
180
181
182            return query;
183
184        }
185
186        private static bool IsNullValue(object value)
187        {
188            if(
189                (value is byte) ||
190                (value is Int32) ||
191                (value is Int16) ||
192                (value is Int64))
193            {
194                if(value.Equals(0))
195                    return true;
196
197                return false;
198            }
199            if(value is DateTime)
200            {
201                if(value.Equals(DateTime.MinValue))
202                    return true;
203
204                return false;
205            }
206
207            if(value is Guid)
208            {
209                if(value.Equals(Guid.Empty))
210                    return true;
211
212                return false;
213            }
214
215
216            throw new NotImplementedException("未对此类型的默认值检查");
217        }
218
219    }
220
221    /**//// <summary>
222    /// HQL生成基类
223    /// </summary>
224    public abstract class HqlAttribute : Attribute
225    {
226
227    }
228
229    /**//// <summary>
230    /// 生成查询时,乎略这个字段
231    /// </summary>
232    public class HqlIgoreAttribute : HqlAttribute
233    {
234    }
235
236    /**//// <summary>
237    /// 表示这个字段要生成无参数的条件
238    /// </summary>
239    public class HqlWhereAttribute : HqlAttribute
240    {
241
242
243    }
244    /**//// <summary>
245    /// 表示这个字段用来执行排序
246    /// </summary>
247    public class HqlOrderAttribute : HqlAttribute
248    {
249
250    }
251
252    public enum EnumHqlCondition
253    {
254        /**//// <summary>
255        /// 等于
256        /// </summary>
257        EQ = 0,
258        /**//// <summary>
259        /// 大于等于
260        /// </summary>
261        GEQ = 1,
262        /**//// <summary>
263        /// 小于
264        /// </summary>
265        LT = 2,
266
267        /**//// <summary>
268        /// 不等于
269        /// </summary>
270        NE = 3,
271
272        /**//// <summary>
273        /// 小于等于
274        /// </summary>
275        LEQ = 4,
276
277        /**//// <summary>
278        /// 相当于SQL的IN运算符
279        /// </summary>
280        IN = 5,
281
282        /**//// <summary>
283        /// 使用Like查询,应用的字段要求是字符串,或者正确实现了ToString()方法
284        /// </summary>
285        Like = 6
286
287    }
288
289
290
291    /**//// <summary>
292    /// 表示一个字段的查询
293    /// </summary>
294    public abstract class HqlValidateAttribute : HqlAttribute
295    {
296        /**//// <summary>
297        /// 这个值作用的字段,可以为 PropertyA.ProperyBPropertyN
298        /// </summary>
299        private string targetName = string.Empty;
300        private object nullValue = null;
301
302        public object NullValue
303        {
304            get { return nullValue; }
305            set { nullValue = value; }
306        }
307        public abstract string Operator { get; }
308
309
310        public HqlValidateAttribute()
311        {
312
313        }
314        public HqlValidateAttribute(object nullValue)
315        {
316            this.nullValue = nullValue;
317        }
318
319
320        public HqlValidateAttribute(string targetName)
321        {
322            this.targetName = targetName;
323        }
324        public HqlValidateAttribute(string targetName, object nullValue)
325        {
326            this.targetName = targetName;
327            this.nullValue = nullValue;
328        }
329
330
331
332        public string BuildHql(MemberInfo fieldInfo, string paramName)
333        {
334            if (string.IsNullOrEmpty(this.targetName))
335            {
336                this.targetName = fieldInfo.Name;
337            }
338
339            return BuildHql(this.targetName, paramName);
340        }
341
342        public virtual void SetParameterValue(IQuery query, HqlQueryBuildParameter prm)
343        {
344            Type fieldType = null;
345            if (prm.MemberInfo is FieldInfo)
346            {
347                FieldInfo fi = (FieldInfo)prm.MemberInfo;
348                fieldType = fi.FieldType;
349            }
350            else if (prm.MemberInfo is PropertyInfo)
351            {
352                PropertyInfo pi = (PropertyInfo)prm.MemberInfo;
353                fieldType = pi.PropertyType;
354            }
355
356            object value = prm.Value;
357            string paramName = prm.Name;
358
359            if (fieldType == typeof(byte[]))
360            {
361                query.SetBinary(paramName, (byte[])value);
362            }
363            else if (fieldType == typeof(bool))
364            {
365                query.SetBoolean(paramName, (bool)value);
366            }
367            else if (fieldType == typeof(byte))
368            {
369                query.SetByte(paramName, (byte)value);
370            }
371            else if (fieldType == typeof(char))
372            {
373                query.SetCharacter(paramName, (char)value);
374            }
375            else if (fieldType == typeof(DateTime))
376            {
377                query.SetDateTime(paramName, (DateTime)value);
378            }
379            else if (fieldType == typeof(decimal))
380            {
381                query.SetDecimal(paramName, (decimal)value);
382            }
383            else if (fieldType == typeof(double))
384            {
385                query.SetDouble(paramName, (double)value);
386            }
387            else if (fieldType == typeof(Int16))
388            {
389                query.SetInt16(paramName, (Int16)value);
390            }
391            else if (fieldType == typeof(Int32))
392            {
393                query.SetInt32(paramName, (Int32)value);
394            }
395
396            else if (fieldType == typeof(Int64))
397            {
398                query.SetInt64(paramName, (Int64)value);
399            }
400
401            else if (fieldType == typeof(float))
402            {
403                query.SetSingle(paramName, (float)value);
404            }
405
406            else if (fieldType == typeof(string))
407            {
408                query.SetString(paramName, (string)value);
409            }
410            else if (Database.Configuration.GetClassMapping(fieldType) != null)
411            {
412                query.SetEntity(paramName, value);
413            }
414            else
415            {
416                query.SetParameter(paramName, value);
417            }
418        }
419
420        public virtual string BuildHql(string targetName, string paramName)
421        {
422            return string.Format("{0} {1} :{2}", targetName, this.Operator, paramName);
423        }
424    }
425
426
427    /**//// <summary>
428    /// 表示一个字段的查询时，要求Target的属性等于该值
429    /// </summary>
430    public class HqlConditionAttribute : HqlValidateAttribute
431    {
432        private EnumHqlCondition condition = EnumHqlCondition.EQ;
433        public override string Operator
434        {
435            get
436            {
437                string oper = "=";
438                switch (condition)
439                {
440                    case EnumHqlCondition.EQ:
441                        oper = "=";
442                        break;
443                    case EnumHqlCondition.GEQ:
444                        oper = ">=";
445                        break;
446                    case EnumHqlCondition.LEQ:
447                        oper = "<=";
448                        break;
449                    case EnumHqlCondition.LT:
450                        oper = "<";
451                        break;
452                    case EnumHqlCondition.NE:
453                        oper = "!=";
454                        break;
455                    case EnumHqlCondition.IN:
456                        oper = "in";
457                        break;
458                    case EnumHqlCondition.Like:
459                        oper = "like";
460                        break;
461                    default:
462                        break;
463
464                }
465                return oper;
466            }
467        }
468
469        /**//// <summary>
470        /// 依该字段的名称作为TargetName来进行相等条件的筛选
471        /// </summary>
472        public HqlConditionAttribute()
473            : base()
474        {
475
476        }
477
478        /**//// <summary>
479        /// 依该字段的名称作为TargetName来筛选
480        /// </summary>
481        /// <param name="condition">条件</param>
482        public HqlConditionAttribute(EnumHqlCondition condition)
483            : base()
484        {
485            this.condition = condition;
486        }
487
488
489        /**//// <summary>
490        ///  通过指定筛选属性和条件来筛选
491        /// </summary>
492        /// <param name="targetName">筛选属性,可以为 PropertyA.ProperyBPropertyN的形式</param>
493        /// <param name="condition">条件</param>
494        public HqlConditionAttribute(string targetName, EnumHqlCondition condition)
495            : base(targetName)
496        {
497            this.condition = condition;
498        }
499
500
501        /**//// <summary>
502        /// 依该字段的名称作为TargetName来筛选
503        /// </summary>
504        /// <param name="condition">条件</param>
505        public HqlConditionAttribute(EnumHqlCondition condition, object nullValue)
506            : base(nullValue)
507        {
508            this.condition = condition;
509        }
510
511
512        /**//// <summary>
513        ///  通过指定筛选属性和条件来筛选
514        /// </summary>
515        /// <param name="targetName">筛选属性,可以为 PropertyA.ProperyBPropertyN的形式</param>
516        /// <param name="condition">条件</param>
517        public HqlConditionAttribute(string targetName, EnumHqlCondition condition, object nullValue)
518            : base(targetName,nullValue)
519        {
520            this.condition = condition;
521        }
522
523
524
525
526        public override void SetParameterValue(IQuery query, HqlQueryBuildParameter prm)
527        {
528
529            switch (condition)
530            {
531                case EnumHqlCondition.IN:
532                    query.SetParameterList(prm.Name, (IEnumerable)prm.Value);
533                    break;
534                case EnumHqlCondition.Like:
535                    query.SetString(prm.Name, string.Format("%{0}%", (string)prm.Value));
536                    break;
537                default:
538                    base.SetParameterValue(query, prm);
539                    break;
540            }
541        }
542
543        public override string BuildHql(string targetName, string paramName)
544        {
545            string hql = string.Empty;
546            switch (condition)
547            {
548                case EnumHqlCondition.IN:
549                    hql = string.Format("{0} {1} (:{2})", targetName, this.Operator, paramName);
550                    break;
551                default:
552                    hql = base.BuildHql(targetName, paramName);
553                    break;
554            }
555
556            return hql;
557        }
558    }
559
560
561
562
563}
564