Oracle + JPA-使用INTERVAL查询 [英] Oracle + JPA - querying with INTERVAL

问题描述

我的数据库表LASTUPDATED中有一个Timestamp列，我通过轮询来决定是否应更新行.如果记录在最近10分钟内未更新，我将对其进行更新.我想将日期处理委派给数据库，但以下任何一项均无效:

I have a Timestamp column in my DB table, LASTUPDATED, which I poll to decide if a row should be updated or not. If the record was not updated in the last 10 minutes, I update it. I want to delegate the date handling to the DB, but none of the following works:

这个人说预期的令牌::附近[...]"

    Query query = entityManager.createQuery("SELECT x FROM MyEntity x WHERE x.lastUpdated < SYSTIMESTAMP - INTERVAL :olderThen MINUTE");
    query.setParameter("olderThen", 10);
    list = query.getResultList();

此人说意外令牌:[...]附近的'10'

    Query query = entityManager.createQuery("SELECT x FROM MyEntity x WHERE x.lastUpdated < SYSTIMESTAMP - INTERVAL '10' MINUTE");
    list = query.getResultList();

这个人说意外的记号:'?'靠近[...]

    Query query = entityManager.createQuery("SELECT x FROM MyEntity x WHERE x.lastUpdated < SYSTIMESTAMP - INTERVAL ?1 MINUTE");
    query.setParameter(1, 10);
    list = query.getResultList();

顺便问一下，我可以在JPA上使用此INTERVAL关键字吗?该表达式在语法上是正确的，我在控制台上对其进行了测试.

Can I use this INTERVAL keyword with JPA by the way? The expression is correct syntactically, I tested it with console.

感谢大家的帮助，

推荐答案

JPA和Hibernate不支持间隔，最后我发现了...

JPA and Hibernate does not sopport interval, finally I found it...

日期算术也受支持，尽管以更有限的方式.这部分是由于数据库支持方面的差异，部分是由于查询语言本身缺少对INTERVAL定义的支持. 来源: http://docs.jboss.org /hibernate/orm/4.1/devguide/zh-CN/html/ch11.html

仅可以使用BETWEEN关键字. 保重

Only the BETWEEN keyword can be used. Take care

正确的表达式是:

     select x from MyEntity x where cast((systimestamp - (1/24/60) * 10) as timestamp) between lastUpdated and systimestamp

这篇关于Oracle + JPA-使用INTERVAL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！