使用 JPA 比较两个日期

发布于 2024-08-02 18:44:58 字数 478 浏览 8 评论 0原文

我需要比较 JPQL 查询中的两个日期，但它不起作用。

这是我的查询：

Query query = em.createQuery(
    "SELECT h 
     FROM PositionHistoric h, 
          SeoDate d 
     WHERE h.primaryKey.siteDb = :site 
     AND h.primaryKey.engineDb = :engine 
     AND h.primaryKey.keywordDb = :keyword 
     AND h.date = d 
     AND d.date <= :date 
     ORDER BY h.date DESC");`

我的参数日期是 java.util.Date

我的查询返回一个对象列表，但日期是我的参数的上限和下限。

有人知道该怎么做吗？

谢谢。

原文

I need to compare two dates in a JPQL query but it doesn't work.

Here is my query:

Query query = em.createQuery(
    "SELECT h 
     FROM PositionHistoric h, 
          SeoDate d 
     WHERE h.primaryKey.siteDb = :site 
     AND h.primaryKey.engineDb = :engine 
     AND h.primaryKey.keywordDb = :keyword 
     AND h.date = d 
     AND d.date <= :date 
     ORDER BY h.date DESC");`

My parameter date is a java.util.Date

My query return a objects list but the dates are upper and lower to my parameter.

Someone kown how to do this ?

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

往事随风而去 2024-08-09 18:44:58

您应该使用的查询是：

 FROM PositionHistoric h
   INNER JOIN FETCH h.date
WHERE h.primaryKey.siteDb = :site
  AND h.primaryKey.engineDb = :engine
  AND h.primaryKey.keywordDb = :keyword
  AND h.date.date <= :date

请注意，按 h.date 排序是毫无意义的（因为它指向 SeoDate 实体，因此实际上您是按 PK 排序）并且您不能按 < code>h.date.date 因为 SeoDate 映射为多对一。

另请注意，INNER JOIN FETCH h.date 行本身并不是必需的，但如果您实际上需要在返回的列表中使用 SeoDate 属性，它将为您节省一些额外的惰性查询开销。

The query you should use is:

 FROM PositionHistoric h
   INNER JOIN FETCH h.date
WHERE h.primaryKey.siteDb = :site
  AND h.primaryKey.engineDb = :engine
  AND h.primaryKey.keywordDb = :keyword
  AND h.date.date <= :date

Note that ordering by h.date is rather pointless (as it points to SeoDate entity and thus in effect you're ordering by PK) and you can't order by h.date.date since SeoDate is mapped as many-to-one.

Also note that INNER JOIN FETCH h.date line is not necessary per se but it will save you some overhead on additional lazy queries IF you will actually need to use SeoDate attributes in the returned list.

回复收藏 0 原文