代码之家 › 专栏 › 技术社区 › Sebastiaan van den Broek

将复杂的Postgres查询转换为用于Spring Hibernate存储库的JPQL

spring-data spring-data-jpa jpa hibernate postgresql

Sebastiaan van den Broek · 技术社区 · 6 年前

我有以下Postgres SQL查询,以便 alerts 最新的 date :

SELECT latest_alerts.subject_id,
       latest_alerts.alertconfiguration_id_id,
       latest_alerts.alert_level,
       latest_alerts.maxdate
FROM   (SELECT subject_id,
               alertconfiguration_id_id,
               alert_level,
               Max(date) AS maxdate
        FROM   alert
        WHERE  subject_id IN ( 'da157532-8de5-4c0c-8608-d924e670d5db', '63b99886-77c8-4784-b8f0-7ff5310f1272' )
               AND alertconfiguration_id_id IN (
                   '6feb6b8b-6b96-4d5d-ac58-713b3cd637a0'
                                               )
        GROUP  BY subject_id,
                  alertconfiguration_id_id,
                  alert_level) AS latest_alerts
       INNER JOIN alert
               ON alert.date = latest_alerts.maxdate
                  AND alert.subject_id = latest_alerts.subject_id
                  AND alert.alertconfiguration_id_id =
                      latest_alerts.alertconfiguration_id_id
                  AND alert.alert_level IN ( 'WARNING' )
ORDER  BY latest_alerts.maxdate DESC;

这在Hibernate生成的Postgres数据库上运行良好。注意,奇数ID_ID结构是因为嵌入了密钥。

但我正在努力将其转换为我可以在Spring引导应用程序中使用的JPA/JPQL查询。到目前为止,我有:

@Query("SELECT" +
        " latest_alerts.subject," +
        " latest_alerts.alertConfiguration," +
        " latest_alerts.alertLevel," +
        " latest_alerts.max_date" +
        "FROM" +
        " (SELECT" +
        " subject," +
        " alertConfiguration," +
        " alertLevel," +
        " MAX(date) AS max_date" +
        " FROM" +
        " alert" +
        " WHERE" +
        " subject.id IN (:subjectIds) AND alertConfiguration.id.id IN (:alertConfigurationIds)" +
        " GROUP BY" +
        " subject, alertConfiguration, alertLevel) AS latest_alerts" +
        " INNER JOIN" +
        " alert" +
        " ON" +
        " alert.date = latest_alerts.max_date" +
        " AND alert.subject = latest_alerts.subject" +
        " AND alert.alertConfiguration = latest_alerts.alertConfiguration" +
        " AND alert.alertlevel IN (:alertLevels)" +
        " ORDER BY latest_alerts.date DESC")
Page<Alert> findLatest(@Param("subjectIds") List<UUID> subjectIds,
                       @Param("alertConfigurationIds") List<UUID> alertConfigurationIds,
                       @Param("alertLevels") List<AlertLevel> alertLevels,
                       Pageable pageable);

但是Hibernate不知道如何处理这个问题,以至于它在解析这个查询时实际上抛出了一个空指针。

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract org.springframework.data.domain.Page ournamespace.sense.repository.AlertRepository.findLatest(java.util.List,java.util.List,java.util.List,org.springframework.data.domain.Pageable)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:93)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:63)
    ... many more
Caused by: java.lang.NullPointerException
    at org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4355)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:993)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3549)

知道这种查询是否可能吗?对于Hibernate抛出的nullpointer,很难看到问题出在查询的哪个部分。

1 回复 | 直到 6 年前

user10639668 6 年前

无法在休眠状态下执行此操作。来自 documentation :

注意,hql子查询只能出现在select或where中条款。

但您可以使用本机查询:

@Query(
  value = "SELECT latest_alerts.subject_id,
       latest_alerts.alertconfiguration_id_id,
       latest_alerts.alert_level,
       latest_alerts.maxdate ...
FROM ...", 
  nativeQuery = true)
Page<Alert> findLatest(@Param("subjectIds") List<UUID> subjectIds,
                       @Param("alertConfigurationIds") List<UUID> alertConfigurationIds,
                       @Param("alertLevels") List<AlertLevel> alertLevels,
                       Pageable pageable);