简化NHibernate查询并分页

    最近公司在做一款OA的二次开发，是基于 J2EE 架构的 采用的 Spirng3 +Struts2+Hibernate3 UI 采用的EXTJS3。这款产品的框架设计的比较好，这几天正在研究。其中hibernate查询采用的通用方法，进行集中的查询字段收集、创建Criteria、查询数据。

    首先介绍一下实现思路。

    定义一个QueryFilter对象，然后在构造中接受一个HttpServletRequest对象，QueryFilter对HttpServletRequest中的 查询参数进行遍历，找到符合约定的参数时，对参数进行分析，并封装成hibernate的criteria对象，再交给service，service再交给dao，进由hibernate进行数据查询。

以下是大致的方法签名和调用

?
1
2
QueryFilter filter = new QueryFilter(HttpServletRequest request);
List<BookType> list = bookTypeService.getAll(filter);

过滤条件的名称必须符合如下规则：Q_firstName_S_EQ ，其中Q标识该查询参数是一个QueryFilter能识别的字符，firstName 表示待查询的属性名称（可以是外键对象的属性），S 表示为 String 类型，EQ 表示 equals ，当然 S 位置 和 EQ 位置还有其它约定的表达式。通过这种方式，在进行数据查询时，不需要进行硬编码，只需要在html代码，或者JavaScript进行 Ajax 查询时，指定符合要求的querystring 即可。

以下是extjs中查询时候的代码

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
return new Ext.Panel({
        id : 'BookTypeView',
        title : '档案类别列表',
        iconCls : 'menu-book-type',
        autoScroll : true,
        items : [new Ext.FormPanel({
            height : 35,
            frame : true,
            id : 'BookTypeSearchForm',
            layout : 'column',
            defaults : {
                xtype : 'label'
            },
            items : [{
                text : '请输入查询条件:'
            }, {
                text : '档案类别'
            }, {
                xtype : 'textfield',
                name : 'Q_typeName_S_LK'
            }, {
                xtype : 'button',
                text : '查询',
                iconCls : 'search',
                handler : function() {
 
                    search();
 
                }
            }, {
                xtype : 'button',
                text : '取消查询条件',
                iconCls : 'btn-del',
                handler : function() {
                    var searchPanel = Ext.getCmp('BookTypeSearchForm');
                    var grid = Ext.getCmp('BookTypeGrid');
                    searchPanel.items.get(2).setValue("");
                    search();
 
                }
            }]
        }), this.setup()]
    });
};

    为了使我以后更懒的工作，周末花了点时间写了一个C#&Asp.Net的实现（其实我主要是做.NET平台的，Java我真的不想做）。大致上还是采用的这款OA中实现的思路。但是发现一个问题，它的QueryFilter只能够进行与查询，也就是各个查询条件是用AND相连接的，无法进行OR查询，或者 多个条件单独与或 在 与 另外一个条件与或 ， 比如 (a = 1 and b = 2) or c = false ，所以我简单实现了 多组查询的功能。查询字符串的约定规则模仿Q_firstName_S_EQ  稍微做了一些改变 ，例如：Q_firstName_S_EQ_AND_0_1 ， 其中 Q_firstName_S_EQ 部分是完全一致的。

AND 为条件聚合方式，可以是 AND 或者 OR ，

0 必须为数字，表示查询条件的组 ， 相同的组ID将被归并成一个查询条件

1 必须为数字，表示查询条件组中成员的顺序

查询条件组 与 组成员都将按照顺序产生 HQL 语句。

可能光从字面上理解有点误会，笔者表达的也却有欠缺。

打个比方：

Q_firstName_S_LK_AND_0_1,

Q_lastName_S_LK_OR_0_2,

Q_age_I_>_AND_1_1,

其中 Q_firstName_S_LK_AND_0_1,Q_lastName_S_LK_OR_0_2 属于同一组 将产生 (firstName like :firstName and lastName like :firstName )

Q_age_I_>_AND_1_1 单独一组 将产生 (age = :age)

两组合并为一句HQL语句为(firstName like :firstName and lastName like :firstName) or (age = :age) 。

那么 and 和 or 又是按照什么规则连续的呢？

组我的规则是，同一组的查询条件的聚合类型作用于下一个查询条件，前一组的最后一个成员的聚合方式作用于下一组，又很拗口…………

对照一下

(firstName like :firstName and lastName like :firstName) or (age = :age) 。

