试图为复杂的Postgres查询编写JPQL

Question

我正在为授权服务器开发一个管理用户界面。其中一个功能是显示登录用户的列表，我们通过查询存储当前发布的刷新令牌的数据库表来实现这一点。用户可以从多个设备登录到同一个应用程序，生成多个令牌。目前的要求不是按设备细分此视图，而是如果用户已登录，则它们将显示在列表中。如果我们撤销访问权（此UI的其他要求之一），那么所有设备的刷新令牌都将被撤销。

不管怎样，最让我困惑的是这个问题。我编写这个查询是为了收回指定用户的所有令牌，但对于每个客户机，只检索最近的一个令牌。也就是说，如果给定的用户/客户端组合有5个令牌，那么只有具有最新时间戳的令牌才会返回到UI。我试图在SpringBoot/Hibernate后端完全使用JPQL来实现这一点，它与Postgres数据库通信。

我可以用几种不同的方式编写SQL。以下是两种返回相同结果的查询形式：

select r1.*
from dev.refresh_tokens r1
join (
    select r2.client_id, max(r2.timestamp) as timestamp
    from dev.refresh_tokens r2
    group by r2.client_id
) r3 on r1.client_id = r3.client_id and r1.timestamp = r3.timestamp
where r1.user_id = 1;

select r1.*
from dev.refresh_tokens r1
where r1.user_id = 1
and (r1.client_id, r1.timestamp) in (
    select r2.client_id, max(r2.timestamp) as timestamp
    from dev.refresh_tokens r2
    group by r2.client_id
);

我想出了多种查询方法的原因是因为我也在试图弄清楚如何将它翻译成JPQL。我尽可能避免在HiberNate中进行本机查询，而是依赖于与数据库无关的JPQL语法。然而，我就是不知道如何将它翻译成JPQL。

我知道原生查询和/或将过滤逻辑放入Java代码都是可选的。然而，我希望这在标准的JPQL查询中是可能的。

Answer 1

你可以用这个：

select r1
from RefreshToken r1
where r1.user.id = 1
and r1.timestamp = (select max(r2.timestamp) from RefreshToken r2 where r2.user.id = r1.user.id);

根据您的确切用例，我认为这个火焰持久实体视图在这里会派上用场。

我创建了这个库，以便在JPA模型和自定义接口或抽象类定义的模型之间进行简单的映射，比如Spring数据在类固醇上的投影。其思想是，您可以按照自己喜欢的方式定义目标结构（域模型），并通过JPQL表达式将属性（getter）映射到实体模型。

在Blaze Persistence实体视图中，用例的DTO模型可能如下所示：

@EntityView(User.class)
public interface UserDto {
    @IdMapping
    Long getId();
    String getName();
    @Limit(limit = "1", order = "timestamp DESC")
    @Mapping("tokens")
    RefreshTokenDto getLatestToken();

    @EntityView(RefreshToken.class)
    interface RefreshTokenDto {
        @IdMapping
        Long getId();
        String getToken();
    }
}

查询是将实体视图应用于查询的问题，最简单的就是按id进行查询。

UserDto a=entityViewManager。find（entityManager，UserDto.class，id）

Spring数据集成让您可以像使用Spring数据投影一样使用它：https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-数据特征

它将在后台使用横向连接查询，这在PostgreSQL上是最有效的：

select u1.id, u1.name, r1.id, r1.token
from dev.user u1
left join lateral (
    select *
    from dev.refresh_tokens r
    where r.user_id = u1.id
    order by r.timestamp desc
    limit 1
) r1
where r1.user_id = 1