Search code examples
postgresqlspring-boothibernatejpa

'(', <expression>, <operator>, FETCH, GROUP, HAVING, LIMIT, OFFSET or ORDER expected, got ''7''

I'm just starting to work with Postgres and Spring Boot, and I would like to know how to solve a problem I'm facing. Specifically, I'm looking for a query that can help me find digital certificates whose deadline is within seven days or less. However, I keep encountering an error message. Could you please advise me on how to proceed?

'(', <expression>, <operator>, FETCH, GROUP, HAVING, LIMIT, OFFSET or ORDER expected, got ''7"

That's my code:

public interface CertificadoRepository extends JpaRepository<Certificado, Long>, JpaSpecificationExecutor<Endereco> {

    //Query para ver os certificados que vencem em 7 dias
    @Query(value = "SELECT c "
        + " FROM Certificate c "
        + " WHERE "
        + " c.dtValidade BETWEEN current_date() AND current_date() + INTERVAL '7' DAY")
    List<Certificado> findCertificadosVencidos();
}

enter image description here

I hope you guys can help me... and fix my code.

Solution

  • That is not HQL syntax for duration expression, that is SQL syntax.

    The correct HQL in Hibernate 6 is:

    select c 
from Certificate c
where c.dtValidade between local date and local date + 7 day

    Please see:

    https://docs.jboss.org/hibernate/orm/6.2/userguide/html_single/Hibernate_User_Guide.html#hql-duration-literals

    and:

    https://docs.jboss.org/hibernate/orm/6.2/userguide/html_single/Hibernate_User_Guide.html#hql-datetime-literals https://docs.jboss.org/hibernate/orm/6.2/userguide/html_single/Hibernate_User_Guide.html#hql-Datetime-arithmetic