CODEWITHSUNDEEP
sqlspring-bootjpajpqldateadd
Kaustav
Kaustav

Reputation: 79

DateAdd function not support in jpa query

DateAdd function is working fine as a sql query in sql-server as i want to substract some number of days from a date and i am getting result but while same i am using in jpa project, spring boot project has not started.

Below the repository class, if i comment out this below line of code, spring boot project starts as expected.

public interface domainRepository extends CrudRepository<domainTable , Long> {

    @Query("Select DATEADD(day,-(1), d.date) from  domainTable d "
           + "where d.id in (:id)")
    public Date getDate(@Param("id") Long id); 

}

How to fix this? or i do have to write a separate function instead of DATEADD?

Upvotes: 3

Views: 10815

Answers (2)

Josep M Beleta
Josep M Beleta

Reputation: 581

At least in the Hibernate (5.3.7) implementation of JPA 2.2 this JPQL query works perfectly for MySQL:

TypedQuery<Usuario> query2 = getEntityManager().createQuery("select distinct p.usuario from Peticion p"
    + " where p.deletionDate is null and p.fechaAprobacion is not null"
    + " and ?1 between subdate(p.fechaInicio, 'interval 1 microsecond') and"
    + "                adddate(p.fechaFin, 'interval 1 day')", Usuario.class);

Date dateDate = Date.from(date.atStartOfDay(ZoneId.systemDefault()).toInstant());
query2.setParameter(1, dateDate, TemporalType.DATE);

Upvotes: 0

Nour Eddine
Nour Eddine

Reputation: 151

Actually, JPA doesn't support time periods operations because not all databases support it. So you have following options:

1- Calculate date programmatically (Java side, using calendar API or Java 8 Date Time API).

2- Use native query.

Upvotes: 4

Related Questions