当使用SetFirstResult(启动)和SetMaxResults(count)方法来实现分页时,我注意到生成的查询只从some_table中选择一个顶部的计数*,而不考虑start参数,或至少不在数据库级别.看来,如果我指示NHibernate执行以下查询：
var users = session.CreateCriteria<User>()
.SetFirstResult(100)
.SetMaxResults(5)
.List<User>();
105个记录将在数据库服务器和应用程序之间传输,该应用程序将照顾剥离前100个记录.对于包含很多行的表,这可能是一个问题.
我已经验证了使用SQLite数据库NHibernate利用OFFSET和LIMIT关键字在数据库级别筛选结果.我知道在sql Server 2000中没有相当于OFFSET关键字和Oracle的ROWNUM,但是有没有解决办法？ sql Server 2005/2008如何？
解决方法
Microsoft sql Server使用的sql语言的变体T-sql没有限制条款.它有一个选择顶部{…}修饰符,您看到NHibernate利用sql Server 2000.
使用sql Server 2005,Microsoft引入了Row_Number()(通过{…}命令)函数,可用于替换limit子句,您可以看到NHibernate利用sql Server 2005/2008的优势.
select c.[ID],c.[Name] from [Codes] c where c.[Key] = 'abcdef' order by c.[Order] limit 20 offset 40
select c.[ID],c.[Name]
from (
select c.[ID],c.[Name],c.[Order],[!RowNum] = Row_Number() over (order by c.[Order])
from [Codes] c
where c.[Key] = 'abcdef'
) c
where c.[!RowNum] > 40 and c.[!RowNum] <= 60
order by c.[Order]
或者使用Common Table Expressions,它可能看起来像
with
[Source] as (
select c.[ID],[!RowNum] = Row_Number() over (order by c.[Order])
from [Codes] c
where c.[Key] = 'abcdef'
)
select c.[ID],c.[Name]
from [Source] c
where c.[!RowNum] > 40 and c.[!RowNum] <= 60
order by c.[Order]
还有一种方法可以在sql Server 2000中执行此操作
select c.[ID],c.[Name]
from (
select top 20 c.[ID],c.[Order]
from (
select top 60 c.[ID],c.[Order]
from [Codes] c
where c.[Key] = 'abcdef'
order by c.[Order]
) c
order by c.[Order] desc
) c
order by c.[Order]
