HQL/JPQL日期算法

我要编写hql或jpql查询

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

有可能吗？我见过一些answers from 2009，上面写着在休眠方言中注册特定于db的函数。Hibernate/JPA仍然不支持日期运算吗？

---

解决方案

为此，我举了一个这样的示例

使用此选项，我将找到project.creation_date > (task.start_date - 10 days)

我向其中添加了一些数据

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

以下是我的实体

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

我的方法是使用native query来处理

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

从这里，您可以获取实体并将它们解析为您想要的任何DTO。在本例中，我创建了一个这样的示例

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

输入days = 10结果为

[{
        "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"
    }
]

说明

我已引用this answer并选择native query

我之所以选择原生查询，是因为我想使用MySQLdate_subfunction。对于Oracle，您可能需要参考this answer

您也可以使用Java(我的意思不是原生查询)to substract dates但在我看来，它更复杂。