JPA根据有序选择计算排名 [英] JPA calculating ranking from ordered selection

问题描述

说，我有下表.

USER_POINT
  USER_ID    | BIGINT | PK
  USER_POINT | BIGINT |

"USER_POINT"可能会不断更新.
在给定USER_ID的情况下，我希望按USER_POINT排序的选择中距顶部的距离.

'USER_POINT' may be constantly updated.
With given USER_ID I want the number of distance from the top in the selection ordered by USER_POINT.

SEQ    USER_ID  USER_POINT
-----  -------  -----------
00001  232132   32423423432
00002  023944   32423423431
.....  ......   ...........
01007  000034   xxxxxxxxxxx // I want to know the 01007 with given 000034

我还没有编程，但是这里有一些我要尝试的伪代码.

I'm not programmed yet but here comes some psuedocode I'm going to try.

public int getRank(final Long userId) {
    final int pageSize = 500;
    int pageCount = 0;
    int firstResult = 0;
    for (int firstResult = 0; true; firstResult += pageSize) {
        final Query query = em.createQuery(
            "SELECT u.id FROM UserPoint u ORDER BY u.point");
        query.setFirstResult(firstResult);
        query.setMaxResults(pageSize);
        final List orders = query.getResultList();
        if (orders.isEmpty()) {
            break;
        }
        final int index = orders.indexOf(userId);
        if (index != -1) {
            return pageSize * pageCount + index;
        }
        pageCount++;
    }
    return -1;
}

我想问

Is this the right way?
Is this the only way?

谢谢.

推荐答案

JPQL不支持此类查询.如果要提高查询效率，应使用SQL.

JPQL doesn't support such queries. You should use SQL for such queries if you want to make them efficient.

在Oracle中，这样的查询看起来像

In Oracle, such a query would look like

select r from (select user_id, user_point, rownum as r 
               from user_point 
               order by user_point desc) where user_id = 34

这篇关于JPA根据有序选择计算排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！