Блог

Я работаю над пользовательским интерфейсом администратора для сервера авторизации. Одной из функций является отображение списка тех, кто вошел в систему, и мы делаем это, запрашивая таблицу базы данных, в которой хранятся выпущенные в данный момент токены обновления. Пользователь может входить с нескольких устройств в одно и то же приложение, генерируя несколько токенов. На данный момент требования НЕ состоят в том, чтобы разбивать это представление по устройствам, вместо этого, если пользователь вообще вошел в систему, они должны быть показаны в списке. Если мы отменим доступ (одно из других требований этого пользовательского интерфейса), то у всех устройств будут отозваны токены обновления.

В любом случае, главное, что сбивает меня с толку, — это запрос. Я пишу запрос, чтобы вернуть все токены для указанного пользователя, но для каждого клиента извлекается только самый последний. т. е., если для данной комбинации пользователь / клиент имеется 5 токенов, в пользовательский интерфейс будет возвращен только тот, у которого самая последняя временная метка. Я пытаюсь сделать это полностью с помощью JPQL в моем бэкэнде SpringBoot / Hibernate, который взаимодействует с базой данных 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. Я стараюсь избегать выполнения собственных запросов в режиме гибернации, насколько это возможно, вместо этого полагаясь на синтаксис JPQL, не зависящий от базы данных. Однако я просто не могу понять, как перевести это в JPQL.

Я знаю, что собственные запросы и / или включение логики фильтра в мой Java-код — это оба варианта. Тем не менее, я надеюсь, что это возможно с помощью стандартного запроса JPQL.

Вы можете использовать это:

 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 и моделями, определяемыми пользовательским интерфейсом или абстрактным классом, что-то вроде весенних прогнозов данных на стероидах. Идея заключается в том, что вы определяете свою целевую структуру (модель домена) так, как вам нравится, и сопоставляете атрибуты (геттеры) через выражения JPQL с моделью сущности.

Модель DTO для вашего варианта использования может выглядеть следующим образом с Blaze-Persistence Entity-Views:

 @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();
    }
}
 

Запрос — это вопрос применения представления сущности к запросу, простейшим из которых является просто запрос по идентификатору.

UserDto a = entityViewManager.find(entityManager, UserDto.class, id);

Интеграция данных Spring позволяет использовать его почти как проекции данных Spring: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Он будет использовать запрос бокового соединения за кулисами, который является наиболее эффективным в 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