HQL / JPA在日期范围内查找可用项目 [英] HQL / JPA find available items between date range

问题描述

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

  + ----------------- -------------------- + 
 | 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 | 
 + ---- + ------------ + ------------ + ------ + 
  

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

 选择
 b.id，b.startDate，b.endDate，b.car.id 
从BorrowedDate作为b 
其中： userDateStart不在startDate和endDate之间
和：userDateEnd不在startDate和endDate之间
 
 ------------ 
：userDateStart = 2017-04-02 00:00:00 
：userDateEnd = 2017-04-10 00:00:00 
  

我的结果是：

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

结果是错误的，因为id = 1的汽车在这段时间被借用。只有car_id = 2应该可用。

解决方案

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

但是，如果你想选择基于汽车id，所以你应该添加汽车id作为一个条件也纳入您的查询

例如

  SELECT b.id，b.startDate，b.endDate，b.car.id 
 FROM BorrowedDate b 
 WHERE： userDateStart NOT BETWEEN b.startDate AND b.endDate 
和：userDateEnd NOT BETWEEN b.startDate AND b.endDate 
和b.car.id =：carId 
  
 
 
 
或者你也可以这样做
  SELECT b.id，b.startDate，b.endDate，c.id 
。从BorrowedDate b INNER JOIN b.car C 
其中：userDateStart NOT BETWEEN b.startDate和b.endDate 
和：userDateEnd NOT BETWEEN b.startDate AND b.endDate 
 AND c.id =：carId 
  
然后你必须通过：carId值等于2 
 
 
 
 更新  
 
 
可以试试这个SQL q如果你需要获得动态结果，我会尽快用JPQL更新它。
 .endDate，b.car.id 
 FROM BorrowedDate b 
在哪里'2017-04-02 00:00:00'不在BETWEEN b.startDate和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'bd.startDate AND bd.endDate OR'2017-04-10 00:00:00'BETWEEN bd.startDate AND bd.endDate）
  

这里也包含 JPQL

  SELECT模型。 ID，model.startDate，model.endDate，model.car.id 
 FROM BorrowedDate模型
 WHERE：userDateStart NOT BETWEEN model.startDate和model.endDate 
和：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 B ETWEEN b.startDate AND b.endDate）
  

It's car rental app. I have table:

+-------------------------------------+
|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     |
+----+------------+------------+------+ 

Hibernate query for find all available cars between user input 'userDateStart' and 'userDateEnd'.

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

My result is:

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

Result is wrong because car with id=1 is borrowed in this time. Only car_id=2 should be available.

解决方案

I see your query is correct.

But if you want to select based on car id so you should add car id as a condition too into your query

For example

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

OR you can do it like that too

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

then you have to pass :carId value equals 2

UPDATE

you can try this SQL query if you need to get result dynamic, i will update it soon with 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) 

HERE are JPQL too

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)

这篇关于HQL / JPA在日期范围内查找可用项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！