【C#】NHibernate下实现SQL2000分页(SQL篇)
拜读《NHibernate 2.0.1 下实现SQL2000真分页》http://www.cnblogs.com/unfeelin/archive/2009/03/15/MsSql2000Dialect.html，觉得作者分页的条件比较苛刻，且使用临时表方法，多线程容易出现问题。
根据我的《SQL Server 2000的分页方法(SQL篇)》http://www.cnblogs.com/litou/articles/1678043.html，使用方法3扩展NHibernate下SQL2000的分页方法，希望抛砖引肉举一反三。使用其他方法类似处理。
本人使用NHibernate 2.1.0 beta
using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using NHibernate.Dialect;
using NHibernate.SqlCommand;
using NHibernate.Util;
namespace Extends.NHibernate
{
/// <summary>
/// 对NHibernate的SqlServer2000分页扩展
/// </summary>
public class PagingMsSql2000Dialect : MsSql2000Dialect
{
/// <summary>
/// 启用分页支持
/// </summary>
public override bool SupportsLimitOffset
{
get
{
return true;
}
}
/// <summary>
/// 返回带分页功能语句
/// </summary>
/// <param name="querySqlString"></param>
/// <param name="offset"></param>
/// <param name="limit"></param>
/// <returns></returns>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
if (offset == 0)
{
return base.GetLimitString(querySqlString, offset, limit);
}
//检查order by语句
int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by ");
if (orderIndex == -1)
{
return base.GetLimitString(querySqlString, offset, limit);
}
//获取select语句部分的字段名和别名
int fromIndex = GetFromIndex(querySqlString);
SqlString select = querySqlString.Substring(0, fromIndex);
List<SqlString> columnsOrAliases; //别名列表
Dictionary<SqlString, SqlString> columnToAlias; //字段名对应别名词典，键为字段，值为别名
ExtractColumnOrAliasNames(select, out columnsOrAliases, out columnToAlias);
//获取order by表达式
SqlString orderBy = querySqlString.Substring(orderIndex).Trim();
SqlString[] sortExpressions = orderBy.Substring(9).Split(",");
int pageSize = limit - offset;
int selectInsertPoint = GetAfterSelectInsertPoint(querySqlString);
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
pagingBuilder.Add("select * from (select top " + pageSize + " * from (");
pagingBuilder.Add(querySqlString.Insert(selectInsertPoint, " top " + limit));
pagingBuilder.Add(") as __page_second_filter order by " + StringHelper.Join(",", FitSortExpressions(sortExpressions, columnToAlias, true)));
pagingBuilder.Add(") as __page_first_filter order by " + StringHelper.Join(",", FitSortExpressions(sortExpressions, columnToAlias, false)));
return pagingBuilder.ToSqlString();
}
/// <summary>
/// 转换排序字段为别名，且按需调转排序方向
/// </summary>
/// <param name="sortExpressions"></param>
/// <param name="columnToAlias"></param>
/// <param name="switchOrderDirection"></param>
/// <returns></returns>
private static SqlString[] FitSortExpressions(SqlString[] sortExpressions, Dictionary<SqlString, SqlString> columnToAlias, bool switchOrderDirection)
{
SqlString[] result = new SqlString[sortExpressions.Length];
for (int i = 0; i < sortExpressions.Length; i++)
{
SqlString sortExpression = RemoveSortOrderDirection(sortExpressions[i]);
if (columnToAlias.ContainsKey(sortExpression))
{
sortExpression = columnToAlias[sortExpression];
}
if (sortExpressions[i].Trim().EndsWithCaseInsensitive("desc"))
{
result[i] = new SqlString(sortExpression + (switchOrderDirection ? " ASC" : " DESC"));
}
else
{
result[i] = new SqlString(sortExpression + (switchOrderDirection ? " DESC" : " ASC"));
}
}
return result;
}
//////////////////////////////////////////////////////////////////////////
// 以下所有方法复制自MsSql2000Dialect或MsSql2005Dialect源码
/// <summary>
/// 获取From语句位置
/// </summary>
/// <param name="querySqlString"></param>
/// <returns></returns>
private static int GetFromIndex(SqlString querySqlString)
{
string subselect = querySqlString.GetSubselectString().ToString();
int fromIndex = querySqlString.IndexOfCaseInsensitive(subselect);
if (fromIndex == -1)
{
fromIndex = querySqlString.ToString().ToLowerInvariant().IndexOf(subselect.ToLowerInvariant());
}
return fromIndex;
}
/// <summary>
/// 获取select后插入点
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private static int GetAfterSelectInsertPoint(SqlString sql)
{
if (sql.StartsWithCaseInsensitive("select distinct"))
{
return 15;
}
else if (sql.StartsWithCaseInsensitive("select"))
{
return 6;
}
return 0;
}
/// <summary>
/// 返回排序语句中的排序字段
/// </summary>
/// <param name="sortExpression"></param>
/// <returns></returns>
private static SqlString RemoveSortOrderDirection(SqlString sortExpression)
{
SqlString trimmedExpression = sortExpression.Trim();
if (trimmedExpression.EndsWithCaseInsensitive("asc"))
return trimmedExpression.Substring(0, trimmedExpression.Length - 3).Trim();
if (trimmedExpression.EndsWithCaseInsensitive("desc"))
return trimmedExpression.Substring(0, trimmedExpression.Length - 4).Trim();
return trimmedExpression.Trim();
}
/// <summary>
/// 解析出字段名和别名
/// </summary>
/// <param name="select"></param>
/// <param name="columnsOrAliases"></param>
/// <param name="columnToAlias"></param>
private static void ExtractColumnOrAliasNames(SqlString select, out List<SqlString> columnsOrAliases,
out Dictionary<SqlString, SqlString> columnToAlias)
{
columnsOrAliases = new List<SqlString>();
columnToAlias = new Dictionary<SqlString, SqlString>();
IList<string> tokens = new QuotedAndParenthesisStringTokenizer(select.ToString()).GetTokens();
int index = 0;
while (index < tokens.Count)
{
string token = tokens[index];
index += 1;
if ("select".Equals(token, StringComparison.InvariantCultureIgnoreCase))
{
continue;
}
if ("distinct".Equals(token, StringComparison.InvariantCultureIgnoreCase))
{
continue;
}
if ("," == token)
{
continue;
}
if ("from".Equals(token, StringComparison.InvariantCultureIgnoreCase))
{
break;
}
//handle composite expressions like 2 * 4 as foo
while (index < tokens.Count && "as".Equals(tokens[index], StringComparison.InvariantCultureIgnoreCase) == false
&& "," != tokens[index])
{
token = token + " " + tokens[index];
index += 1;
}
string alias = token;
bool isFunctionCallOrQuotedString = token.Contains("'") || token.Contains("(");
// this is heuristic guess, if the expression contains ' or (, it is probably
// not appropriate to just slice parts off of it
if (isFunctionCallOrQuotedString == false)
{
int dot = token.IndexOf('.');
if (dot != -1)
{
alias = token.Substring(dot + 1);
}
}
// notice! we are checking here the existence of "as" "alias", two
// tokens from the current one
if (index + 1 < tokens.Count && "as".Equals(tokens[index], StringComparison.InvariantCultureIgnoreCase))
{
alias = tokens[index + 1];
index += 2; //skip the "as" and the alias \
}
columnsOrAliases.Add(new SqlString(alias));
columnToAlias[SqlString.Parse(token)] = SqlString.Parse(alias);
}
}
/// <summary>
/// This specialized string tokenizier will break a string to tokens, taking
/// into account single quotes, parenthesis and commas and [ ]
/// Notice that we aren't differenciating between [ ) and ( ] on purpose, it would complicate
/// the code and it is not legal at any rate.
/// </summary>
public class QuotedAndParenthesisStringTokenizer : IEnumerable<String>
{
private readonly string original;
public QuotedAndParenthesisStringTokenizer(string original)
{
this.original = original;
}
IEnumerator<string> IEnumerable<string>.GetEnumerator()
{
StringBuilder currentToken = new StringBuilder();
TokenizerState state = TokenizerState.WhiteSpace;
int parenthesisCount = 0;
bool escapeQuote = false;
for (int i = 0; i < original.Length; i++)
{
char ch = original[i];
switch (state)
{
case TokenizerState.WhiteSpace:
if (ch == '\'')
{
state = TokenizerState.Quoted;
currentToken.Append(ch);
}
else if (ch == ',')
{
yield return ",";
}
else if (ch == '(' || ch == '[')
{
state = TokenizerState.InParenthesis;
currentToken.Append(ch);
parenthesisCount = 1;
}
else if (char.IsWhiteSpace(ch) == false)
{
state = TokenizerState.Token;
currentToken.Append(ch);
}
break;
case TokenizerState.Quoted:
if (escapeQuote)
{
escapeQuote = false;
currentToken.Append(ch);
}
// handle escaping of ' by using '' or \'
else if (ch == '\\' || (ch == '\'' && i + 1 < original.Length && original[i + 1] == '\''))
{
escapeQuote = true;
currentToken.Append(ch);
}
else if (ch == '\'')
{
currentToken.Append(ch);
yield return currentToken.ToString();
state = TokenizerState.WhiteSpace;
currentToken.Length = 0;
}
else
{
currentToken.Append(ch);
}
break;
case TokenizerState.InParenthesis:
if (ch == ')' || ch == ']')
{
currentToken.Append(ch);
parenthesisCount -= 1;
if (parenthesisCount == 0)
{
yield return currentToken.ToString();
currentToken.Length = 0;
state = TokenizerState.WhiteSpace;
}
}
else if (ch == '(' || ch == '[')
{
currentToken.Append(ch);
parenthesisCount += 1;
}
else
{
currentToken.Append(ch);
}
break;
case TokenizerState.Token:
if (char.IsWhiteSpace(ch))
{
yield return currentToken.ToString();
currentToken.Length = 0;
state = TokenizerState.WhiteSpace;
}
else if (ch == ',') // stop current token, and send the , as well
{
yield return currentToken.ToString();
currentToken.Length = 0;
yield return ",";
state = TokenizerState.WhiteSpace;
}
else if (ch == '(' || ch == '[')
{
state = TokenizerState.InParenthesis;
parenthesisCount = 1;
currentToken.Append(ch);
}
else if (ch == '\'')
{
state = TokenizerState.Quoted;
currentToken.Append(ch);
}
else
{
currentToken.Append(ch);
}
break;
default:
throw new InvalidExpressionException("Could not understand the string " + original);
}
}
if (currentToken.Length > 0)
{
yield return currentToken.ToString();
}
}
public IEnumerator GetEnumerator()
{
return ((IEnumerable<string>)this).GetEnumerator();
}
public enum TokenizerState
{
WhiteSpace,
Quoted,
InParenthesis,
Token
}
public IList<string> GetTokens()
{
return new List<string>(this);
}
}
}
}
使用的时候，如下面代码所示，其中已包括分页方法3中对最后一页的处理
/// <summary>
/// 分页获取多条数据(必须排序)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="session"></param>
/// <param name="obj"></param>
/// <param name="order"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="pk"></param>
/// <param name="count"></param>
/// <returns></returns>
protected ICriteria CreateCriteria<T>(ISession session, T obj, Order order, int start, int limit, string pk, out int count) where T : class
{
ICriteria criteria = session.CreateCriteria<T>();
if (obj != null)
criteria.Add(Example.Create(obj).ExcludeNone().ExcludeNulls());
ICriteria projCriteria = (ICriteria)criteria.Clone();
count = projCriteria.SetProjection(Projections.Count(pk)).UniqueResult<int>();
criteria.AddOrder(order);
if (start + limit <= count) //完全在范围内
{
criteria.SetFirstResult(start);
criteria.SetMaxResults(limit);
}
else
{
if (start < count) //在最后一页
{
criteria.SetFirstResult(start);
criteria.SetMaxResults(count % limit);
}
else //完全在范围外
{
criteria.SetMaxResults(0);
}
}
return criteria;
}
