JPA 如何分页 sqlserver2005的递归查询

小红帽吃香蕉 发布于 2013/08/07 16:20
阅读 1K+
收藏 0


String sql = "WITH r AS ("
+ "SELECT * FROM T_Resource where ParentID = ?1 UNION ALL "
+ "SELECT t.* FROM T_Resource t "
+ "INNER JOIN r ON t.ParentID = r.ID )SELECT * FROM r";  Query query = entityManager.createNativeQuery(sb.toString(), Resource.class);
query.setParameter(1, parentId);
query.setFirstResult(page.get...);
query.setMaxResults(page.get...);
query.getResultList();

如果是第一页 那没问题,sql会是

WITH r AS (
SELECT * FROM T_Resource where ParentID = ? UNION ALL
SELECT t.* FROM T_Resource t
INNER JOIN r ON t.ParentID = r.ID )
SELECT top(?) * FROM r

如果不是第一页,那就出问题了,sql成了
WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__FROM (
    WITH r AS (
        SELECT * FROM T_Resource where ParentID = ? UNION ALL
        SELECT t.* FROM T_Resource t
        INNER JOIN r ON t.ParentID = r.ID )
    SELECT * FROM r
) inner_query)
SELECT * FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?                
正确的sql应该是                                                                                   WITH r AS (
 SELECT * FROM T_Resource where ParentID = ? UNION ALL
 SELECT t.* FROM T_Resource t
 INNER JOIN r ON t.ParentID = r.ID),
query AS (
SELECT inner_query.*, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__
FROM (SELECT * FROM r) inner_query)
SELECT * FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?
请问怎么处理这种情况


加载中
0
hesai_vip
hesai_vip
请问该问题解决了吗?
小红帽吃香蕉
小红帽吃香蕉
没有,最后还是写原生sql
返回顶部
顶部