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屋!