Hibernate生成并发送错误查询给SQL server

Question

下面是SQL Server 2012中hibernate分离条件的SQL跟踪:

declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,N'@P0 bit,@P1 datetime,@P2 datetime,@P3 int,@P4 int',N'WITH query AS (select this_.cid as cid9_0_, this_.ms, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__gfrom as msgfrom9_0_, this_.msgto as msgto9_0_, this_.msgcc as msgcc9_0_, this_.msubject as msubject9_0_, this_.body as body9_0_, this_.createdate as createdate9_0_, this_.maildate as maildate9_0_, this_.expirydate as expirydate9_0_, this_.mailsent as mailsent9_0_, this_.remarks as remarks9_0_, this_.html as html9_0_ from [mail_queue] this_ where this_.mailsent= @P0  and (this_.maildate is null or this_.maildate<= @P1 ) and (this_.expirydate is null or this_.expirydate>= @P2 )) SELECT * FROM query WHERE __hibernate_row_nr__ >=  @P3  AND __hibernate_row_nr__ <  @P4 ',1
select @p1

查询在ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__gfrom as msgfrom9_0_处是畸形的。ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr_部分由hibernate生成，根据我传递的极限参数获取n个结果。

getHibernateTemplate().findByCriteria(criteria, 0, 250)

这是导致问题的原因(虽然它不应该是)，当我删除参数时，问题就不会发生了:

getHibernateTemplate().findByCriteria(criteria)

但是我需要设置限制，因为我不希望获取数千条记录。如有任何帮助，不胜感激。

Answer 1

这可能对您有所帮助。https://www.baeldung.com/spring-data-jpa-pagination-sorting我一直在使用PagingAndSortingRepository与SQL server(虽然不是2012)取得了巨大的成功。