HQL/JPA找到日期范围内的可用项目

Question

这是汽车租赁应用程序。 我有表：

+-------------------------------------+ 
|id |start_date |end_date |car_id| 
+----+------------+------------+------+ 
|1 |2017-04-01 |2017-04-10 |1  | 
|2 |2017-04-15 |2017-04-20 |1  | 
|3 |2017-04-15 |2017-04-20 |2  | 
+----+------------+------------+------+ 

用于查找用户输入“userDateStart”和“userDateEnd”之间的所有可用汽车的Hibernate查询。

select 
b.id, b.startDate, b.endDate, b.car.id 
from BorrowedDate as b 
where :userDateStart not between startDate and endDate 
and :userDateEnd not between startDate and endDate 

------------ 
:userDateStart = 2017-04-02 00:00:00 
:userDateEnd = 2017-04-10 00:00:00 

我的结果是：因为车ID = 1这个时间是借来的

+-------------------------------------+ 
|id |start_date |end_date |car_id| 
+----+------------+------------+------+ 
|2 |2017-04-15 |2017-04-20 |1  | 
|3 |2017-04-15 |2017-04-20 |2  | 
+----+------------+------------+------+ 

结果是错误的。只有car_id = 2应该可用。

Answer 1

我看到您的查询是正确的。

但是，如果你要根据车辆ID来选择，所以你应该添加车ID作为条件太到你的查询

例如

SELECT b.id, b.startDate, b.endDate, b.car.id 
FROM BorrowedDate b 
WHERE :userDateStart NOT BETWEEN b.startDate AND b.endDate 
AND :userDateEnd NOT BETWEEN b.startDate AND b.endDate 
AND b.car.id = :carId 

或者你可以那样做太

SELECT b.id, b.startDate, b.endDate, c.id 
FROM BorrowedDate b INNER JOIN b.car c 
WHERE :userDateStart NOT BETWEEN b.startDate AND b.endDate 
AND :userDateEnd NOT BETWEEN b.startDate AND b.endDate 
AND c.id = :carId 

，那么你必须通过：carId值等于2

UPDATE

，如果你需要得到结果的动态，你可以试试这个SQL查询，我会很快与JPQL更新

SELECT b.id, b.startDate, b.endDate, b.car.id 
FROM BorrowedDate b 
WHERE '2017-04-02 00:00:00' NOT BETWEEN b.startDate AND b.endDate 
AND '2017-04-10 00:00:00' NOT BETWEEN b.startDate AND b.endDate 
AND b.car.id NOT IN (SELECT DISTINCT bd.car.id FROM BorrowedDate bd WHERE '2017-04-02 00:00:00' BETWEEN bd.startDate AND bd.endDate OR '2017-04-10 00:00:00' BETWEEN bd.startDate AND bd.endDate) 

这里有JPQL太

SELECT model.id, model.startDate, model.endDate, model.car.id 
FROM BorrowedDate model 
WHERE :userDateStart NOT BETWEEN model.startDate AND model.endDate 
AND :userDateEnd NOT BETWEEN model.startDate AND model.endDate 
AND model.car.id NOT IN (SELECT DISTINCT b.car.id FROM BorrowedDate b WHERE :userDateStart BETWEEN b.startDate AND b.endDate OR :userDateEnd BETWEEN b.startDate AND b.endDate) 

Answer 2

我认为你正在寻找一个完全重叠的时期。这是完全重叠正确的逻辑：

select b.id, b.startDate, b.endDate, b.car.id 
from BorrowedDate as b 
where :userDateStart <= startDate and 
     :userDateEnd >= endDate; 

所以，这可以让你所租用的客户希望一个整个周期的汽车。这可能不是你的意图。

让我假设你想要的汽车在此期间可用。对于这一点，让我假设你有一个cars表：

select c.* 
from cars as c 
where not exists (select 1 
        from borrowed b 
        where :userDateStart <= endDate and 
         :userDateEnd >= startDate 
       ); 

注：这是标准的（ISH）SQL。 Hibernate有一些不寻常的约定，但这个想法应该是一样的。