Hql / jpql date arithmetic

Question

I want to write a hql or jpql query

...from x, y where x.creationDate > (y.startDate - 10 days)

Is it possible? i've seen some answers from 2009 that says to register db-specific function in hibernate dialect. Does hibernate / jpa still not support date arithmetic?

Answer 1

To do this, I make an example like this

With this, I will find project.creation_date > (task.start_date - 10 days)

And I add some data to it

INSERT INTO `tbl_project` VALUES (1,'To the moon','2021-06-12 00:00:00'),(2,'Study Java','2021-06-17 00:00:00'),(3,'Sleep all day','2021-06-27 00:00:00');

INSERT INTO `tbl_task` VALUES (1,'Buy a space ship',1,'2021-06-27 00:00:00'),(2,'Buy energy',1,'2021-06-27 00:00:00'),(3,'Buy foods',1,'2021-06-17 00:00:00'),(4,'Download IDE',2,'2021-06-27 00:00:00'),(5,'Install JDK',2,'2021-06-27 00:00:00'),(6,'Reading books',2,'2021-06-27 00:00:00'),(7,'Buy a new bed',3,'2021-06-27 00:00:00');

Here are my entities

TblTask

@Entity
@Table(name = "tbl_task", catalog = "project_task")
public class TblTask implements java.io.Serializable {
   private Integer id;
   private TblProject tblProject;
   private String name;
   private Date startedDate;
   
   public TblTask() {
   }
   
   public TblTask(TblProject tblProject) {
    this.tblProject = tblProject;
   }
   
   public TblTask(TblProject tblProject, String name, Date startedDate) {
    this.tblProject = tblProject;
    this.name = name;
    this.startedDate = startedDate;
   }
   
   @Id
   @GeneratedValue(strategy = IDENTITY)
   
   @Column(name = "id", unique = true, nullable = false)
   public Integer getId() {
    return this.id;
   }
   
   public void setId(Integer id) {
    this.id = id;
   }
   
   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = "project_id", nullable = false)
   public TblProject getTblProject() {
    return this.tblProject;
   }
   
   public void setTblProject(TblProject tblProject) {
    this.tblProject = tblProject;
   }
   
   @Column(name = "name", length = 45)
   public String getName() {
    return this.name;
   }
   
   public void setName(String name) {
    this.name = name;
   }
   
   @Temporal(TemporalType.TIMESTAMP)
   @Column(name = "started_date", length = 19)
   public Date getStartedDate() {
    return this.startedDate;
   }
   
   public void setStartedDate(Date startedDate) {
    this.startedDate = startedDate;
   }
}

TblProject

@Entity
@Table(name = "tbl_project", catalog = "project_task")
public class TblProject implements java.io.Serializable {

    private Integer id;
    private String name;
    private Date creationDate;
    private Set<TblTask> tblTasks = new HashSet<TblTask>(0);

    public TblProject() {
    }

    public TblProject(String name, Date creationDate, Set<TblTask> tblTasks) {
        this.name = name;
        this.creationDate = creationDate;
        this.tblTasks = tblTasks;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)

    @Column(name = "id", unique = true, nullable = false)
    public Integer getId() {
        return this.id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Column(name = "name", length = 45)
    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "creation_date", length = 19)
    public Date getCreationDate() {
        return this.creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "tblProject")
    public Set<TblTask> getTblTasks() {
        return this.tblTasks;
    }

    public void setTblTasks(Set<TblTask> tblTasks) {
        this.tblTasks = tblTasks;
    }
}

My approach is to use native query to handle this

@Query(value = "select * from project_task.tbl_task t join 
project_task.tbl_project p on t.project_id = p.id where p.creation_date> 
DATE_SUB(t.started_date, INTERVAL ?1 DAY);",
nativeQuery = true)// notice this
List<TblTask> findLateTasksUsingQuery(int days); 

From here, you can get the entities and parse them to whatever DTOs that you want. In this case, I make an example like this

public List<TaskDto> findTasks(int days) {
    List<TblTask> tasks = taskRepo.findLateTasksUsingQuery(days);
    return tasks.stream().map(task -> {
        TaskDto dto = new TaskDto();
        dto.setId(task.getId());
        dto.setName(task.getName());
        TblProject project = task.getTblProject();
        dto.setProjectName(project.getName());
        dto.setStartedDate(task.getStartedDate());
        dto.setProjectCreatedDate(project.getCreationDate());
        return dto;
    }).collect(Collectors.toList());
}

and with input days = 10 the result is

[{
        "id": 3,
        "name": "Buy foods",
        "startedDate": "2021-06-16T17:00:00.000+00:00",
        "projectName": "To the moon",
        "projectCreatedDate": "2021-06-11T17:00:00.000+00:00"
    }, {
        "id": 7,
        "name": "Buy a new bed",
        "startedDate": "2021-06-26T17:00:00.000+00:00",
        "projectName": "Sleep all day",
        "projectCreatedDate": "2021-06-26T17:00:00.000+00:00"
    }
]

Explanation

I have refer to this answer and I choose native query

The reason why I choose native query because I want to use the MySQL date_sub function. For Oracle, you may need to refer to this answer

You can also use Java (I mean not native query) to substract dates but in my opinion, it is more complicated.