在查询中使用CURRENT_TIMESTAMP()时出错 [英] Error using CURRENT_TIMESTAMP() into a query

问题描述

我想使用当前日期函数在jpql查询中比较两个日期

I want to compare two date in jpql query using the current date function

我遇到了错误

语法错误解析[从d位置d.depid = 1 AND d.typeDossier =:tpd和d.dateCreation<中选择d. CURRENT_TIMESTAMP()+ 5].
[105，107]左表达式不是算术表达式

Syntax error parsing [SELECT d FROM Dossier d WHERE d.depid=1 AND d.typeDossier = :tpd AND d.dateCreation < CURRENT_TIMESTAMP() + 5].
[105, 107] The left expression is not an arithmetic expression

这是我的查询:

public List<Dossier> getDossierFindAllParDepartementDBTECHandUrgen() {
    return (List<Dossier>) em.createQuery("SELECT d FROM Dossier d WHERE d.depid=1 AND d.typeDossier = :tpd AND " +
        "d.dateCreation < CURRENT_TIMESTAMP() + 5",
                                          Dossier.class).setParameter("tpd", "Urgent").getResultList();
}

推荐答案

JPA支持功能CURRENT_TIMESTAMP

https://en.wikibooks.org/wiki/Java_Persistence/JPQL#Functions

，但不适用于算术运算.您可以通过使用参数来解决问题，例如

but will not work with arithmetic operations. You can solve the problem by using a parameter, for example

TypedQuery<Dossier> query = em.createQuery("SELECT d FROM Dossier d WHERE d.depid=1 AND d.typeDossier = :tpd AND " +
        "d.dateCreation < :fiveDaysAhead",
                                          Dossier.class);
Date myFiveDaysAhead = new Date(Calendar.getInstance().add(Calendar.DAYS_OF_YEAR,5).getTimeInMillis());//or something
query.setParameter("tpd", "Urgent");
query.setParameter("fiveDaysAhead", myFiveDaysAhead, TemporalType.TIMESTAMP);

也有可能找到特定于供应商的解决方案，正如我在另一个答案中所注意到的那样 https://stackoverflow.com/a /18514326/2835455

It may also be possible to find vendor specific solutions, as i noticed in one other answer https://stackoverflow.com/a/18514326/2835455

这篇关于在查询中使用CURRENT_TIMESTAMP()时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！