Q_firstName_S_LK_AND_0_1,Q_lastName_S_LK_OR_0_2,Q_age_I_>_AND_1_1  ,??? 最后一个AND 呢？只要当前查询条件是最后一个，AND 或者 OR 将被忽略。很难设计啊…

以下是创建HQL的部分C#代码

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
//-----------------动态创建 where 语句开始--------------------
 
 
 
 
               string lastCT = "";
               var lastGroup = queryable.LastOrDefault();
               foreach (var item in queryable)
               {
                   var last = item.LastOrDefault();
                   hqlSb.Append(" (");
                   foreach (var _item in item)
                   {
 
                       lastCT = _item.ConnectionType;
                       hqlSb.Append(" t.");
                       hqlSb.Append(_item.CreateHqlPart());
                       if (!last.Equals(_item))
                       {
                           hqlSb.Append(" " + lastCT + " ");
                       }
                   }
 
                   hqlSb.Append(") ");
 
                   if (lastGroup.Key != item.Key)
                   {
                       hqlSb.Append(lastCT);
                   }
 
               }
 
 
               //-----------------动态创建 where 语句结束--------------------

项目结构如下，东西不多

image

具体代码如下

Filter.cs

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
/* ***********************************************
 * 作者 :tension 任何转载请务必保留此头部信息 版权所有 盗版必究
 * Email:tension1990@hotmail.com
 * QQ:1881597
 * 描述 :
 * 创建时间:2011/5/15
 * 修改历史:
 * ***********************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Criterion;
 
namespace Tension.NHibernate.Query
{
    /// <summary>
    /// 过滤条件
    /// </summary>
    public class Filter
    {
        #region 字段属性
        public string ParamName { get; set; }
        /// <summary>
        /// 条件中的字段名称
        /// </summary>
        public string PropertyName { get; set; }
 
        /// <summary>
        /// 条件中的字段值
        /// </summary>
        public object PropertyValue { get; set; }
 
        /// <summary>
        /// HQL占位作用的字段名 区别在于 弱 是关联对象查询 则去处 . 号 如 PropertyName=Group.Name 则PlaceholderPropertyName=GroupName
        /// NHibernate 在分析HQL时 若占位符存在 . 会报异常
        /// </summary>
        public string PlaceholderPropertyName { get; set; }
        /// <summary>
        /// 字段数据类型
        /// </summary>
        public string PropertyType { get; set; }
        /// <summary>
        /// 条件类型
        /// </summary>
        public string Operation { get; set; }
        /// <summary>
        /// 条件组连接类型
        /// </summary>
        public string ConnectionType { get; set; }
        /// <summary>
        /// 条件组
        /// </summary>
        public int Group { get; set; }
        /// <summary>
        /// 组成员索引
        /// </summary>
        public int Index { get; set; }
 
        private int pindex = 0;
 
        /// <summary>
        /// 参数索引位置
        /// </summary>
        public int PIndex
        {
            get { return pindex; }
            set
            {
 
                PlaceholderPropertyName = PropertyName.Replace(".", "") + value;
                pindex = value;
            }
        }
 
        /// <summary>
        /// 参数全名
        /// </summary>
        private string paramName;
 
 
        /// <summary>
        /// 不需要添加到IQuery 的参数集合
        /// </summary>
        public bool NoAdd { get; set; }
 
        /// <summary>
        /// 支持的数据类型的简写
        /// </summary>
        private string[] PropertyTypes = new string[] { "S", "D", "DEC", "FT", "I", "IS", "IB", "ST", "L", "B", "ARR" };
        /// <summary>
        /// 支持的比较运算符
        /// </summary>
        private string[] Operations = new string[] { "EQ", "LT", "<", "GT", ">", "LE", "<=", "GE", ">=", "LK", "LIKE", "LFK", "LIKE%", "RHK", "%LIKE", "NULL", "NOTNULL", "IN", "BT" };
 
        #endregion
 
        #region 构造
        /// <summary>
        /// 空构造
        /// </summary>
        public Filter() { }
 
        /// <summary>
        ///
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="paramVal"></param>
        public Filter(string paramName, object propertyValue)
        {
 
            /**
             *
             * Q_propertyName_S_EQ_AND_0_1
             *
             * 使用  _ 分割
             * 0 位置 Q 是判断字符串是否为查询字符串
             * 1 位置 Q_propertyName_S_EQ_AND_0_1 是字段名称
             * 2 位置 S 是字段类型
             *
             *    该位置的其他值有：
             *   S=string D=DateTime，DEC=decimal，FT=float,I=Int32,IS=Int16,IB=Int64,ST=short,L=Long,B=bool
             *
             * 3 位置 EQ 是条件类型 比如EQ代表等于
             * 4 位置 AND 是 AND 或者 OR 查询组的最后一个查询表达式的 AND 或者 OR 将被忽略
             * 5 位置 0 代表的是条件组
             * 6 位置 1 代表条件组中条件的顺序
             *
             *   查询组的组后一个查询成员的  4 位置 值在当前查询组中不起作用
             *   若存在下一查询组 则查询组后一个查询成员的的  4 位置 值作为与下一查询组的连接符
             *     
             *   例如
             *   Q_propertyName0_S_EQ_AND_0_1
             *   Q_propertyName1_S_EQ_AND_1_1
             *   Q_propertyName2_S_EQ_AND_2_1
             *   以上三个查询条件将被翻译为 (propertyName0 = :propertyName0) and (propertyName1 =  :propertyName1) and (propertyName2 =  :propertyName2)
             *  
             *
             *   Q_propertyName0_S_EQ_AND_0_1
             *   Q_propertyName1_S_EQ_OR_1_1
             *   Q_propertyName2_S_EQ_AND_1_2
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
             *   以上三个查询条件将被翻译为 (propertyName0 = :propertyName0) and (propertyName1 = :propertyName1 or propertyName2 = :propertyName2)
             *  
             *   Q_propertyName0_S_EQ_AND_0_1
             *   Q_propertyName1_S_EQ_OR_0_2
             *   Q_propertyName2_S_EQ_AND_1_1
             *    Q_propertyName3_S_EQ_AND_1_2
             *   以上4个查询条件将被翻译为 (propertyName0 = :propertyName0 and propertyName1 = :propertyName1) or (propertyName2= :propertyName2  and or propertyName3= :propertyName3)    
             *
             *
             *
             *
             * */
            ParamName = paramName;
 
            if (paramName.IndexOf("Q_") != 0)
            {
                throw new ArgumentNullException("查询条件[paramName]不是合法的查询语句！必须以 Q_ 开头");
            }
 
            this.paramName = paramName;
            this.PropertyValue = propertyValue;
 
            //执行解析
            Analysis();
 
        }
 
 
        #endregion
 
        #region 方法
        /// <summary>
        /// 解析
        /// </summary>
        private void Analysis()
        {
            if (paramName == null)
            {
                throw new ArgumentNullException("查询条件[paramName]不能为空！");
            }
 
            string[] param_s = paramName.Split('_');
 
            if (param_s.Length < 7)
            {
                throw new ArgumentException(string.Format("\n\n    查询条件: {0} 不符合规范,请按照 如下标准:Q_propertyName0_S_EQ_AND_0_0\n", paramName));
            }
 
 
            PropertyName = param_s[1];
            //除去关联对象查询时的 . 例如 Group.Name
            PlaceholderPropertyName = PropertyName.Replace(".", "");
            //字段类别的简写
            PropertyType = param_s[2].ToUpper();
            if (!PropertyTypes.Contains(PropertyType))
            {
                throw new ArgumentException("参数字符串中[2]位置(字段类别的简写) 必须为：" + string.Join(",", PropertyTypes));
            }
 
 
            Operation = param_s[3].ToUpper();
 
            if (!Operations.Contains(Operation))
            {
                throw new ArgumentException("参数字符串中[3]位置(支持的比较运算符) 必须为：" + string.Join(",", Operations));
            }
 
            ConnectionType = param_s[4].ToLower();
 
            if (ConnectionType != "and" && ConnectionType != "or")
            {
                throw new ArgumentException("参数字符串中[4]位置(查询条件的连接方式) 必须为 AND 或者 OR");
            }
 
            try
            {
                Group = int.Parse(param_s[5]);
 
            }
            catch (Exception)
            {
                throw new ArgumentException("参数字符串中[5]位置(代表所属查询组) 必须为整数数字");
            }
            try
            {
                Index = int.Parse(param_s[6]);
 
            }
            catch (Exception)
            {
                throw new ArgumentException("参数字符串中[6]位置(代表查询组成员排序) 必须为整数数字");
            }
 
 
 
            //处理like 是的 value
 
 
 
            if ("LK" == Operation || "LIKE" == Operation)
            {
                PropertyValue = string.Format("%{0}%", PropertyValue);
            }
            else if ("LFK" == Operation || "LIKE%" == Operation)
            {
                PropertyValue = string.Format("{0}%", PropertyValue);
            }
            else if ("RHK" == Operation || "%LIKE" == Operation)
            {
                PropertyValue = string.Format("%{0}", PropertyValue);
            }
 
            //处理 NULL 或者 NOTNULL 查询设置该filter 是否要添加参数到查询
 
            else if ("NULL" == Operation)
            {
                NoAdd = true;
            }
            else if ("NOTNULL" == Operation)
            {
                NoAdd = true;
            }
 
        }
 
 
 
        /// <summary>
        /// 产生 HQL 的 where 部分条件字符串
        /// </summary>
        /// <returns></returns>
        public string CreateHqlPart()
        {
 
            if ("LT" == Operation || "<" == Operation)
            {
                return string.Format("{0} < :{1} ", PropertyName, PlaceholderPropertyName);
            }
            else if ("GT" == Operation || ">" == Operation)
            {
                return string.Format("{0} > :{1} ", PropertyName, PlaceholderPropertyName);
            }
            else if ("LE" == Operation || "<=" == Operation)
            {
                return string.Format("{0} <= :{1} ", PropertyName, PlaceholderPropertyName);
            }
            else if ("GE" == Operation || ">=" == Operation)
            {
                return string.Format("{0} >= {1} ", PropertyName, PlaceholderPropertyName);
            }
            else if ("LK" == Operation || "LIKE" == Operation)
            {
                return string.Format("{0} like :{1} ", PropertyName, PlaceholderPropertyName);
            }
            else if ("LFK" == Operation || "LIKE%" == Operation)
            {
                return string.Format("{0} like :{1} ", PropertyName, PlaceholderPropertyName);
            }
            else if ("RHK" == Operation || "%LIKE" == Operation)
            {
                return string.Format("{0} like :{1} ", PropertyName, PlaceholderPropertyName);
            }
            else if ("NULL" == Operation)
            {
                return string.Format("{0} is null ", PropertyName);
            }
            else if ("NOTNULL" == Operation)
            {
                return string.Format("{0} is not null ", PropertyName);
            }
            else if ("IN" == Operation)
            {
                return string.Format("{0} in (:{1}) ", PropertyName, PlaceholderPropertyName);
            }
            else if ("BT" == Operation)
            {
                return string.Format("{0} between (:{1}) and (:{2})", PropertyName, PlaceholderPropertyName + "_0", PlaceholderPropertyName + "_1");
            }
            else
            {
                return string.Format("{0} = :{1} ", PropertyName, PlaceholderPropertyName);
            }
        }
 
        #endregion
    }
}

