实现IBatisNet的Dialect分页

Hibernate有其独有的Dialect，对不同的数据库实现sql的分页。

用过MyBatis for Java，它可以拦截SQL语句，通过Interceptor对原始的sql语句进行修改，也就是可以实现自定义Dialect，实现与Hibernate相同效果的分页Dialect。

可是最近搞了个IBatisNet+Spring.Net的框架，IBatisNet是ibatis for Java版本转移过来的。

它没有MyBatis新版本所增加的功能，而IBatisNet自带的分页是通过IDataReader实现的逻辑分页，虽说在局域网环境上，对内存和带宽的影响不是很大，但个人习惯上还是看着很恶心。

上网找了很多天的资料，并没有发现有相当的资料，也似乎没有人这样做过(或许是做了没有共享出来)。

但无意中发现有代码是可以获取到运行时的sql语句的，于是把IBatisNet源码中的实现重新搬出来，修改了一下，算是也能实现Dialect的效果。

主要代码如下：

public static IList QueryPageList(ISqlMapper sqlMap, String statementName, Object parameter, int offset, int limit)
{
    IMappedStatement statement = sqlMap.GetMappedStatement(statementName);
    if(!sqlMap.IsSessionStarted)
    {
        sqlMap.OpenConnection();
    }
    RequestScope request = statement.Statement.Sql.GetRequestScope(statement, parameter, sqlMap.LocalSession);
    request.PreparedStatement.PreparedSql = dialect.GetLimitString(request.PreparedStatement.PreparedSql, offset, limit);
    statement.PreparedCommand.Create(request, sqlMap.LocalSession, statement.Statement, parameter);
    return RunQueryForList(request, sqlMap.LocalSession, parameter, statement.Statement);
}

public static IList<T> QueryPageList<T>(ISqlMapper sqlMap, String statementName, Object parameter, int offset, int limit)
{
    IMappedStatement statement = sqlMap.GetMappedStatement(statementName);
    if(!sqlMap.IsSessionStarted)
    {
        sqlMap.OpenConnection();
    }
    RequestScope request = statement.Statement.Sql.GetRequestScope(statement, new Hashtable(), sqlMap.LocalSession);
    request.PreparedStatement.PreparedSql = dialect.GetLimitString(request.PreparedStatement.PreparedSql, offset, limit);
    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);
            }
        }
    }
}

View Code

其中的dialect.GetLimitString()，你们可以用接口的方式，定义针对不同数据库的分页sql，拼接为分页sql即可。

而ExecutePostSelect和RetrieveOutputParameters方法，则是从按源实现代码来调用的，因为无法调用其原始代码，所以自己重新实现了一部分。

最近有人说，这东西具体怎么用其实看不懂，或者直接获取例子中的svn吧，里面集成的是spring.net+ibatis.net

svn://svn.code.sf.net/p/dswork/code/DsWorkNet

项目中引用的dll位于：svn://svn.code.sf.net/p/dswork/code/LibNet

也有集成了基于.net framework 4.0的MVC4的demo

svn://svn.code.sf.net/p/dswork/code/DsDemo/DemoNet

这个项目的运行还需要同时运行web项目：svn://svn.code.sf.net/p/dswork/code/web

即同时运行两个项目，web和DemoNet，否则样式和js等会找不到路径，在IIS Express中配置一下即可，DemoNet中有说明文件

以上所有的项目，获取到本地路径后，建议全部置于同一目录下。否则自己更改dll的引用路径