Custom (raw) queries in Spring Data JPA

Question

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.

This solution may help your question. http://stackoverflow.com/questions/1945615/how-to-map-the-type-interval-in-hibernate#answer-6139581 — javasenior, Sep 11 '15 at 06:20
JPQL and SQL are two different languages. Trying to use a JPQL query in a postgresql client and vice versa doesn't make much sense. — JB Nizet, Sep 11 '15 at 06:20
have you tried to add `nativeQuery = true` to the @Query annotation? — reto, Sep 11 '15 at 06:20
I have changed the query from SQL to JPQL, you can see I am using objects that are mapped to tables.. they query is giving correct output if I DO NOT use interval. @JBNizet — Danyal Sandeelo, Sep 11 '15 at 06:22
Yes, because there's no such things as interval in JPQL. That's a SQL thing (and probably even a PostgreSQL thing). You can't expect each and every feature of SQL to exist in JPQL: they're different languages. — JB Nizet, Sep 11 '15 at 06:24
@JBNizet thanks that's helpful I had an assumption that the core functions work based on the back-end drivers used..I am using postgresql.. — Danyal Sandeelo, Sep 11 '15 at 06:29
I'd base assumptions on actual documents that define JPQL (and there are many), rather than heresay. Such as http://www.datanucleus.org/products/accessplatform_4_2/jpa/jpql.html — Neil Stockton, Sep 11 '15 at 06:43
@reto solved my issue -> nativeQuery = true , used native query — Danyal Sandeelo, Sep 11 '15 at 06:48

Danyal Sandeelo · Accepted Answer · 2015-09-15T08:45:01.097

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

Custom (raw) queries in Spring Data JPA

1 Answers1