QueryFilter.cs

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using System.Collections;
 
/* ***********************************************
 * 作者 :tension 任何转载请务必保留此头部信息 版权所有 盗版必究
 * Email:tension1990@hotmail.com
 * QQ:1881597
 * 描述 :
 * 创建时间:2011/5/15
 * 修改历史:
 * ***********************************************/
namespace Tension.NHibernate.Query
{
    /// <summary>
    /// 查询过滤器
    /// </summary>
    public class QueryFilter<T> where T : class
    {
 
        #region 字段属性
        /// <summary>
        /// 数据起始位置
        /// </summary>
        public int DataStart { get; set; }
 
        /// <summary>
        /// 每页显示的条数
        /// </summary>
        public int DataLimit { get; set; }
 
        ///// <summary>
        ///// 数据总数
        ///// </summary>
        //public int DataCount { get; set; }
 
        /// <summary>
        /// 排序字段
        /// </summary>
        public string Sort { get; set; }
 
        private string dir = "asc";
        /// <summary>
        /// asc desc
        /// </summary>
        public string Dir { get { return dir; } set { dir = value; } }
 
 
        /// <summary>
        /// 过滤条件集合
        /// </summary>
        private IList<Filter> filters = new List<Filter>();
 
        /// <summary>
        /// 过滤条件缓存
        /// </summary>
        private static Dictionary<string, string> QueryHqlCache;
 
