java - 使用 JPA 避免日期重叠

Question

我有一个具有属性“code”、“fromDate”和“toDate”的实体类，我需要使用 JPA 插入一条新记录，这样给定的代码日期范围不应重叠。

For example 
If code- ABC of date range 01/Feb/2014-10/Feb/2014 exist in DB.
I am inserting code ABC again with date range 
03/Feb/2014-07/Feb/2014 should not accept -  from date and to date is Within existing Date range
28/Jan/2014-02/Feb/2014 should not accept -   to date is Within existing Date range
05/Feb/2014-21/Feb/2014 should not accept -   From date is Within existing Date range
01/Jan/2014-28/Feb/2014 should not accept -   The existing date range is within the given date range so Overlapping will happen.

假设需要插入的数据位于具有类似属性的viewObject中。请帮助我使用 JPA 谓词验证日期重叠

Answer 1

在保存新对象之前，您可以查询数据库以检查是否存在“重叠”记录。如果返回一条记录，则不保存新对象，否则保存；

String query = "SELECT ent FROM Entity ent WHERE ent.fromDate <= :toDate AND ent.toDate >= :fromDate WHERE ent.id = :entId";

List<Entity> overlappingRecords = JPA.em().createQuery(query).setParameter("entId", id).setParameter("fromDate", fromDate).setParameter("toDate", toDate).getResultList();

if(overlappingRecords.isEmpty())
   //Over lap does not exist
else
   //Over lap exists

此查询假设拒绝完全重叠的边。