问题描述
用户可以根据出现值执行操作.当此值等于DAILY"时,我想检索过去 24 小时内未完成的所有日常操作.
A user can perform actions based on an occurrence value. When this value is equal to 'DAILY', I would like to retrieve all daily actions that have not been completed the last 24 hours.
有效的 SQL 查询:
The working SQL query:
SELECT distinct a.* FROM action as a LEFT OUTER JOIN history as h
ON a.id = h.action_id
AND h.user_id= <user> WHERE a.occurrence = 'DAILY' AND (h.id is NULL OR h.entry_date < TIMESTAMP 'yesterday')
等效的原生查询:
@Query(value =
"SELECT distinct a.* FROM action a "
+ "LEFT OUTER JOIN history h "
+ "ON a.id = h.action_id "
+ "AND h.user_id = :userId "
+ "WHERE a.occurrence='DAILY' AND (h.id IS NULL OR h.entry_date < :yesterday) ", nativeQuery = true)
public List<Action> findAllAvailableActions(@Param("userId") Long userId, @Param("yesterday") ZonedDateTime yesterday);
如何在我的服务中调用它:
How it is called in my service :
ZonedDateTime today = ZonedDateTime.now(ZoneOffset.UTC);
ZonedDateTime yesterday = today.minus(1,ChronoUnit.DAYS);
Long userId = userDTO.getId();
List<Action> result = actionRepositoryCustom.findAllAvailableActions(userId, yesterday);
但是,我在测试中确实得到了错误的结果(返回已完成的操作).恐怕这与日期参数有关.属性 entry_date 在我的实体中声明为 ZoneDateTime.我做错了什么?
However, I do get the wrong results in my tests (actions that have already been completed are returned). I am afraid this is linked to the date parameter. The attribute entry_date is declared as ZoneDateTime in my entity. What am I doing wrong ?
休眠:5.2.4
推荐答案
您不能将 ZonedDateTime 传递到本机 SQL 查询中.您需要将其转换为日历:
You can't pass a ZonedDateTime into a native SQL query. You need to convert it to Calendar:
@Query(value =
"SELECT distinct a.* FROM action a "
+ "LEFT OUTER JOIN history h "
+ "ON a.id = h.action_id "
+ "AND h.user_id = :userId "
+ "WHERE a.occurrence='DAILY' AND (h.id IS NULL OR h.entry_date < :yesterday)", nativeQuery = true)
public List<Action> findAllAvailableActions(@Param("userId") Long userId, @Param("yesterday") Calendar yesterday);
您可以通过这种方式转换 ZonedDateTime:
And you can convert your ZonedDateTime this way:
public Calendar convertToDatabaseColumn(ZonedDateTime entityAttribute) {
if (entityAttribute == null) {
return null;
}
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(entityAttribute.toInstant().toEpochMilli());
calendar.setTimeZone(TimeZone.getTimeZone(entityAttribute.getZone()));
return calendar;
}
这里描述了这种方法:链接
这篇关于将日期参数传递给本机查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!