        /// <summary>
        /// 当前的HQL
        /// </summary>
        public string CurrentHQL { get; set; }
 
        /// <summary>
        /// 计算数据条数的HQL
        /// </summary>
        public string CountHQL { get; set; }
        #endregion
 
        #region 构造
        /// <summary>
        /// 静态构造
        /// </summary>
        static QueryFilter()
        {
            QueryHqlCache = new Dictionary<string, string>();
        }
        #endregion
 
        #region 方法
        /// <summary>
        /// 增加过滤条件 类型 Filter
        /// </summary>
        /// <param name="filter"></param>
        public void AddFilter(Filter filter)
        {
            if (filter.PropertyValue.ToString() != "NOTADD")
            {
                filter.PIndex = filters.Count + 1;
                filters.Add(filter);
            }
        }
 
        /// <summary>
        /// 增加过滤条件 类型 string
        /// </summary>
        /// <param name="filter"></param>
        public void AddFilter(string paramName, object obj)
        {
            AddFilter(new Filter(paramName, obj));
        }
 
 
        /// <summary>
        /// 创建查询数据条数的IQuery
        /// </summary>
        /// <param name="session"></param>
        /// <returns></returns>
        public IQuery CreateCountQuery(ISession session)
        {
            if (string.IsNullOrEmpty(CountHQL))
            {
                InitHQL();
            }
            IQuery query = session.CreateQuery(CountHQL);
 
            AddParameters(query);
 
 
            return query;
        }
 
 
 
