[FIXED] Custom queries in Spring Data JPA

Issue

I am using custom queries in JPA, it doesn't let me use interval keyword. It gives correct output if I do not use - interval '7 days' in the query.

Exception says: Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: interval near line 1, column 214

       @Query("select d from DomainName d , DomainNameReminder dr, Reseller  r"+
        " where d.reminder.id = dr.id  "+
        " and dr.secondNotification=current_date - interval '7 days' "+
        " and r.checkParty=true "+
        " and r.id = d.invoicingParty.id ")
        public List<Object> findDomainsBySecondNotificationDate();

This query basically brings all the records that are having second notification date 7 days before today.

My interface is declared as

public interface DomainNameRepository extends JpaRepository<DomainName, Long>, 
QueryDslPredicateExecutor<DomainName> {

My query is giving correct output in pgadmin postgresql client, I am pretty surprised why can't I use keywords here.

Solution

This solved my issue.

I used nativeQuery=true and used the query that I executed in postgresql. Thought to share with others too.

@Query( nativeQuery = true, value = "select domain_name.* from domain_name, domain_name_reminder, reseller " +
        "where domain_name.reminder_id = domain_name_reminder.id " +
        "and domain_name_reminder.second_notification=current_date - interval ':totalDays days' " +
        "and reseller.myCheck=true " +
        "and reseller.id = domain_name.invoicing_party_id ")
public List<DomainName> findDomainsBySecondNotificationDate(@Param("totalDays")Integer totalDays);

Answered By - Danyal Sandeelo
Answer Checked By - Pedro (JavaFixing Volunteer)