亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

本文介紹了HQL/JPQL日期算法的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！

問(wèn)題描述

我要編寫(xiě)hql或jpql查詢

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

有可能嗎？我見(jiàn)過(guò)一些answers from 2009，上面寫(xiě)著在休眠方言中注冊(cè)特定于db的函數(shù)。Hibernate/JPA仍然不支持日期運(yùn)算嗎？

推薦答案

為此，我舉了一個(gè)這樣的示例

使用此選項(xiàng)，我將找到project.creation_date > (task.start_date - 10 days)

我向其中添加了一些數(shù)據(jù)

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

以下是我的實(shí)體

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來(lái)處理

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

從這里，您可以獲取實(shí)體并將它們解析為您想要的任何DTO。在本例中，我創(chuàng)建了一個(gè)這樣的示例

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結(jié)果為

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

說(shuō)明

我已引用this answer并選擇native query

我之所以選擇原生查詢，是因?yàn)槲蚁胧褂肕ySQLdate_subfunction。對(duì)于Oracle，您可能需要參考this answer

您也可以使用Java(我的意思不是原生查詢)to substract dates但在我看來(lái)，它更復(fù)雜。

這篇關(guān)于HQL/JPQL日期算法的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，