        /// <summary>
        /// 创建IQuery对象
        /// </summary>
        /// <param name="session"></param>
        /// <returns></returns>
        public IQuery CreateQuery(ISession session)
        {
            InitHQL();
 
            if (!string.IsNullOrEmpty(Sort))
            {
                CurrentHQL += string.Format(" order by {0} {1}", Sort, Dir);
            }
 
 
            IQuery query = session.CreateQuery(CurrentHQL);
 
            if (DataLimit > 0)
            {
                query.SetMaxResults(DataLimit).
                 SetFirstResult(DataStart);
            }
 
 
 
            AddParameters(query);
 
            return query;
        }
 
 
        /// <summary>
        /// 初始化HQL 该方法同事初始化 CurrentHQL 与 CountHQL
        /// </summary>
        private void InitHQL()
        {
            CurrentHQL = BuildHql();
            CountHQL = CurrentHQL.Replace("from", "select count(*) from");
        }
 
        /// <summary>
        /// 向 IQuery 添加参数
        /// </summary>
        /// <param name="query"></param>
        private void AddParameters(IQuery query)
        {
            foreach (var item in filters)
            {
                if (!item.NoAdd && item.PropertyType == "ARR")
                    query.SetParameterList(item.PlaceholderPropertyName, item.PropertyValue as object[]);
                else if (!item.NoAdd)
                {
                    //query.SetParameter(item.PlaceholderPropertyName, item.PropertyValue);
 
 
                    if (item.Operation == "BT")
                    {
                        string[] valarr = item.PropertyValue.ToString().Split('_');
                        query.SetParameter(item.PlaceholderPropertyName + "_0", ValueConverter.Convert(item.PropertyType, valarr[0]));
                        query.SetParameter(item.PlaceholderPropertyName + "_1", ValueConverter.Convert(item.PropertyType, valarr[1]));
                    }
                    else
                    {
                        query.SetParameter(item.PlaceholderPropertyName, ValueConverter.Convert(item.PropertyType, item.PropertyValue));
                    }
                }
            }
        }
 
        /// <summary>
        /// 创建HQL
        /// </summary>
        /// <returns></returns>
        public string BuildHql()
        {
            string key = CreateQueryFilterKey();
            if (QueryHqlCache.ContainsKey(key))
            {
                return QueryHqlCache[key];
            }
 
            else
            {
                string hqlStr = "";
                var queryable = GetSortedFilterGroup();
                int groupCount = queryable.Count();
                StringBuilder hqlSb = new StringBuilder(string.Format("from {0} t", typeof(T).Name));
 
                if (groupCount != 0)
                {
                    hqlSb.Append(" where ");
                }
 
 
                //-----------------动态创建 where 语句开始--------------------
 
 
 
 
                string lastCT = "";
                var lastGroup = queryable.LastOrDefault();
                foreach (var item in queryable)
                {
                    var last = item.LastOrDefault();
                    hqlSb.Append(" (");
                    foreach (var _item in item)
                    {
 
                        lastCT = _item.ConnectionType;
                        hqlSb.Append(" t.");
                        hqlSb.Append(_item.CreateHqlPart());
                        if (!last.Equals(_item))
                        {
                            hqlSb.Append(" " + lastCT + " ");
                        }
                    }
 
                    hqlSb.Append(") ");
 
                    if (lastGroup.Key != item.Key)
                    {
                        hqlSb.Append(lastCT);
                    }
 
                }
 
 
                //-----------------动态创建 where 语句结束--------------------
 
 
 
                if (!QueryHqlCache.ContainsKey(key))
                {
                    hqlStr = hqlSb.ToString();
                    QueryHqlCache[key] = hqlStr;
                }
                return hqlStr;
            }
 
 
 
        }
 
