在公司工作中，因为考虑到了性能问题。 所以要重构。 而我负责对IBatis.Net 方面的。所以我遇到了一个坎儿。 就是分页问题。
因为之前用到的是IBatis.Net 自带的分页方法QueryForPaginatedList()。 但是这个分页方法是要读取放到内存中，然后筛选的。 数量少还没问题。 一旦数据量变大。 那么就会非常慢。
虽然IBatis.Net 还有另一个 分页的方法 QueryForList(string statementName, object param, int skip, int max) 可问题 还是和前一个的分页是一样的。
因为不能直接在Sql语句中修改。 而且要不影响项目中其他的功能。所以实现的流程应是 从IBatis中拦截到Sql语句，然后拼接成我想要的Sql分页语句。 再然后继续放到IBatis里面执行。
在这个问题上困扰了我4-5天了。 我一直在网上找相关资料。 要么不合适， 要么是适合Java环境的。 最后终于解决了
同样我觉得这是个很重要的问题。所以我就打算写篇文档。 希望后来者遇到这个问题时可以不用像我那么纠结。 代码如下：
using IBatisNet.Common.Utilities.Objects;
using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Configuration.Statements;
using IBatisNet.DataMapper.MappedStatements;
using IBatisNet.DataMapper.MappedStatements.PostSelectStrategy;
using IBatisNet.DataMapper.MappedStatements.ResultStrategy;
using IBatisNet.DataMapper.Scope;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace PratiseMyBatis.Common
{
public class QueryPage
{
public static IList QueryPageList(ISqlMapper sqlMap, String statementName, Object parameter, int PageIndex, int PageCount)
{
IMappedStatement statement = sqlMap.GetMappedStatement(statementName);
if (!sqlMap.IsSessionStarted)
{
sqlMap.OpenConnection();
}
RequestScope request = statement.Statement.Sql.GetRequestScope(statement, parameter, sqlMap.LocalSession);
//zhaosq 是拼接分页Sql用的
request.PreparedStatement.PreparedSql = GetPageSql(request.PreparedStatement.PreparedSql, PageIndex, PageCount);
statement.PreparedCommand.Create(request, sqlMap.LocalSession, statement.Statement, parameter);
return RunQueryForList(request, sqlMap.LocalSession, parameter, statement.Statement);
}
//拼接分页Sql
public static string GetPageSql(string PreparedSql, int PageIndex, int PageCount)
{
string sql = PreparedSql.Replace("select", "SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,");
int SqlNum = PageCount * (PageIndex - 1);
sql = string.Format("SELECT TOP " + PageCount + " * FROM ({0}) T WHERE RowNumber >{1}", sql, SqlNum);
return sql;
}
public static IList<T> QueryPageList<T>(ISqlMapper sqlMap, String statementName, Object parameter, int PageIndex, int PageCount)
{
IMappedStatement statement = sqlMap.GetMappedStatement(statementName);
if (!sqlMap.IsSessionStarted)
{
sqlMap.OpenConnection();
}
RequestScope request = statement.Statement.Sql.GetRequestScope(statement, new Hashtable(), sqlMap.LocalSession);
request.PreparedStatement.PreparedSql = GetPageSql(request.PreparedStatement.PreparedSql, PageIndex, PageCount);
statement.PreparedCommand.Create(request, sqlMap.LocalSession, statement.Statement, parameter);
return (List<T>)RunQueryForList<T>(request, sqlMap.LocalSession, parameter, statement.Statement);
}
private static IList RunQueryForList(RequestScope request, ISqlMapSession session, object parameterObject, IStatement _statement)
{
IList list = null;
using (IDbCommand command = request.IDbCommand)
{
list = (_statement.ListClass == null) ? (new ArrayList()) : (_statement.CreateInstanceOfListClass());
IDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
object obj = ResultStrategyFactory.Get(_statement).Process(request, ref reader, null);
if (obj != BaseStrategy.SKIP)
{
list.Add(obj);
}
}
}
catch
{
throw;
}
finally
{
reader.Close();
reader.Dispose();
}
ExecutePostSelect(request);
RetrieveOutputParameters(request, session, command, parameterObject);
}
return list;
}
private static IList<T> RunQueryForList<T>(RequestScope request, ISqlMapSession session, object parameterObject, IStatement _statement)
{
IList<T> list = new List<T>();
using (IDbCommand command = request.IDbCommand)
{
list = (_statement.ListClass == null) ? (new List<T>()) : (_statement.CreateInstanceOfGenericListClass<T>());
IDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
object obj = ResultStrategyFactory.Get(_statement).Process(request, ref reader, null);
if (obj != BaseStrategy.SKIP)
{
list.Add((T)obj);
}
}
}
catch
{
throw;
}
finally
{
reader.Close();
reader.Dispose();
}
ExecutePostSelect(request);
RetrieveOutputParameters(request, session, command, parameterObject);
}
return list;
}
private static void ExecutePostSelect(RequestScope request)
{
while (request.QueueSelect.Count > 0)
{
PostBindind postSelect = request.QueueSelect.Dequeue() as PostBindind;
PostSelectStrategyFactory.Get(postSelect.Method).Execute(postSelect, request);
}
}
private static void RetrieveOutputParameters(RequestScope request, ISqlMapSession session, IDbCommand command, object result)
{
if (request.ParameterMap != null)
{
int count = request.ParameterMap.PropertiesList.Count;
for (int i = 0; i < count; i++)
{
IBatisNet.DataMapper.Configuration.ParameterMapping.ParameterProperty mapping = request.ParameterMap.GetProperty(i);
if (mapping.Direction == ParameterDirection.Output ||
mapping.Direction == ParameterDirection.InputOutput)
{
string parameterName = string.Empty;
if (session.DataSource.DbProvider.UseParameterPrefixInParameter == false)
{
parameterName = mapping.ColumnName;
}
else
{
parameterName = session.DataSource.DbProvider.ParameterPrefix +
mapping.ColumnName;
}
if (mapping.TypeHandler == null) // Find the TypeHandler
{
lock (mapping)
{
if (mapping.TypeHandler == null)
{
Type propertyType = ObjectProbe.GetMemberTypeForGetter(result, mapping.PropertyName);
mapping.TypeHandler = request.DataExchangeFactory.TypeHandlerFactory.GetTypeHandler(propertyType);
}
}
}
// Fix IBATISNET-239
//"Normalize" System.DBNull parameters
IDataParameter dataParameter = (IDataParameter)command.Parameters[parameterName];
object dbValue = dataParameter.Value;
object value = null;
bool wasNull = (dbValue == DBNull.Value);
if (wasNull)
{
if (mapping.HasNullValue)
{
value = mapping.TypeHandler.ValueOf(mapping.GetAccessor.MemberType, mapping.NullValue);
}
else
{
value = mapping.TypeHandler.NullValue;
}
}
else
{
value = mapping.TypeHandler.GetDataBaseValue(dataParameter.Value, result.GetType());
}
request.IsRowDataFound = request.IsRowDataFound || (value != null);
request.ParameterMap.SetOutputParameter(ref result, mapping, value);
}
}
}
}
}
}其中，GetPageSql()方法 是用来拼接分页Sql语句的。 我这里用到的是Sqlserver 数据库。 你们可以写个接口， 然后针对不同的数据库。
这里也就用到了 IBatisNet.DataMapper.dll 和IBatisNet.Common.dll 程序集 ,版本是:1.6.2.0