Transforming complex postgres query into JPQL for Spring Hibernate repository

Question

I have the following Postgres SQL query, in order to get alerts with the latest 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;

This runs well on the postgres database generated by Hibernate. Note the odd id_id construction is because of an embedded key.

But I'm struggling to transform this into a JPA/JPQL query that I can use in a Spring Boot application. So far I have this:

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

But Hibernate doesn't understand what to do with this to the point where it actually throws a nullpointer while parsing this query.

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)

Any idea if this kind of query is even possible? With a nullpointer thrown by Hibernate it's a bit hard to see what part of the query is the problem.

user10639668 · Accepted Answer · 2018-11-22 07:02:05Z

1

Cannot do it with Hibernate. From the documentation:

Note that HQL subqueries can occur only in the select or where clauses.

But you can use native query:

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

answered Nov 22, 2018 at 7:02

user10639668

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transforming complex postgres query into JPQL for Spring Hibernate repository

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related