        /// <summary>
        /// 创建当前查询的 key 用于缓存查询
        /// </summary>
        /// <returns></returns>
        private string CreateQueryFilterKey()
        {
            var queryable = GetSortedFilterGroup();
 
            StringBuilder key = new StringBuilder();
 
            foreach (var item in queryable)
            {
                foreach (var _item in item)
                {
                    key.Append(_item.ParamName);
                    key.Append("&");
                }
            }
            return key.ToString();
        }
 
        /// <summary>
        /// 获取排序并分组后的查询过滤条件
        /// </summary>
        /// <returns></returns>
        private IOrderedEnumerable<IGrouping<int, Filter>> GetSortedFilterGroup()
        {
            var queryable = filters.Select(s => s).OrderBy(d => d.Index).GroupBy(f => f.Group).OrderBy(f => f.Key);
            return queryable;
        }
 
        #endregion
 
    }
}

ValueConverter.cs

/* ***********************************************
* 作者 :tension 任何转载请务必保留此头部信息 版权所有 盗版必究
* Email:tension1990@hotmail.com
* QQ:1881597
* 描述 :
* 创建时间:2011/5/15
* 修改历史:
* ***********************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Tension.NHibernate.Query
{
    /// <summary>
    /// 值转换器
    /// </summary>
    public class ValueConverter
    {
        #region 方法
        /// <summary>
        /// 转换到对应的类型
        /// </summary>
        /// <param name="type"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static object Convert(string type, object obj)
        {
            string val = obj.ToString();
            //type : S=string D=DateTime，DEC=decimal，FT=float,I=Int32,I16=Int16,I64=Int64,ST=short,L=Long,B=bool;A=object[]
            //"S", "D", "DEC", "FT", "I", "IS", "IB", "ST", "L", "B","ARR"

            switch (type)
            {
                case "S": //string
                    return val;
                case "D": //DateTime
                    return DateTime.Parse(val);
                case "DEC"://decimal
                    return decimal.Parse(val);
                case "FT": //float
                    return float.Parse(val);
                case "I":
                    return Int32.Parse(val);
                case "IS":
                    return Int16.Parse(val);
                case "IB":
                    return Int64.Parse(val);
                case "ST":
                    return short.Parse(val);
                case "L":
                    return long.Parse(val);
                case "B":
                    if (val == "0")
                        return false;
                    if (val == "1")
                    {
                        return true;
                    }
                    return bool.Parse(val);
                case "ARR":
                    return obj as object[];
                default:
                    return type;

            }
        }
        #endregion
    }
}

WebQueryFilter.cs

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
/* ***********************************************
 * 作者 :tension 任何转载请务必保留此头部信息 版权所有 盗版必究
 * Email:tension1990@hotmail.com
 * QQ:1881597
 * 描述 :
 * 创建时间:2011/5/15
 * 修改历史:
 * ***********************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
 
namespace Tension.NHibernate.Query
{
    /// <summary>
    /// 用于web方式的查询过滤器 实现自动注入查询条件
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class WebQueryFilter<T> : QueryFilter<T> where T : class
    {
 
 
        private string sort_qs = "sort";
        private string dir_qs = "dir";
        private string start_qs = "start";
        private string limit_qs = "limit";
 
        #region 构造
        public WebQueryFilter()
            : base()
        {
 
        }
 
        /// <summary>
        /// 自动封装Request.Params 中符合条件的查询参数
        /// </summary>
        /// <param name="Request">HttpRequest</param>
        /// <param name="sort_qs">排序字段在 querystring 中的 名称 指 key </param>
        /// <param name="dir_qs">排序方式 querystring 中的 名称 指 key </param>
        /// <param name="start_qs">分页时数据起始位置 querystring 中的 名称 指 key </param>
        /// <param name="limit_qs">分页时每页的数据条数 querystring 中的 名称 指 key </param>
        public WebQueryFilter(HttpRequest Request, string sort_qs, string dir_qs, string start_qs, string limit_qs)
        {
 
        }
 
        /// <summary>
        /// 参数为 HttpRequest 自动封装Request.Params 中符合条件的查询参数
        /// </summary>
        /// <param name="Request"></param>
        public WebQueryFilter(HttpRequest Request)
        {
            foreach (var item in Request.Params.Keys)
            {
                if (item == null)
                {
                    continue;
                }
                string key = item.ToString();
                if (key.IndexOf("Q_") == 0)
                {
 
                    string[] values = Request.Params.GetValues(key);
                    if (key.Split('_')[2].ToUpper() == "ARR")
                    {
                        base.AddFilter(key, Request.Params.GetValues(key));
                    }
                    else
                    {
                        string val = Request.Params.Get(key);
                        base.AddFilter(key, val);
                    }
                }
            }
 
            string start = Request.Params.Get(start_qs) ?? "0";
            string limit = Request.Params.Get(limit_qs) ?? "max";
 
            if (limit != "max")
            {
                DataStart = int.Parse(start);
                DataLimit = int.Parse(limit);
            }
 
            string sort = Request.Params.Get(sort_qs);
            string dir = Request.Params.Get(dir_qs) ?? "asc";
            if (!string.IsNullOrEmpty(sort))
            {
                Sort = sort;
                Dir = dir;
            }
 
        }
        #endregion
    }
}

简单介绍一下各个类的功能

Filter 主要是对 一个 查询条件进行封装 ，对表达式的解析，值的转换

ValueConverter 是通过约定好的数据类型的简写进行对应的转换

QueryFilter 是将一个查询请求中 所有的 查询条件（Filter） 进行 HQL 的转换，并同时具有Nhibernate 的 IQuery 对象的创建功能，同时通过该类可以进行 多条件查询、分页、排序等操作。

WebQueryFilter 继承自 QueryFilter 主要增加了 ASP.NET 环境下 对查询结果自动封装的功能。QueryFilter 不具有此能力，适用于手动指定查询条件的情况。

通过QueryFilter，只要封装到通用的数据访问类中，大多数查询都不需要进行硬编码。

以下是我的简单的通用数据访问类

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Tension.NHibernate;
using Tension;
using Tension.NHibernate.Query;
using NHibernate;
 
namespace Service
{
    public static class Extension
    {
        public static void Query<T>(this GenericDAL<T> gd, DataPage<T> dataPage, QueryFilter<T> queryFilter)
?
1
2
3
4
5
6
7
8
9
   where T : class
{
    var session = Tension.NHibernate.NHibernateSessionFactory.GetSession();
    IQuery query = queryFilter.CreateQuery(session);
    IQuery count_query = queryFilter.CreateCountQuery(session);
    object o = count_query.UniqueResult();
    int count = int.Parse(o.ToString());
    dataPage.Result = query.List<T>();
    dataPage.PageIndex = (queryFilter.DataStart / (queryFilter.DataLimit == 0
?
1
2
3
4
5
6
7
                                                                    ? 1 : queryFilter.DataLimit)) + 1;
            dataPage.PageSize = queryFilter.DataLimit;
            dataPage.DateCount = count;
            Tension.NHibernate.NHibernateSessionFactory.CloseSession();
        }
    }
}

代码有点凌乱，以上只是扩展了GenericDAL<T> 因为这部分代码已经是封装好的DLL，所以没放源代码里。

页面上条用业务层查询数据返回结果只需如下

?
1
2
3
4
5
CustomerService Service = new CustomerService();
DataPage<Customer> datapage = new DataPage<Customer>();
var wqf = new WebQueryFilter<Customer>(Request);
Service.Query<Customer>(datapage, wqf);
Response.Write(new ExtJsGridData<Customer>()
?
1
{ success = true, totalCount = datapage.DateCount, data = datapage.Result }.ToJson());
?
1
  
?
1
  

是不是觉得很方便？

刚刚用EXTJS 做的一个 demo

image

文章最下面有全部源码以及demo 的下载地址

点我下载Demo

汤晓华 QQ 1881597 MSN tension1990@hotmail.com

2011 05 16

posted @   tandly  阅读(3770)  评论(5)  编辑  收藏  举报
编辑推荐：
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· [AI/GPT/综述] AI Agent的设计模式综述
点击右上角即可分享
微信分享提示
AI IDE Trae