如何与JPA查询进行时间戳比较？

发布于 2024-08-27 08:12:24 字数 843 浏览 12 评论 0 原文

我们需要确保 JPQL 查询仅返回最近 30 天内的结果。示例如下：

Date now = new Date();
Timestamp thirtyDaysAgo = new Timestamp(now.getTime() - 86400000*30);

Query query = em.createQuery(
  "SELECT msg FROM Message msg "+
  "WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '"+thirtyDaysAgo+"'}");
List result = query.getResultList();

这是我们收到的错误：

<openjpa-1.2.3-SNAPSHOT-r422266:907835 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'SELECT msg FROM BroadcastMessage msg WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '2010-04-18 04:15:37.827'}'. Error message: org.apache.openjpa.kernel.jpql.TokenMgrError: Lexical error at line 1, column 217.  Encountered: "{" (123), after : ""

救命！

原文

We need to make sure only results within the last 30 days are returned for a JPQL query. An example follows:

Date now = new Date();
Timestamp thirtyDaysAgo = new Timestamp(now.getTime() - 86400000*30);

Query query = em.createQuery(
  "SELECT msg FROM Message msg "+
  "WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '"+thirtyDaysAgo+"'}");
List result = query.getResultList();

Here is the error we receive:

<openjpa-1.2.3-SNAPSHOT-r422266:907835 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'SELECT msg FROM BroadcastMessage msg WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '2010-04-18 04:15:37.827'}'. Error message: org.apache.openjpa.kernel.jpql.TokenMgrError: Lexical error at line 1, column 217.  Encountered: "{" (123), after : ""

Help!

分享到QQ

分享到微博

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

发布评论

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

岛徒 2024-09-03 08:12:24

所以你输入的查询不是JPQL（你可以通过参考JPA规范看到）。如果要将字段与日期进行比较，则可以将日期作为参数输入到查询

msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > :param

THIS IS NOT SQL。

So the query you input is not JPQL (which you could see by referring to the JPA spec). If you want to compare a field with a Date then you input the Date as a parameter to the query

msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > :param

THIS IS NOT SQL.

回复收藏 0 原文

甜心 2024-09-03 08:12:24

您使用的 OpenJPA 版本可能不支持 JDBC 转义语法。最新 1.2.x 版本的文档位于：http://openjpa.apache.org/builds/1.2.2/apache-openjpa-1.2.2/docs/manual/manual.html#jpa_langref_lit 。

前面提到的文档引用了 OpenJPA 2.0.0（最新）的文档： http://openjpa.apache.org/builds/latest/docs/manual/jpa_langref.html#jpa_langref_lit

也就是说，您是否有任何理由想要将字符串注入 JPQL 中？像下面的代码片段这样的东西怎么样？

Date now = new Date();
Date thirtyDaysAgo = new Date(now.getTime() - (30 * MS_IN_DAY));

Query q = em.createQuery("Select m from Message m " 
    + "where m.targetTime < :now and m.targetTime > :thirtyDays");
q.setParameter("now", now); 
q.setParameter("thirtyDays", thirtyDaysAgo);

List<Message> results = (List<Message>) q.getResultList();

The JDBC escape syntax may not be supported in the version of OpenJPA that you're using. The documentation for the latest 1.2.x release is here: http://openjpa.apache.org/builds/1.2.2/apache-openjpa-1.2.2/docs/manual/manual.html#jpa_langref_lit .

The documentation mentioned earlier refers to the docs for OpenJPA 2.0.0 (latest): http://openjpa.apache.org/builds/latest/docs/manual/jpa_langref.html#jpa_langref_lit

That said is there any reason why you want to inject a string into your JPQL? What about something like the following snippet?

Date now = new Date();
Date thirtyDaysAgo = new Date(now.getTime() - (30 * MS_IN_DAY));

Query q = em.createQuery("Select m from Message m " 
    + "where m.targetTime < :now and m.targetTime > :thirtyDays");
q.setParameter("now", now); 
q.setParameter("thirtyDays", thirtyDaysAgo);

List<Message> results = (List<Message>) q.getResultList();

回复收藏 0 原文

~没有更多了~