上次写了HqlBuilder,实现了用一个Filter类,来描述要生成的查询.但在列表时,我们不应该把所以的东西都列出来,我们应该只列一页的数据,往往是10条就足够了.所以,在很多页面使用了Filter来查询的情况下,如果对每个函数都加上参数,来传递要第几页的几条数据时,改动会很大.
我想到,一般情况下,一个页面只有一个查询.(我的项目中如此,具体问题具体对待).这样我们可以在一个Http交互中,在Thread中保存这两个参数来实现.然后在生成查询时,应用
query.SetFirstResult(firstResult).SetMaxResults(pageSize);
来只返回我们要求的数据.
至于控件,我选择了AspNetPager 这样就要求我们每次页面变化时,都要把相应的参数传递到Thread的变量中,我的方法是写一个控件,直接继承Wuqi.AspnetPager,像下面一样:
public class AspNetPager : Wuqi.Webdiyer.AspNetPager
{
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
this.Page.Load += new EventHandler(Page_Load);
}
void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//如果页面加载,就设置一下控件的样式
this.CustomInfoHTML = "第%CurrentPageIndex%页,共%PageCount%页,每页%PageSize%条";
this.ShowCustomInfoSection = Wuqi.Webdiyer.ShowCustomInfoSection.Right;
//把参数保存到 HqlBuilderPagerParameter ,这是一个Thead相关的变量
HqlBuilderPagerParameter.Current.SetPager(Math.Max(0,this.StartRecordIndex), this.PageSize);
}
}
protected override void OnPageChanged(EventArgs e)
{
base.OnPageChanged(e);
//把参数保存到 HqlBuilderPagerParameter ,这是一个Thead相关的变量
HqlBuilderPagerParameter.Current.SetPager(this.StartRecordIndex, this.PageSize);
//参数发生变动,我们需要重新绑定
Control ctl = this.Parent;
while(!(ctl is UserControlBase))
{
ctl = ctl.Parent;
}
if (ctl is UserControlBase)
{
ctl.DataBind();
}
}
}
这样,我们就把所需要的参数,传到了HqlBuilder.最后写一个Filter基类,用来保存查询的总记录数:
public class HqlBuildFilterBase<T> : HqlBuildFilterBase
{
public override Type ClassType
{
get { return typeof(T); }
}
}
[HqlPager]
public abstract class HqlBuildFilterBase
{
public abstract Type ClassType{get;}
private long resultCount = 0;
/// <summary>
/// 记录总数
/// </summary>
public long ResultCountLong
{
get { return resultCount; }
set { resultCount = value; }
}
public int ResultCount
{
get { return (int)resultCount; }
}
}
最后,我们只要把查询的Filter类改成从 HqlBuildFilterBase<T>类继承,就可以了.
#region Search_Object
public class UserInfoFilter : HqlBuildFilterBase<UserInfo>
{
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";
}
#endregion
前台只要添加
<asp:GridView ID="gvList" runat="server"
onselectedindexchanging="gvList_SelectedIndexChanging" onrowdeleting="gvList_RowDeleting"
AutoGenerateColumns="False">
</asp:GridView>
<div class="footer">
<div class="pager">
<facade:aspnetpager id="pagerList" runat="server" HorizontalAlign="Center"
Width="100%" PageIndexBoxType="DropDownList"
PageSize="10" ></facade:aspnetpager>
</div>
</div>
后台记得要在绑定后:更新一下总记录数:
public override void DataBind()
{
this.gvList.DataSource = GetDataSource();
this.gvList.DataKeyNames = new string[] { "Id" };
//this.gvList.DataBind();
this.pagerList.RecordCount = this.filter.ResultCount;
base.DataBind();
}
最后是完整代码:
![](/Images/OutliningIndicators/ContractedBlock.gif)
完整代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Diagnostics;
using NHibernate;
using System.Collections;
using System.Threading;
namespace Grain.Components.NHibernateHelper
{
public class HqlBuildFilterBase<T> : HqlBuildFilterBase
{
public override Type ClassType
{
get { return typeof(T); }
}
}
[HqlPager]
public abstract class HqlBuildFilterBase
{
public abstract Type ClassType{get;}
private long resultCount = 0;
/// <summary>
/// 记录总数
/// </summary>
public long ResultCountLong
{
get { return resultCount; }
set { resultCount = value; }
}
public int ResultCount
{
get { return (int)resultCount; }
}
}
public class HqlBuilderPagerParameter
{
private const string dataKey = "hqlbuilder.pager";
private int startRecordIndex = 0;
public int StartRecordIndex
{
get { return startRecordIndex; }
set { startRecordIndex = value; }
}
private int pageSize = 2;
public int PageSize
{
get { return pageSize; }
set { pageSize = value; }
}
private static LocalDataStoreSlot GetSlot()
{
return Thread.GetNamedDataSlot(dataKey);
}
public static HqlBuilderPagerParameter Current
{
get
{
HqlBuilderPagerParameter current = Thread.GetData(GetSlot()) as HqlBuilderPagerParameter;
if (current == null)
{
current = new HqlBuilderPagerParameter();
Thread.SetData(GetSlot(),current);
}
return current;
}
}
public void SetPager(int startRecordIndex, int pageSize)
{
this.StartRecordIndex = startRecordIndex;
this.PageSize = pageSize;
}
public void RemovePager()
{
Thread.FreeNamedDataSlot(dataKey);
}
}
public class HqlQueryBuildParameter
{
private string name;
public string Name
{
get { return name; }
set { name = value; }
}
private MemberInfo memberInfo;
public MemberInfo MemberInfo
{
get { return memberInfo; }
set { memberInfo = value; }
}
private HqlValidateAttribute hqlAttribute;
public HqlValidateAttribute HqlAttribute
{
get { return hqlAttribute; }
set { hqlAttribute = value; }
}
private object value;
public object Value
{
get { return this.value; }
set { this.value = value; }
}
}
/// <summary>
/// 依一个Filter对象来生成IQuery
///
/// </summary>
public class HqlQueryBuilder
{
private static object GetValue(MemberInfo mi,object obj)
{
if (mi is FieldInfo)
return ((FieldInfo)mi).GetValue(obj);
else if (mi is PropertyInfo)
return ((PropertyInfo)mi).GetGetMethod().Invoke(obj, null);
else
return null;
}
public static long GetResultCount(Type type, object filter)
{
return GetResultCount(type, filter,"");
}
public static long GetResultCount(Type type, object filter, string hqlWhereEx)
{
return GetResultCount(Database.Session, type, filter, hqlWhereEx);
}
public static long GetResultCount<T>(object filter)
{
return GetResultCount<T>(Database.Session, filter);
}
public static long GetResultCount<T>(object filter, string hqlWhereEx)
{
return GetResultCount<T>(Database.Session, filter, hqlWhereEx);
}
public static long GetResultCount<T>(ISession session, object filter)
{
return GetResultCount<T>(session, filter, string.Empty);
}
public static long GetResultCount<T>(ISession session, object filter, string hqlWhereEx)
{
return GetResultCount(session, typeof(T), filter, hqlWhereEx);
}
public static long GetResultCount(ISession session, Type type, object filter, string hqlWhereEx)
{
Debug.Assert(filter != null);
Type typeFilter = filter.GetType();
string typeClassName = type.Name;
string typeClassPrefix = new string((from c in typeClassName where char.IsUpper(c) == true select c).ToArray<char>()).ToLower();
string selectString = string.Empty;
List<HqlQueryBuildParameter> prms = new List<HqlQueryBuildParameter>();
StringBuilder sb = new StringBuilder(string.Format("from {0} {1}", typeClassName, typeClassPrefix));
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(filter, fi, 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 HqlSelectAttribute)
{
selectString = (string)value;
}
else
{
continue;
}
}
}
if (!string.IsNullOrEmpty(selectString))
{
string hqlSelect = selectString.Replace(typeClassName + ".", typeClassPrefix + ".");
if (!hqlSelect.StartsWith(typeClassPrefix + "."))
hqlSelect = typeClassPrefix + "." + hqlSelect;
sb.Insert(0, string.Format("select count({0}) ", hqlSelect));
}
else
{
sb.Insert(0, string.Format("select count(*) "));
}
IQuery query = session.CreateQuery(sb.ToString());
//设置参数
foreach (HqlQueryBuildParameter prm in prms)
{
prm.HqlAttribute.SetParameterValue(query, prm);
}
return query.UniqueResult<long>();
}
public static IQuery BuildFilter<T>(object filter)
{
return BuildFilter<T>(Database.Session, filter);
}
public static IQuery BuildFilter<T>(object filter, string hqlWhereEx)
{
return BuildFilter<T>(Database.Session, filter, hqlWhereEx);
}
public static IQuery BuildFilter<T>(ISession session, object filter)
{
return BuildFilter<T>(session, filter, string.Empty);
}
public static IQuery BuildFilter<T>(ISession session, object filter, string hqlWhereEx)
{
return BuildFilter(session, typeof(T), filter, hqlWhereEx);
}
public static IQuery BuildFilter(ISession session, Type type, object filter, string hqlWhereEx)
{
Debug.Assert(filter != null);
Type typeFilter = filter.GetType();
string typeClassName = type.Name;
string typeClassPrefix = new string((from c in typeClassName where char.IsUpper(c) == true select c).ToArray<char>()).ToLower();
string orderString = string.Empty;
string selectString = string.Empty;
List<HqlQueryBuildParameter> prms = new List<HqlQueryBuildParameter>();
StringBuilder sb = new StringBuilder(string.Format("from {0} {1}", typeClassName, typeClassPrefix));
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(filter,fi,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 if (hqlAttr is HqlSelectAttribute)
{
selectString = (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);
}
if (!string.IsNullOrEmpty(selectString))
{
string hqlSelect = selectString.Replace(typeClassName + ".", typeClassPrefix + ".");
if (!hqlSelect.StartsWith(typeClassPrefix + "."))
hqlSelect = typeClassPrefix + "." + hqlSelect;
sb.Insert(0,string.Format("select {0} ", hqlSelect));
}
IQuery query = session.CreateQuery(sb.ToString());
//设置参数
foreach(HqlQueryBuildParameter prm in prms)
{
prm.HqlAttribute.SetParameterValue(query, prm);
}
if (Attribute.GetCustomAttribute(typeFilter, typeof(HqlPagerAttribute)) != null)
{
int firstResult = HqlBuilderPagerParameter.Current.StartRecordIndex - 1;
int pageSize = HqlBuilderPagerParameter.Current.PageSize;
query.SetFirstResult(firstResult).SetMaxResults(pageSize);
}
if (filter is HqlBuildFilterBase)
{
HqlBuildFilterBase filterBase = (HqlBuildFilterBase)filter;
filterBase.ResultCountLong = GetResultCount(session, type, filter, hqlWhereEx);
}
return query;
}
private static Dictionary<Type, object> _valueCache = new Dictionary<Type, object>();
private static object GetDefaultFilterObject(Type type)
{
if (_valueCache.ContainsKey(type))
return _valueCache[type];
else
{
object filterDefault = System.Activator.CreateInstance(type);
_valueCache.Add(type, filterDefault);
return filterDefault;
}
}
private static bool IsNullValue(object filter,MemberInfo mi, object value)
{
Type type = filter.GetType();
object filterDefault = GetDefaultFilterObject(type);
object valueDefault = null;
if (mi is PropertyInfo)
{
PropertyInfo pi = (PropertyInfo)mi;
valueDefault = pi.GetGetMethod().Invoke(filterDefault, null);
//如果是一个实体类,只有对象的值是NULL时才返回为空值
if (Database.Configuration.GetClassMapping(pi.PropertyType) != null)
return value == null;
}
else if(mi is FieldInfo)
{
FieldInfo fi = (FieldInfo)mi;
valueDefault = fi.GetValue(filterDefault);
//如果是一个实体类,只有对象的值是NULL时才返回为空值
if (Database.Configuration.GetClassMapping(fi.FieldType) != null)
return value == null;
}
return valueDefault.Equals(value);
}
}
/// <summary>
/// HQL生成基类
/// </summary>
public abstract class HqlAttribute : Attribute
{
}
/// <summary>
/// 说明这个Filter使用Pager类
/// </summary>
public class HqlPagerAttribute : HqlAttribute
{
}
/// <summary>
/// 生成查询时,乎略这个字段
/// </summary>
public class HqlIgoreAttribute : HqlAttribute
{
}
/// <summary>
/// 表示这个字段要生成无参数的条件
/// </summary>
public class HqlWhereAttribute : HqlAttribute
{
}
/// <summary>
/// 表示这个字段用来执行排序
/// </summary>
public class HqlOrderAttribute : HqlAttribute
{
}
/// <summary>
/// 表示这个字段用来执行查询的列
/// </summary>
public class HqlSelectAttribute : 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.ProperyB
PropertyN
/// </summary>
private string targetName = string.Empty;
public abstract string Operator { get; }
public HqlValidateAttribute()
{
}
public HqlValidateAttribute(string targetName)
{
this.targetName = targetName;
}
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.ProperyB
PropertyN的形式</param>
/// <param name="condition">条件</param>
public HqlConditionAttribute(string targetName, EnumHqlCondition condition)
: base(targetName)
{
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;
}
}
}